如何用SQL实现月度累计最大值的滚动求和？

背景与需求 #

现有2022年7月各国对象每日使用数据（示例）：

country  objectid  objectuse
record_date
2022-07-20    chile         0          4
2022-07-01    chile         1          4
2022-07-02    chile         1          4
2022-07-03    chile         1          4
2022-07-04    chile         1          4
...             ...       ...        ...
2022-07-26     peru      3088          4
2022-07-27     peru      3088          4
2022-07-28     peru      3088          4
2022-07-30     peru      3088          4
2022-07-31     peru      3088          4

目前已用SQL算出各国月度最大值总和：

WITH month_max AS (
    SELECT
        country,
        objectid,
        MAX(objectuse) AS maxuse
    FROM mytable
    GROUP BY
        country,
        objectid
)
SELECT
    country,
    SUM(maxuse)
FROM month_max
GROUP BY country;

结果：

country   sum
-------------
chile    1224
peru    17008   

但实际需求是获取从月初到每个日期的累计最大值滚动求和，预期结果如下：

country       sum  
record_date
2022-07-01    chile         1
2022-07-01     peru         1
2022-07-02    chile         2
2022-07-02     peru         3
...             ...       ...
2022-07-31    chile       1224
2022-07-31     peru      17008

之前尝试的窗口函数未达到预期，以下是可行的SQL实现方案：

解决方案（以PostgreSQL为例） #

WITH all_dates AS (
    -- 生成2022年7月的所有日期
    SELECT generate_series('2022-07-01'::date, '2022-07-31'::date, '1 day'::interval)::date AS record_date
),
object_dates AS (
    -- 关联所有日期与每个国家的对象，确保每个对象在每日都有计算维度
    SELECT 
        ad.record_date,
        o.country,
        o.objectid,
        -- 计算从月初到当前日期，该对象的最大使用值
        MAX(t.objectuse) OVER (
            PARTITION BY o.country, o.objectid
            ORDER BY ad.record_date
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS cumulative_max
    FROM all_dates ad
    CROSS JOIN (SELECT DISTINCT country, objectid FROM mytable) o
    LEFT JOIN mytable t 
        ON ad.record_date = t.record_date 
        AND o.country = t.country 
        AND o.objectid = t.objectid
),
filled_max AS (
    -- 处理无数据的情况：对象在当前日期及之前无使用记录时，累计最大值设为0
    SELECT 
        record_date,
        country,
        objectid,
        COALESCE(cumulative_max, 0) AS cumulative_max
    FROM object_dates
)
-- 按日期+国家汇总累计最大值的和
SELECT 
    record_date,
    country,
    SUM(cumulative_max) AS sum
FROM filled_max
GROUP BY record_date, country
ORDER BY record_date, country;

不同数据库适配说明 #

• MySQL：生成日期序列需用递归CTE替代generate_series，示例：

  WITH RECURSIVE all_dates AS (
      SELECT '2022-07-01' AS record_date
      UNION ALL
      SELECT DATE_ADD(record_date, INTERVAL 1 DAY)
      FROM all_dates
      WHERE record_date < '2022-07-31'
  )
  

• SQL Server：生成日期序列可使用DATEADD结合递归CTE，或利用系统表生成。

核心思路 #

1. 补全当月所有日期，避免遗漏无数据的日期导致结果缺失；
2. 为每个对象绑定所有日期，确保每个对象在每一天都能计算累计最大值；
3. 用窗口函数计算每个对象从月初到当前日期的最大使用值；
4. 处理无数据的NULL场景，最后按日期和国家汇总求和，得到预期的滚动累计结果。

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