如何在SQL Server中计算值的存续时长（解决跨区间计数错误）

我有一份客户的每日费率清单，需要计算费率自上次变更以来的时长。当前查询存在问题：当费率匹配到非前一日的历史相同费率时，计算结果会出错。比如11月9日的费率本应计数为1，但因为匹配到了11月2日的相同费率，得到的结果变成了8。

以下是SQL Server中的示例代码：

WITH sample AS (
    SELECT *
    FROM (VALUES
        ('2023-11-15', '94-69867', 66.99, 7),
        ('2023-11-14', '94-69867', 66.99, 6),
        ('2023-11-13', '94-69867', 66.99, 5),
        ('2023-11-12', '94-69867', 66.99, 4),
        ('2023-11-11', '94-69867', 66.99, 3),
        ('2023-11-10', '94-69867', 66.99, 2),
        ('2023-11-09', '94-69867', 66.99, 1),
        ('2023-11-08', '94-69867', 60.99, 4),
        ('2023-11-07', '94-69867', 60.99, 3),
        ('2023-11-06', '94-69867', 60.99, 2),
        ('2023-11-05', '94-69867', 60.99, 1),
        ('2023-11-04', '94-69867', 65.99, 2),
        ('2023-11-03', '94-69867', 65.99, 1),
        ('2023-11-02', '94-69867', 66.99, 7),
        ('2023-11-01', '94-69867', 66.99, 6),
        ('2023-10-31', '94-69867', 66.99, 5),
        ('2023-10-30', '94-69867', 66.99, 4),
        ('2023-10-29', '94-69867', 66.99, 3),
        ('2023-10-28', '94-69867', 66.99, 2),
        ('2023-10-27', '94-69867', 66.99, 1)
    ) AS t (BusinessDate, PropertyFolio, RateToPost, Desired)
)
SELECT *
    ,COUNT(RateToPost) OVER( PARTITION BY PropertyFolio, RateToPost ORDER BY PropertyFolio ASC, BusinessDate ASC) AS FixMe
FROM sample
ORDER BY BusinessDate DESC

问题原因 #

原查询通过PARTITION BY PropertyFolio, RateToPost将所有相同费率的记录归为一组，不管中间是否有费率变更，导致跨区间的相同费率被错误合并，计数结果不符合预期。

解决方案 #

使用**分组岛（Gaps and Islands）**技术，先识别连续相同费率的区间，再在每个区间内计算连续时长：

WITH sample AS (
    SELECT *
    FROM (VALUES
        ('2023-11-15', '94-69867', 66.99, 7),
        ('2023-11-14', '94-69867', 66.99, 6),
        ('2023-11-13', '94-69867', 66.99, 5),
        ('2023-11-12', '94-69867', 66.99, 4),
        ('2023-11-11', '94-69867', 66.99, 3),
        ('2023-11-10', '94-69867', 66.99, 2),
        ('2023-11-09', '94-69867', 66.99, 1),
        ('2023-11-08', '94-69867', 60.99, 4),
        ('2023-11-07', '94-69867', 60.99, 3),
        ('2023-11-06', '94-69867', 60.99, 2),
        ('2023-11-05', '94-69867', 60.99, 1),
        ('2023-11-04', '94-69867', 65.99, 2),
        ('2023-11-03', '94-69867', 65.99, 1),
        ('2023-11-02', '94-69867', 66.99, 7),
        ('2023-11-01', '94-69867', 66.99, 6),
        ('2023-10-31', '94-69867', 66.99, 5),
        ('2023-10-30', '94-69867', 66.99, 4),
        ('2023-10-29', '94-69867', 66.99, 3),
        ('2023-10-28', '94-69867', 66.99, 2),
        ('2023-10-27', '94-69867', 66.99, 1)
    ) AS t (BusinessDate, PropertyFolio, RateToPost, Desired)
),
-- 标记费率变更点
rate_changes AS (
    SELECT 
        *,
        CASE 
            WHEN LAG(RateToPost) OVER (PARTITION BY PropertyFolio ORDER BY BusinessDate) = RateToPost THEN 0
            ELSE 1
        END AS is_new_rate
    FROM sample
),
-- 生成连续费率区间的组ID
rate_groups AS (
    SELECT 
        *,
        SUM(is_new_rate) OVER (PARTITION BY PropertyFolio ORDER BY BusinessDate) AS rate_group_id
    FROM rate_changes
)
-- 在每个区间内计算连续时长
SELECT 
    BusinessDate,
    PropertyFolio,
    RateToPost,
    Desired,
    COUNT(*) OVER (PARTITION BY PropertyFolio, rate_group_id ORDER BY BusinessDate) AS CorrectCount
FROM rate_groups
ORDER BY BusinessDate DESC;

代码说明 #

1. rate_changes：使用LAG函数对比当前记录与前一日的费率，标记是否为新的费率区间起点。
2. rate_groups：对变更标记进行累加，生成唯一的组ID，每个组对应一段连续的相同费率区间。
3. 最终查询：在每个费率组内按日期排序计数，得到自上次费率变更以来的连续时长，结果与Desired列完全匹配。

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