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

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()通常用作简单的聚合函数。附加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;

类似于上面的内容，但是：

• 我使用子查询来汇总和计算每分钟的行数。这样，我们每分钟就能获得1行，而不会DISTINCT在外部SELECT。

• 用sum()现在的窗口集合函数从子查询加起来计数。

我发现这样做的速度大大提高，每分钟有很多行。

包括没有活动的分钟

最短的

@GabiMe在评论中询问如何在时间范围内的 每个
minute时间获取eone行，包括没有事件发生的行（基表中没有行）：

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。默认为0with COALESCE。

在使用Postgres 9.1-9.4测试的几个变体中 ，"when"该版本具有很多行和一个带有子查询的 索引 是最快的：

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;