Estoy tratando de implementar lo que tengo en el código como una consulta de postgres. El siguiente ejemplo no es exactamente lo que estamos tratando de hacer, pero espero que muestre cómo estoy tratando de usar el valor de una fila previamente calculada en la siguiente.
Una tabla de muestra para ayudarme a demostrar lo que estoy tratando de hacer:
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 |Aquí está la consulta en curso:
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 ;Que devuelve lo que quiero hasta ahora...
id | new_score ----+----------- 1 | 1.25 2 | 0.5 3 | 4 |La siguiente iteración de la consulta:
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 ;La diferencia es esta:
case when field1 = 2 then 0.75 * lag (new_score) endSé y entiendo por qué esto no funcionará.
He creado un alias para el campo calculado como new_score y cuando field1 = 2, quiero 0,75 * el valor new_score de las filas anteriores. Entiendo que new_score es un alias y no se puede usar.
¿Hay alguna manera de que pueda lograr esto? Podría intentar copiar esa expresión, envolverla con un lag, alias eso como otra cosa y tratar de trabajar con eso, pero eso sería muy complicado.
¿Algunas ideas?
Muchas gracias.
Postgres le permite usar ventanas en declaraciones CASE . Probablemente te estabas perdiendo la parte OVER (ORDER BY id) . También puede definir diferentes ventanas, pero no puede usar ventanas junto con GROUP BY . Además, no le permitirá usar ventanas de anidación, por lo que debe anotar algunas subconsultas o CTE.
Aquí está la consulta:
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 ) inner1El código para crear y llenar la tabla:
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);La consulta devuelve esta salida:
id | new_score ----+----------- 1 | 1.25 2 | 0.50 3 | 0.3750 4 |