PostgreSQL - 如何获取先前（lag）计算的值

Question

我想要获取之前（延迟）计算出的数值？

   id  | value
-------|-------
    1  |   1
    2  |   3
    3  |   5
    4  |   7
    5  |   9

我想要实现的是这样一个功能：

   id  | value | new value
-------|-------|-----------
   1   |   1   |   10      <-- 1 * lag(new_value)
   2   |   3   |   30      <-- 3 * lag(new_value)
   3   |   5   |  150      <-- 5 * lag(new_value)
   4   |   7   | 1050      <-- 7 * lag(new_value)
   5   |   9   | 9450      <-- 9 * lag(new_value)

我尝试过的方法：

SELECT value,
       COALESCE(lag(new_value) OVER () * value, 10) AS new_value
FROM table

错误：

错误：列“new_value”不存在

- Willem

---

@JuanCarlosOropeza 是的，伙计。我应该在这里使用什么？递归CTE还是Lateral join？ - Willem

递归CTE。很简单。如果需要更多帮助，请告诉我。https://www.postgresql.org/docs/9.4/static/queries-with.html - Juan Carlos Oropeza

你的值是 {1,2,3,4,5}，但之后变成了 {1,3,5,7,9}？ - Juan Carlos Oropeza

那是打错字了，我的错。我想表达的是初始集是随机的。 - Willem

无关，但是：你不需要使用coalesce()来配合lag函数。你可以直接将默认值传递给lag()函数：lag(some_col, 1, 0)。 - user330315

Answer 1

抱歉，这并不像我想的那么容易。虽然已经得到了一个非常接近的结果，但仍需要一些调整。

演示

WITH RECURSIVE t(n, v) AS (
    SELECT MIN(value), 10 
    FROM Table1

    UNION ALL
    SELECT (SELECT min(value) from Table1 WHERE value > n), 
           (SELECT min(value) from Table1 WHERE value > n) * v
    FROM t
    JOIN Table1 on t.n = Table1.value    
)    
SELECT n, v 
FROM t;

- Juan Carlos Oropeza

---

至少你得到了结果。+1 - Willem

如果您在表格上添加一个ID，那将会更容易 ;) - Juan Carlos Oropeza

我有ID。所以你可以将其更改为ID的工作。我会更新问题。 - Willem

你不应该接受，也许其他人已经找到了正确的答案。除非你自己找到了，那么请发布它。 - Juan Carlos Oropeza

Answer 2

类似于Juan的答案，但我还是想发表一下。它至少避免了需要ID列，并且没有最后的空行：

with recursive all_data as (
  select value, value * 10 as new_value
  from data
  where value = 1

  union all

  select c.value, 
         c.value * p.new_value
  from data c
    join all_data p on p.value < c.value
  where c.value = (select min(d.value) 
                   from data d 
                   where d.value > p.value)
)
select *
from all_data
order by value;

这个想法是将递归部分中的一行与一个“父”行精确地连接。虽然“只有一个父级”可以通过派生表和侧向连接来实现（令人惊讶的是允许limit），但不幸的是，从递归部分中的“子”中选择“恰好一行”只能使用带有min()的子查询来完成。

如果在递归部分中也可以使用order by和limit，那么where c.value= (...)就不是必需的，但不幸的是，在当前的Postgres版本中不支持这样做。

在线示例：http://rextester.com/WFBVM53545

- user330315

---

好的。我会试一下你的例子。我还在努力让Juan的工作起来。但是我成功的是限制和偏移量。只需将每个语句用括号括起来。(SELECT ..... LIMIT 1) UNION ALL (SELECT .... OFFSET 1)。 - Willem

我会根据OP解释数据是随机的，将基本情况更改为MIN(value)。 - Juan Carlos Oropeza

是的，那个“order by - limit”限制让我的生活变得很痛苦:( - Juan Carlos Oropeza

将横向连接更改为join all_data p on p.value < c.value可以提高50%的速度... - Willem

@Willem：你说得对，使用select min()实际上并不需要。 - user330315