PostgreSQL中的窗口函数尾随日期 [英] Window function trailing dates in PostgreSQL

问题描述

我正在尝试编写一个查询，将给定计算机今天的平均连接数与7到14天前的平均连接数进行比较。我认为这最好由窗口函数来处理，但我无法获得正确的日期语法。

假设我有一个名为iptable的IP地址和连接记录表，其中ourcreip、estinationip、Timestamp为列。以下是我针对前7天窗口尝试的查询，以仅获取每个来源的计数：

select 
  sourceip, 
  destinationip, 
  timestamp, 
  count(*) OVER (PARTITION BY sourceip order by timestamp
                 RANGE BETWEEN now() - '7 day'::Interval PRECEDING
                               now() - '14 day'::Interval FOLLOWING)
from
 iptable;

编写此类查询的最佳方法是窗口函数方法有意义吗，还是有更优化的方法来处理大型表的情况？

推荐答案

问题的一部分是您选择了一个糟糕的列名"timestamp"。timestamp是内置数据类型的名称，因此要将其用作列名，必须在任何位置都使用它。

但

这还不是全部。您的窗口函数语法错误。请参见window function syntax。您忘记了AND；它是RANGE BETWEEN .. PRECEDING AND ... FOLLOWING。

另外，尽管它不是问题的原因，但您应该使用SQL标准current_timestamp而不是now()。

这将使您遇到新的错误：

CREATE TABLE iptable ( sourceip cidr, destinationip cidr, "timestamp" timestamptz);

regress=> select 
  sourceip, 
  destinationip, 
  timestamp, 
  count(*) OVER (PARTITION BY sourceip order by "timestamp" RANGE BETWEEN current_timestamp - '7 day'::Interval PRECEDING AND current_timestamp - '14 day'::Interval FOLLOWING)

from
 iptable;
ERROR:  RANGE PRECEDING is only supported with UNBOUNDED
LINE 5: ... OVER (PARTITION BY sourceip order by "timestamp" RANGE BETW...
                                                             ^

这表明当前窗口函数实现不会执行您希望它执行的操作。很遗憾。

前面的值和后面的值当前仅 在行模式下允许。它们指示帧的开头或结尾为 当前行之前或之后的行。值必须为 不包含任何变量、聚合的整数表达式 函数或窗口函数。

相反，我只会在输入行上使用普通GROUP BY和WHERE筛选器。

select 
  sourceip,
  count(sourceip) AS n_conns_7_to_14_days_ago
from
 iptable
WHERE age("timestamp") BETWEEN INTERVAL '7' DAY AND INTERVAL '14' DAY
GROUP BY sourceip;

这篇关于PostgreSQL中的窗口函数尾随日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！