Oracle學習日記——時間日期範圍處理

1.定位連續值的範圍

create or replace view v(proj_id,proj_start,proj_end) as

select 1,date'2005-01-01',date'2005-01-02' from dual union all

select 2,date'2005-01-02',date'2005-01-03' from dual union all

select 3,date'2005-01-03',date'2005-01-04' from dual union all

select 4,date'2005-01-04',date'2005-01-05' from dual union all

select 5,date'2005-01-06',date'2005-01-07' from dual union all

select 6,date'2005-01-16',date'2005-01-17' from dual union all

select 7,date'2005-01-17',date'2005-01-18' from dual union all

select 8,date'2005-01-18',date'2005-01-19' from dual union all

select 9,date'2005-01-19',date'2005-01-20' from dual union all

select 10,date'2005-01-21',date'2005-01-22' from dual union all

select 11,date'2005-01-26',date'2005-01-27' from dual union all

select 12,date'2005-01-27',date'2005-01-28' from dual union all

select 13,date'2005-01-28',date'2005-01-29' from dual union all

select 14,date'2005-01-29',date'2005-01-30' from dual ;

需求：把連續的數據查詢出來

方案1：自關聯

select v1.proj_id as 工程號,v1.proj_start as 開始時間,v1.proj_end as 結束時間

from v v1,v v2 where v1.proj_start = v2.proj_end

方案2：使用lead() over() 進行過濾

select * from

(select v1.proj_id as 工程號,v1.proj_start as 開始時間,v1.proj_end as 結束時間,

lead(v1.proj_start)over(order by proj_id) as 下一期工程開始時間

from v v1 )

where 結束時間 = 下一期工程開始時間

在上面的兩種寫法中，自關聯需要掃描兩次視圖“V”，而使用分析函數只需要一次就可以，根據這個特性，大部分情況下可以通過分析函數優化查詢性能。

2.定位連續值範圍的開始點和結束點

需求：現在要求把連續的項目合併，返回合併後的起止時間，如前四個項目合併後起止時間就是1號到5號。

如果是取最小開始時間和最大結束時間，則比較容易操作

select min(proj_start) as 開始,max(proj_end) as 結束 from v;

但是遠遠不能滿足我們的需求。

分析：

(1)提取上一工程的結束日期

create or replace view x0 as

select proj_id as 編號,

proj_start as 開始日期,

proj_end as 結束日期,

lag(proj_end) over(order by proj_id) as 上一工程結束日期

from v

select * from x0

(2)標定工程的連續狀態

create or replace view x1 as

select 編號,

開始日期,

結束日期,

上一工程結束日期,

case when 開始日期 = 上一工程結束日期 then 0 else 1 end as 連續狀態

from x0;

select * from x1

可以看到，在每一個連續分組的開始位置，我們都生成了一個“1”作為標識。

(3)對這個位置狀態進行累加，得到分組依據

create or replace view x2 as

select 編號,

開始日期,

結束日期,

上一工程結束日期,

連續狀態,

sum(連續狀態) over(order by 編號) as 分組依據

from x1;

select * from x2;

可以看到，通過提取數據(上一行日期)、生成標識、累加標識這些操作後，得到了5個連續分組，有分組依據後就容易完成下面的操作。

select 分組依據,min(開始日期) as 開始日期,max(結束日期) as 結束日期

from x2

group by 分組依據

order by 1

把上面各步驟整理在一起的語句如下：

select 分組依據,min(開始日期) as 開始日期,max(結束日期) as 結束日期

from (select 編號,

開始日期,

結束日期,

sum(連續狀態) over(order by 編號) 分組依據

from (select proj_id as 編號,

proj_start as 開始日期,

proj_end as 結束日期,

case when lag(proj_end) over(order by proj_id) = proj_start then 0 else 1 end 連續狀態 from v))

group by 分組依據

order by 1;

3.合併時間段

create or replace Timesheets(tast_id,start_date,end_date) as

select 1,date'1997-01-01',date'1997-01-03' from dual union all

select 2,date'1997-01-02',date'1997-01-04' from dual union all

select 3,date'1997-01-04',date'1997-01-05' from dual union all

select 4,date'1997-01-06',date'1997-01-09' from dual union all

select 5,date'1997-01-09',date'1997-01-09' from dual union all

select 6,date'1997-01-09',date'1997-01-09' from dual union all

select 7,date'1997-01-12',date'1997-01-15' from dual union all

select 8,date'1997-01-13',date'1997-01-13' from dual union all

select 9,date'1997-01-15',date'1997-01-15' from dual union all

select 10,date'1997-01-17',date'1997-01-17' from dual

select * from Timesheets

id7與id9是連續的，但中間id8和id9不連續，所以用lag取上一行來判斷肯定不對。

(1)這時可以用另一個開窗方式來處理：獲取當前行之前的最大“end_date”

select start_date,

end_date,

max(end_date) over(order by start_date rows between unbounded preceding and 1 preceding) as max_end_date

from timesheets b;

between unbounded preceding and 1 preceding :就是between ... and ....子句，意思是：從第一行到上一行

該分析函數就是order by start_date後“第一行到上一行”範圍內的“max(end_date)”

有了這個數據後再來判斷，就可以把id(7、8、9)判斷為連續範圍了。