如何在 PostgreSQL 中获取每分钟运行的行数？

PostgreSQL：按分钟计算运行行数

本文演示了如何获取查询的运行行数（在 PostgreSQL 中按分钟分组）。 挑战在于准确计算每分钟的行数，即使是那些没有活动的行。

利用窗口函数

最有效的解决方案是利用 PostgreSQL 强大的窗口函数。 这些函数对行集进行操作，从而实现跨多行的计算。 在这里，我们在按分钟分区的窗口中使用 COUNT 函数。

使用 SELECT DISTINCT 和 ORDER BY

进行高效查询

此查询使用 SELECT DISTINCT 确保唯一的分钟条目，并使用带有窗口函数的 COUNT 来生成运行计数。 ORDER BY 保证每分钟间隔的正确累积和。

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

理解窗口函数语法

让我们分解一下窗口函数：

• COUNT(*)：计算每个分钟分区内的行数。
• OVER (ORDER BY date_trunc('minute', "when"))：将窗口定义为一系列行，按分钟排序，从头开始累加计数。

零活动处理分钟

要包含没有活动的分钟（零行），我们使用 generate_series 创建一系列分钟，然后执行 LEFT JOIN 来包含这些分钟，即使它们在表中缺少相应的条目：

<code class="language-sql">SELECT DISTINCT
       minute, count(c.minute) OVER (ORDER BY minute) AS running_ct
FROM  (
   SELECT generate_series(date_trunc('minute', min("when")), max("when"), interval '1 min')
   FROM   mytable
   ) m(minute)
LEFT   JOIN (SELECT date_trunc('minute', "when") AS minute FROM mytable) c USING (minute)
ORDER  BY 1;</code>

性能注意事项

虽然窗口函数通常最适合大型数据集，但对于较小的数据集，使用 SUM() 的替代方案可能会提供更好的性能。此方法首先对每分钟的行进行分组和计数，然后使用 SUM() 计算运行总计：

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

最佳方法取决于您的数据集大小和性能要求。 建议测试这两种方法，以确定适合您的特定用例的最有效的解决方案。

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