如何统计2010年至今各公历年年末的有效会员总人数？

实现方案 #

首先生成2010年至今的所有年份序列，再关联会员表逐行判断该年末用户是否为有效会员，最后按年份分组统计即可。

通用逻辑说明 #

对任意年份的年末日期YYYY-12-31，有效会员需同时满足两个条件：

• 入会时间早于等于该年末日期：join_date <= 'YYYY-12-31'
• 退会时间晚于该年末日期，或者至今未退会：leave_date > 'YYYY-12-31' OR leave_date IS NULL

MySQL 8.0+/PostgreSQL 写法（支持递归CTE） #

WITH RECURSIVE years AS (
    -- 递归生成2010年到今年的年份序列
    SELECT 2010 AS year
    UNION ALL
    SELECT year + 1 FROM years WHERE year < YEAR(CURDATE())
)
SELECT 
    y.year,
    COUNT(m.user_id) AS end_year_member_count
FROM years y
LEFT JOIN memberships m
ON  m.join_date <= CONCAT(y.year, '-12-31')
AND (m.leave_date > CONCAT(y.year, '-12-31') OR m.leave_date IS NULL)
GROUP BY y.year
ORDER BY y.year;

MySQL 5.x 低版本写法（不支持CTE） #

如果使用不支持递归CTE的低版本MySQL，可以用数字辅助表生成年份序列：

SELECT 
    y.year,
    COUNT(m.user_id) AS end_year_member_count
FROM (
    -- 生成2010到当前年份的序列，最大支持到2109年
    SELECT 2010 + t1.n + t2.n * 10 AS year
    FROM (SELECT 0 n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t1
    CROSS JOIN (SELECT 0 n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t2
    HAVING year <= YEAR(CURDATE())
) y
LEFT JOIN memberships m
ON  m.join_date <= CONCAT(y.year, '-12-31')
AND (m.leave_date > CONCAT(y.year, '-12-31') OR m.leave_date IS NULL)
GROUP BY y.year
ORDER BY y.year;

注意事项 #

• 如果存在同一个用户有多条入会记录的情况，需要先对memberships表按user_id去重，避免重复计数
• 如果join_date/leave_date字段带时分秒格式，可以把CONCAT(y.year, '-12-31')替换为STR_TO_DATE(CONCAT(y.year, '-12-31 23:59:59'), '%Y-%m-%d %H:%i:%s')，统计更精确

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