How to Generate a Running Row Count in PostgreSQL for Each Minute, Including Inactive Minutes?

PostgreSQL: Generating a Running Row Count by Minute, Including Inactive Periods

This article demonstrates how to generate a running count of rows in a PostgreSQL table for each minute, even if some minutes have no activity.

Method 1: Running Count for Active Minutes Only

This approach uses window functions to calculate a running count for each minute where activity exists:

<code class="language-sql">SELECT DISTINCT
       date_trunc('minute', "when") AS minute,
       count(*) OVER (ORDER BY date_trunc('minute', "when")) AS running_count
FROM   mytable
ORDER  BY 1;</code>

date_trunc('minute', "when") groups rows by minute. The count(*) OVER (ORDER BY date_trunc('minute', "when")) function provides a running total ordered by minute.

Method 2: Including Inactive Minutes

To include minutes with zero activity, we use generate_series to create a complete minute sequence and a LEFT JOIN to incorporate the counts:

<code class="language-sql">WITH cte AS (
   SELECT date_trunc('minute', "when") AS minute, COUNT(*) AS minute_ct
   FROM   mytable
   GROUP  BY 1
)
SELECT m.minute, COALESCE(sum(cte.minute_ct) OVER (ORDER BY m.minute), 0) AS running_count
FROM  (
   SELECT generate_series(min(minute), max(minute), interval '1 min') AS minute
   FROM   cte
) m
LEFT   JOIN cte USING (minute)
ORDER  BY 1;</code>

A CTE (cte) groups rows by minute and counts them. generate_series creates a series of minutes spanning the entire time range. The LEFT JOIN combines this series with the counts from cte, and COALESCE handles minutes with no activity by assigning a running count of 0. The sum() OVER (ORDER BY m.minute) then calculates the cumulative sum.

The above is the detailed content of How to Generate a Running Row Count in PostgreSQL for Each Minute, Including Inactive Minutes?. For more information, please follow other related articles on the PHP Chinese website!