SQL查询生效日期区间数据 如何获取各月末对应最新账户行

你遇到的重复计数问题本质是没有为「每个统计日期+每个用户」筛选唯一的最新版本记录，当单个用户在统计日当天有多条变更记录时，所有符合时间范围的行都会被计入统计，导致结果偏大。

下面给出两种适配SQL Server环境的可行实现方案： #

方案1：CROSS APPLY 实现（性能更优，适合统计日期较少的场景） #

CROSS APPLY可以将外部的月末日期传入内部子查询，逐日期计算每个用户的最大有效版本，避免全局关联产生大量冗余数据。

SELECT 
    l.MonthEndDate,
    COUNT(ua.UserId) AS ActiveAccountCount
FROM LastDayOfMonth l
CROSS APPLY (
    -- 取当前统计日下每个用户的最大版本号
    SELECT 
        UserId,
        MAX([Row Version]) AS MaxRowVersion
    FROM star_UserAccounts u
    WHERE 
        -- 账户生效时间早于等于统计日
        u.InsertedDate <= l.MonthEndDate 
        -- 账户失效时间晚于等于统计日，说明统计日当天该版本有效
        AND u.ValidToDate >= l.MonthEndDate
    GROUP BY UserId
) max_version
-- 关联回原表取对应用户的最新版本记录
INNER JOIN star_UserAccounts ua
    ON ua.UserId = max_version.UserId
    AND ua.[Row Version] = max_version.MaxRowVersion
-- 过滤活跃状态，可根据实际业务规则调整
WHERE ua.UserStatus = 'active'
GROUP BY l.MonthEndDate
ORDER BY l.MonthEndDate

方案2：窗口函数实现（可读性更强，需要输出明细时更易调整） #

用ROW_NUMBER窗口函数按「统计日期+用户ID」分组，按版本号倒序排序后取第一条即为该用户当日的最新有效记录。

WITH user_daily_snapshot AS (
    SELECT 
        l.MonthEndDate,
        u.UserId,
        u.UserStatus,
        -- 同一统计日同一用户按版本号倒序排名
        ROW_NUMBER() OVER(
            PARTITION BY l.MonthEndDate, u.UserId 
            ORDER BY u.[Row Version] DESC
        ) AS row_rank
    FROM LastDayOfMonth l
    INNER JOIN star_UserAccounts u
        ON u.InsertedDate <= l.MonthEndDate
        AND u.ValidToDate >= l.MonthEndDate
)
SELECT 
    MonthEndDate,
    COUNT(UserId) AS ActiveAccountCount
FROM user_daily_snapshot
-- 取每个用户当日的最新版本
WHERE row_rank = 1
AND UserStatus = 'active'
GROUP BY MonthEndDate
ORDER BY MonthEndDate

注意事项 #

1. 如果你的ValidToDate是用9999-12-31这类最大值标记当前有效记录，上述时间过滤逻辑无需调整。
2. 如果统计日期字段包含时间部分，可以用CAST(l.MonthEndDate AS DATE)做日期截断避免匹配误差。

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