Window Function to get pairs of records and compute the time difference

Question

I'm working with an event dataset as the one in the example below:

Event_Type  Event_Timestamp         Is_Active
A           2010-10-01 00:00:00     1
B           2010-10-01 00:00:01     1
A           2010-10-01 00:00:02     0
D           2010-10-01 00:00:03     1
B           2010-10-01 00:00:04     0
C           2010-10-01 00:00:05     1
A           2010-10-01 00:00:06     1
A           2010-10-01 00:00:07     1
A           2010-10-01 00:00:08     0

The dataset is ordered by the timestamp of the events and the data grows as events happens in real time. More event types could be included in the dataset at any time (not limited to only A, B, C, D as in the example) and the same event type could appear in the table multiple times. The Is_Active boolean field serves as a way to indicate whether the event is still active (1) or not (0).

In this sense, I tried to do some transformations on this data by using SQL Window Functions. I'm not necessarily restricted to a specific product or technology, so feel free to tell how you would address the problem below.

What I want to do is dynamically pair each event of the same type when they have opposite Is_Active values and then get how long that event was active. In other words, given an event X, I would need to get the Event_Timestamp for it when Is_Active had 1 for the first time (Begin_Timestamp), and then ignore the rest of the rows for this event X with Is_Active 1 until I get Is_Active 0, so I could take the Event_Timestamp again (End_Timestamp). Then, I would continue applying this logic when I find event X having 1 in the Is_Active column again.

An example of the resulting table would be:

Event_Type  Begin_Timestamp         End_Timestamp           Duration
A           2010-10-01 00:00:00     2010-10-01 00:00:02     2 seconds
B           2010-10-01 00:00:01     2010-10-01 00:00:04     3 seconds
D           2010-10-01 00:00:03     null                    null
C           2010-10-01 00:00:05     null                    null
A           2010-10-01 00:00:06     2010-10-01 00:00:08     2 seconds

Is there any window function that would help me get these pairs of events so I could compute the duration of each event?

Answer 1

You need something like

-- get previous status, use -1 value if no previous record (because NULL value needs more complex condition in cte2)
WITH cte1 AS (SELECT Event_Type, 
                     Event_Timestamp, 
                     Is_Active, 
                     COALESCE(LAG(Is_Active) OVER (PARTITION BY Event_Type 
                                                   ORDER BY Event_Timestamp ASC), -1) prev_event
              FROM datatable),
     -- remove records where status not altered
     cte2 AS (SELECT Event_Type, 
                     Event_Timestamp, 
                     Is_Active
              FROM cte1
              WHERE Is_Active != prev_event)
-- get data we need
SELECT Event_Type, 
       Event_Timestamp Begin_Timestamp, 
       LEAD(Event_Timestamp) OVER (PARTITION BY Event_Type 
                                   ORDER BY Event_Timestamp ASC) End_Timestamp, 
       LEAD(Event_Timestamp) OVER (PARTITION BY Event_Type 
                                   ORDER BY Event_Timestamp ASC) - Event_Timestamp Duration
FROM cte2
WHERE Is_Active = 1

Of course you can combine it into the query using one CTE which calculates all window values. The above long way is used for to explain the algorythm.

Answer 2

Given the data

SELECT x.Event_Type, x.Event_Timestamp, x.Is_Active
INTO #Data
FROM (VALUES 
  ('A', CONVERT(DATETIME, '2010-10-01 00:00:00'), 1),
  ('B', CONVERT(DATETIME, '2010-10-01 00:00:01'), 1),
  ('A', CONVERT(DATETIME, '2010-10-01 00:00:02'), 0),
  ('D', CONVERT(DATETIME, '2010-10-01 00:00:03'), 1),
  ('B', CONVERT(DATETIME, '2010-10-01 00:00:04'), 0),
  ('C', CONVERT(DATETIME, '2010-10-01 00:00:05'), 1),
  ('A', CONVERT(DATETIME, '2010-10-01 00:00:06'), 1),
  ('A', CONVERT(DATETIME, '2010-10-01 00:00:07'), 1),
  ('A', CONVERT(DATETIME, '2010-10-01 00:00:08'), 0)
) x (Event_Type, Event_Timestamp, Is_Active);

Your result will use some stages to be calculated (each in a CTE):

WITH cte_First
AS
(
  SELECT d.Event_Type,
         d.Event_Timestamp,
         d.Is_Active,
         -- Get the active state of the previous row ordered by the timestamp and partitioned by the event type
         Lag(d.Is_Active, 1, NULL) OVER(PARTITION BY d.Event_Type ORDER BY d.Event_Timestamp) AS Prev_Active
  FROM #Data d
), cte_activity
AS
(
  SELECT Event_Type,
         Event_Timestamp,
         Is_Active,
         Prev_Active,
         -- check if the event is a start, an end, or can just be ignored because of the same value
         CASE 
           WHEN Is_Active = Prev_Active THEN 0 -- same value
           WHEN Is_Active = 1 THEN 1 -- start
           WHEN Is_Active = 0 THEN 2 -- end
         END AS start_definition
  FROM cte_First
), cte_start_end
AS
(
  SELECT Event_Type,
         Event_Timestamp,
         Is_Active,
         Prev_Active,
         start_definition,
         -- get the event timestamp from the next row 
         -- (because we only have the start and the end events this will be the end timestamp
         -- for the start row and null for the end row)
         LEAD(Event_Timestamp, 1, NULL) OVER(PARTITION BY Event_Type ORDER BY Event_Timestamp) AS End_Timestamp
  FROM cte_activity
  WHERE start_definition > 0
)
SELECT Event_Type,
       Event_Timestamp,
       End_Timestamp,
       -- calculate the difference in seconds
       DATEDIFF(SECOND, Event_Timestamp, End_Timestamp) AS Duration_Seconds
FROM cte_start_end
-- include only the start rows
WHERE start_definition = 1
ORDER BY Event_Timestamp

If your data also contains more than one end event you must adjust the recognition of the end event in the case statement. This will produce the result as you expected in your post:

Event_Type Event_Timestamp         End_Timestamp           Duration_Seconds
---------- ----------------------- ----------------------- ----------------
A          2010-10-01 00:00:00.000 2010-10-01 00:00:02.000 2
B          2010-10-01 00:00:01.000 2010-10-01 00:00:04.000 3
D          2010-10-01 00:00:03.000 NULL                    NULL
C          2010-10-01 00:00:05.000 NULL                    NULL
A          2010-10-01 00:00:06.000 2010-10-01 00:00:08.000 2

(5 rows affected)

Answer 3

create table #temp(Event_Type varchar(50), Event_Timestamp  datetime, Is_Active bit)
insert into #temp values
 ('A','2010-10-01 00:00:00',1)
,('B','2010-10-01 00:00:01',1)
,('A','2010-10-01 00:00:02',0)
,('D','2010-10-01 00:00:03',1)
,('B','2010-10-01 00:00:04',0)
,('C','2010-10-01 00:00:05',1)
,('A','2010-10-01 00:00:06',1)
,('A','2010-10-01 00:00:07',1)
,('A','2010-10-01 00:00:08',0)
,('A','2010-10-01 00:00:09',1);

;

WITH CTE
AS (
    SELECT 
        a.Event_Type
        ,A.Event_Timestamp AS Begin_Timestamp
        ,b.Event_Timestamp AS End_Timestamp
        ,datediff(second, a.Event_Timestamp, b.Event_Timestamp) Duration
    FROM #temp A
    OUTER APPLY (
        SELECT TOP 1 
            b.Event_Type
            ,b.Event_Timestamp
        FROM #temp B
        WHERE a.Event_Type = b.Event_Type
            AND b.Is_Active = 0
            AND b.Event_Timestamp > a.Event_Timestamp
        ORDER BY b.Event_Timestamp
        ) b
    WHERE a.is_active = 1
    )
SELECT Event_Type
,Begin_Timestamp
,End_Timestamp
,Duration
FROM cte A
WHERE NOT EXISTS (
        SELECT 1
        FROM cte B
        WHERE a.Event_Type = b.Event_Type
            AND b.Begin_Timestamp < a.Begin_Timestamp
            AND a.Begin_Timestamp BETWEEN b.Begin_Timestamp
                AND b.end_Timestamp
        )


Drop TABLE #temp

No need of any Window Function.

You can test my script with different sample data , and also give me scenario for performance test.

In fact you should provide Real Table Schema .