7

So I've got a table in SQL Server 2014 that stores events. Events are generated no faster than once per 10 seconds, but can be much slower (minutes or hours between events). The time between events is not uniform.

I'd like to sample the records by time.
For instance, I'd like to ask for a 10 minute sampling - so it would grab record 1, and the next record should be one that is at least 10 minutes later, and then the next is at least 10 minutes later than row 2, and so on.

I can achieve this with an iterative approach, looping over each record, but I'd like to let the database do the work. The problem is, I don't know how to do this using T-SQL set/window statements.

Can anyone help me out?

Example data:

EventID | RecordTime
--------+--------------------
   1    | 2017-04-01 12:00:00
   2    | 2017-04-01 12:00:10
   3    | 2017-04-01 12:00:20
   4    | 2017-04-01 12:00:32
   5    | 2017-04-01 12:05:42
   6    | 2017-04-01 12:09:00
   7    | 2017-04-01 12:24:12
   8    | 2017-04-01 12:36:46
   9    | 2017-04-01 12:36:57
  10    | 2017-04-01 15:00:00

Desired result:

EventID | RecordTime
--------+--------------------
   1    | 2017-04-01 12:00:00
   7    | 2017-04-01 12:24:12
   8    | 2017-04-01 12:36:46
  10    | 2017-04-01 15:00:00
  • EventIDs 2-6 are all within 10 minutes of record 1, so I don't want them.
  • EventID 7 is 24 minutes 12 seconds later than the last record in my result set (EventID 1), so I do want it.
  • EventID 8 is 12 minutes 34 seconds later than the last record in my result set (EventID 7), so I do want it.
  • EventID 9 is 11 seconds later than the last record in my result set (EventID 8), so I do not want it.
  • EventID 10 is apx. 2.5 hours later than the last record in my result set (EventID 9), so I do want it.
  • Please, add some sample data and the desired result. – McNets Apr 2 '17 at 11:24
  • @McNets: added some example data and desired results. Let me know if more/different data is needed. – Sam Axe Apr 3 '17 at 3:28
  • What if you had an event at 2017-04-01 12:10:01? It is only 1 minute 1 second after the previous event (2017-04-01 12:09:00), but it is more than 10 minutes after the first event (2017-04-01 12:00:00). Do you want to see this event in the result set? – Vladimir Baranov Apr 3 '17 at 5:12
  • It is easy to pick events that are closest to the uniform 10 minutes intervals (12:00, 12:10, 12:20, 12:30, ...), but the final result may have events that occasionally have less than 10 minutes between them. Is this kind of result acceptable? – Vladimir Baranov Apr 3 '17 at 5:16
  • @VladimirBaranov: No, I would not want the event at 12:10:01. I agree, picking events closest to fixed intervals would be simple. I may go that route if I cant figure out how to do this. – Sam Axe Apr 3 '17 at 5:22
4

If you don't want event at 12:10:01, then answer is very simple - use LEAD or LAG to compare timestamps of the two consecutive rows. Just be aware that if you have a long sequence of events in which each pair of events is less than 10 minutes apart, then the query will return only the first event of this sequence.

In other words, there has to be gap of more than 10 minutes between two events to add a row to the result set.

Sample data

DECLARE @T TABLE(EventID int, RecordTime datetime2(0));
INSERT INTO @T (EventID, RecordTime) VALUES
( 1, '2017-04-01 12:00:00'),
( 2, '2017-04-01 12:00:10'),
( 3, '2017-04-01 12:00:20'),
( 4, '2017-04-01 12:00:32'),
( 5, '2017-04-01 12:05:42'),
( 6, '2017-04-01 12:09:00'),
( 7, '2017-04-01 12:24:12'),
( 8, '2017-04-01 12:36:46'),
( 9, '2017-04-01 12:36:57'),
(10, '2017-04-01 15:00:00');

Query

WITH
CTE
AS
(
    SELECT
        EventID
        ,RecordTime
        ,LAG(RecordTime) OVER (ORDER BY EventID) AS PrevRecordTime
    FROM @T
)
SELECT
    EventID
    ,RecordTime
FROM
    CTE
    CROSS APPLY
    (
        SELECT DATEDIFF(second, PrevRecordTime, RecordTime) AS RecordDiffSeconds
    ) AS CA
WHERE
    RecordDiffSeconds IS NULL
    OR RecordDiffSeconds > 600
ORDER BY EventID;

Result

+---------+---------------------+
| EventID |     RecordTime      |
+---------+---------------------+
|       1 | 2017-04-01 12:00:00 |
|       7 | 2017-04-01 12:24:12 |
|       8 | 2017-04-01 12:36:46 |
|      10 | 2017-04-01 15:00:00 |
+---------+---------------------+
  • First, I sure appreciate the help. Second, I tried your query with the sample set and it does indeed return the proper records. However, when applied to my real data set (141 records spanning 2 hours) it returns results that are incoherent. – Sam Axe Apr 3 '17 at 22:36
  • @SamAxe, thank you Sam. I'd recommend you to ask another question with more detailed sample data that covers all interesting cases. Show your expected result for these cases. – Vladimir Baranov Apr 3 '17 at 23:48
  • A follow-on if you don't mind (or I can post a new question, whatever makes more sense): If we change EventID 7's RecordTime to 2017-04-01 12:10:12 (putting it 10 min 12 sec after EventID 1) it does not get included because the gap to the previous record (EventID 6) is 1 min 12 sec. However, it is desired that it IS included. Any thoughts? – Sam Axe Apr 4 '17 at 0:13
  • @SamAxe, I think that it is better to ask another question. At first you told in the comment that you don't want to include such events. Now you've changed your mind. Ask another question, provide a good set of sample data that covers all corner cases and explain the logic/what result you need in each case. – Vladimir Baranov Apr 4 '17 at 2:34
  • Oh man. I totally misread that question in the comments. My appologies. Yes, I will post a new question. – Sam Axe Apr 4 '17 at 3:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.