求编写按月度统计年初至今（YTD）活跃会员数的SQL查询语句

需要编写SQL查询，按月度统计**年初至今（YTD）**的活跃memberid去重计数，规则如下：

• 202201：统计当月活跃的去重memberid
• 202202：统计202201-202202期间活跃的去重memberid
• 202203：统计202201-202203期间活跃的去重memberid

示例数据表 #

期望查询结果 #

解决方案 #

以下是通用型SQL写法，适配大多数关系型数据库（MySQL、PostgreSQL、SQL Server等）：

SELECT 
    t1.yearmonth,
    COUNT(DISTINCT t2.memberid) AS active_count
FROM 
    -- 获取所有唯一的月份维度
    (SELECT DISTINCT yearmonth FROM your_table) t1
LEFT JOIN 
    -- 筛选出所有活跃状态的会员记录
    (SELECT memberid, yearmonth FROM your_table WHERE activestatus = 'Y') t2
ON 
    -- 关联条件：同一年份内，活跃记录的月份 <= 当前统计月份
    t2.yearmonth <= t1.yearmonth
    AND LEFT(t1.yearmonth, 4) = LEFT(t2.yearmonth, 4)
GROUP BY 
    t1.yearmonth
ORDER BY 
    t1.yearmonth;

逻辑说明 #

1. t1子查询提取数据表中所有唯一的月份，作为统计的时间维度
2. t2子查询筛选出所有activestatus='Y'的活跃会员记录
3. 通过自连接，将每个月份与同一年份内所有早于等于该月份的活跃记录关联
4. 按月份分组后，用COUNT(DISTINCT memberid)统计去重的活跃会员数

适配窗口函数的优化写法（部分数据库支持） #

如果你的数据库支持窗口函数（如PostgreSQL、SQL Server、MySQL 8.0+），可以用以下更高效的写法：

WITH active_members AS (
    -- 去重获取每个会员的活跃月份
    SELECT DISTINCT memberid, yearmonth
    FROM your_table
    WHERE activestatus = 'Y'
),
month_list AS (
    -- 获取所有唯一月份
    SELECT DISTINCT yearmonth FROM your_table
)
SELECT 
    ml.yearmonth,
    -- 窗口函数统计年初至今的去重会员数
    COUNT(DISTINCT am.memberid) OVER (
        PARTITION BY LEFT(ml.yearmonth, 4)
        ORDER BY ml.yearmonth
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS active_count
FROM month_list ml
LEFT JOIN active_members am 
    ON am.yearmonth <= ml.yearmonth
    AND LEFT(ml.yearmonth, 4) = LEFT(am.yearmonth, 4)
ORDER BY ml.yearmonth;

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