How to Calculate a Running Row Count by Minute in PostgreSQL?

PostgreSQL: Count query result row count by minute

This article describes how to use window functions in PostgreSQL to calculate the number of running rows of query results by minutes.

1. Number of lines per minute

The following query returns the number of rows per minute:

<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>

2. Cumulative number of lines per minute

To calculate the cumulative number of rows per minute, you can use the following query:

<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
  minute;</code>

Instructions:

• date_trunc('minute', "when") Truncate the timestamp column "when" to the nearest minute.
• COUNT(*) OVER (ORDER BY date_trunc('minute', "when")) Counts the number of rows run per minute. The ORDER BY clause specifies the ordering of the aggregated rows.
• DISTINCT is used to ensure that only unique minutes are returned. This is because the COUNT aggregate function will return multiple rows for the same minute.

3. Including minutes with no activity

If you want to include minutes with no activity in the results, you can use the following query:

<code class="language-sql">SELECT
  m.minute,
  COALESCE(SUM(c.minute_count) OVER (ORDER BY m.minute), 0) AS running_count
FROM
  (
    SELECT
      generate_series(MIN(minute), MAX(minute), INTERVAL '1 minute') AS minute
    FROM
      (
        SELECT
          date_trunc('minute', "when") AS minute,
          COUNT(*) AS minute_count
        FROM
          mytable
        GROUP BY
          minute
      ) AS sub
  ) AS m
LEFT JOIN
  (
    SELECT
      date_trunc('minute', "when") AS minute,
      COUNT(*) AS minute_count
    FROM
      mytable
    GROUP BY
      minute
  ) AS c
ON
  m.minute = c.minute
ORDER BY
  m.minute;</code>

Instructions:

• This query uses LEFT JOIN to include minutes with no activity.
• generate_series() The function generates a range of minutes between the minimum and maximum minutes in the table.
The
• COALESCE() function ensures that minutes with no activity have a running count of 0.

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