如何在BigQuery/SQL中按日期分组计算累计求和？

要实现截至每个日期的累计求和，核心是处理原查询中的null值并使用窗口函数计算累计值，具体步骤如下：

关键思路 #

1. 替换null值：原结果中的null表示当日该类别无数据，累计时需视为0，用COALESCE函数完成转换。
2. 窗口函数累计求和：使用SUM() OVER (ORDER BY date)，按日期顺序对每行及之前的所有行数据求和，得到截至当日的累计值。

示例SQL代码 #

假设你原有的按日期分组统计的查询结果为子查询daily_stats，最终SQL如下：

SELECT
    date,
    SUM(COALESCE(bad, 0)) OVER (ORDER BY date) AS bad,
    SUM(COALESCE(normal, 0)) OVER (ORDER BY date) AS normal,
    SUM(COALESCE(good, 0)) OVER (ORDER BY date) AS good
FROM (
    -- 此处替换为你已有的按日期分组统计的Query
    SELECT
        date,
        COUNT(CASE WHEN rating = 'bad' THEN 1 END) AS bad,
        COUNT(CASE WHEN rating = 'normal' THEN 1 END) AS normal,
        COUNT(CASE WHEN rating = 'good' THEN 1 END) AS good
    FROM your_table
    GROUP BY date
) AS daily_stats
ORDER BY date;

注意事项 #

• 如果你的date字段是字符串格式（如01.10），需确保排序逻辑正确。例如：
  • MySQL中可使用ORDER BY STR_TO_DATE(date, '%d.%m')转换为日期后排序
  • PostgreSQL中可使用ORDER BY TO_DATE(date, 'DD.MM')转换后排序

结果验证 #

执行上述SQL后，会得到你期望的累计求和结果：

date        bad   normal    good
01.10       2     4         2
02.10       2     8         5
03.10       3     8         10
04.10       6     9         11
05.10       11    10        11

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