MySQL如何计算每个日期对应前n天总和及当月累计值

前提假设 #

先明确通用表结构作为示例，你可以根据自己的实际表字段调整：

• 表名：daily_data
• 字段1：dt DATE类型，存储日期值
• 字段2：val 数值类型（INT/DECIMAL都可），存储日期对应的统计值

实现方案（MySQL 8.0+ 推荐，基于窗口函数） #

窗口函数性能和可读性都最优，直接执行即可：

SELECT
  dt,
  val,
  -- 计算当前日期+前4天共5天的数值总和
  SUM(val) OVER (ORDER BY dt ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS last_5d_sum,
  -- 计算当月月初到当前日期的累计总和
  SUM(val) OVER (
    PARTITION BY DATE_FORMAT(dt, '%Y-%m') 
    ORDER BY dt 
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS month_to_date_sum
FROM daily_data
ORDER BY dt;

特殊场景适配 #

如果你的日期存在断层（部分日期没有数据未入库），要统计自然日的前4天（缺失日期值按0计算），可以把近5天的统计逻辑改为范围匹配：

SUM(val) OVER (ORDER BY TO_DAYS(dt) RANGE BETWEEN 4 PRECEDING AND CURRENT ROW) AS last_5d_sum

实现方案（MySQL 5.x 兼容版本，基于子查询） #

如果你的MySQL版本不支持窗口函数，可以用关联子查询实现：

SELECT
  a.dt,
  a.val,
  -- 近5天总和
  (SELECT SUM(val) FROM daily_data b WHERE b.dt BETWEEN DATE_SUB(a.dt, INTERVAL 4 DAY) AND a.dt) AS last_5d_sum,
  -- 当月累计总和
  (SELECT SUM(val) FROM daily_data c WHERE DATE_FORMAT(c.dt, '%Y-%m') = DATE_FORMAT(a.dt, '%Y-%m') AND c.dt <= a.dt) AS month_to_date_sum
FROM daily_data a
ORDER BY a.dt;

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