sql - PostgreSQL 中的窗口函数尾随日期

Question

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

假设我有一个名为 iptable 的 IP 地址和连接记录表，其中 soucreip、destinationip、timestamp 作为列。这是我在前 7 天窗口中尝试获取每个 sourceip 的计数的查询:

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;

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

Answer 1

部分问题是您选择了一个糟糕的列名 "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...
                                                             ^

这表明当前的窗口函数实现不会执行您希望它执行的操作。可悲的是。

The value PRECEDING and value FOLLOWING cases are currently only allowed in ROWS mode. They indicate that the frame starts or ends with the row that many rows before or after the current row. value must be an integer expression not containing any variables, aggregate functions, or window functions.

相反，我只是在输入行上使用带有 WHERE 过滤器的普通 GROUP BY。

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;