Oracle GROUP BY按日期分组异常，需获取成员OE时间区间

当按id和OE分组查询成员在各OE的时间区间时，现有语句SELECT id, min(date) as mind, max(date) as maxd,OE FROM <table> GROUP BY id,oe ORDER BY mind desc会将同一成员同一OE的非连续日期段合并，无法正确展示OE变更的时间线（比如成员从OE1转到OE2再转回OE1，两次OE1的时间会被合并成一个区间）。需要获取分段的连续时间区间。

解决方案 #

利用窗口函数给连续的同一OE记录标记分组，再基于分组计算时间区间。以下是通用的SQL实现（适用于支持窗口函数的数据库如MySQL 8.0+、PostgreSQL、SQL Server等）：

WITH ranked_records AS (
    SELECT 
        id,
        date,
        OE,
        -- 按成员ID和日期排序的全局编号
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY date) AS global_row,
        -- 按成员ID、OE和日期排序的分组编号
        ROW_NUMBER() OVER (PARTITION BY id, OE ORDER BY date) AS oe_row
    FROM <table>
),
grouped_intervals AS (
    SELECT 
        id,
        OE,
        date,
        -- 用两个编号的差作为连续区间的分组标识
        global_row - oe_row AS interval_group
    FROM ranked_records
)
SELECT 
    id,
    OE,
    MIN(date) AS mind,
    MAX(date) AS maxd
FROM grouped_intervals
GROUP BY id, OE, interval_group
ORDER BY id, mind;

原理说明 #

1. ranked_records阶段：给每个成员的所有记录按日期生成全局排序编号，同时给每个成员同一OE的记录单独生成排序编号。
2. grouped_intervals阶段：两个编号的差值会成为连续区间的唯一标识——同一成员连续处于同一OE时，差值保持不变；一旦OE切换，新的OE段会生成新的差值。
3. 最终按id、OE和interval_group分组，即可得到每个连续OE段的起始和结束日期。

旧版本数据库兼容写法（无CTE） #

如果数据库不支持WITH语句，可以用子查询嵌套实现：

SELECT 
    id,
    OE,
    MIN(date) AS mind,
    MAX(date) AS maxd
FROM (
    SELECT 
        id,
        date,
        OE,
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY date) 
        - ROW_NUMBER() OVER (PARTITION BY id, OE ORDER BY date) AS interval_group
    FROM <table>
) AS temp
GROUP BY id, OE, interval_group
ORDER BY id, mind;

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