时间戳

首页 > 解决方案 > PostgreSQL LAG() 函数可以引用自身吗？

postgresql - PostgreSQL LAG() 函数可以引用自身吗？

问题描述

我刚刚在 PostgreSQL 中发现了LAG() 函数，我一直在尝试看看它可以实现什么。虽然我可能会用它计算阶乘，但我写了

SELECT i, i * lag(factorial, 1, 1) OVER (ORDER BY i, 1) as factorial FROM generate_series(1, 10) as i;

但是在线IDE抱怨说42703 column "factorial" does not exist。

有什么方法可以访问之前 LAG 调用的结果吗？

标签： postgresql

解决方案

您不能在其定义中递归地引用该列。

但是，您可以将阶乘计算表示为：

SELECT i, EXP(SUM(LN(i)) OVER w)::int factorial
FROM generate_series(1, 10) i
WINDOW w AS (ORDER BY i ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW);
-- outputs:
 i  | factorial 
----+-----------
  1 |         1
  2 |         2
  3 |         6
  4 |        24
  5 |       120
  6 |       720
  7 |      5040
  8 |     40320
  9 |    362880
 10 |   3628800
(10 rows)

Postgresql 确实支持称为递归查询的高级 SQL 功能，它也可以用于递归地表示阶乘表：

WITH RECURSIVE series AS (
  SELECT i FROM generate_series(1, 10) i
)
, rec AS (
  SELECT i, 1 factorial FROM series WHERE i = 1
  UNION ALL
  SELECT series.i, series.i * rec.factorial 
  FROM series 
  JOIN rec ON series.i = rec.i + 1
)
SELECT * 
FROM rec;

做什么EXP(SUM(LN(i)) OVER w)：

这利用了以下数学恒等式：

[1]: log(a * b * c) = log (a) + log (b) + log (c)
[2]: exp (log a) = a
[combining 1&2]: exp(log a + log b + log c) = a * b * c

SQL 没有聚合乘法运算，所以要执行聚合乘法运算，我们首先要获取每个值的对数，然后我们可以使用 sum 聚合函数给我们值乘积的对数。我们用最后的求幂步骤反转。

只要被相乘的值是正数log（对于 0 和负数未定义），这就会起作用。如果你有负数或零，诀窍是检查任何值是否为 0，则整个聚合为 0，并检查负值的个数是否为偶数，则结果为正，否则为负。或者，您也可以将实数转换为复平面，然后使用恒等式Log(z) = ln(r) - iπ

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW做什么

这声明了一个扩展窗口框架，其中包括所有前面的行和当前行。

例如

when i equals 1 the values in this window frame are {1}
when i equals 2 the values in this window frame are {1,2}
when i equals 3 the values in this window frame are {1,2,3}

什么是递归查询

递归查询允许您使用 SQL 表达递归逻辑。递归查询通常用于从关系数据生成父子关系（想想经理报告或产品分类层次结构），但它们通常可用于查询任何树状结构。

这是我不久前写的一个 SO 答案，它说明并解释了递归查询的一些功能。

还有大量关于递归查询的有用教程。它是一个非常强大的 sql 语言特性，解决了一种在没有递归的情况下很难解决的问题。

希望这能让您更深入地了解代码的作用。快乐学习！

推荐阅读