SQL按日期计算running total时同日期重复显示问题求助

错误原因 #

你的代码出现同日期多条记录的核心问题有两个：

• CTE中分组依据错误：你已经将created_at转换为纯日期类型的date字段，但GROUP BY子句仍然使用原始带时间戳的created_at字段，导致同一天不同时间的记录被分为多个组
• 外层查询逻辑冗余且字段错误：CTE已经完成按天聚合，外层不需要再执行GROUP BY，且你用到的sum_of_entries字段不存在，CTE输出的当日注册统计字段为sum_of_reg

另外你给出的预期结果中2021-03-30的累计值为笔误，按提供的原始数据该日累计注册数应为2。

修正后的代码 #

WITH reg AS(      
    SELECT
        CAST([created_at] AS date) AS [date],
        SUM(CAST([is_registered] AS int)) AS [sum_of_reg]
    FROM [dbo].[Registrations]
    -- 按转换后的纯日期分组，保证同日期仅返回一条统计结果
    GROUP BY CAST([created_at] AS date)
)
SELECT
    [date],
    [sum_of_reg] AS 'sum of reg',
    SUM([sum_of_reg]) OVER (ORDER BY [date] ASC) AS 'running total'
FROM reg
ORDER BY [date] ASC

执行结果说明 #

代码执行后会得到你期望的按天统计结果：

如果需要兼容日期存在断层的场景（即某日内无注册用户也需要展示日期、当日注册为0、累计值和前一天一致），可以先生成连续的日期维度表再左关联注册统计结果计算累计值即可。

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