oracle - 重叠日期 Oracle

Question

我有一组日期范围，它可以将配置值设置为“S”或“D”，但下属日期范围可以有重叠日期，我正在尝试获取相应日期的配置值。

注意：如果配置值为“D”，则日期范围将在包含配置值“S”的另一行的数据范围内（如果存在）。

数据：

Start_DATE       End_Date    Config
01-Jan-2020      31-Jan-2020    S
15-Jan-2020      20-Jan-2020    D
15-Feb-2020      20-Feb-2020    D
01-Mar-2020      20-Mar-2020    S

我想要一个像这样的输出

Start_DATE       End_Date    Config
01-Jan-2020      14-Jan-2020    S
15-Jan-2020      20-Jan-2020    D
16-Jan-2020      31-Jan-2020    S
15-Feb-2020      20-Feb-2020    D
01-Mar-2020      20-Mar-2020    S

尝试了很多东西，但对我来说没有什么能完全正确地做到这一点..

逻辑：

1. 在给定的日期范围内可能存在重叠的配置值“S”或“D”。
2. 查询应提取日期范围为“S”或“D”的位置

标签： oracleoracle11goracle10g

Answer 1

我只为这种情况写了一个解决方案。正如 Belayer 已经告诉您的那样，您应该提供其他案例（如果有）并向我们展示您之前尝试过的内容。很可能它会变成CASE WHEN地狱，但我希望下面的这个解决方案能给你一些见解。

解决方案

WITH MAIN
     AS (SELECT TO_DATE ('01012020', 'DDMMYYYY') AS Start_DATE,
                TO_DATE ('31012020', 'DDMMYYYY') AS End_Date,
                'S' AS config
           FROM DUAL
         UNION ALL
         SELECT TO_DATE ('15012020', 'DDMMYYYY') AS Start_DATE,
                TO_DATE ('20012020', 'DDMMYYYY') AS End_Date,
                'D' AS config
           FROM DUAL
         UNION ALL
         SELECT TO_DATE ('15022020', 'DDMMYYYY') AS Start_DATE,
                TO_DATE ('20022020', 'DDMMYYYY') AS End_Date,
                'D' AS config
           FROM DUAL
         UNION ALL
         SELECT TO_DATE ('01032020', 'DDMMYYYY') AS Start_DATE,
                TO_DATE ('20032020', 'DDMMYYYY') AS End_Date,
                'S' AS config
           FROM DUAL
        ),
     MAIN2
     AS (SELECT START_DATE AS DET FROM MAIN
         UNION
         SELECT END_DATE FROM MAIN),
     MAIN3
     AS (SELECT DET AS START_DATE,
                LEAD (DET, 1)
                   OVER (PARTITION BY TO_CHAR (DET, 'YYYYMM') ORDER BY DET)
                   AS END_DATE,
                  LEAD (DET, 1)
                     OVER (PARTITION BY TO_CHAR (DET, 'YYYYMM') ORDER BY DET)
                - 1
                   ED2,
                  LEAD (DET, 1)
                     OVER (PARTITION BY TO_CHAR (DET, 'YYYYMM') ORDER BY DET)
                + 1
                   ED3
           FROM MAIN2),
     MAIN4
     AS (SELECT *
           FROM MAIN3
          WHERE END_DATE IS NOT NULL),
     MAIN5
     AS (SELECT CASE
                   WHEN B.START_DATE IS NOT NULL
                   THEN
                      B.START_DATE
                   WHEN     B.START_DATE IS NULL
                        AND LAG (
                               A.START_DATE,
                               1)
                            OVER (
                               PARTITION BY TO_CHAR (A.START_DATE, 'YYYYMM')
                               ORDER BY A.START_DATE)
                               IS NOT NULL
                   THEN
                        LAG (
                           A.START_DATE,
                           1)
                        OVER (PARTITION BY TO_CHAR (A.START_DATE, 'YYYYMM')
                              ORDER BY A.START_DATE)
                      + 1
                   WHEN     B.START_DATE IS NULL
                        AND LAG (
                               A.START_DATE,
                               1)
                            OVER (
                               PARTITION BY TO_CHAR (A.START_DATE, 'YYYYMM')
                               ORDER BY A.START_DATE)
                               IS NULL
                   THEN
                      A.START_DATE
                END
                   START_DATE,
                CASE
                   WHEN B.END_DATE IS NOT NULL
                   THEN
                      B.END_DATE
                   WHEN     B.END_DATE IS NULL
                        AND LEAD (
                               A.END_DATE,
                               1)
                            OVER (PARTITION BY TO_CHAR (A.END_DATE, 'YYYYMM')
                                  ORDER BY A.START_DATE)
                               IS NOT NULL
                   THEN
                      A.END_DATE - 1
                   WHEN     B.END_DATE IS NULL
                        AND LEAD (
                               A.END_DATE,
                               1)
                            OVER (PARTITION BY TO_CHAR (A.END_DATE, 'YYYYMM')
                                  ORDER BY A.START_DATE)
                               IS NULL
                   THEN
                      A.END_DATE
                END
                   END_DATE,
                B.START_DATE ST,
                B.END_DATE ED,
                CASE WHEN B.CONFIG IS NOT NULL THEN B.CONFIG ELSE 'S' END
                   CONFIG
           FROM MAIN4 A
                LEFT JOIN MAIN B
                   ON B.START_DATE = A.START_DATE AND B.END_DATE = A.END_DATE --AND B.CONFIG='D'
                                                                             )
  SELECT START_DATE, END_DATE, CONFIG
    FROM MAIN5
ORDER BY 1