本文介紹了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_dateSCHEDULE_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
這篇關于Oracle生成具有時間間隔的計劃行的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，
