MySQL中计算video_count列增量值的实现方案问询

云原生阿岚

2026-7-22

解决方案

在MySQL中，你可以使用**窗口函数LAG()**来获取前一天的video_count，进而计算每日增量值increment。以下是适配不同MySQL版本的实现方案：

方案1：MySQL 8.0及以上（支持窗口函数与CTE）

这是最简洁高效的写法，利用CTE先获取基础统计数据，再通过窗口函数计算增量：

WITH daily_counts AS (
    SELECT 
        DATE(created_at) AS datee, 
        COUNT(*) AS video_count
    FROM test_ambil_nihongo_doang_loop_jan2023
    WHERE MONTH(video_time_published) = 1 AND YEAR(video_time_published) = 2023
    GROUP BY DATE(created_at)
)
SELECT 
    datee,
    video_count,
    -- 第一行无前置数据时返回0，若保留NULL可去掉COALESCE
    COALESCE(video_count - LAG(video_count) OVER (ORDER BY datee), 0) AS increment
FROM daily_counts
ORDER BY datee;

关键说明：

LAG(video_count) OVER (ORDER BY datee)：按日期排序后，获取当前行的前一行video_count值
COALESCE()：将第一行的NULL增量替换为0，符合多数场景的统计需求

方案2：MySQL 5.7及以下（不支持窗口函数）

通过自连接的方式关联当前日期与前一天的统计数据：

SELECT 
    t1.datee,
    t1.video_count,
    COALESCE(t1.video_count - t2.video_count, 0) AS increment
FROM (
    SELECT DATE(created_at) AS datee, COUNT(*) AS video_count
    FROM test_ambil_nihongo_doang_loop_jan2023
    WHERE MONTH(video_time_published) = 1 AND YEAR(video_time_published) = 2023
    GROUP BY DATE(created_at)
) t1
LEFT JOIN (
    SELECT DATE(created_at) AS datee, COUNT(*) AS video_count
    FROM test_ambil_nihongo_doang_loop_jan2023
    WHERE MONTH(video_time_published) = 1 AND YEAR(video_time_published) = 2023
    GROUP BY DATE(created_at)
) t2 ON t1.datee = DATE_ADD(t2.datee, INTERVAL 1 DAY)
ORDER BY t1.datee;