sql - PostgreSQL:运行查询的行数 'by minute'

Question

我需要查询每分钟截至该分钟的总行数。

到目前为止，我所能达到的最好成绩并没有达到目的。它返回每分钟的计数，而不是每分钟的总计数:

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

Answer 1

仅返回有事件的分钟数

最短

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，因为您想要 GROUP BY 分钟切片。

count() 通常用作普通 aggregate function .添加 OVER 子句使其成为 window function .在窗口定义中省略 PARTITION BY - 您需要对所有行进行运行计数。。默认情况下，按照 ORDER BY 的定义，从第一行计数到当前行的最后一个对等行。 The manual :

The default framing option is RANGE UNBOUNDED PRECEDING, which is thesame as RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. With ORDER BY,this sets the frame to be all rows from the partition start upthrough the current row's last ORDER BY peer.

而这恰好正是您所需要的。

使用 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;

和上面很像，但是:

我使用子查询来聚合和计算每分钟的行数。这样我们每分钟得到 1 行，而在外部 SELECT 中没有 DISTINCT。

现在使用 sum() 作为窗口聚合函数来将子查询的计数相加。

我发现每分钟有很多行，速度要快得多。

包括没有事件的分钟数

最短

@GabiMe asked in a comment如何在时间范围内获取每 分钟 的行，包括那些没有发生事件的行(基表中没有行):

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;