在Oracle中对重叠的时间间隔进行分组 [英] Grouping overlapping time intervals in Oracle

问题描述

在Oracle 10g中有一个包含列date_from和date_to的数据集(或者因为长度是常数，所以只是date_from).

There is a dataset with columns date_from and date_to in Oracle 10g (or rather just date_from, because the length is constant).

我需要折叠重叠的间隔，例如这些行:

I need to collapse overlapping intervals, e.g. these rows:

date_from  date_to
2015-01-01 2015-01-10
2015-01-03 2015-01-11

需要成为:

2015-01-01 2015-01-11

我想知道是否有一种方法可以在不使用游标的情况下一次性进行整齐的查询...我的查询量很大，在边缘情况下仍然无法折叠所有内容.也许Oracle 10g中有一些SQL扩展可以解决我可以使用的此类问题?

I wonder if there is a way to query this neatly in one go without using cursors... my query is huge and still doesn't collapse everything in edge cases. Maybe there is some SQL extension in Oracle 10g for problems like this that I can use?

推荐答案

WITH data (date_from, date_to) AS (
    SELECT DATE'2015-01-01', DATE'2015-01-10' FROM DUAL UNION ALL
    SELECT DATE'2015-01-03', DATE'2015-01-11' FROM DUAL
)
SELECT
    min(date_from) date_from, max(date_to) date_to 
FROM (
    SELECT
        date_from, date_to,
        sum(merge) OVER (ORDER BY date_from) group_id
    FROM (
        SELECT
            date_from, date_to,
            case when date_from <= lag(date_to) OVER (ORDER BY date_from)
                 THEN 0
                 ELSE 1
            end as merge
        FROM data)
    ) intervals
GROUP BY group_id
ORDER BY min(intervals.date_from);

这篇关于在Oracle中对重叠的时间间隔进行分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！