基于Azure Synapse的滚动3个月周期会员统计SQL实现问询

需求明确 #

基于Appointments表（含MemberID、DateOfConsultation字段），实现以下统计：

• 滚动3个月周期：以当月为周期末月，覆盖前两个月（如2024年12月周期包含2024-10、11、12三个月的记录）
• 每个周期需输出两个指标：
  1. 周期内的唯一会员总数
  2. 周期内咨询次数≥2的唯一会员数

解决方案 #

采用分层CTE（公共表表达式）实现，逻辑清晰且适配Azure Synapse的分布式查询优化，避免自连接带来的重复计算问题：

WITH Periods AS (
    -- 提取所有存在咨询记录的月份作为周期末月
    SELECT DISTINCT
        DATEADD(MONTH, DATEDIFF(MONTH, 0, DateOfConsultation), 0) AS PeriodEndMonth
    FROM Appointments
),
MemberPeriodStats AS (
    -- 统计每个会员在对应周期内的咨询次数
    SELECT
        p.PeriodEndMonth,
        a.MemberID,
        COUNT(*) AS ConsultationCount
    FROM Periods p
    LEFT JOIN Appointments a
        -- 筛选周期内的记录：周期末月前推2个月的起始日，到周期末月的下一个月起始日
        ON a.DateOfConsultation >= DATEADD(MONTH, -2, p.PeriodEndMonth)
        AND a.DateOfConsultation < DATEADD(MONTH, 1, p.PeriodEndMonth)
    GROUP BY p.PeriodEndMonth, a.MemberID
    HAVING a.MemberID IS NOT NULL -- 排除无会员数据的周期
)
-- 聚合计算最终统计指标
SELECT
    PeriodEndMonth,
    COUNT(DISTINCT MemberID) AS TotalUniqueMembers,
    SUM(CASE WHEN ConsultationCount >= 2 THEN 1 ELSE 0 END) AS MembersWith2PlusConsultations
FROM MemberPeriodStats
GROUP BY PeriodEndMonth
ORDER BY PeriodEndMonth;

代码说明 #

1. Periods CTE：从Appointments表中提取所有有咨询记录的月份，作为统计的周期末月集合，确保不会遗漏任何有数据的周期。
2. MemberPeriodStats CTE：将周期表与咨询表关联，通过日期范围筛选出每个周期内的会员记录，统计每个会员在该周期内的咨询次数。
3. 最终聚合：基于会员级别的统计结果，计算每个周期的总唯一会员数，以及咨询次数≥2的会员数（通过CASE语句标记符合条件的会员后求和）。

示例验证 #

针对你提到的2024年12月周期，若数据中存在4个唯一会员、其中3个咨询次数≥2，执行上述代码后，对应PeriodEndMonth为2024-12-01的行将返回：

• TotalUniqueMembers = 4
• MembersWith2PlusConsultations = 3

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