Postgres查询:下一步使用先前动态创建的COLUM值

Question

我正在尝试将代码中的内容作为postgres查询实现。下面的示例并不是我们要做的，但我希望它显示我如何尝试使用下一个先前计算的行中的值。

一个示例表可以帮助我演示我要做什么：

test=# select * from test ;
 id | field1 | field2 | field3 | score
----+--------+--------+--------+-------
  1 |      1 |      3 |      2 |  1.25
  2 |      1 |     -1 |      1 |
  3 |      2 |      1 |      5 |
  4 |      3 |     -2 |      4 |

这是正在进行的查询：

select id,
    coalesce (
            score,
            case when lag_field3 = 2 then 0.25*(3*field1+field2) end
    ) as new_score
from (
    select id, field1, field2, field3, score,
    lag (field3)  over (order by id) as lag_field3
    from test
) inner1 ;

返回到目前为止我想要的东西...

 id | new_score
----+-----------
  1 |      1.25
  2 |       0.5
  3 |
  4 |

查询的下一个迭代：

select id,
    coalesce (
            score,
            case when lag_field3 = 2 then 0.25*(3*field1+field2) end,
            case when field1 = 2 then 0.75 * lag (new_score) end
    ) as new_score
from (
select id, field1, field2, field3, score,
    lag (field3)  over (order by id) as lag_field3
from test
) inner1 ;

区别在于：

case when field1 = 2 then 0.75 * lag (new_score) end

我知道并了解为什么这不起作用。

我将计算出的字段称为new_score，当field1 = 2时，我想要0.75 *先前的行new_score值。我知道new_score是一个别名，不能使用。

有什么办法可以实现这一目标？我可以尝试复制该表达式，围绕它缠绕一个滞后，别名，它是其他东西并尝试使用它，但这会变得非常混乱。

有什么想法？

非常感谢。

Answer 1

Postgres允许您在CASE语句中使用Windows。您可能缺少OVER (ORDER BY id)部分。您还可以定义不同的窗口，但不能将窗口与GROUP BY结合使用。另外，它不会让您使用年鉴窗口，因此您必须写下一些子查询或CTE。

这是查询：

SELECT id, COALESCE(tmp_score,
                    CASE 
                        WHEN field1 = 2 
                            THEN 0.75 * LAG(tmp_score) OVER (ORDER BY id) 
                            -- missing ELSE statement here
                    END
           ) AS new_score
FROM (
    SELECT id, field1,
        COALESCE (
                score,
                CASE 
                    WHEN LAG(field3) OVER (ORDER BY id) = 2 
                    THEN 0.25*(3*field1+field2) 
                END
        ) AS tmp_score
    FROM test
) inner1

创建和填充表的代码：

CREATE TABLE test(
    id int,
    field1 int,
    field2 int,
    field3 int,
    score numeric
);
INSERT INTO test VALUES
(1, 1, 3, 2, 1.25),
(2, 1, -1, 1, NULL),
(3, 2, 1, 5, NULL),
(4, 3, -2, 4, NULL);

查询返回此输出：

 id | new_score 
----+-----------
  1 |      1.25
  2 |      0.50
  3 |    0.3750
  4 |