1

I have the following table:

id      start_date                 end_date                    status
----------------------------------------------------------------------
1a      2020-04-09 21:34:00.000    2020-04-13 15:46:52.000      green
1a      2020-04-13 15:46:52.000    2020-04-16 15:46:52.000      red
1a      2020-04-16 15:46:52.000    null                         yellow
2a      2020-04-09 20:33:00.000    2020-04-16 15:46:52.000      green
2a      2020-04-16 15:46:52.000    2020-04-21 15:46:52.000      red
2a      2020-04-21 15:46:52.000    null                         yellow
3a      2020-04-09 21:34:00.000    2020-04-09 22:34:00.000      green
3a      2020-04-09 22:34:00.000    2020-04-09 23:36:00.000      red
3a      2020-04-09 23:36:00.000    2020-04-09 23:38:00.000      yellow
3a      2020-04-09 23:38:00.000    null                         red

I am interested to count how many unique ids were in each state everyday between two time ranges. So I need to generate days between two time ranges as a starter. For example, item with id 1a was green everyday from 2020-04-09 21:34:00.000 to 2020-04-13 15:46:52.000 and I need to count that.

Edit: The most important thing is that if one item changes several states on the same day, I need to count it only once for the most recent state on a day. For example, item with id 3a changed states several times on 2020-04-09. However, I only care about the most recent state which is red (on 2020-04-09 23:38:00.000). This is to make sure that I don't artificially inflate the data.

My desired output is this:

day           state          total       ids
2020-04-09    green          2           [1a, 2a]
2020-04-09.   yellow         0           []
2020-04-09.   red            1           [3a]
2020-04-10.   green          2           [1a, 2a]
2020-04-10.   yellow         0           []
.....etc         

I have this query:

 select serie.day as time,
 a.state,
 count(a.start_date) as total,
 a.id
 from (
 SELECT date_series::date AS day
    FROM generate_series(
        '2020-04-09 00:00:00.000'::date,
        '2020-04-24 15:22:22.000',
        '1 day'
    ) AS date_series
 ) as serie
 left join event_entries a on a.start_date::date = serie.day::date 
 group by serie.day, a.state, a.id order by time

But it only records the total # ids per state on the start_date and nothing in between. How can I refactor this query to produce the desired result?

Here is the fiddle

8
  • @Vérace-getVACCINATEDNOW, I updated my post with the fiddle
    – kris
    Dec 8, 2021 at 6:04
  • @Vérace-getVACCINATEDNOW yes, I updated the table to set the end_date for the last entry of 3a to null, indicating that 3a is currently in a red state as of 2020-04-09 23:38:00.000
    – kris
    Dec 8, 2021 at 6:29
  • @Akina, I have a fiddle with the scripts to create the table + fill it with the data. I did not include the entire output as it is very long and you get the point when reading the problem.
    – kris
    Dec 8, 2021 at 6:31
  • 1
    Well, show complete desired output for your fiddle (and do not forget that the data in the fiddle differs from one in the question text).
    – Akina
    Dec 8, 2021 at 6:45
  • I have edited my post to say that if an item changed to different states on the same day, I am only interested to count it once for the most recent state on that day.
    – kris
    Dec 8, 2021 at 6:49

1 Answer 1

1

The following solution assumes that most IDs (event_entries.id) have entries for most days. For different data distributions, different solutions will be (much) faster. You didn't specify.

Full solution:

SELECT (the_day AT TIME ZONE 'UTC')::date AS "day"  -- UTC days
     , state
     , COALESCE(e.ct, 0) AS total
     , COALESCE(e.ids, '{}') AS ids
FROM  (
   SELECT d.the_day, a.state, count(*) AS ct, array_agg(i.id) AS ids
   FROM   generate_series(timestamptz '2020-04-09 00:00:00+0'  -- UTC timestamps
                        , timestamptz '2020-04-24 15:22:22+0'
                        , interval    '1 day') d(the_day)
   CROSS  JOIN (SELECT DISTINCT id FROM event_entries) AS i  -- there are faster ways
   CROSS  JOIN LATERAL (
      SELECT state
      FROM   event_entries a
      WHERE  a.id = i.id
      AND    a.start_date <  d.the_day + interval '1 day'
      AND   (a.end_date   >= d.the_day OR a.end_date IS NULL)
      ORDER  BY a.end_date DESC  -- NULLS FIRST is the default we need
      LIMIT  1
      ) a
   GROUP  BY d.the_day, a.state
   ) e
RIGHT JOIN (
   generate_series(timestamptz '2020-04-09 00:00:00+0'  -- UTC timestamps
                 , timestamptz '2020-04-24 15:22:22+0'
                 , interval    '1 day') d(the_day)
   CROSS JOIN (SELECT DISTINCT state FROM event_entries) s -- there are faster ways
   ) e0  USING (the_day, state)
ORDER  BY the_day, state;

db<>fiddle here

The core solution is:

SELECT (d.the_day AT TIME ZONE 'UTC')::date AS "day"  -- UTC days
     , a.state
     , count(*) AS ct
     , array_agg(i.id) AS ids
FROM   generate_series(timestamptz '2020-04-09 00:00:00+0'  -- UTC timestamps
                     , timestamptz '2020-04-24 15:22:22+0'
                     , interval    '1 day') d(the_day)
CROSS  JOIN (SELECT DISTINCT id FROM event_entries) AS i  -- there are faster ways
CROSS  JOIN LATERAL (
   SELECT state
   FROM   event_entries a
   WHERE  a.id = i.id
   AND    a.start_date <  d.the_day + interval '1 day'
   AND   (a.end_date   >= d.the_day OR a.end_date IS NULL)
   ORDER  BY a.end_date DESC  -- NULLS FIRST is the default we need
   LIMIT  1
   ) a
GROUP  BY d.the_day, a.state
ORDER  BY d.the_day, a.state;

RIGHT JOIN that to a Cartesian product of all states and days to include missing states with a total of 0, and arrive at the full solution above.

Explanation

The core solution starts with a Cartesian Product (CROSS JOIN) of all days of interest (d) and all IDs (i). Note the adapted generate_series() expression. Since your table holds timestamptz, it's simplest and fastest to work with timestamptz all the way. This also fixes the widespread, sneaky corner-case error with dates silently depending on the current TimeZone setting of the session. I assumed UTC days. Replace with your time zone. See:

To work with standard calendar days, the first argument to generate_series() should have a 00:00 time component. You can adapt to shift days any way you like.

Since the same derived table generated with generate_series() is used twice, we might break that out into a CTE. But generate_series() is so fast that the overhead hardly pays.

The LATERAL subquery a then picks the latest ( = effective) state for each of these combinations. The special case end_date IS NULL counts as latest entry. Since NULL sorts first in descending order, this falls in line effortlessly. See:

A multicolumn index on (id, start_date, end_date DESC) should help performance a lot. See:

Alternatively, consider the SQL OVERLAPS operator, or (maybe best) tstzrange data types (and matching indexes). See:

In the outer SELECT, GROUP BY & ORDER BY to arrive at your counts.

I added the not "there are faster ways" two times for subqueries that should really be replaced with tables readily holding unique rows for (much) better performance. You didn't specify what's at our disposal.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.