I am using SQL Server 2017.
I want to solve how to know duration time without overlapping time. refer the below code.
data:
========================================================
Group | FromDate | ToDate
1 | 2019-09-30 11:13:00 | 2019-09-30 11:13:50
1 | 2019-09-30 11:13:20 | 2019-09-30 11:14:10
2 | 2019-09-30 11:20:00 | 2019-09-30 11:20:20
1 | 2019-09-30 11:20:10 | 2019-09-30 11:20:20
3 | 2019-09-30 11:25:00 | 2019-09-30 11:25:30
=========================================================
result (second):
========================
Group | DurationTime
1 | 80
2 | 60
3 | 30
=========================
Already I solved the calculation of the total duration time using DATEDIFF
.
But, I missed the overlapping time like group 1 data.
group | FromDate | ToDate
1 | 2019-09-30 11:13:00 | 2019-09-30 11:13:50
1 | 2019-09-30 11:13:20 | 2019-09-30 11:14:10
How can I calculate the duration time without overlapping time?
It is difficult to make queries easily and with good performance.
7条答案
按热度按时间pes8fvy91#
SQL implementation of Marzullo's algorithm: https://en.wikipedia.org/wiki/Marzullo%27s_algorithm .
The outcome:
The idea is simple:
p4rjhz4m2#
With reference to, and based on, SQL Queries for Overlapping Time Periods on SQL Server ...
When comparing two time periods T1 and T2, there are five possibilites:
This is compounded when T3 is introduced and can overlap any, all or none of T1 and T2.
Starting with your example data:
We can group the chronologies by identifying overlapping time periods, assigning overlapping periods to a single span of time...
We can use the Span column to coalesce a Group's time periods, i.e.:
group by [Group], Span
allows us to usemin(FromDate)
andmax(ToDate)
to calculate a given Span's time duration withdatediff()
and we cansum()
those durations to arrive at yourDurationTime
result...Which gives us the final result:
qncylg1j3#
You can try method called "gaps and islands":
Result:
jecbmhm34#
hc2pp10m5#
I would approach this as a gaps-and-islands problem. You can identify the "islands" by doing the following:
You can do this using window functions:
You can then aggregate this by
groupid
:Here is a db<>fiddle.
vmjh9lq96#
I elaborated on the answer by Tomas J. here https://stackoverflow.com/a/58133814/11317556 and adjusted the algorithm to work with SNOWFLAKE DB.. here's the code:
The results are same:
![](https://i.stack.imgur.com/VpvzX.png)
toiithl67#
Marzullo's algorithm ( https://stackoverflow.com/a/58133814/12130544 ) is the best one from my experience. Not only for calculating the duration of event, but especially for identification non-intersecting (non-overlapping) intervals of validity. Successfully used this algorithm to identify periods when SKU is in assortment of the store in the retail and to identify begin and end dates of mobile data session of the customer in telecommunications. And for many another scenarios. Strongly recommend.