在窗口函数的 FILTER 子句中引用当前行

一尘不染

在窗口函数的 FILTER 子句中引用当前行

sql

在PostgreSQL 9.4 中，窗口函数有一个新选项 aFILTER来选择窗口框架的子集进行处理。文档提到了它，但没有提供示例。在线搜索产生了一些示例，包括来自2ndQuadrant 的示例，但我发现的所有示例都是带有常量表达式的相当琐碎的示例。我正在寻找的是一个包含当前行值的过滤器表达式。

假设我有一个包含一堆列的表，其中一列是date类型：

col1 | col2 |     dt
------------------------
  1  |  a   | 2015-07-01
  2  |  b   | 2015-07-03
  3  |  c   | 2015-07-10
  4  |  d   | 2015-07-11
  5  |  e   | 2015-07-11
  6  |  f   | 2015-07-13
...

一个用于处理date整个表的窗口定义被简单地构造：WINDOW win AS (ORDER BY dt)

我有兴趣知道在当前行（含）之前的 4 天中存在多少行。所以我想生成这个输出：

col1 | col2 |     dt     | count
--------------------------------
  1  |  a   | 2015-07-01 |   1
  2  |  b   | 2015-07-03 |   2
  3  |  c   | 2015-07-10 |   1
  4  |  d   | 2015-07-11 |   3
  5  |  e   | 2015-07-11 |   3
  6  |  f   | 2015-07-13 |   4
...

FILTER窗口函数的子句似乎是显而易见的选择：

count(*) FILTER (WHERE current_row.dt - dt <= 4) OVER win
但是我如何指定current_row.dt（因为缺乏更好的语法）？这甚至可能吗？

如果无法做到这一点，是否还有其他方法date可以在窗口框架中选择范围？框架规范没有帮助，因为它都是基于行的。

我对使用子查询的替代解决方案不感兴趣，它必须基于窗口处理。

阅读 139

2021-05-30

共1个答案

一尘不染

您实际上并未聚合行，因此新的聚合FILTER子句不是正确的工具。窗口函数更像它，但是仍然存在问题：窗口的帧定义不能取决于当前行的值。它只能计算该ROWS子句之前或之后的给定行数。

为了使这项工作，每天汇总计数和LEFT JOIN范围内的一整套天数。然后你可以应用一个窗口函数：

SELECT t.*, ct.ct_last4days
FROM  (
   SELECT *, sum(ct) OVER (ORDER BY dt ROWS 3 PRECEDING) AS ct_last4days
   FROM  (
      SELECT generate_series(min(dt), max(dt), interval '1 day')::date AS dt
      FROM   tbl t1
      ) d
   LEFT   JOIN (SELECT dt, count(*) AS ct FROM tbl GROUP BY 1) t USING (dt)
   ) ct
JOIN  tbl t USING (dt);

ORDER BY dt在寡妇框架定义中省略通常有效，因为顺序是从generate_series()子查询中继承的。但是在 SQL 标准中没有明确的保证，ORDER BY它可能会在更复杂的查询中中断。

2021-05-30