Multiple Group by boolean fields with date interval

Question

I have a table like this:

User	Record Date	Online
Alice	2023/05/01	Online
Alice	2023/05/03	Online
Alice	2023/05/15	Online
Alice	2023/05/31	Offline
Alice	2023/06/01	Offline
Alice	2023/06/04	Offline
Alice	2023/06/20	Online
Alice	2023/06/21	Online

And I would like to have a result like this:

Alice was online from 2023/05/01 to 2023/05/15
Alice was offline from 2023/05/31 to 2023/06/04
Alice was online 2023/06/20 to 2023/06/21

I tried to use this query:

_users = (MyUserModel.objects.filter(record_date__year=2023)
        .values('online', 'record_date')
        .annotate(min_date=Min('record_date'), max_date=Max('record_date'))
        .order_by()
)

but it doesn't return a correct result

This seems very similar to https://stackoverflow.com/questions/24244659/group-consecutive-rows-of-same-value-using-time-spans. To my knowledge, those queries cannot be written with the ORM — Alombaros, Jul 17 '23 at 07:32

ps- · Answer 1 · 2023-07-17T07:37:01.837

Using the following approach -

WITH cte AS (
  SELECT
    `User`,
    `Record Date`,
    `Online`,
    ROW_NUMBER() OVER (PARTITION BY `User` ORDER BY `Record Date`) AS rn
  FROM t1
)
SELECT
  `User`,
  MIN(`Record Date`) AS start_date,
  MAX(`Record Date`) AS end_date,
  `Online` AS status
FROM cte
GROUP BY `User`, `Online`, DATE_SUB(`Record Date`, INTERVAL rn - 1 DAY)
ORDER BY `User`, start_date;

You should be able to achieve your desired result.

Fiddle Link

Edited: In case you want to group by online status in sequence, use this solution.

WITH cte AS (
SELECT 
  user,
  record_date,
  online,
  CASE
    WHEN @prev_online = online THEN @group_no
    ELSE @group_no := @group_no + 1
  END AS group_no,
  @prev_online := online
FROM t1, (SELECT @group_no := 0, @prev_online := '') AS vars
ORDER BY record_date
)
SELECT
  group_concat(distinct user),
  MIN(record_date) AS start_date,
  MAX(record_date) AS end_date,
  group_concat(distinct online) AS status
FROM cte
GROUP BY group_no
ORDER BY group_no;

Fiddle Link 2

Based on the output of your fiddle link, the result is a little different: there is not the sum of first period "Alice" was online (from 2023/05/01 to 2023/05/15) just as there is not the sum of period in which Alice was offline (from 2023/05/31 to 2023/06/04) — Sandy, Jul 17 '23 at 07:13

Multiple Group by boolean fields with date interval

1 Answers1