How to consolidate time blocks?

Question

I have a view with a list of relative seconds for a foreign key (ID):

CREATE TABLE Times ( ID INT , TimeFrom INT , TimeTo INT ); 

The table contains mostly non-overlapping data, but there are times when I have a TimeTo <TimeFrom of another record:

 +----+----------+--------+ | ID | TimeFrom | TimeTo | +----+----------+--------+ | 10 | 10 | 30 | | 10 | 50 | 70 | | 10 | 60 | 150 | | 10 | 75 | 150 | | .. | ... | ... | +----+----------+--------+ 

The result set is designed to smooth out a linear downtime report, but with too many of these overlaps, I get negative usage time. That is, if the window ID = 10 above was 150 seconds, and I calculated the difference in relative seconds to subtract from the window size, I would end with 150-(20+20+90+75)=-55 . I tried this approach, and it led me to understand the presence of overlaps that needed to be smoothed out.

So, what I'm looking for is a solution to smooth out overlaps one set of times:

 +----+----------+--------+ | ID | TimeFrom | TimeTo | +----+----------+--------+ | 10 | 10 | 30 | | 10 | 50 | 150 | | .. | ... | ... | +----+----------+--------+ 

Considerations: performance is very important here, as it is part of a larger request that will work well on it, and I don't want to greatly affect its performance if I can help it.

In the comment on “What seconds is the interval”, this is what I tried for the final result, and I'm looking for something with better performance. Adapted to my example:

 SELECT SUM(CN) FROM ( SELECT AN, ROW_NUMBER()OVER(ORDER BY AN) RowID FROM (SELECT TOP 60 1 N FROM master..spt_values) A , (SELECT TOP 720 1 N FROM master..spt_values) B ) C WHERE EXISTS ( SELECT 1 FROM Times SE WHERE SE.ID = 10 AND SE.TimeFrom <= C.RowID AND SE.TimeTo >= C.RowID AND EXISTS ( SELECT 1 FROM Times2 D WHERE ID = SE.ID AND D.TimeFrom <= C.RowID AND D.TimeTo >= C.RowID ) GROUP BY SE.ID ) 

The problem with this solution is that I get the Row Count Spool from the EXISTS query in the query plan with the number of executions equal to COUNT (C. *). I left the real numbers in this query to illustrate that getting around this approach is best. Because even with the Row Count Spool, which significantly reduces the cost of the request, the execution counter also increases the cost of the request as a whole.

Further editing. The ultimate goal is to put this in the procedure, so variable tables and tone tables are also a possible tool to use.

Answer 1

On the left, attach each line to its successor, overlapping a line with the same identifier value (where such exists).

Now, for each row in the LHS left join RHS result set, the past contribution for the identifier is:

isnull(RHS.TimeFrom,LHS.TimeTo) - LHS.TimeFrom as TimeElapsed

Summing up the identifier should give the correct answer.

Note that:
- where there is no overlapping succession sequence, the calculation is simply LHS.TimeTo - LHS.TimeFrom
- where there is an overlapping sequence of successors, the calculation will be clean for use (RHS.TimeFrom - LHS.TimeFrom) + (RHS.TimeTo - RHS.TimeFrom)
which simplifies RHS.TimeTo - LHS.TimeFrom

Answer 2

OK I'm still trying to do this with just one SELECT . But this fully works:

 DECLARE @tmp TABLE (ID INT, GroupId INT, TimeFrom INT, TimeTo INT) INSERT INTO @tmp SELECT ID, 0, TimeFrom, TimeTo FROM Times ORDER BY Id, TimeFrom DECLARE @timeTo int, @id int, @groupId int SET @groupId = 0 UPDATE @tmp SET @groupId = CASE WHEN id != @id THEN 0 WHEN TimeFrom > @timeTo THEN @groupId + 1 ELSE @groupId END, GroupId = @groupId, @timeTo = TimeTo, @id = id SELECT Id, MIN(TimeFrom), Max(TimeTo) FROM @tmp GROUP BY ID, GroupId ORDER BY ID 

Answer 3

Something like below (assuming SQL 2008+ due to CTE):

  WITH Overlaps AS ( SELECT t1.Id, TimeFrom = MIN(t1.TimeFrom), TimeTo = MAX(t2.TimeTo) FROM dbo.Times t1 INNER JOIN dbo.Times t2 ON t2.Id = t1.Id AND t2.TimeFrom > t1.TimeFrom AND t2.TimeFrom < t1.TimeTo GROUP BY t1.Id ) SELECT o.Id, o.TimeFrom, o.TimeTo FROM Overlaps o UNION ALL SELECT t.Id, t.TimeFrom, t.TimeTo FROM dbo.Times t INNER JOIN Overlaps o ON o.Id = t.Id AND (o.TimeFrom > t.TimeFrom OR o.TimeTo < t.TimeTo); 

I don’t have a lot of data to test, but I think this is acceptable for the small data sets that I have.

Answer 4

I also wrapped around this problem - and after that I found that the problem is in your data.

You state (if I understand correctly) that these records should reflect the relative times when the user goes into standby / returns.

So, you should consider misinforming your data and reorganizing your inserts to create valid data sets. For example, two lines:

 +----+----------+--------+ | ID | TimeFrom | TimeTo | +----+----------+--------+ | 10 | 50 | 70 | | 10 | 60 | 150 | 

how can it be possible that the user is idle until the second 70, but goes into standby mode on the second 60? This already implies that he returned to second place for 59 years.

I can only assume that this problem occurs from different threads and / or browser windows (tabs) with which the user can use your application. (Each of them has its own "idle definition")

So, instead of getting around the symptoms, you have to fix the cause! Why is it a data record inserted into a table? You can avoid this by simply checking if the user is already idle before inserting a new line.

• Create a unique key restriction on ID and TimeTo

• If an unoccupied event is detected, run the following query:

  INSERT IGNORE INTO Times (ID,TimeFrom,TimeTo)VALUES('10', currentTimeStamp, -1); -- (If the user is already "idle" - nothing will happen)

• Whenever a return event is detected, run the following query:

  UPDATE Times SET TimeTo=currentTimeStamp WHERE ID='10' and TimeTo=-1 -- (If the user is already "back" - nothing will happen)

The fiddle related here: http://sqlfiddle.com/#!2/dcb17/1 will play the event chain for your example, but the result is a clean and logical idle timeout set -windows:

 ID TIMEFROM TIMETO 10 10 30 10 50 70 10 75 150 

Note. . The result is slightly different from the desired result. But I believe that this is more accurate, because the reason mentioned above: the user can not stand idle on the second 70 without returning from it to the current state of inactivity. It either STAYS is idle (and the second thread / tab starts in an unoccupied event), or it returned between them.

---

Specifically, to increase maximum productivity, you should correct the data, not invent a job request. It may be 3 ms for inserts, but it may be worth 20 seconds after selection!

---

Edit: if a multi-threaded / multiple session is causing a bad insert, you will also need to check if most_recent_come_back_time < now() - idleTimeout - otherwhise the user can return to tab1 and write inactive on tab2 after a few seconds, since tab2 started a time into it Downtime out as user only updated tab1.

Answer 5

I had the "same" problem once with the "days" (additionally excluding WE and Holidays) The word count gave me the following idea:

 create table Seconds ( sec INT); insert into Seconds values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9), ... select count(distinct sec) from times t, seconds s where s.sec between t.timefrom and t.timeto-1 and id=10; 

you can cut the beginning to 0 (I put "10" here in braces)

 select count(distinct sec) from times t, seconds s where s.sec between t.timefrom- (10) and t.timeto- (10)-1 and id=10; 

and finaly

 select count(distinct sec) from times t, seconds s, (select min(timefrom) m from times where id=10) as m where s.sec between t.timefrom-mm and t.timeto-mm-1 and id=10; 

Additionally you can “ignore”, for example. 10 seconds dividing you to a certain level, but earn speed

 select count(distinct sec)*d from times t, seconds s, (select min(timefrom) m from times where id=10) as m, (select 10 d) as d where s.sec between (t.timefrom-m)/d and (t.timeto-m)/d-1 and id=10; 

Of course, it depends on the range you should look at, but a “day” or two seconds should work (although I have not tested it).

violin...