能否用Oracle SQL将日粒度数据生成连续值日期范围？需用哪些函数？

源数据 #

id     date     value
1      01.08.22 a
1      02.08.22 a
1      03.08.22 a
1      04.08.22 b
1      05.08.22 b
1      06.08.22 a
1      07.08.22 a
2      01.08.22 a
2      02.08.22 a
2      03.08.22 c
2      04.08.22 a
2      05.08.22 a

期望输出 #

id     date_from     date_until    value
1      01.08.22      03.08.22      a
1      04.08.22      05.08.22      b
1      06.08.22      07.08.22      a
2      01.08.22      02.08.22      a     
2      03.08.22      03.08.22      c
2      04.08.22      05.08.22      a

实现方案 #

完全可以通过Oracle SQL实现该需求，核心是识别同一id下连续出现的相同value组，主要用到以下函数：

• ROW_NUMBER()：窗口函数，生成分组内行号，辅助标记连续分组
• MIN()/MAX()：聚合函数，提取每组的起止日期

具体SQL代码 #

假设源表名为your_table，date列存储字符串格式日期，代码如下：

SELECT
    id,
    TO_CHAR(MIN(TO_DATE(date, 'DD.MM.RR')), 'DD.MM.RR') AS date_from,
    TO_CHAR(MAX(TO_DATE(date, 'DD.MM.RR')), 'DD.MM.RR') AS date_until,
    value
FROM (
    SELECT
        id,
        date,
        value,
        -- 计算分组标识：同一id全局行号减去同一id+value内的行号，差值相同即为连续组
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY TO_DATE(date, 'DD.MM.RR')) 
        - ROW_NUMBER() OVER (PARTITION BY id, value ORDER BY TO_DATE(date, 'DD.MM.RR')) AS group_id
    FROM your_table
) t
GROUP BY id, value, group_id
ORDER BY id, TO_DATE(date_from, 'DD.MM.RR');

代码说明 #

1. 子查询中通过两个ROW_NUMBER()的差值生成group_id：同一id下，连续相同value的记录会拥有相同的group_id，value变化时group_id随之改变。
2. 外层查询按id、value、group_id分组，用MIN()和MAX()分别获取每组的起始、结束日期，再格式化为原字符串样式输出。
3. 最后按id和起始日期排序，保证结果顺序符合预期。

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