How to Efficiently Calculate Running Row Counts per Minute in PostgreSQL?

Efficient calculation method of row count per minute in PostgreSQL

Get row count

<code class="language-sql">SELECT COUNT(id) AS count,
       EXTRACT(hour FROM "when") AS hour,
       EXTRACT(minute FROM "when") AS minute
FROM mytable
GROUP BY hour, minute;</code>

This query counts rows per minute but does not provide a running total.

Get running count

Method 1: Only return minutes with activity records

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

This query uses the date_trunc() function to return the row count for each active minute. It calculates the total number of runs using a window function with a ORDER BY clause.

Contains minutes with no activity

Method 2: Use subquery with join

<code class="language-sql">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;</code>

This method aggregates row counts per minute in a subquery. The main query then joins it to accumulate the count and include minutes with no activity.

Method 3: Use CTE (fastest)

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

This method combines CTE, subquery and left join. This is an efficient approach for large data sets with "when" indexes.

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