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
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
|
--!syntax_pg
--TPC-DS Q58
-- start query 1 in stream 0 using template ../query_templates/query58.tpl
with ss_items as
(select i_item_id item_id
,sum(ss_ext_sales_price) ss_item_rev
from plato.store_sales
,plato.item
,plato.date_dim
where ss_item_sk = i_item_sk
and d_date in (select d_date
from plato.date_dim
where d_week_seq = (select d_week_seq
from plato.date_dim
where d_date = '1998-02-19'::date))
and ss_sold_date_sk = d_date_sk
group by i_item_id),
cs_items as
(select i_item_id item_id
,sum(cs_ext_sales_price) cs_item_rev
from plato.catalog_sales
,plato.item
,plato.date_dim
where cs_item_sk = i_item_sk
and d_date in (select d_date
from plato.date_dim
where d_week_seq = (select d_week_seq
from plato.date_dim
where d_date = '1998-02-19'::date))
and cs_sold_date_sk = d_date_sk
group by i_item_id),
ws_items as
(select i_item_id item_id
,sum(ws_ext_sales_price) ws_item_rev
from plato.web_sales
,plato.item
,plato.date_dim
where ws_item_sk = i_item_sk
and d_date in (select d_date
from plato.date_dim
where d_week_seq =(select d_week_seq
from plato.date_dim
where d_date = '1998-02-19'::date))
and ws_sold_date_sk = d_date_sk
group by i_item_id)
select ss_items.item_id
,ss_item_rev
,ss_item_rev/((ss_item_rev+cs_item_rev+ws_item_rev)/3::numeric) * 100::numeric ss_dev
,cs_item_rev
,cs_item_rev/((ss_item_rev+cs_item_rev+ws_item_rev)/3::numeric) * 100::numeric cs_dev
,ws_item_rev
,ws_item_rev/((ss_item_rev+cs_item_rev+ws_item_rev)/3::numeric) * 100::numeric ws_dev
,(ss_item_rev+cs_item_rev+ws_item_rev)/3::numeric average
from ss_items,cs_items,ws_items
where ss_items.item_id=cs_items.item_id
and ss_items.item_id=ws_items.item_id
and ss_item_rev between 0.9::numeric * cs_item_rev and 1.1::numeric * cs_item_rev
and ss_item_rev between 0.9::numeric * ws_item_rev and 1.1::numeric * ws_item_rev
and cs_item_rev between 0.9::numeric * ss_item_rev and 1.1::numeric * ss_item_rev
and cs_item_rev between 0.9::numeric * ws_item_rev and 1.1::numeric * ws_item_rev
and ws_item_rev between 0.9::numeric * ss_item_rev and 1.1::numeric * ss_item_rev
and ws_item_rev between 0.9::numeric * cs_item_rev and 1.1::numeric * cs_item_rev
order by item_id
,ss_item_rev
limit 100;
-- end query 1 in stream 0 using template ../query_templates/query58.tpl
|
