Question 218599 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Find overlap interval through query

I have the following table_1 which shows the time each person in Room_A.

person start_time end_time
John 5 16
Mary 7 10
Peter 8 12

Then I want to have an output table which shows the time and the people in the Room_A, like:

people start_time end_time
[John] 5 7
[John, Mary] 7 8
[John, Mary, Peter] 8 10
[John, Peter] 10 12
[John] 12 16

I am wondering if this is something achievable using SQL/Presto query ? Thanks!

Tags:

2

Answers


  1. 0

    I suggest the following (untested) query:

    WITH numbers AS (
  SELECT * FROM UNNEST(SEQUENCE(1, 100)) AS number
) 
SELECT
    people,
    min(number) AS starts_time,
    max(number) AS end_time
FROM (
    SELECT
        n.number,
        array_join(t.person, ',') AS people
    FROM table_1 AS t 
    INNER JOIN numbers AS n ON n.number BETWEEN t.start_time AND t.end_time
    GROUP BY n.number
) AS p
GROUP BY people
ORDER BY people

    I have the equivalent query operating (in Postgres) here: https://dbfiddle.uk/n6b28oZb and the query above is (I believe) suitable for Presto

    people starts_time end_time
    John 5 16
    John,Mary 7 7
    John,Mary,Peter 8 10
    John,Peter 11 12

    But I have just noticed it isn’t a full match to your wanted result

    Login or Signup to reply.
  2. 0

    the idea being to JOIN with the generated list of all possible intervals:

    with rooms(person, start_time, end_time) as (
        select 'John', 5, 16 from dual union all
        select 'Mary', 7, 10 from dual union all
        select 'Peter', 8, 12 from dual -- union all
    )
    , times(hour) as (
        select start_time from rooms
        union
        select end_time from rooms
    )
    , intervals(start_time, end_time) as (
        select hour as start_time, lead(hour) over(order by hour) as end_time
        from times
    )
    select start_time, end_time, group_concat(person order by person) as persons
    from (
        select p2.person, 
            p1.start_time,
            p1.end_time
        from intervals p1
        join rooms p2 on 
            greatest(p1.start_time, p2.start_time) < least(p1.end_time, p2.end_time)
    ) t
    group by start_time, end_time
    ;

5   7   John
7   8   John,Mary
8   10  John,Mary,Peter
10  12  John,Peter
12  16  John
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search