本文介紹了Oracle生成具有時(shí)間間隔的計(jì)劃行的處理方法,對(duì)大家解決問題具有一定的參考價(jià)值,需要的朋友們下面隨著小編來一起學(xué)習(xí)吧!
問題描述
我有一些每5分鐘生成一次行的SQL。如何對(duì)其進(jìn)行修改以消除重疊時(shí)間(見下文)
注意:每一行都應(yīng)該與一個(gè)沒有重復(fù)的Location_id相關(guān)聯(lián)。在本例中,應(yīng)該生成25行,因此CONNECT BY應(yīng)該類似于SELECT COUNT(*)FROM LOCATIONS。
我的目標(biāo)是創(chuàng)建一個(gè)函數(shù),它接受以下格式的Schedule_id和Start_Date
‘MMDDYYYY HH24:MI’;如果下一個(gè)條目將跨越午夜,則停止創(chuàng)建行;這意味著可能無法使用某些Location_id。
最終結(jié)果是將行放置在下面的明細(xì)表中。由于我還沒有函數(shù),Schedule_id可以硬編碼為1。我聽說過遞歸CTE,該方法會(huì)有這樣的質(zhì)量嗎?
提前感謝所有回復(fù)的人和您的專業(yè)知識(shí)。
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|
…
推薦答案
您可以避免遞歸查詢或循環(huán),因?yàn)槟旧闲枰?code>locations表中每一行的行號(hào)。因此,您需要為分析函數(shù)提供適當(dāng)?shù)呐判蝽樞颉R韵率遣樵儯?/p>
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
更新:
或者如果你想要一個(gè)程序,那么它就更簡(jiǎn)單了。因?yàn)閺?2c開始Oracle有fetch first
加法,解析函數(shù)可以簡(jiǎn)化為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
這篇關(guān)于Oracle生成具有時(shí)間間隔的計(jì)劃行的文章就介紹到這了,希望我們推薦的答案對(duì)大家有所幫助,