]>
git.proxmox.com Git - ceph.git/blob - ceph/src/s3select/TPCDS/sample-queries-tpcds/query53.sql
1 -- start query 1 in stream 0 using template query53.tpl and seed 1819994127
4 sum(ss_sales_price
) sum_sales
,
5 avg(sum(ss_sales_price
)) over (partition by i_manufact_id
) avg_quarterly_sales
6 from item
, store_sales
, date_dim
, store
7 where ss_item_sk
= i_item_sk
and
8 ss_sold_date_sk
= d_date_sk
and
9 ss_store_sk
= s_store_sk
and
10 d_month_seq
in (1212,1212+1,1212+2,1212+3,1212+4,1212+5,1212+6,1212+7,1212+8,1212+9,1212+10,1212+11) and
11 ((i_category
in ('Books','Children','Electronics') and
12 i_class
in ('personal','portable','reference','self-help') and
13 i_brand
in ('scholaramalgamalg #14','scholaramalgamalg #7',
14 'exportiunivamalg #9','scholaramalgamalg #9'))
15 or(i_category
in ('Women','Music','Men') and
16 i_class
in ('accessories','classical','fragrances','pants') and
17 i_brand
in ('amalgimporto #1','edu packscholar #1','exportiimporto #1',
19 group by i_manufact_id
, d_qoy
) tmp1
20 where case when avg_quarterly_sales
> 0
21 then abs (sum_sales
- avg_quarterly_sales
)/ avg_quarterly_sales
23 order by avg_quarterly_sales
,
28 -- end query 1 in stream 0 using template query53.tpl