Omega359 commented on code in PR #22474:
URL: https://github.com/apache/datafusion/pull/22474#discussion_r3313512795
##########
datafusion/core/tests/tpc-ds/5.sql:
##########
@@ -1,129 +1,146 @@
--- Copyright 2015 Transaction Processing Performance Council
-
-with ssr as
- (select s_store_id,
- sum(sales_price) as sales,
- sum(profit) as profit,
- sum(return_amt) as returns,
- sum(net_loss) as profit_loss
- from
- ( select ss_store_sk as store_sk,
- ss_sold_date_sk as date_sk,
- ss_ext_sales_price as sales_price,
- ss_net_profit as profit,
- cast(0 as decimal(7,2)) as return_amt,
- cast(0 as decimal(7,2)) as net_loss
- from store_sales
- union all
- select sr_store_sk as store_sk,
- sr_returned_date_sk as date_sk,
- cast(0 as decimal(7,2)) as sales_price,
- cast(0 as decimal(7,2)) as profit,
- sr_return_amt as return_amt,
- sr_net_loss as net_loss
- from store_returns
- ) salesreturns,
- date_dim,
- store
- where date_sk = d_date_sk
- and d_date between cast('1998-08-04' as date)
- and (cast('1998-08-04' as date) + INTERVAL '14 days')
- and store_sk = s_store_sk
- group by s_store_id)
- ,
- csr as
- (select cp_catalog_page_id,
- sum(sales_price) as sales,
- sum(profit) as profit,
- sum(return_amt) as returns,
- sum(net_loss) as profit_loss
- from
- ( select cs_catalog_page_sk as page_sk,
- cs_sold_date_sk as date_sk,
- cs_ext_sales_price as sales_price,
- cs_net_profit as profit,
- cast(0 as decimal(7,2)) as return_amt,
- cast(0 as decimal(7,2)) as net_loss
- from catalog_sales
- union all
- select cr_catalog_page_sk as page_sk,
- cr_returned_date_sk as date_sk,
- cast(0 as decimal(7,2)) as sales_price,
- cast(0 as decimal(7,2)) as profit,
- cr_return_amount as return_amt,
- cr_net_loss as net_loss
- from catalog_returns
- ) salesreturns,
- date_dim,
- catalog_page
- where date_sk = d_date_sk
- and d_date between cast('1998-08-04' as date)
- and (cast('1998-08-04' as date) + INTERVAL '14 days')
- and page_sk = cp_catalog_page_sk
- group by cp_catalog_page_id)
- ,
- wsr as
- (select web_site_id,
- sum(sales_price) as sales,
- sum(profit) as profit,
- sum(return_amt) as returns,
- sum(net_loss) as profit_loss
- from
- ( select ws_web_site_sk as wsr_web_site_sk,
- ws_sold_date_sk as date_sk,
- ws_ext_sales_price as sales_price,
- ws_net_profit as profit,
- cast(0 as decimal(7,2)) as return_amt,
- cast(0 as decimal(7,2)) as net_loss
- from web_sales
- union all
- select ws_web_site_sk as wsr_web_site_sk,
- wr_returned_date_sk as date_sk,
- cast(0 as decimal(7,2)) as sales_price,
- cast(0 as decimal(7,2)) as profit,
- wr_return_amt as return_amt,
- wr_net_loss as net_loss
- from web_returns left outer join web_sales on
- ( wr_item_sk = ws_item_sk
- and wr_order_number = ws_order_number)
- ) salesreturns,
- date_dim,
- web_site
- where date_sk = d_date_sk
- and d_date between cast('1998-08-04' as date)
- and (cast('1998-08-04' as date) + INTERVAL '14 days')
- and wsr_web_site_sk = web_site_sk
- group by web_site_id)
- select channel
- , id
- , sum(sales) as sales
- , sum(returns) as returns
- , sum(profit) as profit
- from
- (select 'store channel' as channel
- , 'store' || s_store_id as id
- , sales
- , returns
- , (profit - profit_loss) as profit
- from ssr
- union all
- select 'catalog channel' as channel
- , 'catalog_page' || cp_catalog_page_id as id
- , sales
- , returns
- , (profit - profit_loss) as profit
- from csr
- union all
- select 'web channel' as channel
- , 'web_site' || web_site_id as id
- , sales
- , returns
- , (profit - profit_loss) as profit
- from wsr
- ) x
- group by rollup (channel, id)
- order by channel
- ,id
- limit 100;
-
-
+WITH
+ ssr AS (
+ SELECT
+ s_store_id,
+ sum(sales_price) AS sales,
+ sum(profit) AS profit,
+ sum(return_amt) AS returns,
+ sum(net_loss) AS profit_loss
+ FROM
+ (
+ SELECT
+ ss_store_sk AS store_sk,
+ ss_sold_date_sk AS date_sk,
+ ss_ext_sales_price AS sales_price,
+ ss_net_profit AS profit,
+ cast(0 AS decimal(7, 2)) AS return_amt,
+ cast(0 AS decimal(7, 2)) AS net_loss
+ FROM store_sales
+ UNION ALL
+ SELECT
+ sr_store_sk AS store_sk,
+ sr_returned_date_sk AS date_sk,
+ cast(0 AS decimal(7, 2)) AS sales_price,
+ cast(0 AS decimal(7, 2)) AS profit,
+ sr_return_amt AS return_amt,
+ sr_net_loss AS net_loss
+ FROM store_returns
+ ) salesreturns,
+ date_dim,
+ store
+ WHERE
+ date_sk = d_date_sk
+ AND d_date BETWEEN cast('2000-08-23' AS date) AND (
+ cast('2000-08-23' AS date) + INTERVAL '14 days'
+ )
+ AND store_sk = s_store_sk
+ GROUP BY s_store_id
+ ),
+ csr AS (
+ SELECT
+ cp_catalog_page_id,
+ sum(sales_price) AS sales,
+ sum(profit) AS profit,
+ sum(return_amt) AS returns,
+ sum(net_loss) AS profit_loss
+ FROM
+ (
+ SELECT
+ cs_catalog_page_sk AS page_sk,
+ cs_sold_date_sk AS date_sk,
+ cs_ext_sales_price AS sales_price,
+ cs_net_profit AS profit,
+ cast(0 AS decimal(7, 2)) AS return_amt,
+ cast(0 AS decimal(7, 2)) AS net_loss
+ FROM catalog_sales
+ UNION ALL
+ SELECT
+ cr_catalog_page_sk AS page_sk,
+ cr_returned_date_sk AS date_sk,
+ cast(0 AS decimal(7, 2)) AS sales_price,
+ cast(0 AS decimal(7, 2)) AS profit,
+ cr_return_amount AS return_amt,
+ cr_net_loss AS net_loss
+ FROM catalog_returns
+ ) salesreturns,
+ date_dim,
+ catalog_page
+ WHERE
+ date_sk = d_date_sk
+ AND d_date BETWEEN cast('2000-08-23' AS date) AND (
+ cast('2000-08-23' AS date) + INTERVAL '14 days'
+ )
+ AND page_sk = cp_catalog_page_sk
+ GROUP BY cp_catalog_page_id
+ ),
+ wsr AS (
+ SELECT
+ web_site_id,
+ sum(sales_price) AS sales,
+ sum(profit) AS profit,
+ sum(return_amt) AS returns,
+ sum(net_loss) AS profit_loss
+ FROM
+ (
+ SELECT
+ ws_web_site_sk AS wsr_web_site_sk,
+ ws_sold_date_sk AS date_sk,
+ ws_ext_sales_price AS sales_price,
+ ws_net_profit AS profit,
+ cast(0 AS decimal(7, 2)) AS return_amt,
+ cast(0 AS decimal(7, 2)) AS net_loss
+ FROM web_sales
+ UNION ALL
+ SELECT
+ ws_web_site_sk AS wsr_web_site_sk,
+ wr_returned_date_sk AS date_sk,
+ cast(0 AS decimal(7, 2)) AS sales_price,
+ cast(0 AS decimal(7, 2)) AS profit,
+ wr_return_amt AS return_amt,
+ wr_net_loss AS net_loss
+ FROM web_returns
+ LEFT OUTER JOIN
+ web_sales
+ ON (wr_item_sk = ws_item_sk AND wr_order_number =
ws_order_number)
+ ) salesreturns,
+ date_dim,
+ web_site
+ WHERE
+ date_sk = d_date_sk
+ AND d_date BETWEEN cast('2000-08-23' AS date) AND (
+ cast('2000-08-23' AS date) + INTERVAL '14 days'
+ )
+ AND wsr_web_site_sk = web_site_sk
+ GROUP BY web_site_id
+ )
+SELECT channel, id, sum(sales) AS sales, sum(returns) AS returns, sum(profit)
AS profit
+FROM
+ (
+ SELECT
+ 'store channel' AS channel,
+ concat('store', s_store_id) AS id,
+ sales,
+ returns,
+ (profit - profit_loss) AS profit
+ FROM ssr
+ UNION ALL
+ SELECT
+ 'catalog channel' AS channel,
+ concat('catalog_page', cp_catalog_page_id) AS id,
Review Comment:
I wasn't aware that df supported ||. It likely boils down to the same
function call but good to align.
--
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]
