如何高效查找累计总额首次转为负值的最近日期（SQL）

我现有SQL代码可计算累计总额（running total）为负的最近日期，但实际需要查找累计总额首次转为负值的最近一次日期。想请教是否可以不用临时表（temp table）或公共表表达式（CTE）实现该需求，以此保障查询性能？

现有实现代码 #

declare @CustomerCode int=2134

select CustomerCode, CustomerType, max(PaymentDate) as MaxPaymentDate
from (
    select b.CustomerCode, b.CustomerType, b.PaymentDate from Payments as a
    join Payments as b
    on a.CustomerCode = b.CustomerCode and a.CustomerType = b.CustomerType
    where b.PaymentDate <= a.PaymentDate
        AND a.CustomerCode = @CustomerCode 
    group by b.CustomerCode, b.CustomerType, b.PaymentDate
    having sum(b.paymentamount) <= 0
) as T
group by CustomerCode, CustomerType

示例数据与期望结果 #

期望返回2023-01-07，因为这是累计总额首次转为负值的最近一次记录（2023-01-03转负后后续累计回到正值，2023-01-07是再次首次转负的最近日期）。

可以不用临时表/CTE，直接通过窗口函数实现，性能远优于原代码的自连接方案（原代码为O(n²)复杂度，窗口函数为O(n)）。

优化后的SQL代码 #

DECLARE @CustomerCode INT = 2134;

SELECT TOP 1
    CustomerCode,
    CustomerType,
    PaymentDate AS FirstNegativeRunningTotalDate
FROM (
    SELECT
        CustomerCode,
        CustomerType,
        PaymentDate,
        -- 计算截至当前日期的累计总额
        SUM(PaymentAmount) OVER (PARTITION BY CustomerCode, CustomerType ORDER BY PaymentDate) AS RunningTotal,
        -- 获取上一个日期的累计总额，用于判断是否首次转负
        LAG(SUM(PaymentAmount) OVER (PARTITION BY CustomerCode, CustomerType ORDER BY PaymentDate), 1, 0) 
            OVER (PARTITION BY CustomerCode, CustomerType ORDER BY PaymentDate) AS PreviousRunningTotal
    FROM Payments
    WHERE CustomerCode = @CustomerCode
) AS RunningTotals
-- 筛选首次转负的记录：当前累计≤0，且上一期累计>0
WHERE RunningTotal <= 0 AND PreviousRunningTotal > 0
-- 取最近的一次转负记录
ORDER BY PaymentDate DESC;

代码说明 #

1. 高效计算累计总额：用SUM(...) OVER (...)窗口函数直接计算每个日期的累计值，避免了原代码的自连接分组操作，执行效率大幅提升。
2. 精准判断首次转负：通过LAG()窗口函数获取上一个日期的累计总额，当当前累计≤0且上一期累计>0时，即可判定为首次转为负值的日期。
3. 直接获取最近记录：通过ORDER BY PaymentDate DESC+TOP 1，无需额外分组取最大值，一步拿到最近的符合条件的记录。

性能保障 #

• 窗口函数是SQL引擎原生优化的操作，在大数据量场景下的执行效率远高于自连接分组。
• 建议为Payments表创建(CustomerCode, CustomerType, PaymentDate)的复合索引，让窗口函数计算直接走索引，避免全表扫描。

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