英文:
Oracle SQL question: How to create periods based on a table of days
问题
2023-02-01 - 2023-02-03
2023-02-05 - 2023-02-09
2023-02-12 - 2023-02-28
英文:
I have a table Calendar that contains days.
If, as an example, the Calendar contains all days in the period 2023-02-01 to 2023-02-28 except the days 4, 10 and 11.
I would like to write a SQL that returns the three periods without the missing days like:<br/>
2023-02-01 - 2023-02-03<br/>
2023-02-05 - 2023-02-09<br/>
2023-02-12 - 2023-02-28
I write in the title that it is an Oracle question since I guess it is necessary to use analytical functions, but I cannot find out how.
Can anyone help me with that?
答案1
得分: 1
你可以尝试使用以下代码来获取实际间隔：
with dates as (select trunc(sysdate,'yy')+level sd from dual connect by level<10
union all
select trunc(sysdate,'yy')+level+13 sd from dual connect by level<5
union all
select trunc(sysdate,'yy')+level+25 sd from dual connect by level<3
)
select *
from (select sd, lag(sd) over (order by sd) lg
from dates) match_recognize(
order by sd
measures first(sd) as start_sd, last(sd)+1 as end_sd
one row per match
pattern (e+)
define e as sd=next(sd)-1);
结果如下：
02/01/2023 00:00:00 10/01/2023 00:00:00
15/01/2023 00:00:00 18/01/2023 00:00:00
27/01/2023 00:00:00 28/01/2023 00:00:00
英文:
You can try this to get the actual intervals
with dates as (select trunc(sysdate,'yy')+level sd from dual connect by level<10
union all
select trunc(sysdate,'yy')+level+13 sd from dual connect by level<5
union all
select trunc(sysdate,'yy')+level+25 sd from dual connect by level<3
)
select *
from (select sd, lag(sd) over (order by sd) lg
from dates) match_recognize(
order by sd
measures first(sd) as start_sd, last(sd)+1 as end_sd
one row per match
pattern (e+)
define e as sd=next(sd)-1);
02/01/2023 00:00:00 10/01/2023 00:00:00
15/01/2023 00:00:00 18/01/2023 00:00:00
27/01/2023 00:00:00 28/01/2023 00:00:00
答案2
得分: 0
我认为这应该有助于您获得您的结果
SELECT
prev_day, date_col
FROM (
SELECT
date_col
, LAG(date_col) OVER(ORDER BY date_col) prev_day
FROM
Calendar
WHERE
date_col BETWEEN sysdate - 6 AND sysdate -- 指定期间
) WHERE date_col - prev_day > 1
英文:
I think this should help you to got your result
SELECT
prev_day, date_col
FROM (
SELECT
date_col
, LAG(date_col) OVER(ORDER BY date_col) prev_day
FROM
Calendar
WHERE
date_col BETWEEN sysdate - 6 AND sysdate -- spec period
) WHERE date_col - prev_day > 1
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论