SQL Server如何查询用户累计交易额达到指定阈值的对应日期

基础说明 #

现有存储交易记录的数据表，核心字段如下：

• User：用户唯一标识
• Date：交易发生日期
• Transaction amount：单条交易的金额

提示：User属于SQL Server内置关键字，实际查询时需用方括号[]包裹避免语法报错

需求目标 #

返回每个用户累计交易金额首次达到指定阈值（示例阈值为100）的对应日期，后续用户累计金额超过阈值后，结果固定为首次达标的日期，不会更新为后续交易日期。

实现SQL代码 #

-- 阈值变量，可根据业务需求修改数值
DECLARE @Threshold DECIMAL(18,2) = 100.00;

WITH CumulativeTran AS (
    SELECT
        [User],
        [Date],
        -- 按用户分组、交易日期升序，计算到当前交易为止的累计金额
        SUM([Transaction amount]) OVER (
            PARTITION BY [User]
            ORDER BY [Date]
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS cumulative_amount
    FROM 你的交易表名 -- 替换为实际的交易表名称
),
FirstReach AS (
    SELECT
        [User],
        [Date],
        -- 同一用户的达标记录按日期升序排序，取第一条即可得到首次达标记录
        ROW_NUMBER() OVER (
            PARTITION BY [User]
            ORDER BY [Date] ASC
        ) AS rn
    FROM CumulativeTran
    WHERE cumulative_amount >= @Threshold
)
SELECT 
    [User] AS 用户标识,
    [Date] AS 首次达标日期
FROM FirstReach
WHERE rn = 1;

逻辑说明 #

1. 第一层CTECumulativeTran计算每个用户按交易时间排序的累计金额，即使同一用户同一天有多笔交易也能正确累加
2. 第二层CTEFirstReach筛选出所有累计金额达标的记录，给同一用户的达标记录按时间排序取序号为1的记录，保证返回的是最早的达标日期
3. 阈值单独声明为变量，后续调整阈值时仅需要修改变量值即可，不需要改动查询逻辑

扩展说明：如果需要同时返回累计金额尚未达标的用户，可将最终查询改为左关联用户全量表，未达标用户的首次达标日期字段会返回NULL。

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