How to Calculate a Running Total of Rows per Minute in PostgreSQL?

PostgreSQL: Running total of query row count by minute

Problem Overview

You need to retrieve the total number of rows per minute up to that specific time.

The best way to calculate totals

SELECT DISTINCT date_trunc('minute', "when") AS minute,
       count(*) OVER (ORDER BY date_trunc('minute', "when")) AS running_ct
FROM   mytable
ORDER  BY 1;

This query uses the date_trunc() function to extract only the minutes portion of the "when" field. The count() function aggregated over this truncated minute component as a window function provides the running total.

The fastest way

SELECT minute, sum(minute_ct) OVER (ORDER BY minute) AS running_ct
FROM  (
   SELECT date_trunc('minute', "when") AS minute,
          count(*) AS minute_ct
   FROM   tbl
   GROUP  BY 1
   ) sub
ORDER  BY 1;

This query aggregates into subqueries to calculate the number of rows per minute. It then performs a running sum using a window function.

Handle minutes of inactivity

To ensure every minute is counted, even those with no activity:

SELECT m.minute,
       COALESCE(sum(c.minute_ct) OVER (ORDER BY m.minute), 0) AS running_ct
FROM  (
   SELECT generate_series(date_trunc('minute', min("when")),
                          max("when"),
                          interval '1 min')
   FROM   tbl
   ) m(minute)
LEFT   JOIN (
   SELECT date_trunc('minute', "when") AS minute,
          count(*) AS minute_ct
   FROM   tbl
   GROUP  BY 1
   ) c USING (minute)
ORDER  BY 1;

This query uses generate_series() to create a row for each minute in the range and LEFT JOIN to count and summarize the rows in the base table.

The above is the detailed content of How to Calculate a Running Total of Rows per Minute in PostgreSQL?. For more information, please follow other related articles on the PHP Chinese website!