如何高效计算 PostgreSQL 每分钟运行行数？

PostgreSQL中每分钟行计数的有效计算方法

获取行计数

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

此查询计算每分钟的行数，但不提供运行总数。

获取运行计数

方法一：仅返回有活动记录的分钟

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

此查询使用date_trunc()函数，返回每个活动分钟的行计数。它使用带ORDER BY子句的窗口函数计算运行总数。

包含无活动记录的分钟

方法二：使用带连接的子查询

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

此方法在一个子查询中聚合每分钟的行计数。然后，主查询连接它以累加计数，并包含没有活动的分钟。

方法三：使用CTE（最快）

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

此方法结合了CTE、子查询和左连接。对于具有“when”索引的大型数据集，这是一种有效的方法。

以上是如何高效计算 PostgreSQL 每分钟运行行数？的详细内容。更多信息请关注PHP中文网其他相关文章！