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?

By *count of devices*, you mean the count of rows per device, right? Please always declare your version of Postgres. And by *daily status* you mean .. one row per day for every device in the table? Or what exactly? — Erwin Brandstetter, Jan 28 '21 at 00:31

Erwin Brandstetter · Accepted Answer · 2021-01-28T02:44:06.203

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.

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

That seems great. How can I get the date also. Basically, I want to get device counts which are failed, pass, and warning for every day (date) , between a start date and end date. — Ash, Jan 28 '21 at 00:33
@Ash: For every day, ok. And for what devices per day exactly? I updated to provide one possible interpretation. — Erwin Brandstetter, Jan 28 '21 at 00:34
This is the problem statement - Draw a stacked bar chart, Y axis will have Device Count and X-Axis will have Date. User can select a range of dates for which the Y Axis will show devices, Failed, Pass, And warning in the form of a stacked bar for every date falling within the range. — Ash, Jan 28 '21 at 00:38
@Ash: OK, one data point per day. But for which devices? All devices in the table / every day? Or all devices in the time frame? Or just for those devices that happen to have data for the day? Or is "device count" supposed to mean number of (distinct?) devices per day? (My comment under the question.) — Erwin Brandstetter, Jan 28 '21 at 00:39
Yes. All devices in the table/Every day - If there is no data it should show empty bar. But should display date on x-axis that there is no data for that day. — Ash, Jan 28 '21 at 00:42
Is there a table `device` listing all devices? Or do you intend to extract the list of distinct devices from the same table? If so, all devices in the whole table? Or just those popping up in the time frame? Please update your question to clarify. — Erwin Brandstetter, Jan 28 '21 at 00:44

Count devices per day in a given date range

1 Answers1