blob: 0d44f7482b7e3dcba140bca7e9fb63039ed068f5 (
plain) (
blame)
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
|
--!syntax_pg
--TPC-DS Q86
-- start query 1 in stream 0 using template ../query_templates/query86.tpl
select
sum(ws_net_paid) as total_sum
,i_category
,i_class
,grouping(i_category)+grouping(i_class) as lochierarchy
,rank() over (
partition by grouping(i_category)+grouping(i_class),
case when grouping(i_class) = 0 then i_category end
order by sum(ws_net_paid) desc) as rank_within_parent
from
plato.web_sales
,plato.date_dim d1
,plato.item
where
d1.d_month_seq between 1212 and 1212+11
and d1.d_date_sk = ws_sold_date_sk
and i_item_sk = ws_item_sk
group by rollup(i_category,i_class)
order by
lochierarchy desc,
case when lochierarchy = 0 then i_category end,
rank_within_parent
limit 100;
-- end query 1 in stream 0 using template ../query_templates/query86.tpl
|
