TSQL如何按连续日期分组合并同价格商品的有效期区间

问题原因 #

你当前使用的DENSE_RANK仅按article和price排序分组，会把全局所有相同商品+价格的记录分到同一组，无法区分被其他价格区间隔开的同价格段，这是典型的「数据孤岛」计算场景。

解决思路 #

通过两次窗口排序的差值生成唯一分组标识，区分被隔断的相同价格段：

1. 对同一商品的所有记录按valid_from升序生成全局行号
2. 对同一商品+相同价格的记录按valid_from升序生成组内行号
3. 两个行号的差值即可作为唯一分组ID：连续同价格的记录差值完全相同，被其他价格隔断的同价格记录差值不同

完整实现代码 #

DECLARE @TBL_DATA AS TABLE (
    valid_from DATE NOT NULL,
    valid_to DATE NOT NULL,
    article VARCHAR(20) NOT NULL,
    price FLOAT NOT NULL
)

INSERT INTO @TBL_DATA (
    valid_from,
    valid_to,
    article,
    price
)
VALUES
('2021-01-01','2021-01-02','AAA',10.99),
('2021-01-02','2021-01-03','AAA',10.99),
('2021-01-03','2021-01-05','AAA',10.99),
('2021-01-05','2021-01-06','AAA',12.99),
('2021-01-06','2021-01-09','AAA',12.99),
('2021-01-09','2021-01-14','AAA',10.99),
('2021-01-14','2021-01-15','AAA',10.99),
('2021-01-15','2021-01-16','AAA',10.99)

;WITH CTE_RN AS (
    SELECT 
        valid_from,
        valid_to,
        article,
        price,
        -- 同商品全局排序行号
        ROW_NUMBER() OVER(PARTITION BY article ORDER BY valid_from ASC) AS rn_all,
        -- 同商品同价格组内排序行号
        ROW_NUMBER() OVER(PARTITION BY article, price ORDER BY valid_from ASC) AS rn_group
    FROM @TBL_DATA
)
SELECT 
    MIN(valid_from) AS valid_from,
    MAX(valid_to) AS valid_to,
    article,
    price
FROM CTE_RN
GROUP BY article, price, rn_all - rn_group
ORDER BY valid_from ASC

输出结果 #

和预期完全一致：

扩展说明 #

如果实际业务场景中存在同商品同价格但日期间断不衔接的情况，额外增加相邻行日期连续性判断调整分组逻辑即可。

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