如何填補日期范圍空白OracleSQL-有解無憂

使用給定的資料集：

WITH ranges AS (
select to_date('01.01.2021 00:00:00','DD.MM.YYYY hh24:mi:ss') date_from,
    to_date('31.03.2021 00:00:00','DD.MM.YYYY hh24:mi:ss') date_to 
from dual 
union
select to_date('27.03.2021 00:00:00','DD.MM.YYYY hh24:mi:ss') date_from,
    to_date('27.04.2021 00:00:00','DD.MM.YYYY hh24:mi:ss') date_to
from dual 
union
select to_date('01.05.2021 00:00:00','DD.MM.YYYY hh24:mi:ss') date_from,
    to_date('31.12.2021 00:00:00','DD.MM.YYYY hh24:mi:ss') date_to 
from dual 
)
SELECT * FROM ranges;

如何找到28.04.2021-30.04.2021.的差距？還要考慮到兩者之間可能存在多個間隙，并且范圍可能重疊。

有什么建議嗎？

uj5u.com熱心網友回復：

試試這個查詢，調整你的需求：

WITH steps AS (
  SELECT date_from as dt, 1 as step FROM ranges
   UNION ALL
  SELECT date_to as dt, -1 as step FROM ranges
)
SELECT dt as dt_from,
       lead(dt) over (order by dt) as dt_to,
       sum(step) over (order by dt) as cnt_ranges
  FROM steps;


dt_from                 | dt_to                   | cnt_ranges
------------------------ ------------------------- -----------
2021-01-01 00:00:00.000 | 2021-03-27 00:00:00.000 | 1
2021-03-27 00:00:00.000 | 2021-03-31 00:00:00.000 | 2
2021-03-31 00:00:00.000 | 2021-04-27 00:00:00.000 | 1
2021-04-27 00:00:00.000 | 2021-05-01 00:00:00.000 | 0
2021-05-01 00:00:00.000 | 2021-12-31 00:00:00.000 | 1
2021-12-31 00:00:00.000 |                         | 0

uj5u.com熱心網友回復：

您對日期范圍的建模不正確；例如，在 2021 年 2 月 14 日午夜結束的時間間隔不應包括2021 年 2 月 14 日。在您的模型中確實如此。

這會在您針對模型撰寫的所有查詢中導致不必要的復雜化。在下面的解決方案中，我需要先將結束日期加 1，進行所有處理，然后在最后減去 1。

with
  ranges (date_from, date_to) as (
    select to_date('01.01.2021 00:00:00','DD.MM.YYYY hh24:mi:ss'),
           to_date('31.03.2021 00:00:00','DD.MM.YYYY hh24:mi:ss')
    from   dual
    union  all
    select to_date('27.03.2021 00:00:00','DD.MM.YYYY hh24:mi:ss'),
           to_date('27.04.2021 00:00:00','DD.MM.YYYY hh24:mi:ss')
    from   dual 
    union  all
    select to_date('01.05.2021 00:00:00','DD.MM.YYYY hh24:mi:ss'),
           to_date('31.12.2021 00:00:00','DD.MM.YYYY hh24:mi:ss') 
    from   dual 
)
select first_missing, last_missing - 1 as last_missing
from   (
         select dt                          as first_missing, 
                lead(df) over (order by dt) as last_missing
         from   (select date_from, date_to   1 as date_to from ranges)
         match_recognize(
           order by date_from
           measures first(date_from) as df, max(date_to) as dt
           pattern  (a* b)
           define   a as max(date_to) >= next (date_from)
         )
       )
where  last_missing is not null
;

FIRST_MISSING       LAST_MISSING       
------------------- -------------------
28.04.2021 00:00:00 30.04.2021 00:00:00

轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/425009.html

標籤：sql 甲骨文差距和岛屿

上一篇：如何在BigQuery中使用ST_UNION

下一篇：如何在SQL中獲取不匹配的記錄