Omega359 commented on code in PR #22474:
URL: https://github.com/apache/datafusion/pull/22474#discussion_r3313514145
##########
datafusion/core/tests/tpc-ds/78.sql:
##########
@@ -1,59 +1,90 @@
--- Copyright 2015 Transaction Processing Performance Council
-
-with ws as
- (select d_year AS ws_sold_year, ws_item_sk,
- ws_bill_customer_sk ws_customer_sk,
- sum(ws_quantity) ws_qty,
- sum(ws_wholesale_cost) ws_wc,
- sum(ws_sales_price) ws_sp
- from web_sales
- left join web_returns on wr_order_number=ws_order_number and
ws_item_sk=wr_item_sk
- join date_dim on ws_sold_date_sk = d_date_sk
- where wr_order_number is null
- group by d_year, ws_item_sk, ws_bill_customer_sk
- ),
-cs as
- (select d_year AS cs_sold_year, cs_item_sk,
- cs_bill_customer_sk cs_customer_sk,
- sum(cs_quantity) cs_qty,
- sum(cs_wholesale_cost) cs_wc,
- sum(cs_sales_price) cs_sp
- from catalog_sales
- left join catalog_returns on cr_order_number=cs_order_number and
cs_item_sk=cr_item_sk
- join date_dim on cs_sold_date_sk = d_date_sk
- where cr_order_number is null
- group by d_year, cs_item_sk, cs_bill_customer_sk
- ),
-ss as
- (select d_year AS ss_sold_year, ss_item_sk,
+WITH
+ ws AS (
+ SELECT
+ d_year AS ws_sold_year,
+ ws_item_sk,
+ ws_bill_customer_sk ws_customer_sk,
+ sum(ws_quantity) ws_qty,
+ sum(ws_wholesale_cost) ws_wc,
+ sum(ws_sales_price) ws_sp
+ FROM web_sales
+ LEFT JOIN
+ web_returns ON wr_order_number = ws_order_number AND ws_item_sk =
wr_item_sk
+ JOIN date_dim ON ws_sold_date_sk = d_date_sk
+ WHERE wr_order_number IS NULL
+ GROUP BY d_year, ws_item_sk, ws_bill_customer_sk
+ ),
+ cs AS (
+ SELECT
+ d_year AS cs_sold_year,
+ cs_item_sk,
+ cs_bill_customer_sk cs_customer_sk,
+ sum(cs_quantity) cs_qty,
+ sum(cs_wholesale_cost) cs_wc,
+ sum(cs_sales_price) cs_sp
+ FROM catalog_sales
+ LEFT JOIN
+ catalog_returns
+ ON cr_order_number = cs_order_number
+ AND cs_item_sk = cr_item_sk
+ JOIN date_dim ON cs_sold_date_sk = d_date_sk
+ WHERE cr_order_number IS NULL
+ GROUP BY d_year, cs_item_sk, cs_bill_customer_sk
+ ),
+ ss AS (
+ SELECT
+ d_year AS ss_sold_year,
+ ss_item_sk,
+ ss_customer_sk,
+ sum(ss_quantity) ss_qty,
+ sum(ss_wholesale_cost) ss_wc,
+ sum(ss_sales_price) ss_sp
+ FROM store_sales
+ LEFT JOIN
+ store_returns
+ ON sr_ticket_number = ss_ticket_number
+ AND ss_item_sk = sr_item_sk
+ JOIN date_dim ON ss_sold_date_sk = d_date_sk
+ WHERE sr_ticket_number IS NULL
+ GROUP BY d_year, ss_item_sk, ss_customer_sk
+ )
+SELECT
+ ss_sold_year,
+ ss_item_sk,
ss_customer_sk,
- sum(ss_quantity) ss_qty,
- sum(ss_wholesale_cost) ss_wc,
- sum(ss_sales_price) ss_sp
- from store_sales
- left join store_returns on sr_ticket_number=ss_ticket_number and
ss_item_sk=sr_item_sk
- join date_dim on ss_sold_date_sk = d_date_sk
- where sr_ticket_number is null
- group by d_year, ss_item_sk, ss_customer_sk
- )
- select
-ss_sold_year, ss_item_sk, ss_customer_sk,
-round(ss_qty/(coalesce(ws_qty,0)+coalesce(cs_qty,0)),2) ratio,
-ss_qty store_qty, ss_wc store_wholesale_cost, ss_sp store_sales_price,
-coalesce(ws_qty,0)+coalesce(cs_qty,0) other_chan_qty,
-coalesce(ws_wc,0)+coalesce(cs_wc,0) other_chan_wholesale_cost,
-coalesce(ws_sp,0)+coalesce(cs_sp,0) other_chan_sales_price
-from ss
-left join ws on (ws_sold_year=ss_sold_year and ws_item_sk=ss_item_sk and
ws_customer_sk=ss_customer_sk)
-left join cs on (cs_sold_year=ss_sold_year and cs_item_sk=ss_item_sk and
cs_customer_sk=ss_customer_sk)
-where (coalesce(ws_qty,0)>0 or coalesce(cs_qty, 0)>0) and ss_sold_year=2000
-order by
- ss_sold_year, ss_item_sk, ss_customer_sk,
- ss_qty desc, ss_wc desc, ss_sp desc,
- other_chan_qty,
- other_chan_wholesale_cost,
- other_chan_sales_price,
- ratio
-limit 100;
-
-
+ round(ss_qty / (coalesce(ws_qty, 0) + coalesce(cs_qty, 0)), 2) ratio,
+ ss_qty store_qty,
+ ss_wc store_wholesale_cost,
+ ss_sp store_sales_price,
+ coalesce(ws_qty, 0) + coalesce(cs_qty, 0) other_chan_qty,
+ coalesce(ws_wc, 0) + coalesce(cs_wc, 0) other_chan_wholesale_cost,
+ coalesce(ws_sp, 0) + coalesce(cs_sp, 0) other_chan_sales_price
+FROM ss
+LEFT JOIN
+ ws
+ ON (
+ ws_sold_year = ss_sold_year
+ AND ws_item_sk = ss_item_sk
+ AND ws_customer_sk = ss_customer_sk
+ )
+LEFT JOIN
+ cs
+ ON (
+ cs_sold_year = ss_sold_year
+ AND cs_item_sk = ss_item_sk
+ AND cs_customer_sk = ss_customer_sk
+ )
+WHERE (coalesce(ws_qty, 0) > 0 OR coalesce(cs_qty, 0) > 0) AND ss_sold_year =
2000
+ORDER BY
+ ss_sold_year,
+ ss_item_sk,
+ ss_customer_sk,
+ ss_qty desc,
+ ss_wc desc,
+ ss_sp desc,
+ other_chan_qty,
+ other_chan_wholesale_cost,
+ other_chan_sales_price,
+ round(ss_qty / (coalesce(ws_qty + cs_qty, 1)), 2)
Review Comment:
Thanks, I'll fix this.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
