Count devices per day in a given date range

Question

I have a table which has devices with 3 statuses, Pass, Fail and Warning.

Device	Status	Date
Device1	Pass	12/1/2020
Device2	Fail	12/1/2020
Device3	Warning	12/1/2020
Device1	Fail	12/2/2020
Device2	Warning	12/2/2020
Device3	Pass	12/2/2020

I want to generate a trend graph of count of devices based on the daily status. The count is on all the devices for each day. The table above will have device data repeated for multiple dates.

Example: I want to generate a stacked bar graph, which will show count of devices which are pass, fail or warning. Need to get a query which I can use to get the response back with DateTime, count of failed devices, count of devices passed, count of devices having warning over a range of dates.

select * (select count(*) from status_table where overall_status = 'Fail' and startDate > "" and endDate < "") as failedCount,
(select count(*) from status_table  where overall_status = 'Warning' and startDate > "" and endDate < "") as WarningCount,
(select count(*) from status_table  where overall_status = 'Pass'  startDate > "" and endDate < "") as passCount from status_table

Is there a better solution?

Answer 1

You can use the aggregate FILTER clause to do it in a single query.

This gets three counts (fail, pass, warn) for every selected device on every day in the selected date range. A count of NULL for days without any appearance. 0 if the device appeared, but not with this status:

SELECT date, device_name
     , fail_count, warning_count, pass_count
FROM  (SELECT DISTINCT device_name FROM status_table) d  -- all devices ①
CROSS  JOIN (
   SELECT generate_series(timestamp '2020-12-01'
                        , timestamp '2020-12-31'
                        , interval  '1 day')::date
   ) t(date)  -- all dates
LEFT   JOIN (
   SELECT date, device_name
        , count(*) FILTER (WHERE overall_status = 'Fail')    AS fail_count
        , count(*) FILTER (WHERE overall_status = 'Warning') AS warning_count
        , count(*) FILTER (WHERE overall_status = 'Pass')    AS pass_count 
   FROM   status_table
   WHERE  date >= '2020-12-01'  -- same date range as above
   AND    date <= '2020-12-31'
   GROUP  BY 1, 2
   ) s USING (date, device_name)
ORDER  BY 1, 2;

Basically, you CROSS JOIN all devices to all dates (Cartesian product), the append data where data can be found with a LEFT JOIN.

① Since you don't seem to have a device table (which you probably should), generate the full list on the fly. The above query with DISTINCT is good for few rows per device. Else, there are (much) faster techniques like:

WITH RECURSIVE cte AS (
   (SELECT device_name FROM status_table ORDER BY 1 LIMIT 1)
   UNION ALL
   SELECT (SELECT device_name FROM status_table
           WHERE  device_name > t.device_name ORDER BY 1 LIMIT 1)
   FROM   cte
   WHERE  device_name IS NOT NULL
   )
SELECT * FROM cte
WHERE  device_name IS NOT NULL;

See:

• https://wiki.postgresql.org/wiki/Loose_indexscan

The subquery s aggregates only rows from the given date range. It's strictly optional. You can also left-join to the underlying table directly, and then aggregate all. But this approach is typically (much) faster.

You can convert NULL to zero or vice versa with COALESCE / NULLIF.

Related:

• PostgreSQL: running count of rows for a query 'by minute'

• Aggregate columns with additional (distinct) filters

For more flags, a crosstab() query might be faster. See:

• PostgreSQL Crosstab Query

About generating a date range:

• Generating time series between two dates in PostgreSQL

Be aware that dates are defined by your current time zone setting if you operate with timestamp with time zone. See:

• Ignoring time zones altogether in Rails and PostgreSQL