如何在SQL中结合GROUP BY实现按日分组的累计求和？

现有数据表business.revenue结构及数据如下：

当前使用以下查询获取每条记录的累计求和：

SELECT  office_id,
        date_trunc('day', time) as ts,
        sum(revenue) over (partition by office_id order by time) as cum_rev
FROM    business.revenue
ORDER BY office_id, ts;

得到的结果是：

期望得到按截断后的日期ts分组的结果，即每天只保留当天累计的最终值：

需要修改现有查询以实现该结果，尝试按ts分组但遇到困难，请问如何调整？

你可以通过两种方式实现需求：

方式一：基于原有累计结果分组取最大值 #

先保留原有逻辑计算每条记录的累计值，再对每个office_id和ts分组，取组内最大的累计值（当天最后一条记录的累计值就是截止到当天的总累计）：

SELECT 
    office_id,
    ts,
    MAX(cum_rev) AS cum_rev
FROM (
    SELECT  
        office_id,
        date_trunc('day', time) as ts,
        sum(revenue) over (partition by office_id order by time) as cum_rev
    FROM    business.revenue
) AS subquery
GROUP BY office_id, ts
ORDER BY office_id, ts;

方式二：先按天统计收入再做累计 #

先分组计算每日收入，再基于每日收入做累计求和，这种方式逻辑更直观且可能更高效：

SELECT 
    office_id,
    ts,
    SUM(daily_revenue) OVER (PARTITION BY office_id ORDER BY ts) AS cum_rev
FROM (
    SELECT  
        office_id,
        date_trunc('day', time) as ts,
        SUM(revenue) AS daily_revenue
    FROM    business.revenue
    GROUP BY office_id, ts
) AS subquery
ORDER BY office_id, ts;

两种方式最终都会输出你期望的分组累计结果。

内容的提问来源于stack exchange，提问作者Michael Anckaert