今天在对数据进行补充时,我有这么一个表单数据,其中病人的每日体重数据是有所缺失的,我需要利用最近一天的非空值进行填充。
pid | day | weight |
---|---|---|
10001 | 1 | 47 |
10001 | 2 | (null) |
10001 | 3 | 50 |
10002 | 1 | 47 |
10002 | 2 | (null) |
10002 | 3 | (null) |
10002 | 3 | 50 |
我查询到的SQL代码虽然提供了一个解决思路
Oracle解决方法:都是利用last_value或者lag,然后对非空值进行排序,其中需要利用到psql很多没有的语法——
nvl(last_value(case when col2 > 0 then col2 end) ignore nulls over (order by original_order$ rows between unbounded preceding and current row), col2) as col2
根据PostgreSQL的官方手册说明:这种方法寄了,不提供ignore nulls的方法。
下面的思路为:利用count的效果,进行聚合标签的标定。
当count(1),count(1, NULL)与count(1, NULL,NULL)结果都是1,count(1, NULL, NULL, 10)的结果是2,则此时,前三行会与1对应的形成一个聚合组,第四行单独形成聚合组。
pid | day | weight | count |
---|---|---|---|
10001 | 1 | 47 | 1 |
10001 | 2 | (null) | 1 |
10001 | 3 | 50 | 2 |
10002 | 1 | 47 | 1 |
10002 | 2 | (null) | 1 |
10002 | 3 | (null) | 1 |
10002 | 3 | 50 | 2 |
此时根据count和pid聚合对空值进行填充即可。
--前沿补值
select pid, weight, coalesce(weight, max(weight) over (partition by pid, day))
from (select b.*, count(weight) over (partition by pid order by day) as grp
from base b
) b
order by idx;
--后沿补值
select pid, weight, coalesce(weight, max(weight) over (partition by pid, day))
from (select b.*, count(weight) over (partition by pid order by day desc) as grp
from base b
) b
order by idx;