Коллеги, а кто-то знает оптимальный (и по скорости, и по

читаемости) способ заполнять NULL в sparse данных? Делать своеобразный forward fill по многим столбцам по партициям.

Сейчас решил вот таким образом, но, возможно, здесь у кого-то будут советы получше?

(в CTE products подобие исходной таблицы)

WITH products AS (
SELECT
unnest(ARRAY[1,1,1,1,2,2,2]) AS product_id,
unnest(ARRAY['2020-10-01','2020-10-02','2020-10-03','2020-10-04','2020-10-01','2020-10-02','2020-10-03']) AS change_date,
unnest(ARRAY[1,NULL,2,NULL,1,NULL,NULL]) AS status,
unnest(ARRAY[100,110,NULL,120,110,100,NULL]) AS price
)

SELECT
product_id,
change_date,
(jsonb_agg(status) FILTER (WHERE status IS NOT NULL) OVER w) ->> -1 AS status,
(jsonb_agg(price) FILTER (WHERE price IS NOT NULL) OVER w) ->> -1 AS price
FROM products dts
WINDOW w AS (PARTITION BY product_id ORDER BY change_date)
;

Похоже, что Вам нужен "IGNORE NULLS" в оконных функциях вроде LAG/LEAD (он есть в ISO SQL и некоторых СУБД, но не в PostgreSQL). Т.е. погуглите способы его эмуляции и посмотрите, какой больше понравится.

Да, именно это меня бы и устроило, но большинство советов по его эмуляции сводятся к кастомным функциям, чего хотелось бы избежать :)

Мне кажется, там было как минимум четыре способа — пара с вложенными запросами с оконными функциями, с массивами (вроде того, что у Вас, но без JSON), и custom functions, да. Вы всех их посмотрели / сравнили?

С вложенными запросами посмотрел, сравнил с массивами на сравнительно небольшом объеме данных, разница минимальна была во времени выполнения, но читаемость ухудшается, т.к. нужно условно создавать псевдо-партиции с кумулятивной суммой, а потом по ним делать first_value. Поэтому пока остановился на массивах, но решил спросить здесь, может кто-то ещё как-то решал эту проблему. Мало ли какие хитрые способы кому в голову приходили)

> разница минимальна Вообще, это как-то странно (казалось бы, все эти сворачивания/разворачивания JSON совсем не бесплатны). > на сравнительно небольшом объеме данных, Может, дело именно в этом? > т.к. нужно условно создавать псевдо-партиции с кумулятивной суммой, Ну да, что-то такое там было... но Вы могли бы показать все найденные решения, если они у Вас уже есть (чтобы не повторяться). > Мало ли какие хитрые способы кому в голову приходили Коррелированные подзапросы? ;)

можно сделать свою агреггатную ф-цию, которая будет возвращать массив и игнорировать NULL-ы, но возникает заковырка с получением последнего элемента массива — негативные индексы как в случае с JSON* типами не работают. т.е. надо городить ещё и pop-функцию. ваше решение вполне компактное, хорошо читается и, главное, реализуется стандартными средствами.

Если нужен просто аналог "LAG(an_expression) OVER (... IGNORE NULLS)", достаточно чего-то такого, по идее: CREATE FUNCTION lag_ignore_nulls_sfunc(state anyelement, value anyelement) RETURNS anyelement AS $$ SELECT COALESCE(value, state); $$ LANGUAGE SQL IMMUTABLE; CREATE AGGREGATE lag_ignore_nulls(anyelement) ( SFUNC = lag_ignore_nulls_sfunc, STYPE = anyelement );