Как эффективно выбрать предыдущее ненулевое значение?

У меня есть таблица в Postgres, которая выглядит так:

# select * from p;
 id | value 
----+-------
  1 |   100
  2 |      
  3 |      
  4 |      
  5 |      
  6 |      
  7 |      
  8 |   200
  9 |          
(9 rows)

И я бы хотел запросить, чтобы он выглядел следующим образом:

# select * from p;
 id | value | new_value
----+-------+----------
  1 |   100 |    
  2 |       |    100
  3 |       |    100
  4 |       |    100
  5 |       |    100
  6 |       |    100
  7 |       |    100
  8 |   200 |    100
  9 |       |    200
(9 rows)

Я уже могу сделать это с подзапросом в select, но в моих реальных данных у меня есть 20k или более строк, и это становится довольно медленным.

Можно ли это сделать в оконной функции? Я хотел бы использовать lag(), но, похоже, он не поддерживает опцию IGNORE NULLS.

select id, value, lag(value, 1) over (order by id) as new_value from p;
 id | value | new_value
----+-------+-----------
  1 |   100 |      
  2 |       |       100
  3 |       |      
  4 |       |
  5 |       |
  6 |       |
  7 |       |
  8 |   200 |
  9 |       |       200
(9 rows)

Ответ 1

Я нашел этот ответ для SQL Server, который также работает в Postgres. Я никогда не делал этого раньше, я думал, что техника была довольно умной. В принципе, он создает настраиваемый раздел для функции windowing, используя оператор case внутри вложенного запроса, который увеличивает сумму, когда значение не равно null, и оставляет его в одиночку в противном случае. Это позволяет разграничить каждый нулевой раздел с тем же номером, что и предыдущее ненулевое значение. Здесь запрос:

SELECT
  id, value, value_partition, first_value(value) over (partition by value_partition order by id)
FROM (
  SELECT
    id,
    value,
    sum(case when value is null then 0 else 1 end) over (order by id) as value_partition

  FROM p
  ORDER BY id ASC
) as q

И результаты:

 id | value | value_partition | first_value
----+-------+-----------------+-------------
  1 |   100 |               1 |         100
  2 |       |               1 |         100
  3 |       |               1 |         100
  4 |       |               1 |         100
  5 |       |               1 |         100
  6 |       |               1 |         100
  7 |       |               1 |         100
  8 |   200 |               2 |         200
  9 |       |               2 |         200
(9 rows)

Ответ 2

Ну, я не могу гарантировать, что это самый эффективный способ, но работает:

SELECT id, value, (
    SELECT p2.value
    FROM p p2
    WHERE p2.value IS NOT NULL AND p2.id <= p1.id
    ORDER BY p2.id DESC
    LIMIT 1
) AS new_value
FROM p p1 ORDER BY id;

Следующий индекс может улучшить подзапрос для больших наборов данных:

CREATE INDEX idx_p_idvalue_nonnull ON p (id, value) WHERE value IS NOT NULL;

Предполагая, что value разрежен (например, есть много нулей), он будет работать нормально.

Ответ 3

Вы можете создать настраиваемую функцию агрегации в Postgres. Здесь приведен пример типа int:

CREATE FUNCTION coalesce_agg_sfunc(state int, value int) RETURNS int AS
$$
    SELECT coalesce(value, state);
$$ LANGUAGE SQL;

CREATE AGGREGATE coalesce_agg(int) (
    SFUNC = coalesce_agg_sfunc,
    STYPE  = int);

Затем выполните запрос как обычно.

SELECT *, coalesce_agg(b) over w, sum(b) over w FROM y
  WINDOW w AS (ORDER BY a);

a b coalesce_agg sum 
- - ------------ ---
a 0            0   0
b ∅            0   0
c 2            2   2
d 3            3   5
e ∅            3   5
f 5            5  10
(6 rows)

Ответ 4

Вы можете использовать LAST_VALUE с FILTER для достижения того, что вам нужно (по крайней мере, в PG 9.4)

WITH base AS (
SELECT 1 AS id , 100 AS val
UNION ALL
SELECT 2 AS id , null AS val
UNION ALL
SELECT 3 AS id , null AS val
UNION ALL
SELECT 4 AS id , null AS val
UNION ALL
SELECT 5 AS id , 200 AS val
UNION ALL
SELECT 6 AS id , null AS val
UNION ALL
SELECT 7 AS id , null AS val
)
SELECT id, val, last(val) FILTER (WHERE val IS NOT NULL) over(ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) new_val
  FROM base