为了账号安全,请及时绑定邮箱和手机立即绑定

请问PostgreSQL:运行查询“按分钟”的行数

/ 猿问

请问PostgreSQL:运行查询“按分钟”的行数

繁华开满天机 2019-10-22 17:12:47

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

我需要为每分钟查询到该分钟的行总数。

到目前为止,我所能达到的最好成绩并不能做到这一点。它返回每分钟的计数,而不是每分钟的总计数:

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


查看完整描述

2 回答

?
MM们

只返回有活动的分钟

最短

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()通常用作普通聚合函数..附加OVER从句使它成为窗口函数..省略PARTITION BY在窗口定义中-您想要一个运行计数一排排。默认情况下,这将从当前行的第一行到当前行的最后一个对等点进行计数。ORDER BY我引用手册:

    默认的框架选项是RANGE UNBOUNDED PRECEDING,这与RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW..带着ORDER BY,这将框架设置为从分区启动到当前行的最后一行的所有行。ORDER BY同侪。

    而这恰好是一点儿没错你所需要的。

  • 使用count(*)而不是count(id)..它最好适合你的问题(“行数”)。一般是轻微的更快count(id)..虽然我们可能会假设idNOT NULL,问题中没有具体说明,所以count(id)不对,严格地说,因为空值不被计算为count(id).

  • 你不能GROUP BY同一查询级别的分钟切片。应用聚合函数以前窗口函数count(*)这样每分钟只能看到一行。
    但是,你可以,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_ctFROM   mytableORDER  BY date_trunc('minute', "when");

最快

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

很像上面提到的,但是:

  • 我使用子查询来每分钟聚合和计数行。这样我们每分钟就可以得到一排DISTINCT在外面SELECT.

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

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

包括没有活动的分钟

最短

@GabiMe在评论中问如何获得一个一行每一,每个 minute在时间范围内,包括没有发生事件的时间(基表中没有行):

SELECT DISTINCT
       minute, count(c.minute) OVER (ORDER BY minute) AS running_ctFROM  (
   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_ctFROM  (
   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..默认为0带着COALESCE.

有许多行和一个索引"when"在我用Postgres 9.1-9.4测试过的几个变体中,带有子查询的这个版本是最快的:

SELECT m.minute     , COALESCE(sum(c.minute_ct) OVER (ORDER BY m.minute), 0) AS running_ctFROM  (
   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;



查看完整回答
反对 回复 2019-10-23
?
青春有我

我们都有一个问题,“数行切成分钟”VS(它返回计数每分钟“VS”,但计数的每分钟。我建议你重新考虑和修改你的问题,以澄清这些不一致之处。

查看完整回答
反对 回复 2019-10-23

添加回答

回复

举报

0/150
提交
取消
意见反馈 帮助中心 APP下载
官方微信