SQL YTD去重计数异常求助：月度与YTD结果一致如何修复

我尝试通过以下SQL代码计算YTD（年初至今）去重账户数，但发现月度活跃账户数（MonthlyActiveAccounts）与YTD活跃账户数（YTDActiveAccounts）结果完全相同，不符合预期。

原SQL代码：

SELECT 
COUNT ( DISTINCT ua.AccountId ) MonthlyActiveAccounts
,   FORMAT( ua.FilePathDate, 'yyyy-MM-01') Dt
,   COALESCE ( a.RegisteredCountry, 'Belgium' ) RegisteredCountry
,   COUNT ( DISTINCT YTD.AccountId ) YTDActiveAccounts

FROM [dbo].[incremental_UserActions] ua
LEFT JOIN [dbo].[normalized_Account] a
ON ua.AccountId = a.AccountId

LEFT JOIN [dbo].[incremental_UserActions]  YTD
ON ua.AccountId = YTD.AccountId
and datediff(year, YTD.FilePathDate, ua.FilePathDate) = 0 
and YTD.FilePathDate <= ua.FilePathDate

GROUP BY 
FORMAT( ua.FilePathDate, 'yyyy-MM-01'),
COALESCE ( a.RegisteredCountry, 'Belgium' ) 

ORDER BY 2 , 3 

示例数据：

期望输出：

01/01/22 --> YTD 2 , Monthly --> 2
01/02/22 --> YTD 3 , Monthly --> 1 

问题原因 #

原SQL的核心问题是自连接逻辑错误：YTD表仅关联了当前行对应的AccountId，每个ua的账户只能匹配到自身的历史记录，导致COUNT(DISTINCT YTD.AccountId)和COUNT(DISTINCT ua.AccountId)结果完全一致，根本没有统计到当前月份之前所有账户的YTD数据。

修复方案 #

方案一：窗口函数实现（推荐） #

先提取月度去重账户，再用累计窗口函数统计YTD数据，逻辑简洁高效：

WITH MonthlyUniqueAccounts AS (
    SELECT 
        FORMAT(ua.FilePathDate, 'yyyy-MM-01') AS Dt,
        COALESCE(a.RegisteredCountry, 'Belgium') AS RegisteredCountry,
        ua.AccountId
    FROM [dbo].[incremental_UserActions] ua
    LEFT JOIN [dbo].[normalized_Account] a 
        ON ua.AccountId = a.AccountId
    GROUP BY FORMAT(ua.FilePathDate, 'yyyy-MM-01'), COALESCE(a.RegisteredCountry, 'Belgium'), ua.AccountId
)
SELECT 
    Dt,
    RegisteredCountry,
    COUNT(AccountId) AS MonthlyActiveAccounts,
    COUNT(DISTINCT AccountId) OVER (
        PARTITION BY RegisteredCountry, YEAR(Dt)
        ORDER BY Dt
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS YTDActiveAccounts
FROM MonthlyUniqueAccounts
GROUP BY Dt, RegisteredCountry
ORDER BY Dt, RegisteredCountry;

方案二：先聚合月度再关联YTD范围 #

先完成月度账户统计，再关联当前年份内所有早于等于当前月份的账户数据：

WITH MonthlyAgg AS (
    SELECT 
        FORMAT(ua.FilePathDate, 'yyyy-MM-01') AS Dt,
        COALESCE(a.RegisteredCountry, 'Belgium') AS RegisteredCountry,
        COUNT(DISTINCT ua.AccountId) AS MonthlyActiveAccounts
    FROM [dbo].[incremental_UserActions] ua
    LEFT JOIN [dbo].[normalized_Account] a 
        ON ua.AccountId = a.AccountId
    GROUP BY FORMAT(ua.FilePathDate, 'yyyy-MM-01'), COALESCE(a.RegisteredCountry, 'Belgium')
)
SELECT 
    ma.Dt,
    ma.RegisteredCountry,
    ma.MonthlyActiveAccounts,
    COUNT(DISTINCT ua.AccountId) AS YTDActiveAccounts
FROM MonthlyAgg ma
LEFT JOIN [dbo].[incremental_UserActions] ua 
    ON YEAR(ua.FilePathDate) = YEAR(ma.Dt)
    AND FORMAT(ua.FilePathDate, 'yyyy-MM-01') <= ma.Dt
LEFT JOIN [dbo].[normalized_Account] a 
    ON ua.AccountId = a.AccountId
    AND COALESCE(a.RegisteredCountry, 'Belgium') = ma.RegisteredCountry
GROUP BY ma.Dt, ma.RegisteredCountry, ma.MonthlyActiveAccounts
ORDER BY ma.Dt, ma.RegisteredCountry;

方案说明 #

• 方案一通过GROUP BY先去重月度账户，再用窗口函数按国家和年份分区，累计统计到当前月份的所有去重账户，性能更优。
• 方案二先完成月度聚合，再通过关联筛选当前年份内的历史数据，适合对窗口函数不熟悉的场景。

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