PostgreSQL. Реализация Lag ignore nulls.

В заметке приведён вариант реализации конструкции lag(my_val) ignore nulls для PostgreSQL.

Тренироваться будем на таблице scott.emp. Скрипты создания таблицы и её наполнения можно взять на моём github

Итак, наша задача получить из этого датасета

select empno, comm from scott.emp

такой:

В Oracle эта задача решалась бы в 1 строчку

select empno
, comm
, lag(comm) ignore nulls over (order by empno) comm_ignore_nulls
from scott.emp

В PostgreSQL, к сожалению, ignore nulls не завезли, поэтому приходится использовать подзапрос.

--Решение в PostgreSQL
with cte_grp as(
select empno, comm
,count (comm) over (order by empno) grp
from scott.emp)
select empno, comm
,max(comm) over (partition by grp) comm_ignore_nulls
from cte_grp

Зачем это может понадобиться в реальной жизни? Например, как один из этапов в решении задачи gaps and islands.

P.S. Для того, чтобы получить вместо lag lead, нужно изменить порядок сортировки: order by empno desc