Oracle將日期范圍拆分為天和自定義時間間隔-有解無憂

我正在嘗試將作業班次日期范圍拆分為不同的日期和時間間隔。已經找到了一些答案，但仍在嘗試按時間間隔拆分。提前感謝您的任何想法或提示。

每天都需要單獨拆分
白班時間為 0600-22:00
夜班是 2200-0600

Range1:
2022-02-03 08:40 to 2022-02-04 10:07
Split Rows:
   2022-02-03 08:40 to 2022-02-03 22:00 DAY
   2022-02-03 22:00 to 2022-02-04 06:00 NIGHT
   2022-02-04 06:00 to 2022-02-04 10:07 DAY
Range2:
2022-02-03 08:40 to 2022-02-04 02:07
Split Rows:
   2022-02-03 08:40 to 2022-02-03 22:00 DAY
   2022-02-03 22:00 to 2022-02-04 02:07 NIGHT
Range3:
2022-02-03 04:40 to 2022-02-04 02:07
Split Rows:
   2022-02-03 04:40 to 2022-02-03 06:00 NIGHT
   2022-02-03 08:40 to 2022-02-03 22:00 DAY
   2022-02-03 22:00 to 2022-02-04 02:07 NIGHT

示例資料（使用橫向查詢還沒有作業。我會更新，如果我弄明白了）還試圖看看我是否可以每小時拆分它們并稍后總結，就像在這里將時間分成小時間隔一樣


WITH SAMPLE AS (
   SELECT
      1  AS ID, 
      TO_DATE('2022-02-03 08:40', 'YYYY-MM-DD HH24:MI') AS STARTDATE,
      TO_DATE('2022-02-04 10:07', 'YYYY-MM-DD HH24:MI') AS ENDDATE
   FROM
      DUAL
   UNION ALL
   SELECT
      2  AS ID, 
      TO_DATE('2022-02-03 08:40', 'YYYY-MM-DD HH24:MI') AS STARTDATE,
      TO_DATE('2022-02-04 02:07', 'YYYY-MM-DD HH24:MI') AS ENDDATE
   FROM
      DUAL
   UNION ALL
   SELECT
      3  AS ID, 
      TO_DATE('2022-02-03 04:40', 'YYYY-MM-DD HH24:MI') AS STARTDATE,
      TO_DATE('2022-02-04 02:07', 'YYYY-MM-DD HH24:MI') AS ENDDATE
   FROM
      DUAL
)
SELECT
   ID,
   L.STARTDATE,
   L.ENDDATE
FROM
   SAMPLE,
   LATERAL (
      SELECT
         CASE LEVEL
            WHEN 1 THEN STARTDATE
            ELSE TRUNC(STARTDATE)   LEVEL - 1
         END                                                         STARTDATE,
         LEAST(TRUNC(STARTDATE)   LEVEL - 1 / 24 / 60, ENDDATE) ENDDATE
      FROM
         DUAL
      CONNECT BY
         TRUNC(STARTDATE)   LEVEL - 1 <= ENDDATE
   ) L;

uj5u.com熱心網友回復：

您可以使用：

WITH SAMPLE (ID, startdate, enddate ) AS (
   SELECT 1, 
          TO_DATE('2022-02-03 08:40', 'YYYY-MM-DD HH24:MI'),
          TO_DATE('2022-02-04 10:07', 'YYYY-MM-DD HH24:MI')
   FROM   DUAL
   UNION ALL
   SELECT 2, 
          TO_DATE('2022-02-03 08:40', 'YYYY-MM-DD HH24:MI'),
          TO_DATE('2022-02-04 02:07', 'YYYY-MM-DD HH24:MI')
   FROM   DUAL
   UNION ALL
   SELECT 3, 
          TO_DATE('2022-02-03 04:40', 'YYYY-MM-DD HH24:MI'),
          TO_DATE('2022-02-04 02:07', 'YYYY-MM-DD HH24:MI')
   FROM   DUAL
)
SELECT ID,
       o.type,
       GREATEST(L.start_date   o.start_offset, s.startdate) AS startdate,
       LEAST(L.start_date   o.end_offset, s.enddate) AS enddate
FROM   SAMPLE s
       CROSS JOIN LATERAL (
         SELECT TRUNC(startdate - INTERVAL '6' HOUR)
                    INTERVAL '6' HOUR
                    LEVEL - 1 AS start_date
         FROM   DUAL
         CONNECT BY
                TRUNC(startdate - INTERVAL '6' HOUR)
                    INTERVAL '6' HOUR
                    LEVEL - 1
                < ENDDATE
       ) L
       CROSS JOIN (
         SELECT 'DAY' AS type,
                INTERVAL '0' HOUR AS start_offset,
                INTERVAL '16' HOUR AS end_offset
         FROM   DUAL
       UNION ALL
         SELECT 'NIGHT' AS type,
                INTERVAL '16' HOUR AS start_offset,
                INTERVAL '24' HOUR AS end_offset
         FROM   DUAL
       ) o
WHERE  L.start_date   o.start_offset < s.enddate
AND    L.start_date   o.end_offset > s.startdate;

哪個輸出：

ID 型別開始日期結束日期

1 日 2022-02-03 08:40:00 2022-02-03 22:00:00

1 夜晚 2022-02-03 22:00:00 2022-02-04 06:00:00

1 日 2022-02-04 06:00:00 2022-02-04 10:07:00

2 日 2022-02-03 08:40:00 2022-02-03 22:00:00

2 夜晚 2022-02-03 22:00:00 2022-02-04 02:07:00

3 夜晚 2022-02-03 04:40:00 2022-02-03 06:00:00

3 日 2022-02-03 06:00:00 2022-02-03 22:00:00

3 夜晚 2022-02-03 22:00:00 2022-02-04 02:07:00

ID	型別	開始日期	結束日期
1	日	2022-02-03 08:40:00	2022-02-03 22:00:00
1	夜晚	2022-02-03 22:00:00	2022-02-04 06:00:00
1	日	2022-02-04 06:00:00	2022-02-04 10:07:00
2	日	2022-02-03 08:40:00	2022-02-03 22:00:00
2	夜晚	2022-02-03 22:00:00	2022-02-04 02:07:00
3	夜晚	2022-02-03 04:40:00	2022-02-03 06:00:00
3	日	2022-02-03 06:00:00	2022-02-03 22:00:00
3	夜晚	2022-02-03 22:00:00	2022-02-04 02:07:00

db<>在這里擺弄

uj5u.com熱心網友回復：

第1步。

首先，您需要生成所有可能的間隔。你可以使用簡單的橫向來做到這一點。為了使它更容易和更敏捷，我將在INTERVALSCTE 中節省白班：

DBFiddle

WITH SAMPLE AS (
   SELECT
      1  AS ID, 
      TO_DATE('2022-02-03 08:40', 'YYYY-MM-DD HH24:MI') AS STARTDATE,
      TO_DATE('2022-02-04 10:07', 'YYYY-MM-DD HH24:MI') AS ENDDATE
   FROM
      DUAL
   UNION ALL
   SELECT
      2  AS ID, 
      TO_DATE('2022-02-03 08:40', 'YYYY-MM-DD HH24:MI') AS STARTDATE,
      TO_DATE('2022-02-04 02:07', 'YYYY-MM-DD HH24:MI') AS ENDDATE
   FROM
      DUAL
   UNION ALL
   SELECT
      3  AS ID, 
      TO_DATE('2022-02-03 04:40', 'YYYY-MM-DD HH24:MI') AS STARTDATE,
      TO_DATE('2022-02-04 02:07', 'YYYY-MM-DD HH24:MI') AS ENDDATE
   FROM
      DUAL
)
,intervals(i_name,i_start,i_end) as (
select 'Day Shift'  ,'0600', '2159' from dual union all
select 'Night Shift','2200', '0559' from dual
)
SELECT
   s.*
   ,days.*
   ,ints.*
FROM
   SAMPLE s
  ,lateral(
      select trunc(startdate)   n as n_day
      from xmltable(
         '-1 to xs:integer(.)'
         passing trunc(trunc(enddate) - trunc(startdate))
         columns n int path '.'
      ) 
   ) days
  ,lateral(
      select 
         i.*
        ,to_date(to_char(n_day,'yyyy-mm-dd ')||i_start, 'yyyy-mm-dd hh24mi')
           as dtm_start
        ,to_date(to_char(n_day,'yyyy-mm-dd ')||i_end  , 'yyyy-mm-dd hh24mi')
           case when i_end < i_start then 1 else 0 end --  1 if it ends on next day
           as dtm_end
      from intervals i
   ) ints
order by id,startdate,n_day,dtm_start;

結果：

 ID STARTDATE           ENDDATE             N_DAY      I_NAME      I_ST I_EN DTM_START           DTM_END
--- ------------------- ------------------- ---------- ----------- ---- ---- ------------------- -------------------
  1 2022-02-03 08:40:00 2022-02-04 10:07:00 2022-02-02 Day Shift   0600 2159 2022-02-02 06:00:00 2022-02-02 21:59:00
  1 2022-02-03 08:40:00 2022-02-04 10:07:00 2022-02-02 Night Shift 2200 0559 2022-02-02 22:00:00 2022-02-03 05:59:00
  1 2022-02-03 08:40:00 2022-02-04 10:07:00 2022-02-03 Day Shift   0600 2159 2022-02-03 06:00:00 2022-02-03 21:59:00
  1 2022-02-03 08:40:00 2022-02-04 10:07:00 2022-02-03 Night Shift 2200 0559 2022-02-03 22:00:00 2022-02-04 05:59:00
  1 2022-02-03 08:40:00 2022-02-04 10:07:00 2022-02-04 Day Shift   0600 2159 2022-02-04 06:00:00 2022-02-04 21:59:00
  1 2022-02-03 08:40:00 2022-02-04 10:07:00 2022-02-04 Night Shift 2200 0559 2022-02-04 22:00:00 2022-02-05 05:59:00
  2 2022-02-03 08:40:00 2022-02-04 02:07:00 2022-02-02 Day Shift   0600 2159 2022-02-02 06:00:00 2022-02-02 21:59:00
  2 2022-02-03 08:40:00 2022-02-04 02:07:00 2022-02-02 Night Shift 2200 0559 2022-02-02 22:00:00 2022-02-03 05:59:00
  2 2022-02-03 08:40:00 2022-02-04 02:07:00 2022-02-03 Day Shift   0600 2159 2022-02-03 06:00:00 2022-02-03 21:59:00
  2 2022-02-03 08:40:00 2022-02-04 02:07:00 2022-02-03 Night Shift 2200 0559 2022-02-03 22:00:00 2022-02-04 05:59:00
  2 2022-02-03 08:40:00 2022-02-04 02:07:00 2022-02-04 Day Shift   0600 2159 2022-02-04 06:00:00 2022-02-04 21:59:00
  2 2022-02-03 08:40:00 2022-02-04 02:07:00 2022-02-04 Night Shift 2200 0559 2022-02-04 22:00:00 2022-02-05 05:59:00
  3 2022-02-03 04:40:00 2022-02-04 02:07:00 2022-02-02 Day Shift   0600 2159 2022-02-02 06:00:00 2022-02-02 21:59:00
  3 2022-02-03 04:40:00 2022-02-04 02:07:00 2022-02-02 Night Shift 2200 0559 2022-02-02 22:00:00 2022-02-03 05:59:00
  3 2022-02-03 04:40:00 2022-02-04 02:07:00 2022-02-03 Day Shift   0600 2159 2022-02-03 06:00:00 2022-02-03 21:59:00
  3 2022-02-03 04:40:00 2022-02-04 02:07:00 2022-02-03 Night Shift 2200 0559 2022-02-03 22:00:00 2022-02-04 05:59:00
  3 2022-02-03 04:40:00 2022-02-04 02:07:00 2022-02-04 Day Shift   0600 2159 2022-02-04 06:00:00 2022-02-04 21:59:00
  3 2022-02-03 04:40:00 2022-02-04 02:07:00 2022-02-04 Night Shift 2200 0559 2022-02-04 22:00:00 2022-02-05 05:59:00

Note, that since I have specified time intervals in hhmi (ie hh24mi in oracle datetime format models), we need to ignore seconds.

As you can see lateral(...) days generates all dates between one day before startdate (to cover the end of night shift) and enddate.

Then ints generates day and night shifts for all those days.

Step 2.

So the only thing you need now is to filter them and correct start time and end time of partial intervals.

These 2 predicates filters them:

   and ints.dtm_end   >= s.startdate
   and ints.dtm_start <= s.enddate

and these 2 lines return correct start and end time:

   greatest(s.startdate, ints.dtm_start) as startdate,
   least   (s.enddate  , ints.dtm_end  ) as enddate,

So full solution: DBFiddle

WITH SAMPLE AS (
   SELECT
      1  AS ID, 
      TO_DATE('2022-02-03 08:40', 'YYYY-MM-DD HH24:MI') AS STARTDATE,
      TO_DATE('2022-02-04 10:07', 'YYYY-MM-DD HH24:MI') AS ENDDATE
   FROM
      DUAL
   UNION ALL
   SELECT
      2  AS ID, 
      TO_DATE('2022-02-03 08:40', 'YYYY-MM-DD HH24:MI') AS STARTDATE,
      TO_DATE('2022-02-04 02:07', 'YYYY-MM-DD HH24:MI') AS ENDDATE
   FROM
      DUAL
   UNION ALL
   SELECT
      3  AS ID, 
      TO_DATE('2022-02-03 04:40', 'YYYY-MM-DD HH24:MI') AS STARTDATE,
      TO_DATE('2022-02-04 02:07', 'YYYY-MM-DD HH24:MI') AS ENDDATE
   FROM
      DUAL
)
,intervals(i_name,i_start,i_end) as (
select 'Day Shift'  ,'0600', '2159' from dual union all
select 'Night Shift','2200', '0559' from dual
)
SELECT
   s.id,
   greatest(s.startdate, ints.dtm_start) as startdate,
   least   (s.enddate  , ints.dtm_end  ) as enddate,
   i_name,
   i_start,
   i_end
FROM
   SAMPLE s
  ,lateral(
      select trunc(startdate)   n as n_day
      from xmltable(
         '-1 to xs:integer(.)'
         passing trunc(trunc(enddate) - trunc(startdate))
         columns n int path '.'
      ) 
   ) days
  ,lateral(
      select 
         i.*
        ,to_date(to_char(n_day,'yyyy-mm-dd ')||i_start, 'yyyy-mm-dd hh24mi')
           as dtm_start
        ,to_date(to_char(n_day,'yyyy-mm-dd ')||i_end  , 'yyyy-mm-dd hh24mi')
           case when i_end < i_start then 1 else 0 end --  1 if it ends on next day
           as dtm_end
      from intervals i
   ) ints
where 1=1
   -- filter `ints`:
   and ints.dtm_end   >= s.startdate
   and ints.dtm_start <= s.enddate
order by 1,2,3;

Results:

        ID STARTDATE        ENDDATE          I_NAME      I_ST I_EN
---------- ---------------- ---------------- ----------- ---- ----
         1 2022-02-03 08:40 2022-02-03 21:59 Day Shift   0600 2159
         1 2022-02-03 22:00 2022-02-04 05:59 Night Shift 2200 0559
         1 2022-02-04 06:00 2022-02-04 10:07 Day Shift   0600 2159
         2 2022-02-03 08:40 2022-02-03 21:59 Day Shift   0600 2159
         2 2022-02-03 22:00 2022-02-04 02:07 Night Shift 2200 0559
         3 2022-02-03 04:40 2022-02-03 05:59 Night Shift 2200 0559
         3 2022-02-03 06:00 2022-02-03 21:59 Day Shift   0600 2159
         3 2022-02-03 22:00 2022-02-04 02:07 Night Shift 2200 0559

8 rows selected.

Obviously, you can remove i_start and i_end columns from the output. I showed them just to highlight day/night shift intervals.

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

標籤：sql 甲骨文日期

上一篇：為從1開始計數的每個sequence_id插入遞增的event_ids

下一篇：oracle欄位組合