SQL Server 2008大表每日用户等级统计实现方案问询

现有表结构与数据 #

[game].[dbo].[gamer]表存储用户等级变更记录，结构及示例数据如下：

name     record_date   date_order   level  
Bob      2023-01-03    1            silver
Bob      2023-01-02    2            silver
Bob      2022-12-31    3            gold
Heidi    2023-01-03    1            brown
Heidi    2023-01-01    2            silver

等级生效逻辑 #

每条记录代表用户等级的变更节点：date_order=1是最新记录，等级从当前记录的record_date开始生效，直到下一次等级变更的前一天；如果是用户最早的记录（最大date_order），等级则从该记录的record_date向前覆盖到统计起始日期。

示例说明：

• Heidi：2023-01-01 ~ 2023-01-02 处于silver等级，2023-01-03起处于brown等级
• Bob：2022-12-31 ~ 2023-01-01 处于gold等级，2023-01-02 ~ 2023-01-03 处于silver等级

统计需求 #

统计指定日期范围（示例：2022-12-30至2023-01-03；实际：2018-01-01至2023-11-13）内，每日各等级的用户数量，并将结果插入新表[game].[dbo].[level_count]。

预期结果示例：

date          level        count
2022-12-31    gold         1
2023-01-01    gold         1  
2023-01-01    silver       1  
2023-01-02    silver       2
2023-01-03    silver       1
2023-01-03    brown        1

已有初步代码 #

已实现日期范围生成的CTE，但需完善等级统计逻辑：

WITH Dates AS (
         SELECT
         [Date] = CONVERT(DATETIME,'2022-12-30')
         UNION ALL SELECT
         [Date] = DATEADD(DAY, 1, [Date])
         FROM
         Dates
         WHERE
         Date < '2023-01-04'
 )  
 insert into [game].[dbo].[level_count]
 SELECT Date 
 --use Date here to count how many people are on level X that date from [game].[dbo].[gamer]
 FROM Dates

高效实现代码（适配SQL Server 2008） #

由于SQL Server 2008不支持LAG()/LEAD()函数，需通过子查询获取用户的下一次等级变更日期，结合日期表完成统计。同时针对1800万行数据做性能优化：

步骤1：创建性能优化索引 #

先给gamer表创建复合索引，避免全表扫描：

CREATE NONCLUSTERED INDEX IX_gamer_name_date_order 
ON [game].[dbo].[gamer] (name, date_order)
INCLUDE (record_date, level);

步骤2：完整统计插入代码 #

-- 定义统计日期范围
DECLARE @StartDate DATETIME = '2018-01-01';
DECLARE @EndDate DATETIME = '2023-11-13';

WITH Dates AS (
    -- 生成日期序列，MAXRECURSION 0 支持超过100天的范围
    SELECT @StartDate AS [Date]
    UNION ALL
    SELECT DATEADD(DAY, 1, [Date])
    FROM Dates
    WHERE [Date] < @EndDate
),
-- 处理用户等级的生效区间
UserLevelRanges AS (
    SELECT 
        g.name,
        g.level,
        -- 等级生效起始日期：取当前记录日期与统计起始日期的最大值
        CASE WHEN g.record_date < @StartDate THEN @StartDate ELSE g.record_date END AS LevelStartDate,
        -- 等级生效结束日期：下一条记录的前一天，若无下一条则为统计结束日期
        ISNULL(DATEADD(DAY, -1, g_next.record_date), @EndDate) AS LevelEndDate
    FROM [game].[dbo].[gamer] g
    -- 关联获取用户的下一条更早的等级记录（date_order+1）
    LEFT JOIN [game].[dbo].[gamer] g_next 
        ON g.name = g_next.name 
        AND g.date_order = g_next.date_order - 1
)
-- 插入统计结果到目标表
INSERT INTO [game].[dbo].[level_count] ([date], [level], [count])
SELECT 
    d.[Date],
    ulr.level,
    COUNT(DISTINCT ulr.name) AS [count]
FROM Dates d
-- 关联日期与等级生效区间
JOIN UserLevelRanges ulr 
    ON d.[Date] BETWEEN ulr.LevelStartDate AND ulr.LevelEndDate
GROUP BY d.[Date], ulr.level
ORDER BY d.[Date], ulr.level
OPTION (MAXRECURSION 0); -- 解除递归CTE的天数限制

性能说明 #

• 索引IX_gamer_name_date_order确保按用户分组查找下一条记录时无需全表扫描；
• 使用COUNT(DISTINCT name)避免同一用户在同一天被重复统计；
• 递归CTE是SQL Server 2008中高效生成连续日期的方法，OPTION (MAXRECURSION 0)解决大日期范围的递归次数限制；
• 若数据量过大导致单次执行超时，可考虑按月份分批统计插入，减少单次处理的数据量。

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