Oracle SQLで、期間が重なるレコード同士をマージしてSELECTしたい。

Question

oracle sqlで、以下の実現するSQLをご教授お願い致します。

以下のようなテーブルデータがあるとき、
列3(start_date)と列4(end_date)の日時が重なる場合、
それらをマージした状態でSELECTしたいです。
この時、マージ対象となるレコードは、
列1,列2のセットが同じ値のものとします。
以下の例ですと、(C1,D1)と(C1,D2)のペア単位でマージとなります。

CREATE TABLE KIKAN_MERGE_TB
(
   C1 VARCHAR2(10),
   C2 VARCHAR2(10),
   C3 DATE,
   C4 DATE
)

INSERT ALL
INTO KIKAN_MERGE_TB(C1,C2,C3,C4) VALUES('C1','D1',TO_DATE('2020/05/01 09:00','YYYY/MM/DD HH24:MI'), TO_DATE('2020/05/01 12:00','YYYY/MM/DD HH24:MI'))
INTO KIKAN_MERGE_TB(C1,C2,C3,C4) VALUES('C1','D1',TO_DATE('2020/05/01 11:00','YYYY/MM/DD HH24:MI'), TO_DATE('2020/05/02 10:00','YYYY/MM/DD HH24:MI'))
INTO KIKAN_MERGE_TB(C1,C2,C3,C4) VALUES('C1','D1',TO_DATE('2020/04/30 07:00','YYYY/MM/DD HH24:MI'), TO_DATE('2020/05/03 11:00','YYYY/MM/DD HH24:MI'))
INTO KIKAN_MERGE_TB(C1,C2,C3,C4) VALUES('C1','D1',TO_DATE('2020/05/03 10:00','YYYY/MM/DD HH24:MI'), TO_DATE('2020/05/04 11:00','YYYY/MM/DD HH24:MI'))
INTO KIKAN_MERGE_TB(C1,C2,C3,C4) VALUES('C1','D1',TO_DATE('2020/05/04 18:00','YYYY/MM/DD HH24:MI'), TO_DATE('2020/05/05 11:00','YYYY/MM/DD HH24:MI'))
INTO KIKAN_MERGE_TB(C1,C2,C3,C4) VALUES('C1','D2',TO_DATE('2020/05/01 09:00','YYYY/MM/DD HH24:MI'), TO_DATE('2020/05/01 12:00','YYYY/MM/DD HH24:MI'))
INTO KIKAN_MERGE_TB(C1,C2,C3,C4) VALUES('C1','D2',TO_DATE('2020/05/01 12:00','YYYY/MM/DD HH24:MI'), TO_DATE('2020/05/02 10:00','YYYY/MM/DD HH24:MI'))
INTO KIKAN_MERGE_TB(C1,C2,C3,C4) VALUES('C1','D2',TO_DATE('2020/05/03 12:00','YYYY/MM/DD HH24:MI'), TO_DATE('2020/05/04 18:00','YYYY/MM/DD HH24:MI'))
INTO KIKAN_MERGE_TB(C1,C2,C3,C4) VALUES('C1','D2',TO_DATE('2020/05/05 10:00','YYYY/MM/DD HH24:MI'), TO_DATE('2020/05/05 15:00','YYYY/MM/DD HH24:MI'))
INTO KIKAN_MERGE_TB(C1,C2,C3,C4) VALUES('C1','D2',TO_DATE('2020/05/05 14:00','YYYY/MM/DD HH24:MI'), TO_DATE('2020/05/05 18:00','YYYY/MM/DD HH24:MI'))
select  * from dual;

列1 列2 　列3 　　　　列4
C1 D1 2020/05/01 09:00 2020/05/01 12:00
C1 D1 2020/05/01 11:00 2020/05/02 10:00
C1 D1 2020/04/30 07:00 2020/05/03 11:00
C1 D1 2020/05/03 10:00 2020/05/04 11:00
C1 D1 2020/05/04 18:00 2020/05/05 11:00 ※
C1 D2 2020/05/01 09:00 2020/05/01 12:00
C1 D2 2020/05/01 12:00 2020/05/02 10:00
C1 D2 2020/05/03 12:00 2020/05/04 18:00 ※
C1 D2 2020/05/05 10:00 2020/05/05 15:00
C1 D2 2020/05/05 14:00 2020/05/05 18:00

⇓マージ

列1 列2 　 列3 　　　 列4
C1 D1 2020/04/30 09:00 2020/05/04 11:00
C1 D1 2020/05/04 18:00 2020/05/05 11:00　※
C1 D2 2020/05/01 09:00 2020/05/02 10:00
C1 D2 2020/05/03 12:00 2020/05/04 18:00　※
C1 D2 2020/05/05 10:00 2020/05/05 18:00

※のレコードは、start_date,end_date共に、重なりがないため、そのまま表示となっております。

よろしくお願い致します。

Comments

[Orlofsky]

SQLの動作確認が必要な質問はSQLを実行し易いようにCREATE TABLとINSERTに https://teratail.com/help/question-tips#questionTips3-7 の [コード] に修正できた方が適切なコメントが付き易いかと。

[abcmart]

コード追記しました。

[KOZ6.0]

「「開始日付」と「終了日付」で管理しているデータをSQLで期間集約するには?」
https://qiita.com/toronya/items/8fdd99717b78ccb0bdd2
ウインドウ関数を駆使すれば可能なようです。
自分だったらストアド作って一時表に書き込むか、テーブル・ファンクションを使うと思います。

[abcmart]

いただいたサイトページ内のページ真ん中あたりの”日付が連続しているか判定する”における、
開始差有と終了差有の'0'or'1'を決めるcase式を変える認識のように見えますが、具体的な式はわかりますか？

[KOZ6.0]

そこはちょっと考えてみてくださいな。

[abcmart]

いただいたサイトのSQLで検証しました。
サイト内の例のようなデータを作成(start_date = end_date +1日)でまずは検証し、抽出できることは確認できました。
※２つ目のPARTITION BYに２項目(サイト例でいう食物)を自分で付け足さないと正しい値を取らないようです。。サイトの例のSQLをそのままコピペしてもだめでした。
一方で、今回の本目的(重なるデータをまとめる)はまだ試せておりません。

[Orlofsky]

将来のバージョンではエラーになるかもしれないので、データと書式マスクは一致させておいた方が良いのでは？

TO_DATE('2020/05/01 09:00','YYYY-MM-DD HH24:MI:SS')
↓
TO_DATE('2020/05/01 09:00','YYYY/MM/DD HH24:MI')

[abcmart]

参考にさせていただきます。

[Orlofsky]

初心者もこの掲示板を見ることありますから、きちんと直しては？
実務でこんなコードを書いていたら信用をなくしますし。

Answer 1

もっと良い方法があるような気がしますが、これでできました。
が、性能要件や開発上の制約が許すのであれば、
個人的には表関数なりSELECTするプログラム側でループするなりして
プログラムで何とかした方が良い気がします。
想定結果の１行目の列3は2020/04/30 07:00の誤りですね。

WITH
    T1 AS (
        --C1,C2ごとにC3,C4でソートした時の１行前のC3とC4を取得
        SELECT
            T.C1 C1
        ,   T.C2 C2
        ,   T.C3 C3
        ,   T.C4 C4
        ,   LAG(T.C3) OVER(PARTITION BY T.C1,T.C2 ORDER BY T.C3,T.C4) LAG_C3
        ,   LAG(T.C4) OVER(PARTITION BY T.C1,T.C2 ORDER BY T.C3,T.C4) LAG_C4
        FROM
            KIKAN_MERGE_TB T
    )
,   T2 AS (
        --1行前までのC3の最小値、1行前までのC4の最大値を求める
        SELECT
            T1.C1 C1
        ,   T1.C2 C2
        ,   T1.C3 C3
        ,   T1.C4 C4
        ,   MIN(T1.LAG_C3) OVER(PARTITION BY T1.C1,T1.C2 ORDER BY T1.C3,T1.C4) MIN_LAG_C3
        ,   MAX(T1.LAG_C4) OVER(PARTITION BY T1.C1,T1.C2 ORDER BY T1.C3,T1.C4) MAX_LAG_C4
        FROM
            T1
    )
,   T3 AS (
        --C3,1行前までのC3の最小値,C4,一行前までのC4の最大値を使用し、重複判定を行い、重複していなければカウントアップする(GRP_KEY)
        SELECT
            T2.C1 C1
        ,   T2.C2 C2
        ,   T2.C3 C3
        ,   T2.C4 C4
        ,   SUM(
                CASE 
                    WHEN T2.C3 <= T2.MAX_LAG_C4 AND T2.C4 >= T2.MIN_LAG_C3 
                    THEN 0 ELSE 1
                END
           ) 
           OVER(PARTITION BY T2.C1,T2.C2 ORDER BY T2.C3,T2.C4) GRP_KEY
        FROM
            T2
    )
--C1,C2,GRP_KEYごとに集約
SELECT
    T3.C1 C1
,   T3.C2 C2
,   MIN(T3.C3) C3
,   MAX(T3.C4) C4
FROM
    T3
GROUP BY
    T3.C1
,   T3.C2
,   T3.GRP_KEY

Comments

[abcmart]

ありがとうございます！
動作確認し、問題なく抽出できること確認しました。
想定結果の１行目はおっしゃる通り誤りですね。
大変助かりました。
改めまて、感謝致します。

[abcmart]

実際にはストアドプロシージャを使用しておりますが、今回のようにSQL単体でまかなえるのであれば、これはこれでシンプルで問題ないとおもっております。