PostgreSQL：按分钟运行查询结果行数的计数

Question

PostgreSQL：按分钟运行查询结果行数的计数

sqlpostgresqldatetimeaggregate-functionswindow-functions

47

我需要针对每分钟查询到该分钟之前的总行数。

到目前为止，我能够做到的最好的方法并不能实现这一点。它返回每分钟的计数，而不是每分钟之前的总计数：

SELECT COUNT(id) AS count
     , EXTRACT(hour from "when") AS hour
     , EXTRACT(minute from "when") AS minute
  FROM mytable
 GROUP BY hour, minute

- GabiMe

你想要列数的总和还是计数？这个示例查询不够清晰。 - Martijn

行之和是什么意思？ - Krzysztof

1

我们都遇到了“每分钟切片的行数”与“（它返回每分钟的计数）”与“但是每分钟的计数”的问题。我建议您重新考虑并编辑您的问题，以澄清这些不一致之处。 - Michael Durrant

在我看来，你应该这样做：

SELECT MAX(rnum) AS count, EXTRACT(hour from "when") AS hour, EXTRACT(minute from "when") AS minute FROM (SELECT mytable.*, ROW_NUMBER() OVER(ORDER BY "When") as rnum FROM mytable) GROUP BY hour, minute

。其中rnum应该是行号（但我不记得如何实现了）。 - Krzysztof

@Michael Durrant - 好的，我重新表述了问题。 - GabiMe

显示剩余5条评论

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Erwin Brandstetter · Accepted Answer

只返回有活动的分钟

最短

SELECT DISTINCT
       date_trunc('minute', "when") AS minute
     , count(*) OVER (ORDER BY date_trunc('minute', "when")) AS running_ct
FROM   mytable
ORDER  BY 1;

使用date_trunc()函数，它会返回你需要的精确结果。

不要在查询中包含id字段，因为你想对每分钟进行聚合。

count()通常用作普通的聚合函数。附加一个OVER子句可以将其变成窗口函数。在窗口定义中省略PARTITION BY - 你想对所有行进行累计计数。默认情况下，它从第一行开始计算，直到当前行根据ORDER BY定义的最后一个同级为止。手册:

默认的框架选项是RANGE UNBOUNDED PRECEDING，与RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW相同。通过ORDER BY，这将设置该框架为从分区开始到当前行的最后一个ORDER BY同侪的所有行。

这恰好是你所需的。

使用count(*)而不是count(id)。它更符合你的问题（“行数计数”）。通常比count(id)稍微快一些。虽然我们可能假设id是NOT NULL，但在问题中没有指定，因此count(id)严格来说是错误的，因为NULL值不会被计入count(id)。

你不能在同一查询级别上按分钟切片GROUP BY。聚合函数在窗口函数之前应用，这种方式窗口函数count(*)每分钟只能看到1行。
但是，你可以使用SELECT DISTINCT，因为DISTINCT在窗口函数之后应用。

ORDER BY 1在这里只是ORDER BY date_trunc('minute', "when")的简写。
1是对SELECT列表中第一个表达式的位置引用。

如果需要格式化结果，请使用to_char()。例如：

SELECT DISTINCT
       to_char(date_trunc('minute', "when"), 'DD.MM.YYYY HH24:MI') AS minute
     , count(*) OVER (ORDER BY date_trunc('minute', "when")) AS running_ct
FROM   mytable
ORDER  BY date_trunc('minute', "when");

最快

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;

与上面类似，但是:

我使用子查询来聚合和计算每分钟的行数。这样我们就可以在外部SELECT中不使用DISTINCT得到每分钟1行。

现在将sum()作为窗口聚合函数，以从子查询中累加计数。

我发现这种方式在每分钟有很多行的情况下速度明显更快。

包含没有活动的分钟

最短的

@GabiMe 在评论中问道如何获得时间范围内每个分钟的一行，包括那些没有事件发生的时间（基本表中没有行）：

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   tbl
   ) m(minute)
LEFT   JOIN (SELECT date_trunc('minute', "when") FROM tbl) c(minute) USING (minute)
ORDER  BY 1;

使用generate_series()在第一个和最后一个事件之间的时间范围内为每一分钟生成一行 - 直接基于子查询的聚合值。

LEFT JOIN到所有被截断到分钟并计数的时间戳。NULL值（没有行存在的地方）不会增加运行计数。

最快

使用CTE：

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;

再次，首先按分钟聚合和计数行，它省略了后续使用DISTINCT的需要。

与count()不同，sum()可以返回NULL。使用COALESCE默认为0。

对于许多行和在"when"上建立的索引，我在Postgres 9.1-9.4中测试了几个变体，这个带有子查询的版本是最快的：

SELECT m.minute
     , COALESCE(sum(c.minute_ct) OVER (ORDER BY m.minute), 0) AS running_ct
FROM  (
   SELECT generate_series(date_trunc('minute', min("when"))
                        ,                      max("when")
                        , interval '1 min')
   FROM   tbl
   ) m(minute)
LEFT   JOIN (
   SELECT date_trunc('minute', "when") AS minute
        , count(*) AS minute_ct
   FROM   tbl
   GROUP  BY 1
   ) c USING (minute)
ORDER  BY 1;