月度快照ID新增、删除、留存统计及SQL优化需求

需求说明 #

• 持有月度快照数据，每月执行1-2次，执行时间不固定
• 基于唯一ID列，统计每个快照与上月快照（而非当月其他快照）的对比指标：
  • same：留存ID数（当前快照与上月快照均存在的ID）
  • dropped：删除ID数（上月快照存在、当前快照不存在的ID）
  • added：新增ID数（当前快照存在、上月快照不存在的ID）
  • total_current：当前快照的总ID数

示例数据 #

ID  snapshot
1   01/01/2022
2   01/01/2022
3   01/01/2022
1   03/02/2022
2   03/02/2022
4   03/02/2022
1   15/02/2022
3   15/02/2022
4   15/02/2022

期望输出 #

snapshot   same   dropped  added  total_current
01/01/2022  0     0        3      3
03/02/2022  2     1        1      3
15/02/2022  2     1        1      3

现有SQL问题 #

原SQL通过LAST_DAY将同月快照合并为单一日期，无法保留每个独立快照的统计结果，不符合需求：

SELECT
    coalesce(last_day(c.snapshot), add_months(last_day(p.snapshot), 1) )         
    ,SUM(CASE WHEN c.ID is not null and p.ID is not null THEN 1 ELSE 0 END) AS same
    ,SUM(CASE WHEN c.ID is null and p.ID is not null THEN 1 ELSE 0 END) AS dropped
    ,SUM(CASE WHEN c.ID is not null and p.ID is null THEN 1 ELSE 0 END) AS added
    ,count(c.snapshot) as total_current
FROM table c
full outer join table p
 ON c.ID = p.ID
 AND LAST_DAY(c.snapshot) = add_months(last_day(p.snapshot), 1))
GROUP BY 1;

优化方案 #

通用SQL版本（适配BigQuery、Snowflake等支持数组的数据库） #

核心思路：先按独立快照分组聚合ID，再关联上月全量ID集合做对比统计

WITH snapshot_groups AS (
    -- 按每个快照日期分组，聚合ID集合并计算当前总ID数
    SELECT
        snapshot,
        DATE_TRUNC(snapshot, MONTH) AS current_month,
        ARRAY_AGG(DISTINCT ID) AS current_ids,
        COUNT(DISTINCT ID) AS total_current
    FROM your_table
    GROUP BY snapshot, current_month
),
prev_month_ids AS (
    -- 按月份分组，聚合上月所有快照的ID并集
    SELECT
        DATE_TRUNC(snapshot, MONTH) AS prev_month,
        ARRAY_AGG(DISTINCT ID) AS prev_ids
    FROM your_table
    GROUP BY prev_month
)
SELECT
    sg.snapshot,
    -- 统计留存ID数
    (SELECT COUNT(DISTINCT id) FROM UNNEST(sg.current_ids) id WHERE id IN UNNEST(pmi.prev_ids)) AS same,
    -- 统计删除ID数
    (SELECT COUNT(DISTINCT id) FROM UNNEST(pmi.prev_ids) id WHERE id NOT IN UNNEST(sg.current_ids)) AS dropped,
    -- 统计新增ID数
    (SELECT COUNT(DISTINCT id) FROM UNNEST(sg.current_ids) id WHERE id NOT IN UNNEST(pmi.prev_ids)) AS added,
    sg.total_current
FROM snapshot_groups sg
LEFT JOIN prev_month_ids pmi
    ON DATE_ADD(sg.current_month, INTERVAL -1 MONTH) = pmi.prev_month
ORDER BY sg.snapshot;

MySQL适配版本（无数组函数） #

WITH snapshot_groups AS (
    SELECT
        snapshot,
        DATE_FORMAT(snapshot, '%Y-%m-01') AS current_month,
        COUNT(DISTINCT ID) AS total_current
    FROM your_table
    GROUP BY snapshot, current_month
),
prev_month_ids AS (
    SELECT DISTINCT
        DATE_FORMAT(snapshot, '%Y-%m-01') AS prev_month,
        ID
    FROM your_table
)
SELECT
    sg.snapshot,
    -- 留存ID数
    (SELECT COUNT(DISTINCT yt.ID) 
     FROM your_table yt
     WHERE yt.snapshot = sg.snapshot
     AND EXISTS (SELECT 1 FROM prev_month_ids pmi 
                 WHERE pmi.prev_month = DATE_SUB(sg.current_month, INTERVAL 1 MONTH) 
                 AND pmi.ID = yt.ID)) AS same,
    -- 删除ID数
    (SELECT COUNT(DISTINCT pmi.ID) 
     FROM prev_month_ids pmi
     WHERE pmi.prev_month = DATE_SUB(sg.current_month, INTERVAL 1 MONTH)
     AND NOT EXISTS (SELECT 1 FROM your_table yt 
                     WHERE yt.snapshot = sg.snapshot 
                     AND yt.ID = pmi.ID)) AS dropped,
    -- 新增ID数
    (SELECT COUNT(DISTINCT yt.ID) 
     FROM your_table yt
     WHERE yt.snapshot = sg.snapshot
     AND NOT EXISTS (SELECT 1 FROM prev_month_ids pmi 
                     WHERE pmi.prev_month = DATE_SUB(sg.current_month, INTERVAL 1 MONTH) 
                     AND pmi.ID = yt.ID)) AS added,
    sg.total_current
FROM snapshot_groups sg
ORDER BY sg.snapshot;

方案说明 #

1. snapshot_groups：保留每个独立快照的日期、所属月份、全量ID集合及总ID数
2. prev_month_ids：按月份聚合上月所有快照的ID并集，确保对比的是上月完整的ID范围
3. 通过关联当前快照月份与上月ID集合，分别统计留存、删除、新增的ID数量
4. 自动处理首个月份无上月数据的场景，此时same和dropped为0，added等于当前总ID数

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