Search code examples
sqlitetimeintervalsoverlap

The number of overlapping events SQL

Hello everyone I'm trying to solve the following problem: let's say I have a table with

event_type chat time_start time_end
A chat 1 15:01:21 15:04:20
A chat 2 15:00:13 15:03:10
A chat 3 15:03:00 15:17:00
A chat 4 14:50:00 15:03:05
B chat 1 17:00:00 17:30:00

My goal is to output for each event_type and each chat the number of intersections with other chats. That is, I want the result to look like this

event_type chat Number of intersections
A chat 1 4
A chat 2 4
A chat 3 4
A chat 4 4
B chat 1 0

I understand that I can use JON, but my real database is very large, and it does not load join's. So I thought of the following query

WITH T1
WITH Intervals AS (
    SELECT 
        time_start AS Event_time,
        1 AS cnt,
        event_type
    FROM 
        events
    UNION ALL
    SELECT 
        time_end AS Event_time,
        -1 AS cnt,
        event_type
    FROM 
        events
)
SELECT 
        event_type, chat,
        SUM(cnt) OVER (PARTITION BY event_type ORDER BY Event_time) AS Cumulative_count
 FROM 
        Intervals
 GROUP BY   event_type,chat.

But the result is not what I need. Does anyone have any idea how to solve this?

Solution

  • Assuming that you actually want:-

    enter image description here

    i.e. as per

    My goal is to output for each event_type and each chat the number of intersections with other chats.

    • "chat 1 type A" will intersect with "chat 2", "chat 3" and "chat 4" not itself and not chat 1 type B.
    • etc
    • "chat 1 type B" will intersect with nothing

    then you could use:-

    SELECT 
    event_type,
    chat,
    (
        SELECT count() 
        FROM events AS t2 
        WHERE
            t2.rowid <> t1.rowid
            AND
                (
                    t1.time_start BETWEEN t2.time_start AND t2.time_end
                    OR
                    t1.time_end BETWEEN t2.time_start AND t2.time_end
                )
    ) AS NOI
FROM events AS t1 
ORDER BY event_type,chat
;
    • this does assume that the table is not a WITHOUT ROWID table, although you could use t2.event_type||t2.chat <> t1.event_type||t1.chat, instead of t2.rowid <> t1.rowid to achieve the same result.
    • if you wanted the results as per your expected results i.e. to include the chat itself as one of the intersections but only if there are intersections then you could replace count() with count() + (count() > 0) and thus get:- enter image description here