日日操夜夜添-日日操影院-日日草夜夜操-日日干干-精品一区二区三区波多野结衣-精品一区二区三区高清免费不卡

公告:魔扣目錄網(wǎng)為廣大站長(zhǎng)提供免費(fèi)收錄網(wǎng)站服務(wù),提交前請(qǐng)做好本站友鏈:【 網(wǎng)站目錄:http://www.ylptlb.cn 】, 免友鏈快審服務(wù)(50元/站),

點(diǎn)擊這里在線咨詢客服
新站提交
  • 網(wǎng)站:51998
  • 待審:31
  • 小程序:12
  • 文章:1030137
  • 會(huì)員:747

本文介紹了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ì)大家有所幫助,

分享到:
標(biāo)簽:Date errorOraclegeneratingschedulerowswithaninterval exception intervals 
用戶無頭像

網(wǎng)友整理

注冊(cè)時(shí)間:

網(wǎng)站:5 個(gè)   小程序:0 個(gè)  文章:12 篇

  • 51998

    網(wǎng)站

  • 12

    小程序

  • 1030137

    文章

  • 747

    會(huì)員

趕快注冊(cè)賬號(hào),推廣您的網(wǎng)站吧!
最新入駐小程序

數(shù)獨(dú)大挑戰(zhàn)2018-06-03

數(shù)獨(dú)一種數(shù)學(xué)游戲,玩家需要根據(jù)9

答題星2018-06-03

您可以通過答題星輕松地創(chuàng)建試卷

全階人生考試2018-06-03

各種考試題,題庫(kù),初中,高中,大學(xué)四六

運(yùn)動(dòng)步數(shù)有氧達(dá)人2018-06-03

記錄運(yùn)動(dòng)步數(shù),積累氧氣值。還可偷

每日養(yǎng)生app2018-06-03

每日養(yǎng)生,天天健康

體育訓(xùn)練成績(jī)?cè)u(píng)定2018-06-03

通用課目體育訓(xùn)練成績(jī)?cè)u(píng)定