SQL Server中使用递归CTE透视实现成员状态滚动计数

你当前的透视查询仅统计了当日状态变更的会员数量，无法实现截止到每个日期的滚动聚合（即统计当天及之前所有会员的当前状态总数）。要实现需求，确实需要结合递归CTE处理时间序列，再配合透视转换，具体方案如下：

核心思路 #

每个会员的状态随时间更新，我们需要先确定每个目标日期下，所有会员的最新状态，再按日期和状态计数，最后转为列格式。

完整SQL代码 #

-- 递归CTE生成所有状态变更日期的序列
WITH DateList AS (
    SELECT MIN(st) AS dt FROM MyTable
    UNION ALL
    SELECT DATEADD(month, 1, dt) 
    FROM DateList
    WHERE DATEADD(month, 1, dt) <= (SELECT MAX(st) FROM MyTable)
),
-- 匹配每个日期下会员的最新状态
MemberStatusAtDate AS (
    SELECT 
        dl.dt,
        m.memberID,
        -- 取会员在当前日期前的最后一次状态
        FIRST_VALUE(mt.status) OVER (
            PARTITION BY m.memberID 
            ORDER BY mt.st DESC 
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        ) AS current_status
    FROM DateList dl
    -- 关联所有会员
    CROSS JOIN (SELECT DISTINCT memberID FROM MyTable) m
    -- 关联该会员在当前日期及之前的状态记录
    LEFT JOIN MyTable mt ON m.memberID = mt.memberID AND mt.st <= dl.dt
)
-- 透视转换为状态列，计算滚动计数
SELECT 
    dt AS st,
    ISNULL([1], 0) AS [1],
    ISNULL([2], 0) AS [2]
FROM (
    SELECT dt, current_status 
    FROM MemberStatusAtDate
    WHERE current_status IS NOT NULL -- 过滤还未设置状态的会员
) AS Src
PIVOT (
    COUNT(current_status) 
    FOR current_status IN ([1], [2])
) AS PivotTable
ORDER BY dt;

代码说明 #

1. DateList：递归生成表中所有的状态变更日期（示例按月递增，若需连续日可改为DATEADD(day,1,dt)）。
2. MemberStatusAtDate：通过FIRST_VALUE窗口函数，获取每个会员在当前日期前的最后一次状态，确定该日期时的会员状态。
3. 透视查询：将状态按列展示，统计每个日期下各状态的会员总数，用ISNULL处理空值为0。

执行结果 #

运行后将得到符合需求的滚动计数：

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