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
69
70
|
--!syntax_pg
--TPC-DS Q83
-- start query 1 in stream 0 using template ../query_templates/query83.tpl
with sr_items as
(select i_item_id item_id,
sum(sr_return_quantity) sr_item_qty
from plato.store_returns,
plato.item,
plato.date_dim
where sr_item_sk = i_item_sk
and d_date in
(select d_date
from plato.date_dim
where d_week_seq in
(select d_week_seq
from plato.date_dim
where d_date in ('1998-01-02'::date,'1998-10-15'::date,'1998-11-10'::date)))
and sr_returned_date_sk = d_date_sk
group by i_item_id),
cr_items as
(select i_item_id item_id,
sum(cr_return_quantity) cr_item_qty
from plato.catalog_returns,
plato.item,
plato.date_dim
where cr_item_sk = i_item_sk
and d_date in
(select d_date
from plato.date_dim
where d_week_seq in
(select d_week_seq
from plato.date_dim
where d_date in ('1998-01-02'::date,'1998-10-15'::date,'1998-11-10'::date)))
and cr_returned_date_sk = d_date_sk
group by i_item_id),
wr_items as
(select i_item_id item_id,
sum(wr_return_quantity) wr_item_qty
from plato.web_returns,
plato.item,
plato.date_dim
where wr_item_sk = i_item_sk
and d_date in
(select d_date
from plato.date_dim
where d_week_seq in
(select d_week_seq
from plato.date_dim
where d_date in ('1998-01-02'::date,'1998-10-15'::date,'1998-11-10'::date)))
and wr_returned_date_sk = d_date_sk
group by i_item_id)
select sr_items.item_id
,sr_item_qty
,sr_item_qty::numeric/(sr_item_qty+cr_item_qty+wr_item_qty)::numeric/3.0::numeric * 100::numeric sr_dev
,cr_item_qty
,cr_item_qty::numeric/(sr_item_qty+cr_item_qty+wr_item_qty)::numeric/3.0::numeric * 100::numeric cr_dev
,wr_item_qty
,wr_item_qty::numeric/(sr_item_qty+cr_item_qty+wr_item_qty)::numeric/3.0::numeric * 100::numeric wr_dev
,(sr_item_qty+cr_item_qty+wr_item_qty)::numeric/3.0::numeric average
from sr_items
,cr_items
,wr_items
where sr_items.item_id=cr_items.item_id
and sr_items.item_id=wr_items.item_id
order by sr_items.item_id
,sr_item_qty
limit 100;
-- end query 1 in stream 0 using template ../query_templates/query83.tpl
|
