SQL Redshift选择查询以获取desc中的总销售额

时间:2022-05-01 23:05:36

Can some 1 help me with sql redshift query to get the result the way mentioned below

可以帮助我使用sql redshift查询以下面提到的方式得到结果

3 columns
order_date ,daily_sale (sale made on tht day), total_sale (total_sale till date) order_date daily_sale total_sale 2017-01-31 1830 206316.4 2017-01-30 2487.5 206316.4 2017-01-29 5456 206316.4 2017-01-28 2786.2 206316.4 2017-01-27 2337 206316.4 2017-01-26 1404.2 206316.4

3列ORDER_DATE下达,daily_sale(发售THT一天进行),total_sale(total_sale直到日期)order_date的daily_sale total_sale 2017年1月31日1830 206316.4 2017年1月30日2487.5 206316.4 2017年1月29日5456 206316.4 2017年1月28日2786.2 206316.4 2017-01-27 2337 206316.4 2017-01-26 1404.2 206316.4

How can get total sale in such a way

如何以这种方式获得全部销售

order_date daily_sale total_sale 2017-01-31 1830 206316.4 2017-01-30 2487.5 204486.4 (206316.4 -1830) 2017-01-29 5456 201999.4 (206316.4 -1830-2487.5) and so on

order_date daily_sale total_sale 2017-01-31 1830 206316.4 2017-01-30 2487.5 204486.4(206316.4 -1830)2017-01-29 5456 201999.4(206316.4 -1830-2487.5)等等

1 个解决方案

#1


1  

I don't know your service (DB) supports window functions or not.

我不知道你的服务(DB)是否支持窗口功能。

If yes, you can try this query:

如果是,您可以尝试以下查询:

(put instead of ttt your table name)

(把你的表名改为ttt)

SELECT ttt.order_date, daily_sale, total_sale,  daily_sale+total_sale-t.total_last_daily  AS desirable_column  FROM ttt
INNER JOIN (SELECT order_date, SUM(daily_sale) OVER( ORDER BY order_date desc) total_last_daily FROM ttt) AS t
ON ttt.order_date = t.order_date
ORDER BY ttt.order_date DESC

#1


1  

I don't know your service (DB) supports window functions or not.

我不知道你的服务(DB)是否支持窗口功能。

If yes, you can try this query:

如果是,您可以尝试以下查询:

(put instead of ttt your table name)

(把你的表名改为ttt)

SELECT ttt.order_date, daily_sale, total_sale,  daily_sale+total_sale-t.total_last_daily  AS desirable_column  FROM ttt
INNER JOIN (SELECT order_date, SUM(daily_sale) OVER( ORDER BY order_date desc) total_last_daily FROM ttt) AS t
ON ttt.order_date = t.order_date
ORDER BY ttt.order_date DESC