CODEWITHSUNDEEP
postgresql
Sam
Sam

Reputation: 581

How can I query concurrent events, i.e. usage, in postgres?

From my first table of events below, what query would give me my second table of usage?

start end
08:42 08:47
08:44 08:50
start end count
08:42 08:44 1
08:44 08:47 2
08:47 08:50 1

What if any indexes should I create to speed this up?

The main thing I often need is the peak usage and when it is (i.e. max count row from above), so also is there a quicker way to get one/both of these?

Also, is it quicker to query for each second (which I can imagine how to do), e.g:

time count
08:42 1
08:43 1
08:44 2
08:45 2
08:46 2
08:47 1
08:48 1
08:49 1

NB my actual starts/ends are timestamp(6) with time zone and I have thousands of records, but I hope my example above is useful.

Upvotes: 1

Views: 117

Answers (1)

S-Man
S-Man

Reputation: 23746

step-by-step demo:db<>fiddle

SELECT 
    t as start, 
    lead as "end",
    sum as count
FROM (
    SELECT
        t,
        lead(t) OVER (ORDER BY t),     -- 2a
        type,
        SUM(type) OVER (ORDER BY t)    -- 2b
    FROM (
        SELECT                         -- 1
            start as t,
            1 as type
        FROM mytable
        
        UNION 
        
        SELECT
            stop,
            -1 as type
        FROM mytable
    ) s
) s
WHERE sum > 0                          -- 3
  1. Put all time values into one column. Add the 1 value to former start values and -1 to former end values
  2. a) put the next time values into the current record b) use cumulative SUM() over the newly added 1/-1 value. Each start point increased the count, each end value decreased it. This is your expected count
  3. Remove all records without an interval.

The above only works properly if your borders are distinct. If you have interval borders at the same time point, you have to change the UNION into UNION ALL (which keeps same values) and group this result afterwards to generate for example -2 from two -1 values at same time slot:

step-by-step demo:db<>fiddle

SELECT 
    t as start, 
    lead as "end",
    sum as count
FROM (
    SELECT
        t,
        lead(t) OVER (ORDER BY t),
        type,
        SUM(type) OVER (ORDER BY t)
    FROM (
        SELECT
            t,
            SUM(type) AS type
        FROM (
            SELECT
                start as t,
                1 as type
            FROM mytable
            
            UNION ALL
            
            SELECT
                stop,
                -1 as type
            FROM mytable
        ) s
        GROUP BY t
    ) s
) s
WHERE sum > 0

Upvotes: 3

Related Questions