Skip to main content
added 1899 characters in body
Source Link
Jeremy
  • 6.6k
  • 18
  • 20

To expand on this answer a bit, you can use row_number() to give you a good idea of the frame you are looking at. For your proposed solution, look at the row numbers for each row, when you partition by id:

SELECT id, row_number() OVER (PARTITION BY id ORDER BY case WHEN value IS NULL THEN 0 ELSE 1 END ASC)
FROM test;
 id | row_number
----+------------
  0 |          1
  1 |          1
  2 |          1
  3 |          1
  4 |          1
  5 |          1
  6 |          1
(7 rows)

Each row is its own frame, so you won't be able to get anything values from other rows.

If we don't partition by id, but still use your ordering, you can see why this still won't work for last_value:

 SELECT id, row_number() OVER (ORDER BY case WHEN value IS NULL THEN 0 ELSE 1 END ASC, id)
FROM test;
 id | row_number
----+------------
  5 |          1
  0 |          2
  1 |          3
  2 |          4
  3 |          5
  4 |          6
  6 |          7
(7 rows)

In this case, the row that was NULL is first. By default, last_value will include rows up to the current row, which in this case is just the current row for id 5. You could include all rows in your frame:

SELECT id, 
  row_number() OVER (ORDER BY case WHEN value IS NULL THEN 0 ELSE 1 END ASC, 
id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), 
  last_value(value) OVER (ORDER BY case WHEN value IS NULL THEN 0 ELSE 1 END ASC, id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
FROM test;
 id | row_number | last_value
----+------------+------------
  5 |          1 | F
  0 |          2 | F
  1 |          3 | F
  2 |          4 | F
  3 |          5 | F
  4 |          6 | F
  6 |          7 | F
(7 rows)

But now the last row is the end of the frame and it's clearly not what you want. If you're looking for the previous row, choose lag().

To expand on this answer a bit, you can use row_number() to give you a good idea of the frame you are looking at. For your proposed solution, look at the row numbers for each row, when you partition by id:

SELECT id, row_number() OVER (PARTITION BY id ORDER BY case WHEN value IS NULL THEN 0 ELSE 1 END ASC)
FROM test;
 id | row_number
----+------------
  0 |          1
  1 |          1
  2 |          1
  3 |          1
  4 |          1
  5 |          1
  6 |          1
(7 rows)

Each row is its own frame, so you won't be able to get anything values from other rows.

If we don't partition by id, but still use your ordering, you can see why this still won't work for last_value:

 SELECT id, row_number() OVER (ORDER BY case WHEN value IS NULL THEN 0 ELSE 1 END ASC, id)
FROM test;
 id | row_number
----+------------
  5 |          1
  0 |          2
  1 |          3
  2 |          4
  3 |          5
  4 |          6
  6 |          7
(7 rows)

In this case, the row that was NULL is first. By default, last_value will include rows up to the current row, which in this case is just the current row for id 5. You could include all rows in your frame:

SELECT id, 
  row_number() OVER (ORDER BY case WHEN value IS NULL THEN 0 ELSE 1 END ASC, 
id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), 
  last_value(value) OVER (ORDER BY case WHEN value IS NULL THEN 0 ELSE 1 END ASC, id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
FROM test;
 id | row_number | last_value
----+------------+------------
  5 |          1 | F
  0 |          2 | F
  1 |          3 | F
  2 |          4 | F
  3 |          5 | F
  4 |          6 | F
  6 |          7 | F
(7 rows)

But now the last row is the end of the frame and it's clearly not what you want. If you're looking for the previous row, choose lag().

Source Link
Jeremy
  • 6.6k
  • 18
  • 20

I'm not sure last_value will do what you want. It would be better to use lag:

select id,
coalesce(value, lag(value) OVER (order by id))
FROM test;
 id | coalesce
----+----------
  0 | A
  1 | B
  2 | C
  3 | D
  4 | E
  5 | E
  6 | F
(7 rows)

last_value will return the last value of the current frame. Since you partitioned by id, there's only ever one value in the current frame. lag will return the previous row (by default) in the frame, which seems to be exactly what you want.