数据分组累积值

Question

我需要一些专门的帮助，在下面的查询。因此，我需要根据GMV量每天进行累计计算。

我这里最大的问题是，我不能从组中删除gmv，否则我会出错。

我有什么办法解决这个问题吗？

SELECT CONVERT(varchar, order_date, 103) AS date,
       DATEPART(WEEKDAY, order_date) AS weekday,
       SUM(gmv) OVER (ORDER BY order_date ROWS UNBOUNDED PRECEDING) AS cumulative
FROM database
WHERE order_date BETWEEN '2017-02-01' AND '2017-08-01'
GROUP BY order_date,
         gmv;

Answer 1

正如我在注释中提到的，首先使用派生表获取组中的聚合，然后执行累积SUM。

WITH CTE AS
    (SELECT CONVERT(date, order_date) AS OrderDate, --I assume here that Order_date is a date and time value
            SUM(GMV) AS GMV
     FROM dbo.Raw_data
     GROUP BY CONVERT(date, order_date))
SELECT OrderDate,
       DATEPART(WEEKDAY, OrderDate) AS WeekDay,
       SUM(GMV) OVER (ORDER BY OrderDate ASC
                      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS CumulativeGMV
FROM CTE;