ceph/src/s3select/TPCDS/sample-queries-tpcds/query88.sql

   1 -- start query 1 in stream 0 using template query88.tpl and seed 318176889
   2 select  *
   3 from
   4  (select count(*) h8_30_to_9
   5  from store_sales, household_demographics , time_dim, store
   6  where ss_sold_time_sk = time_dim.t_time_sk
   7      and ss_hdemo_sk = household_demographics.hd_demo_sk
   8      and ss_store_sk = s_store_sk
   9      and time_dim.t_hour = 8
  10      and time_dim.t_minute >= 30
  11      and ((household_demographics.hd_dep_count = 2 and household_demographics.hd_vehicle_count<=2+2) or
  12           (household_demographics.hd_dep_count = 4 and household_demographics.hd_vehicle_count<=4+2) or
  13           (household_demographics.hd_dep_count = 3 and household_demographics.hd_vehicle_count<=3+2))
  14      and store.s_store_name = 'ese') s1,
  15  (select count(*) h9_to_9_30
  16  from store_sales, household_demographics , time_dim, store
  17  where ss_sold_time_sk = time_dim.t_time_sk
  18      and ss_hdemo_sk = household_demographics.hd_demo_sk
  19      and ss_store_sk = s_store_sk
  20      and time_dim.t_hour = 9
  21      and time_dim.t_minute < 30
  22      and ((household_demographics.hd_dep_count = 2 and household_demographics.hd_vehicle_count<=2+2) or
  23           (household_demographics.hd_dep_count = 4 and household_demographics.hd_vehicle_count<=4+2) or
  24           (household_demographics.hd_dep_count = 3 and household_demographics.hd_vehicle_count<=3+2))
  25      and store.s_store_name = 'ese') s2,
  26  (select count(*) h9_30_to_10
  27  from store_sales, household_demographics , time_dim, store
  28  where ss_sold_time_sk = time_dim.t_time_sk
  29      and ss_hdemo_sk = household_demographics.hd_demo_sk
  30      and ss_store_sk = s_store_sk
  31      and time_dim.t_hour = 9
  32      and time_dim.t_minute >= 30
  33      and ((household_demographics.hd_dep_count = 2 and household_demographics.hd_vehicle_count<=2+2) or
  34           (household_demographics.hd_dep_count = 4 and household_demographics.hd_vehicle_count<=4+2) or
  35           (household_demographics.hd_dep_count = 3 and household_demographics.hd_vehicle_count<=3+2))
  36      and store.s_store_name = 'ese') s3,
  37  (select count(*) h10_to_10_30
  38  from store_sales, household_demographics , time_dim, store
  39  where ss_sold_time_sk = time_dim.t_time_sk
  40      and ss_hdemo_sk = household_demographics.hd_demo_sk
  41      and ss_store_sk = s_store_sk
  42      and time_dim.t_hour = 10
  43      and time_dim.t_minute < 30
  44      and ((household_demographics.hd_dep_count = 2 and household_demographics.hd_vehicle_count<=2+2) or
  45           (household_demographics.hd_dep_count = 4 and household_demographics.hd_vehicle_count<=4+2) or
  46           (household_demographics.hd_dep_count = 3 and household_demographics.hd_vehicle_count<=3+2))
  47      and store.s_store_name = 'ese') s4,
  48  (select count(*) h10_30_to_11
  49  from store_sales, household_demographics , time_dim, store
  50  where ss_sold_time_sk = time_dim.t_time_sk
  51      and ss_hdemo_sk = household_demographics.hd_demo_sk
  52      and ss_store_sk = s_store_sk
  53      and time_dim.t_hour = 10
  54      and time_dim.t_minute >= 30
  55      and ((household_demographics.hd_dep_count = 2 and household_demographics.hd_vehicle_count<=2+2) or
  56           (household_demographics.hd_dep_count = 4 and household_demographics.hd_vehicle_count<=4+2) or
  57           (household_demographics.hd_dep_count = 3 and household_demographics.hd_vehicle_count<=3+2))
  58      and store.s_store_name = 'ese') s5,
  59  (select count(*) h11_to_11_30
  60  from store_sales, household_demographics , time_dim, store
  61  where ss_sold_time_sk = time_dim.t_time_sk
  62      and ss_hdemo_sk = household_demographics.hd_demo_sk
  63      and ss_store_sk = s_store_sk
  64      and time_dim.t_hour = 11
  65      and time_dim.t_minute < 30
  66      and ((household_demographics.hd_dep_count = 2 and household_demographics.hd_vehicle_count<=2+2) or
  67           (household_demographics.hd_dep_count = 4 and household_demographics.hd_vehicle_count<=4+2) or
  68           (household_demographics.hd_dep_count = 3 and household_demographics.hd_vehicle_count<=3+2))
  69      and store.s_store_name = 'ese') s6,
  70  (select count(*) h11_30_to_12
  71  from store_sales, household_demographics , time_dim, store
  72  where ss_sold_time_sk = time_dim.t_time_sk
  73      and ss_hdemo_sk = household_demographics.hd_demo_sk
  74      and ss_store_sk = s_store_sk
  75      and time_dim.t_hour = 11
  76      and time_dim.t_minute >= 30
  77      and ((household_demographics.hd_dep_count = 2 and household_demographics.hd_vehicle_count<=2+2) or
  78           (household_demographics.hd_dep_count = 4 and household_demographics.hd_vehicle_count<=4+2) or
  79           (household_demographics.hd_dep_count = 3 and household_demographics.hd_vehicle_count<=3+2))
  80      and store.s_store_name = 'ese') s7,
  81  (select count(*) h12_to_12_30
  82  from store_sales, household_demographics , time_dim, store
  83  where ss_sold_time_sk = time_dim.t_time_sk
  84      and ss_hdemo_sk = household_demographics.hd_demo_sk
  85      and ss_store_sk = s_store_sk
  86      and time_dim.t_hour = 12
  87      and time_dim.t_minute < 30
  88      and ((household_demographics.hd_dep_count = 2 and household_demographics.hd_vehicle_count<=2+2) or
  89           (household_demographics.hd_dep_count = 4 and household_demographics.hd_vehicle_count<=4+2) or
  90           (household_demographics.hd_dep_count = 3 and household_demographics.hd_vehicle_count<=3+2))
  91      and store.s_store_name = 'ese') s8
  92 ;
  93
  94 -- end query 1 in stream 0 using template query88.tpl