PostgreSQL窗口函数忽略空值 [英] PostgreSQL Window Functions Ignore Null Values
问题描述
我对PostgreSQL来说还比较陌生,在忽略空值的同时尝试实现lag()函数时遇到了困难.根据文档, http://www.postgresql.org/docs/9.4/static/functions-window.html ,不可能简单地将参数传递给函数来实现此目的.
I am relatively new to PostgreSQL and am having a difficult time trying to implement the lag() function while ignoring null values. According to the docs, http://www.postgresql.org/docs/9.4/static/functions-window.html, it is not possible to simply pass a parameter to the function to achieve this.
有人知道任何解决方法吗?您可以组成任何示例,但如果有帮助,这里可以帮助您入门:
Does anyone know of any workaround? You can make up any example but if it helps here is something to get you started:
Col_A Col_B Col_A_Lag Col_B_Lag ID Date
VA_1 100 VA_1 100 AA Jan-1
null null VA_1 100 AA Jan-2
null null VA_1 100 AA Jan-3
VA_2 250 VA_2 250 AA Jan-4
VA_2 300 VA_2 300 AA Jan-5
null null VA_2 300 AA Jan-6
如果我是从tbl_x中提取的,则下面是一个简单的SQL脚本:
If I am pulling from tbl_x, here is a simple SQL script:
select
Col_A,
Col_B,
case when Col_A is null then lag(Col_A) over w else Col_A end as Col_A_Lag,
case when Col_B is null then lag(Col_B) over w else Col_B end as Col_B_Lag
from tbl_x
window w as (partition by ID order by Date)
此脚本将不返回我想要的内容,因为在滞后中回头"时它包含空值.
This script will not return what I want because it includes the null values when it 'looks back' in the lag.
提前谢谢.
推荐答案
我想你不能只是:
select
from tbl_x
window w as (partition by ID order by Date)
where col_a is null;
否则,您可能必须:
select
Col_A,
Col_B,
case when Col_A is null
then (select col_a
from tbl_x x2
where x2.id = x1.id
and col_a is not null
and x2.date < x1.date
order by date desc
limit 1)
else Col_A
end Col_A_Lag,
case when Col_B is null
then (select col_b
from tbl_x x2
where x2.id = x1.id
and col_b is not null
and x2.date < x1.date
order by date desc
limit 1)
else Col_B
end Col_B_Lag
from tbl_x x1;
通过适当的索引编制,性能可能会很好.
With appropriate indexing the performance would probably be pretty good.
create index ... (id, date desc, col_b) where col_b is not null;
这篇关于PostgreSQL窗口函数忽略空值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!