如何选择最后一个不为空的值?

Question

我有以下表格:

id a b 
1  1 kate
1  4 null
1  3 paul
1  3 paul
1  2 lola
2  1 kim
2  9 null
2  2 null

结果应该是这样的:

1 3 paul
2 1 kim

我想要得到最后一个a，其中b is not null。比如:

select b
from (select,b 
row_num() over (partition by id order by a desc) as num) as f
where num = 1

但是这样我得到一个空值，因为最后一个a = 4对应于b IS NULL。也许有一种方法重写ffill方法从熊猫?

Answer 1

假设:

• a定义为NOT NULL。
• 您需要a最大的行，其中b IS NOT NULL-每id。

SELECT DISTINCT ON (id) *
FROM   tbl
WHERE  b IS NOT NULL
ORDER  BY id, a DESC;

db<此处小提琴>

详细解释:

• 选择每个GROUP BY组的第一行?

Answer 2

尝试:

select id, a, b
from (select id, a, b,
row_num() over (partition by id order by a desc nulls last) as num
from unnamedTable) t
where num = 1

或者，如果不正确，请尝试使用nulls first。我永远记不起desc的工作方式。

Answer 3

如果您不能保证每个id至少有一个非空，那么您将希望将空移到列表的底部，而不是完全过滤掉这些行。

select id, a, b
from (
select id, a, b, 
row_number() over (
partition by id
order by case when b is not null then 0 else 1 end, a desc
) as num
) as f
where num = 1

Answer 4

如果您希望保持原始列的原样，您可以将其包装在cte和join周围，将其返回到主表，但是查看您预期的输出和逻辑，这应该可以做到。话虽如此，基于row_number()的方法可能会快一点。

select distinct 
id, 
max(a) over (partition by id) as a, 
first_value(b) over (partition by id order by a desc) as b
from tbl
where b is not null;