如何对同组连续相同费率的记录生成有效日期范围？

表结构与规则 #

我们有如下结构的Rates表：

CREATE TABLE Rates
(
   RateGroup int NOT NULL,
   Rate decimal(5, 2) NOT NULL,
   DueDate date NOT NULL
);

该表存储的费率自指定DueDate起生效，至下一个DueDate的前一天结束；若无后续DueDate，则无生效截止日期。同一费率组内可能存在连续日期的相同费率，也可能在非连续日期出现相同费率。同一DueDate可存在于多个费率组，但每组仅出现一次。

示例数据 #

插入示例数据的SQL语句：

INSERT INTO Rates(RateGroup, Rate, DueDate)
VALUES
      (1, 1.2, '20210101'), (1, 1.2, '20210215'), (1, 1.5, '20210216'),
      (1, 1.2, '20210501'), (2, 3.7, '20210101'), (2, 3.7, '20210215'),
      (2, 3.7, '20210216'), (2, 3.7, '20210501'), (3, 2.9, '20210101'),
      (3, 2.5, '20210215'), (3, 2.5, '20210216'), (3, 2.1, '20210501');

对应的数据表如下：

期望结果 #

需要将同一费率组内连续的相同费率记录合并为单条记录，包含该费率的生效起止日期，期望结果如下：

解决方案 #

可以通过窗口函数实现连续相同值的分组与聚合，具体SQL如下：

WITH RankedRates AS (
    SELECT 
        RateGroup,
        Rate,
        DueDate,
        -- 生成连续相同费率的分组标识
        ROW_NUMBER() OVER (PARTITION BY RateGroup ORDER BY DueDate) 
        - ROW_NUMBER() OVER (PARTITION BY RateGroup, Rate ORDER BY DueDate) AS GroupId
    FROM Rates
),
GroupedRates AS (
    SELECT 
        RateGroup,
        Rate,
        MIN(DueDate) AS StartDate
    FROM RankedRates
    GROUP BY RateGroup, Rate, GroupId
)
SELECT 
    gr.RateGroup,
    gr.Rate,
    gr.StartDate,
    -- 获取下一个分组的起始日期并减1，作为当前分组的结束日期
    DATEADD(day, -1, LEAD(gr.StartDate) OVER (PARTITION BY gr.RateGroup ORDER BY gr.StartDate)) AS EndDate
FROM GroupedRates gr
ORDER BY gr.RateGroup, gr.StartDate;

逻辑说明 #

1. RankedRates 阶段：通过两个ROW_NUMBER()窗口函数的差值生成分组ID。同一RateGroup内，连续相同的Rate会被分配相同的GroupId；当Rate发生变化时，GroupId会随之改变，从而实现连续相同费率的分组。
2. GroupedRates 阶段：按RateGroup、Rate、GroupId分组，取每组的最小DueDate作为该费率生效的起始日期。
3. 最终查询阶段：使用LEAD()窗口函数获取当前分组的下一个分组的起始日期，将其减1天得到当前费率的生效结束日期；如果是最后一个分组，LEAD()返回NULL，对应EndDate为NULL，符合需求。

内容的提问来源于stack exchange，提问作者Bill Tür stands with Ukraine