From: | Tatsuo Ishii <ishii(at)sraoss(dot)co(dot)jp> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | TPC-DS queries |
Date: | 2019-03-11 05:30:04 |
Message-ID: | 20190311.143004.1675828601408112218.t-ishii@sraoss.co.jp |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
I played with TPC-DS and found some of them can't be executed because
of SQL errors and I am not sure why.
For example with query 36:
select
sum(ss_net_profit)/sum(ss_ext_sales_price) as gross_margin
,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(ss_net_profit)/sum(ss_ext_sales_price) asc) as rank_within_parent
from
store_sales
,date_dim d1
,item
,store
where
d1.d_year = 2000
and d1.d_date_sk = ss_sold_date_sk
and i_item_sk = ss_item_sk
and s_store_sk = ss_store_sk
and s_state in ('TN','TN','TN','TN',
'TN','TN','TN','TN')
group by rollup(i_category,i_class)
order by
lochierarchy desc
,case when lochierarchy = 0 then i_category end -- line 25 is here.
,rank_within_parent
limit 100;
psql:query_0.sql:1935: ERROR: column "lochierarchy" does not exist
LINE 25: ,case when lochierarchy = 0 then i_category end
I have follwed the instruction here.
https://ankane.org/tpc-ds
PostgreSQL is master branch HEAD. For me, the SQL above looks to be
valid.
Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp
From | Date | Subject | |
---|---|---|---|
Next Message | Nanda Kumar | 2019-03-11 05:44:08 | RE: Ran out of memory retrieving query results. |
Previous Message | Adrian Klaver | 2019-03-11 04:15:47 | Re: migration of 100+ tables |