sql - 使用窗口函数动态选择任意 n 列

Question

我的真实选择语句摘要:

select 
  lag(somecol) over (partition by thing_id) as prev_1,
  lag(somecol,2) over (partition by thing_id) as prev_2,
  lag(somecol,3) over (partition by thing_id) as prev_3,
  othercol,
  ...

在实际查询中，over 要复杂得多，这会导致非常密集、不可读的代码。此外，获取最后 3 行是硬编码的(相对于 n=whatever)。

在直接 SQL 中是否有任何方法可以迭代或递归地指定这些 prev_x 列，以便 1) 代码更具可读性和 2) 您可以动态指定前列的数量 n？

Answer 1

为了仅回答第一个问题，为了使代码更具可读性，Postgres 允许定义窗口，为其命名，然后在查询中多次引用它。

请参阅 Window Functions 的文档:

When a query involves multiple window functions, it is possible to write out each one with a separate OVER clause, but this is duplicative and error-prone if the same windowing behavior is wanted for several functions. Instead, each windowing behavior can be named in a WINDOW clause and then referenced in OVER. For example:

SELECT sum(salary) OVER w, avg(salary) OVER w
FROM empsalary
WINDOW w AS (PARTITION BY depname ORDER BY salary DESC);

我不知道这个特性是否是 SQL 标准的一部分，但我知道 SQL Server 不支持它。

因此，您的查询将如下所示:

select 
  lag(somecol) over w as prev_1,
  lag(somecol,2) over w as prev_2,
  lag(somecol,3) over w as prev_3,
  othercol,
  ...
from
  ...
WINDOW w AS (partition by thing_id)
;

---

关于你的第二个问题如何“动态指定前列的数量 n”——你需要动态生成 SELECT 语句的文本来实现它。 RDBMS 假定稳定模式，即表和查询中的列数通常是固定的，而不是动态的。