PostgreSQL：运行查询行的计数“分钟”

概述我需要查询每分钟的总计数，直到那一分钟。 到目前为止，我所能达到的最好的并不是诀窍。它每分钟返回计数，而不是每分钟的总计数： SELECT COUNT(id) AS count , EXTRACT(hour from "when") AS hour , EXTRACT(minute from "when") AS minute FROM mytable GROUP BY 我需要查询每分钟的总计数，直到那一分钟。

到目前为止，我所能达到的最好的并不是诀窍。它每分钟返回计数，而不是每分钟的总计数：

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

只有几分钟的活动

最短

不会比这更简单：

SELECT disTINCT       date_trunc('minute',"when") AS minute,count(*) OVER (ORDER BY date_trunc('minute',"when")) AS running_ctFROM   mytableORDER  BY 1;

>使用date_trunc().它给你准确的你所需要的。
>不要在查询中包含ID，因为你想要GROUP BY分片。
> count()主要用作纯aggregate function.附加一个OVER子句使其成为window function.在窗口定义中忽略PARTITION BY – 您希望在所有行上运行计数。默认情况下，这是从ORDER BY定义的当前行的第一行到最后一个对等体。 I quote the manual：

The default framing option is RANGE UNBOUNDED PRECEDING,which is the
same as RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW; it sets the
frame to be all rows from the partition start up through the current
row’s last peer in the ORDER BY ordering.

这正是你所需要的。
>使用count(*)而不是count(ID)。它更适合你的问题(“行数”)。它通常比count(ID)稍快。而且，虽然我们可能认为ID不为NulL，但是在问题中还没有指定，所以count(ID)严格来说是错误的。
>您不能在同一查询级别的GROUP BY分片。在窗口函数之前应用聚合函数，窗口函数count(*)每分钟只能看到1行。
但是，您可以选择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,"when")) AS running_ctFROM   mytableORDER  BY date_trunc('minute',"when");

最快的

SELECT minute,sum(minute_ct) OVER (ORDER BY minute) AS running_ctFROM  (   SELECT date_trunc('minute',count(*) AS minute_ct   FROM   tbl   GROUP  BY 1   ) subORDER  BY 1;

很像上面，但是：

>我使用一个子查询来折叠并计算每分钟的行数。
>这样我们可以在外部查询中每分钟得到不同的行，并且不需要disTINCT步骤。
>现在使用sum()作为窗口聚合函数从子查询中加入计数。

我发现这是每分钟许多行快得多。

包括没有活动的分钟

最短

@GabiMe在评论中询问如何在时间范围内每分钟获得一行，包括那些没有事件发生的位置(基表中没有行)：

SELECT disTINCT       m.minute,count(c.minute) OVER (ORDER BY m.minute) AS running_ctFROM  (SELECT generate_serIEs(date_trunc('minute',min("when")),max(minute),'1 min') AS minute FROM tbl) mleft   JOIN (SELECT date_trunc('minute',"when") AS minute FROM tbl) c                                                        USING (minute)ORDER  BY 1;

>使用generate_series()在第一个和最后一个事件之间的时间范围内为每一分钟生成一行。将generate_serIEs()与聚合函数组合在一个子查询中。
> left JOIN，将所有时间戳缩短到分和计数。 NulL值(没有行存在)不添加到运行计数。

最快的

与CTE：

WITH cte AS (   SELECT date_trunc('minute',count(*) AS minute_ct   FROM   tbl   GROUP  BY 1   ) SELECT m.minute,COALESCE(sum(c.minute_ct) OVER (ORDER BY m.minute),0) AS running_ctFROM  (SELECT generate_serIEs(date_trunc('minute','1 min') AS minute FROM cte) mleft   JOIN cte c USING (minute)ORDER  BY 1;

很像上面，但是：

>再次，在第一步中每分钟折叠和计数行数，省略了后来的disTINCT的需要。
>不同于count()，sum()可以返回NulL。所以我把它包裹在COALESCE中以取代0。

每分钟有很多行和几行，并且使用索引“when”这个带有子查询的版本应该更快：

SELECT m.minute,max("when"),'1 min') AS minute FROM tbl) mleft   JOIN (   SELECT date_trunc('minute',count(*) AS minute_ct   FROM   tbl   GROUP  BY 1   ) c USING (minute)ORDER  BY 1;

>这是我用Postgres 9.1 – 9.4测试的几个变体中最快的。

总结

以上是内存溢出为你收集整理的PostgreSQL：运行查询行的计数“分钟”全部内容，希望文章能够帮你解决PostgreSQL：运行查询行的计数“分钟”所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。