如何实现带时间依赖的列自比较：逐月累积对比邮件数据

要实现你需要的“随时间推移逐步加入2022年邮件到对比池”的需求，核心是判断每个2022年的邮件是否在**当前月份及之前的所有历史数据（2021全年 + 2022年当前月之前的月份）**中出现过。以下是两种可行的SQL方案：

方案一：按月统计新/重复邮件数量（高效聚合版） #

这个方案直接按月份输出统计结果，适合报表分析场景：

WITH email_first_occurrence AS (
    -- 计算每个email在全表中的首次出现日期
    SELECT 
        email,
        MIN(date) AS first_seen_date
    FROM T1
    GROUP BY email
),
monthly_email_data AS (
    -- 筛选2022年数据，关联首次出现日期并截断到月份
    SELECT 
        DATE_TRUNC('month', t.date) AS month_date,
        t.email,
        efo.first_seen_date
    FROM T1 t
    JOIN email_first_occurrence efo 
        ON t.email = efo.email
    WHERE t.date >= '2022-01-01' 
      AND t.date <= '2022-12-31'
)
-- 按月统计新、重复邮件数量
SELECT 
    month_date,
    -- 首次出现在当月的邮件为新邮件
    COUNT(DISTINCT CASE WHEN first_seen_date >= month_date 
                          AND first_seen_date < DATEADD('month', 1, month_date) 
                        THEN email END) AS "New email",
    -- 首次出现在当月之前的为重复邮件（含2021年及2022更早月份）
    COUNT(DISTINCT CASE WHEN first_seen_date < month_date 
                        THEN email END) AS "Repeated email"
FROM monthly_email_data
GROUP BY month_date
ORDER BY month_date;

逻辑说明 #

1. 第一个CTE email_first_occurrence：一次性计算每个email在整个表中的首次出现日期，覆盖2021和2022年所有数据。
2. 第二个CTE monthly_email_data：筛选2022年的邮件，将日期截断到月份维度，同时关联对应的首次出现日期。
3. 最终统计：对每个月份，判断email的首次出现时间——如果是当月则归为新邮件，否则归为重复邮件（自动包含2021年和2022年之前月份的历史数据）。

方案二：保留单条记录的判断逻辑（兼容原代码结构） #

如果需要保留原代码中每条记录的Logic字段，同时按月统计数量，可以用窗口函数实现：

WITH email_first_occurrence AS (
    SELECT 
        email,
        MIN(date) AS first_seen_date
    FROM T1
    GROUP BY email
)
SELECT 
    t.date,
    -- 判断当前邮件是否在当月之前已出现过
    IFF(efo.first_seen_date < DATE_TRUNC('month', t.date), 'TRUE', 'FALSE') AS "Logic",
    -- 按月统计当月新邮件总数
    SUM(CASE WHEN efo.first_seen_date >= DATE_TRUNC('month', t.date) 
                  AND efo.first_seen_date < DATEADD('month', 1, DATE_TRUNC('month', t.date)) 
                THEN 1 ELSE 0 END) 
        OVER (PARTITION BY DATE_TRUNC('month', t.date)) AS "New email",
    -- 按月统计当月重复邮件总数
    SUM(CASE WHEN efo.first_seen_date < DATE_TRUNC('month', t.date) 
                THEN 1 ELSE 0 END) 
        OVER (PARTITION BY DATE_TRUNC('month', t.date)) AS "Repeated email"
FROM T1 t
JOIN email_first_occurrence efo 
    ON t.email = efo.email
WHERE t.date >= '2022-01-01' 
  AND t.date <= '2022-12-31';

逻辑说明 #

• 用DATE_TRUNC('month', t.date)将日期统一到月份维度，作为分组依据。
• 通过窗口函数OVER (PARTITION BY ...)，对每个月份内的所有记录计算新/重复邮件的总数，同时保留每条记录的Logic判断结果。

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