我想創建一個UDF，它接受一個日期范圍并詢問一個表，以回傳該表中記錄的唯一重疊之和。
CREATE TABLE dbo.Shifts (
Id INT IDENTITY(1,1) NOT NULL,
StartTime DATETIME2(0) NOT NULL,
EndTime DATETIME2(0) NOT NULL.
CONSTRAINT [PK_Shifts] PRIMARY KEY CLUSTERED ([Id] ASC)
)
問題陳述。給出@Start and @End引數。
寫一個UDF，回傳的獨特的時間重疊by的百分比。
記錄 在 dbo.Shifts table中的記錄
將開始和停止時間改為最近的15-分鐘
例如。
Timeline : <---------------------------------------------------------->
輸入引數。 @Start-----------------------------------@End
輸入引數。 !-------------------------------------------!
重疊移位
Shift 1 : |----------------|
Shift 2 : |----------------| 。
Shift 3 : |--------------| 。
Unique 重疊
輸入引數: !-------------------------------------------!
移位 : |--------------------| |-|
%重疊 : ~ 58%
uj5u.com熱心網友回復：
這有兩個組成部分。 更有趣的是一個間隙和島嶼問題。 第二個是重疊問題。 你想把空白和島嶼限制在跨越范圍的資料上，然后把它們結合起來。 因此，為了獲得相關的分組：
select min（starttime） as island_start, max(endtime) as island_end
from (select s.*,
sum(case when prev_endtime > = starttime then 0 else 1 end) over (order by starttime) as grp
from (select t.*,
max(endtime) over (order by starttime rows between unbounded preceding and 1 preceding) as prev_endtime
from shifts s
where endtime >/span> @start or
starttime < @end
) s
) s
group by grp;
這是最里面的子查詢，計算所有移位的前端。 如果沒有重疊，那么這一行就是一個島的開始。 然后，累積總和確定島上的所有行。 而外部查詢則將每一個島嶼上的記錄匯總到一起。
然后，你想得到這個和你關心的區間的重疊情況：
with i as (
select min(starttime) as island_start, max(endtime) as island_end
from (select s.*,
sum(case when prev_endtime > = starttime then 0 else 1 end) over (order by starttime) as grp
from (select s.*,
max(endtime) over (order by starttime rows between unbounded preceding and 1 preceding) as prev_endtime
from shifts s
where endtime >/span> @start or
starttime < @end
) s
) s
group by grp
)
select sum(dateiff(second, interval_start, interval_end) ) as shift_seconds,
datediff(second(second, @start, @end) as overall_seconds
from (select i.*,
(case when island_end > @endtime then @end else island_end) as interval_end,
(case when island_start < @start then @start else island_start end) as interval_start
from i
) i;
你想要的比例是兩個值的比率。
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/323121.html
標籤：