Oracle生成具有时间间隔的计划行

我有一些每5分钟生成一次行的SQL。如何对其进行修改以消除重叠时间(见下文)

注意：每一行都应该与一个没有重复的Location_id相关联。在本例中，应该生成25行，因此CONNECT BY应该类似于SELECT COUNT(*)FROM LOCATIONS。

我的目标是创建一个函数，它接受以下格式的Schedule_id和Start_Date ‘MMDDYYYY HH24：MI’；如果下一个条目将跨越午夜，则停止创建行；这意味着可能无法使用某些Location_id。

最终结果是将行放置在下面的明细表中。由于我还没有函数，Schedule_id可以硬编码为1。我听说过递归CTE，该方法会有这样的质量吗？

提前感谢所有回复的人和您的专业知识。

ALTER SESSION SET NLS_DATE_FORMAT = 'MMDDYYYY HH24:MI:SS';

create table schedule(
       schedule_id NUMBER(4),
       location_id number(4),
       start_date DATE,
       end_date DATE,
         CONSTRAINT start_min check (start_date=trunc(start_date,'MI')),   
       CONSTRAINT end_min check (end_date=trunc(end_date,'MI')),
 CONSTRAINT end_gt_start CHECK (end_date >= start_date),
CONSTRAINT same_day CHECK (TRUNC(end_date) = TRUNC(start_date))
      );

CREATE TABLE locations AS
SELECT level AS location_id,
   'Door ' || level AS location_name,

CASE.    round(dbms_random.value(1,3)) 
        WHEN 1 THEN 'A' 
        WHEN 2 THEN 'T' 
        WHEN 3 THEN 'G' 
     END AS location_type

FROM   dual
CONNECT BY level <= 25; 

with
   row_every_5_mins as
   ( select trunc(sysdate) + (rownum-1)*5/1440 t_from,
            trunc(sysdate) + rownum*5/1440 t_to
     from dual
      connect by level <= 1440/5
   ) SELECT * from row_every_5_mins;

Current output:

|T_FROM|T_TO|
|-----------------|-----------------|
|08162021 00:00:00|08162021 00:05:00|
|08162021 00:05:00|08162021 00:10:00|
|08162021 00:10:00|08162021 00:15:00|
|08162021 00:15:00|08162021 00:20:00|
…

所需输出

|T_FROM|T_TO|
|-----------------|-----------------|
|08162021 00:00:00|08162021 00:05:00|
|08162021 00:10:00|08162021 00:15:00|
|08162021 00:20:00|08162021 00:25:00|
…

---

解决方案

您可以避免递归查询或循环，因为您基本上需要locations表中每一行的行号。因此，您需要为分析函数提供适当的排序顺序。以下是查询：

with a as (
  select
    date '2021-01-01'
    + to_dsinterval('0 23:30:00')
    as start_dt_param
  from dual
)
, date_gen as (
  select
    location_id
    , start_dt_param
    , start_dt_param + (row_number() over(order by location_id) - 1)
        * interval '10' minute as start_dt
    , start_dt_param + (row_number() over(order by location_id) - 1)
        * interval '10' minute + interval '5' minute as end_dt
from a
  cross join locations
)
select
  location_id
  , start_dt
  , end_dt
from date_gen
where end_dt < trunc(start_dt_param + 1)

LOCATION_ID | START_DT            | END_DT             
----------: | :------------------ | :------------------
          1 | 2021-01-01 23:30:00 | 2021-01-01 23:35:00
          2 | 2021-01-01 23:40:00 | 2021-01-01 23:45:00
          3 | 2021-01-01 23:50:00 | 2021-01-01 23:55:00

更新： 或者如果你想要一个程序，那么它就更简单了。因为从12c开始Oracle有fetch first加法，解析函数可以简化为rownum伪列：

create or replace procedure populate_schedule (
  p_schedule_id in number
  , p_start_date in date
) as
begin
  insert into schedule (schedule_id, location_id, start_date, end_date)
  select
    p_schedule_id
    , location_id
    , p_start_date + (rownum - 1) * interval '10' minute
    , p_start_date + (rownum - 1) * interval '10' minute + interval '5' minute
  from locations
  /*Put your order of location assignment here*/
  order by location_id
  /*The number of 10-minute intervals before midnight from the first end_date*/
  fetch first ((trunc(p_start_date + 1) - p_start_date + 1/24/60*5)*24*60/10) rows only
  ;
  
  commit;
end;
/

begin
  populate_schedule(1, timestamp '2020-01-01 23:37:00');
  populate_schedule(2, timestamp '2020-01-01 23:35:00');
  populate_schedule(3, timestamp '2020-01-01 23:33:00');
end;/

select *
from schedule
order by schedule_id, start_date

SCHEDULE_ID | LOCATION_ID | START_DATE          | END_DATE           
----------: | ----------: | :------------------ | :------------------
          1 |           1 | 2020-01-01 23:37:00 | 2020-01-01 23:42:00
          1 |           2 | 2020-01-01 23:47:00 | 2020-01-01 23:52:00
          2 |           1 | 2020-01-01 23:35:00 | 2020-01-01 23:40:00
          2 |           2 | 2020-01-01 23:45:00 | 2020-01-01 23:50:00
          2 |           3 | 2020-01-01 23:55:00 | 2020-01-02 00:00:00
          3 |           1 | 2020-01-01 23:33:00 | 2020-01-01 23:38:00
          3 |           2 | 2020-01-01 23:43:00 | 2020-01-01 23:48:00
          3 |           3 | 2020-01-01 23:53:00 | 2020-01-01 23:58:00

小提琴here