sql - PostgreSQL 忽略窗口函数中的 NULLS

Question

左边面板数据没有IGNORE NULLS。
在右侧面板数据上使用 IGNORE NULLS。

所以我需要在 PostgreSQL 中获得正确的变体

需要在 PostgreSQL 的窗口函数(LEAD 和 LAG)中模拟 Oracle IGNORE NULLS。

SELECT empno,
   ename,
   orig_salary,
   LAG(orig_salary, 1, 0) IGNORE NULLS OVER (ORDER BY orig_salary) AS sal_prev
FROM   tbl_lead;

如果有NULL，则返回最新的非空值。

我已经通过 PostgreSQL 用户定义的聚合函数进行了尝试，但是很难理解它的方法 https://www.postgresql.org/docs/9.6/static/sql-createaggregate.html

解决方案不能通过WITH子句或子查询来实现，因为它用于复杂查询。

Answer 1

我更新了@klin 的回答。下面的函数允许传递任何元素，具有偏移量和默认参数。

滞后(表达式[，偏移[，默认]])

create or replace function swf_lag_trans(anyarray, anyelement, integer, 
anyelement)
returns anyarray language plpgsql as $$
begin
if $1 is null then
    $1:= array_fill($4, array[$3+1]);
end if;
if $1[$3+1] is not null then 
for i in 1..$3 loop
        $1[i]:= $1[i+1];
        i := i+1;
    end loop;
    $1[$3+1]:= $2;
end if;
return $1;
end $$;
create or replace function swf_lag_final(anyarray)
returns anyelement language sql as $$
select $1[1];
$$;
create aggregate swf_lag(anyelement, integer, anyelement) (
sfunc = swf_lag_trans,
stype = anyarray,
finalfunc = swf_lag_final
);

和用法:

with my_table(name, salary) as (
values
    ('A', 100),
    ('B', 200),
    ('C', 300),
    ('D', null),
    ('E', null),
    ('F', null)
)

select 
    name, salary, 
    lag(salary, 2, 123) over (order by salary) prev_salary,
    swf_lag(salary, 2, 123)  over (order by salary) my_prev_salary
from my_table;

它对我有用。如果需要，请更正。