月度会员福利活跃数统计SQL查询优化求助

需求 #

• 按月份统计拥有指定福利类型的会员活跃数量
• 统计规则：若会员福利结束日期在上月，活跃数减1；若开始日期在本月，活跃数加1
• YEAR_MONTH需为连续日期，范围为该福利类型的最早开始日期年月到最晚结束日期年月

表结构与示例数据 #

create table #MemberTable
(memberId int
,benefitId int
,startDate datetime
,endDate dateTime)

insert #MemberTable
values(1,1,'2020-01-15','2022-01-15'),
(1,2,'2019-05-20','2020-10-15'),
(2,1,'2022-12-06','2024-01-20'),
(2,2,'2020-01-05','2020-11-06'),
(1,3,'2021-06-15','2022-07-01'),
(3,3,'2020-02-28','2022-02-28'),
(3,2,'2020-01-15','2020-12-15')

福利ID=2的期望结果 #

YEAR_MONTH  benefitId   active_b_count
201905          2           1
201906          2           1
201907          2           1
201908          2           1
201909          2           1
201910          2           1
201911          2           1
201912          2           1
202001          2           3
202002          2           3
202003          2           3
202004          2           3
202005          2           3
202006          2           3
202007          2           3
202008          2           3
202009          2           3
202010          2           3
202011          2           2
202012          2           1

原SQL的问题 #

1. 连续日期生成失败：仅通过聚合现有记录的年月生成序列，缺失无活动的月份
2. 统计范围错误：使用全表的最大结束日期而非指定福利的最大结束日期，导致统计到无关年月（如2024年）
3. 统计逻辑不符需求：直接count关联记录数，未按照“本月新增、上月结束减1”的规则计算活跃数

优化后的SQL #

-- 针对福利ID=2的优化查询
WITH Benefit2DateRange AS (
    -- 先获取福利ID=2的时间边界：最早开始月、最晚结束月
    SELECT 
        DATEFROMPARTS(YEAR(MIN(startDate)), MONTH(MIN(startDate)), 1) AS min_month,
        DATEFROMPARTS(YEAR(MAX(endDate)), MONTH(MAX(endDate)), 1) AS max_month
    FROM #MemberTable
    WHERE benefitId = 2
),
-- 递归生成连续的年月序列
ContinuousMonths AS (
    SELECT min_month AS month_date
    FROM Benefit2DateRange
    UNION ALL
    SELECT DATEADD(MONTH, 1, month_date)
    FROM ContinuousMonths
    CROSS JOIN Benefit2DateRange
    WHERE month_date < max_month
),
-- 计算每个月的增减量：本月新增数、上月结束数
MonthlyDelta AS (
    SELECT
        cm.month_date,
        -- 本月新增：startDate落在当前月的会员数（去重避免重复统计）
        COUNT(DISTINCT CASE WHEN DATEFROMPARTS(YEAR(t.startDate), MONTH(t.startDate), 1) = cm.month_date THEN t.memberId END) AS add_num,
        -- 本月减少：endDate落在上个月的会员数
        COUNT(DISTINCT CASE WHEN DATEFROMPARTS(YEAR(t.endDate), MONTH(t.endDate), 1) = DATEADD(MONTH, -1, cm.month_date) THEN t.memberId END) AS subtract_num
    FROM ContinuousMonths cm
    LEFT JOIN #MemberTable t ON t.benefitId = 2
    GROUP BY cm.month_date
),
-- 累计计算每月活跃数
ActiveMemberCounts AS (
    SELECT
        YEAR(month_date)*100 + MONTH(month_date) AS YEAR_MONTH,
        2 AS benefitId,
        -- 从第一个月开始累计增减量
        SUM(add_num - subtract_num) OVER (ORDER BY month_date) AS active_b_count
    FROM MonthlyDelta
)
SELECT YEAR_MONTH, benefitId, active_b_count
FROM ActiveMemberCounts
ORDER BY YEAR_MONTH;

优化说明 #

• 精准时间范围：仅针对福利ID=2计算时间边界，避免其他福利数据干扰
• 连续年月生成：用递归CTE生成完整的年月序列，确保无缺失
• 符合需求的统计逻辑：先计算每月的新增/减少量，再通过窗口函数累计求和，严格匹配“开始加1、结束减1”的规则
• 去重处理：用COUNT(DISTINCT)避免同一会员因多条同福利记录被重复统计

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