SQL实现本月访问量为0上月不为0的ID数量统计

你要统计的是当月无访问、上月活跃的ID数量，可以用窗口函数LAG获取每个ID上月的访问量，再按条件过滤统计即可，具体写法如下：

最终SQL语句 #

场景1：单ID单月仅1条记录（和你提供的示例结构一致） #

SELECT 
    month,
    COUNT(ID) AS target_id_count
FROM (
    SELECT 
        ID,
        DATE_FORMAT(DATE, '%Y-%m') AS month,
        VISIT,
        -- 按ID分组，按月份排序，取该ID上一个月的访问量
        LAG(VISIT, 1) OVER (PARTITION BY ID ORDER BY DATE) AS last_month_visit
    FROM 你的表名
) AS temp
WHERE VISIT = 0 AND last_month_visit > 0
GROUP BY month;

场景2：单ID单月可能有多条访问记录（更通用的写法） #

先按ID+月份聚合月度总访问量，再做上月访问量关联：

SELECT 
    month,
    COUNT(ID) AS target_id_count
FROM (
    SELECT 
        ID,
        month,
        monthly_visit,
        LAG(monthly_visit, 1) OVER (PARTITION BY ID ORDER BY month) AS last_month_visit
    FROM (
        -- 先聚合每个ID每个月的总访问量
        SELECT 
            ID,
            DATE_FORMAT(DATE, '%Y-%m') AS month,
            SUM(VISIT) AS monthly_visit
        FROM 你的表名
        GROUP BY ID, DATE_FORMAT(DATE, '%Y-%m')
    ) AS monthly_agg
) AS temp
WHERE monthly_visit = 0 AND last_month_visit > 0
GROUP BY month;

结果验证 #

用你提供的示例数据执行上述SQL，2015-02月的统计结果为1，和预期一致。

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