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

   1 -- start query 1 in stream 0 using template query66.tpl and seed 2042478054
   2 select
   3          w_warehouse_name
   4         ,w_warehouse_sq_ft
   5         ,w_city
   6         ,w_county
   7         ,w_state
   8         ,w_country
   9         ,ship_carriers
  10         ,year
  11         ,sum(jan_sales) as jan_sales
  12         ,sum(feb_sales) as feb_sales
  13         ,sum(mar_sales) as mar_sales
  14         ,sum(apr_sales) as apr_sales
  15         ,sum(may_sales) as may_sales
  16         ,sum(jun_sales) as jun_sales
  17         ,sum(jul_sales) as jul_sales
  18         ,sum(aug_sales) as aug_sales
  19         ,sum(sep_sales) as sep_sales
  20         ,sum(oct_sales) as oct_sales
  21         ,sum(nov_sales) as nov_sales
  22         ,sum(dec_sales) as dec_sales
  23         ,sum(jan_sales/w_warehouse_sq_ft) as jan_sales_per_sq_foot
  24         ,sum(feb_sales/w_warehouse_sq_ft) as feb_sales_per_sq_foot
  25         ,sum(mar_sales/w_warehouse_sq_ft) as mar_sales_per_sq_foot
  26         ,sum(apr_sales/w_warehouse_sq_ft) as apr_sales_per_sq_foot
  27         ,sum(may_sales/w_warehouse_sq_ft) as may_sales_per_sq_foot
  28         ,sum(jun_sales/w_warehouse_sq_ft) as jun_sales_per_sq_foot
  29         ,sum(jul_sales/w_warehouse_sq_ft) as jul_sales_per_sq_foot
  30         ,sum(aug_sales/w_warehouse_sq_ft) as aug_sales_per_sq_foot
  31         ,sum(sep_sales/w_warehouse_sq_ft) as sep_sales_per_sq_foot
  32         ,sum(oct_sales/w_warehouse_sq_ft) as oct_sales_per_sq_foot
  33         ,sum(nov_sales/w_warehouse_sq_ft) as nov_sales_per_sq_foot
  34         ,sum(dec_sales/w_warehouse_sq_ft) as dec_sales_per_sq_foot
  35         ,sum(jan_net) as jan_net
  36         ,sum(feb_net) as feb_net
  37         ,sum(mar_net) as mar_net
  38         ,sum(apr_net) as apr_net
  39         ,sum(may_net) as may_net
  40         ,sum(jun_net) as jun_net
  41         ,sum(jul_net) as jul_net
  42         ,sum(aug_net) as aug_net
  43         ,sum(sep_net) as sep_net
  44         ,sum(oct_net) as oct_net
  45         ,sum(nov_net) as nov_net
  46         ,sum(dec_net) as dec_net
  47  from (
  48      select
  49         w_warehouse_name
  50         ,w_warehouse_sq_ft
  51         ,w_city
  52         ,w_county
  53         ,w_state
  54         ,w_country
  55         ,'MSC' || ',' || 'GERMA' as ship_carriers
  56        ,d_year as year
  57         ,sum(case when d_moy = 1
  58                 then ws_sales_price* ws_quantity else 0 end) as jan_sales
  59         ,sum(case when d_moy = 2
  60                 then ws_sales_price* ws_quantity else 0 end) as feb_sales
  61         ,sum(case when d_moy = 3
  62                 then ws_sales_price* ws_quantity else 0 end) as mar_sales
  63         ,sum(case when d_moy = 4
  64                 then ws_sales_price* ws_quantity else 0 end) as apr_sales
  65         ,sum(case when d_moy = 5
  66                 then ws_sales_price* ws_quantity else 0 end) as may_sales
  67         ,sum(case when d_moy = 6
  68                 then ws_sales_price* ws_quantity else 0 end) as jun_sales
  69         ,sum(case when d_moy = 7
  70                 then ws_sales_price* ws_quantity else 0 end) as jul_sales
  71         ,sum(case when d_moy = 8
  72                 then ws_sales_price* ws_quantity else 0 end) as aug_sales
  73         ,sum(case when d_moy = 9
  74                 then ws_sales_price* ws_quantity else 0 end) as sep_sales
  75         ,sum(case when d_moy = 10
  76                 then ws_sales_price* ws_quantity else 0 end) as oct_sales
  77         ,sum(case when d_moy = 11
  78                 then ws_sales_price* ws_quantity else 0 end) as nov_sales
  79         ,sum(case when d_moy = 12
  80                 then ws_sales_price* ws_quantity else 0 end) as dec_sales
  81         ,sum(case when d_moy = 1
  82                 then ws_net_paid_inc_ship_tax * ws_quantity else 0 end) as jan_net
  83         ,sum(case when d_moy = 2
  84                 then ws_net_paid_inc_ship_tax * ws_quantity else 0 end) as feb_net
  85         ,sum(case when d_moy = 3
  86                 then ws_net_paid_inc_ship_tax * ws_quantity else 0 end) as mar_net
  87         ,sum(case when d_moy = 4
  88                 then ws_net_paid_inc_ship_tax * ws_quantity else 0 end) as apr_net
  89         ,sum(case when d_moy = 5
  90                 then ws_net_paid_inc_ship_tax * ws_quantity else 0 end) as may_net
  91         ,sum(case when d_moy = 6
  92                 then ws_net_paid_inc_ship_tax * ws_quantity else 0 end) as jun_net
  93         ,sum(case when d_moy = 7
  94                 then ws_net_paid_inc_ship_tax * ws_quantity else 0 end) as jul_net
  95         ,sum(case when d_moy = 8
  96                 then ws_net_paid_inc_ship_tax * ws_quantity else 0 end) as aug_net
  97         ,sum(case when d_moy = 9
  98                 then ws_net_paid_inc_ship_tax * ws_quantity else 0 end) as sep_net
  99         ,sum(case when d_moy = 10
 100                 then ws_net_paid_inc_ship_tax * ws_quantity else 0 end) as oct_net
 101         ,sum(case when d_moy = 11
 102                 then ws_net_paid_inc_ship_tax * ws_quantity else 0 end) as nov_net
 103         ,sum(case when d_moy = 12
 104                 then ws_net_paid_inc_ship_tax * ws_quantity else 0 end) as dec_net
 105      from
 106           web_sales
 107          ,warehouse
 108          ,date_dim
 109          ,time_dim
 110           ,ship_mode
 111      where
 112             ws_warehouse_sk =  w_warehouse_sk
 113         and ws_sold_date_sk = d_date_sk
 114         and ws_sold_time_sk = t_time_sk
 115         and ws_ship_mode_sk = sm_ship_mode_sk
 116         and d_year = 2001
 117         and t_time between 9453 and 9453+28800
 118         and sm_carrier in ('MSC','GERMA')
 119      group by
 120         w_warehouse_name
 121         ,w_warehouse_sq_ft
 122         ,w_city
 123         ,w_county
 124         ,w_state
 125         ,w_country
 126        ,d_year
 127  union all
 128      select
 129         w_warehouse_name
 130         ,w_warehouse_sq_ft
 131         ,w_city
 132         ,w_county
 133         ,w_state
 134         ,w_country
 135         ,'MSC' || ',' || 'GERMA' as ship_carriers
 136        ,d_year as year
 137         ,sum(case when d_moy = 1
 138                 then cs_ext_list_price* cs_quantity else 0 end) as jan_sales
 139         ,sum(case when d_moy = 2
 140                 then cs_ext_list_price* cs_quantity else 0 end) as feb_sales
 141         ,sum(case when d_moy = 3
 142                 then cs_ext_list_price* cs_quantity else 0 end) as mar_sales
 143         ,sum(case when d_moy = 4
 144                 then cs_ext_list_price* cs_quantity else 0 end) as apr_sales
 145         ,sum(case when d_moy = 5
 146                 then cs_ext_list_price* cs_quantity else 0 end) as may_sales
 147         ,sum(case when d_moy = 6
 148                 then cs_ext_list_price* cs_quantity else 0 end) as jun_sales
 149         ,sum(case when d_moy = 7
 150                 then cs_ext_list_price* cs_quantity else 0 end) as jul_sales
 151         ,sum(case when d_moy = 8
 152                 then cs_ext_list_price* cs_quantity else 0 end) as aug_sales
 153         ,sum(case when d_moy = 9
 154                 then cs_ext_list_price* cs_quantity else 0 end) as sep_sales
 155         ,sum(case when d_moy = 10
 156                 then cs_ext_list_price* cs_quantity else 0 end) as oct_sales
 157         ,sum(case when d_moy = 11
 158                 then cs_ext_list_price* cs_quantity else 0 end) as nov_sales
 159         ,sum(case when d_moy = 12
 160                 then cs_ext_list_price* cs_quantity else 0 end) as dec_sales
 161         ,sum(case when d_moy = 1
 162                 then cs_net_paid_inc_ship * cs_quantity else 0 end) as jan_net
 163         ,sum(case when d_moy = 2
 164                 then cs_net_paid_inc_ship * cs_quantity else 0 end) as feb_net
 165         ,sum(case when d_moy = 3
 166                 then cs_net_paid_inc_ship * cs_quantity else 0 end) as mar_net
 167         ,sum(case when d_moy = 4
 168                 then cs_net_paid_inc_ship * cs_quantity else 0 end) as apr_net
 169         ,sum(case when d_moy = 5
 170                 then cs_net_paid_inc_ship * cs_quantity else 0 end) as may_net
 171         ,sum(case when d_moy = 6
 172                 then cs_net_paid_inc_ship * cs_quantity else 0 end) as jun_net
 173         ,sum(case when d_moy = 7
 174                 then cs_net_paid_inc_ship * cs_quantity else 0 end) as jul_net
 175         ,sum(case when d_moy = 8
 176                 then cs_net_paid_inc_ship * cs_quantity else 0 end) as aug_net
 177         ,sum(case when d_moy = 9
 178                 then cs_net_paid_inc_ship * cs_quantity else 0 end) as sep_net
 179         ,sum(case when d_moy = 10
 180                 then cs_net_paid_inc_ship * cs_quantity else 0 end) as oct_net
 181         ,sum(case when d_moy = 11
 182                 then cs_net_paid_inc_ship * cs_quantity else 0 end) as nov_net
 183         ,sum(case when d_moy = 12
 184                 then cs_net_paid_inc_ship * cs_quantity else 0 end) as dec_net
 185      from
 186           catalog_sales
 187          ,warehouse
 188          ,date_dim
 189          ,time_dim
 190          ,ship_mode
 191      where
 192             cs_warehouse_sk =  w_warehouse_sk
 193         and cs_sold_date_sk = d_date_sk
 194         and cs_sold_time_sk = t_time_sk
 195         and cs_ship_mode_sk = sm_ship_mode_sk
 196         and d_year = 2001
 197         and t_time between 9453 AND 9453+28800
 198         and sm_carrier in ('MSC','GERMA')
 199      group by
 200         w_warehouse_name
 201         ,w_warehouse_sq_ft
 202         ,w_city
 203         ,w_county
 204         ,w_state
 205         ,w_country
 206        ,d_year
 207  ) x
 208  group by
 209         w_warehouse_name
 210         ,w_warehouse_sq_ft
 211         ,w_city
 212         ,w_county
 213         ,w_state
 214         ,w_country
 215         ,ship_carriers
 216        ,year
 217  order by w_warehouse_name
 218  limit 100;
 219
 220 -- end query 1 in stream 0 using template query66.tpl