Oracle中无逻辑分组条件时如何实现数据分组？

针对你需要将同一id、code、sinfo下的日期按“次日连续”规则分组，输出每个时间段的开始和结束日期的需求，用分析函数可以这样实现：

1. 模拟你的表数据 #

先构造测试数据集（实际使用时替换成你的表名即可）：

WITH test_data AS (
    SELECT 1 AS id, 'x' AS code, 'y' AS sinfo, TO_DATE('1-Jan-07', 'DD-Mon-RR') AS tdate FROM dual
    UNION ALL
    SELECT 1 AS id, 'x' AS code, 'y' AS sinfo, TO_DATE('6-Apr-07', 'DD-Mon-RR') AS tdate FROM dual
    UNION ALL
    SELECT 1 AS id, 'x' AS code, 'y' AS sinfo, TO_DATE('9-Apr-07', 'DD-Mon-RR') AS tdate FROM dual
)

2. 核心SQL实现 #

利用ROW_NUMBER()生成排序序号，通过日期减序号的方式生成分组标识——连续日期的该标识值相同，非连续的则不同，最后按分组聚合得到时间段：

,WITH test_data AS (
    SELECT 1 AS id, 'x' AS code, 'y' AS sinfo, TO_DATE('1-Jan-07', 'DD-Mon-RR') AS tdate FROM dual
    UNION ALL
    SELECT 1 AS id, 'x' AS code, 'y' AS sinfo, TO_DATE('6-Apr-07', 'DD-Mon-RR') AS tdate FROM dual
    UNION ALL
    SELECT 1 AS id, 'x' AS code, 'y' AS sinfo, TO_DATE('9-Apr-07', 'DD-Mon-RR') AS tdate FROM dual
),
grouped_data AS (
    SELECT 
        id,
        code,
        sinfo,
        tdate,
        -- 生成分组标识：连续日期的grp值完全一致
        tdate - ROW_NUMBER() OVER (PARTITION BY id, code, sinfo ORDER BY tdate) AS grp
    FROM test_data
)
SELECT 
    id,
    code,
    sinfo,
    MIN(tdate) AS start_date,
    MAX(tdate) AS end_date
FROM grouped_data
GROUP BY id, code, sinfo, grp
ORDER BY start_date;

3. 执行结果 #

针对你的原始数据，输出结果如下：

如果存在连续日期（比如新增7-Apr-07的记录），SQL会自动合并连续时间段：

关键逻辑解释 #

• PARTITION BY id, code, sinfo：限定只在相同维度的记录内进行分组计算
• ROW_NUMBER() OVER (...)：按日期升序生成连续序号，确保日期排序后序号连续
• tdate - ROW_NUMBER()：连续日期的计算结果相同，以此作为分组依据，将所有次日相连的日期归为同一组
• 最后通过GROUP BY聚合分组，取每组的最小日期作为开始、最大日期作为结束

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