按企业规模统计退订时间与退订窗口起始时间同月的员工数SQL问题

嘿，我来帮你搞定这个SQL的问题！你要统计的是退订日期（opted_out_on）和退订窗口期开始日期（opt_out_window_starts_on）同月的员工数，还要按企业规模分组对吧？之前的SQL出错，大概率是这几个地方没处理好，我给你梳理下修正方案：

核心思路说明 #

首先得明确几个关键前提：

• employee表本身没有企业规模字段，必须关联存储企业信息的表（比如叫company，包含company_id和company_size字段）才能按企业规模分组
• 日期比较必须同时匹配年份+月份，只比月份会把不同年份的同月数据错误归为一类
• 要过滤掉未退订的员工（即opted_out_on为NULL的记录）
• 要避免同一员工被重复统计（如果employee表存在重复记录）

分数据库的修正SQL示例 #

MySQL版本 #

SELECT 
    c.company_size,
    COUNT(DISTINCT e.employee_id) AS same_month_opt_out_count
FROM 
    employee e
JOIN 
    company c ON e.company_id = c.company_id
WHERE 
    e.opted_out_on IS NOT NULL
    -- 提取年月进行匹配
    AND DATE_FORMAT(e.opted_out_on, '%Y-%m') = DATE_FORMAT(e.opt_out_window_starts_on, '%Y-%m')
GROUP BY 
    c.company_size;

PostgreSQL版本 #

SELECT 
    c.company_size,
    COUNT(DISTINCT e.employee_id) AS same_month_opt_out_count
FROM 
    employee e
JOIN 
    company c ON e.company_id = c.company_id
WHERE 
    e.opted_out_on IS NOT NULL
    AND TO_CHAR(e.opted_out_on, 'YYYY-MM') = TO_CHAR(e.opt_out_window_starts_on, 'YYYY-MM')
GROUP BY 
    c.company_size;

SQL Server版本 #

SELECT 
    c.company_size,
    COUNT(DISTINCT e.employee_id) AS same_month_opt_out_count
FROM 
    employee e
JOIN 
    company c ON e.company_id = c.company_id
WHERE 
    e.opted_out_on IS NOT NULL
    AND CONVERT(VARCHAR(7), e.opted_out_on, 120) = CONVERT(VARCHAR(7), e.opt_out_window_starts_on, 120)
GROUP BY 
    c.company_size;

常见错误排查 #

• 未关联企业表：如果直接从employee表分组，没有企业规模字段，结果肯定不符合要求
• 日期只比较月份：比如只用MONTH(opted_out_on) = MONTH(opt_out_window_starts_on)，会把2023-05和2024-05的员工错误统计到一起
• 未去重员工ID：如果employee表存在同一员工的多条记录，会导致计数重复，必须用COUNT(DISTINCT employee_id)
• 未过滤NULL值：把还没退订的员工（opted_out_on为NULL）也统计进去，导致结果偏大

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