MySQL按日期分组统计截止对应日期的全表累计数据总量

云原生阿岚

2026-9-25

解决方案

核心思路：先统计近7天之前的全表历史存量数据，再基于这个存量累加近7天的每日新增，即可得到包含所有历史数据的当日累计总量。

MySQL 8.0+ 版本（支持CTE与窗口函数）

WITH RECURSIVE date_series AS (
    -- 生成最近7天的完整日期序列，避免无新增的日期缺失
    SELECT CURDATE() AS day
    UNION ALL
    SELECT SUBDATE(day, 1)
    FROM date_series
    WHERE day > SUBDATE(CURDATE(), 6)
),
history_total AS (
    -- 统计近7天之前的全表历史存量
    SELECT COUNT(id) AS base_count
    FROM properties
    WHERE created_at < SUBDATE(CURDATE(), 7)
),
daily_new AS (
    -- 统计近7天每日新增量，无新增则补0
    SELECT 
        ds.day,
        IFNULL(COUNT(p.id), 0) AS new_count
    FROM date_series ds
    LEFT JOIN properties p ON DATE(p.created_at) = ds.day
    GROUP BY ds.day
)
SELECT 
    day,
    -- 历史存量 + 从第一天到当前天的新增累计，即为截止当日总数据量
    history_total.base_count + SUM(daily_new.new_count) OVER(ORDER BY day ASC) AS total_count
FROM daily_new, history_total
ORDER BY day DESC;

MySQL 5.x 版本（兼容旧版本，使用用户变量实现）

SELECT 
    day,
    @cumulative := @cumulative + new_count AS total_count
FROM (
    -- 先获取每日新增
    SELECT 
        DATE(p.created_at) AS day,
        COUNT(p.id) AS new_count
    FROM properties p
    WHERE DATE(p.created_at) >= SUBDATE(CURDATE(), 7)
    GROUP BY DATE(p.created_at)
    ORDER BY day ASC
) t,
-- 初始化累计变量，值为近7天之前的历史总存量
(SELECT @cumulative := (SELECT COUNT(id) FROM properties WHERE created_at < SUBDATE(CURDATE(), 7))) init
ORDER BY day DESC;