1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
|
--!syntax_pg
--TPC-DS Q71
-- start query 1 in stream 0 using template ../query_templates/query71.tpl
select i_brand_id brand_id, i_brand brand,t_hour,t_minute,
sum(ext_price) ext_price
from plato.item, (select ws_ext_sales_price as ext_price,
ws_sold_date_sk as sold_date_sk,
ws_item_sk as sold_item_sk,
ws_sold_time_sk as time_sk
from plato.web_sales,plato.date_dim
where d_date_sk = ws_sold_date_sk
and d_moy=12
and d_year=2000
union all
select cs_ext_sales_price as ext_price,
cs_sold_date_sk as sold_date_sk,
cs_item_sk as sold_item_sk,
cs_sold_time_sk as time_sk
from plato.catalog_sales,plato.date_dim
where d_date_sk = cs_sold_date_sk
and d_moy=12
and d_year=2000
union all
select ss_ext_sales_price as ext_price,
ss_sold_date_sk as sold_date_sk,
ss_item_sk as sold_item_sk,
ss_sold_time_sk as time_sk
from plato.store_sales,plato.date_dim
where d_date_sk = ss_sold_date_sk
and d_moy=12
and d_year=2000
) tmp,plato.time_dim
where
sold_item_sk = i_item_sk
and i_manager_id=1
and time_sk = t_time_sk
and (t_meal_time = 'breakfast' or t_meal_time = 'dinner')
group by i_brand, i_brand_id,t_hour,t_minute
order by ext_price desc, i_brand_id
;
-- end query 1 in stream 0 using template ../query_templates/query71.tpl
|
