某列为空时选择前面的非空值

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了某列为空时选择前面的非空值，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含3856字，纯文字阅读大概需要6分钟。

内容图文

如题，前端时间群里有人问的这个问题，正好有空，就关注了一下。 现状 dbone=# select * from tb_test;id | uname | addr----+-------+------1 | a |2 | a |3 | |4 | |5 | |6 | bb |7 | |8 | |9 | x |10 | |(10 rows)期望结果：id | uname----+-------1 | a2

如题，前端时间群里有人问的这个问题，正好有空，就关注了一下。

现状

dbone=# select * from tb_test;
id | uname | addr
----+-------+------
1 | a |
2 | a |
3 | |
4 | |
5 | |
6 | bb |
7 | |
8 | |
9 | x |
10 | |
(10 rows)

期望结果：
id | uname
----+-------
1 | a
2 | a
3 | a
4 | a
5 | a
6 | bb
7 | bb
8 | bb
9 | x
10 | x
(10 rows)

可以如下：
dbone=# select b.id,
case
when b.uname!='' then b.uname
else (select a.uname from tb_test a where a.uname!='' and a.id<b.id order by a.id desc limit 1)
end
from tb_test b order by b.id;
id | uname
----+-------
1 | a
2 | a
3 | a
4 | a
5 | a
6 | bb
7 | bb
8 | bb
9 | x
10 | x
(10 rows)

也可以用窗口函数，如下：

dbone=# SELECT
dbone-# id, uname, uname_partition, first_value(uname) over (partition by uname_partition order by id)
dbone-# FROM (
dbone(# SELECT
dbone(# id,
dbone(# uname,
dbone(# sum(case when uname is null then 0 else 1 end) over (order by id) as uname_partition
dbone(# FROM tb_test
dbone(# ORDER BY id ASC
dbone(# ) as q;
id | uname | uname_partition | first_value
----+-------+-----------------+-------------
1 | a | 1 | a
2 | a | 2 | a
3 | | 2 | a
4 | | 2 | a
5 | | 2 | a
6 | bb | 3 | bb
7 | | 3 | bb
8 | | 3 | bb
9 | x | 4 | x
10 | | 4 | x
(10 rows)


dbone=# \timing
Timing is on.
dbone=# SELECT
id, uname, uname_partition, first_value(uname) over (partition by uname_partition order by id)
FROM (
SELECT
id,
uname,
sum(case when uname is null then 0 else 1 end) over (order by id) as uname_partition
FROM tb_test
ORDER BY id ASC
) as q;
id | uname | uname_partition | first_value
----+-------+-----------------+-------------
1 | a | 1 | a
2 | a | 2 | a
3 | | 2 | a
4 | | 2 | a
5 | | 2 | a
6 | bb | 3 | bb
7 | | 3 | bb
8 | | 3 | bb
9 | x | 4 | x
10 | | 4 | x
(10 rows)


Time: 0.805 ms
dbone=# select b.id,
case
when b.uname!='' then b.uname
else (select a.uname from tb_test a where a.uname!='' and a.id<b.id order by a.id desc limit 1)
end
from tb_test b order by b.id;
id | uname
----+-------
1 | a
2 | a
3 | a
4 | a
5 | a
6 | bb
7 | bb
8 | bb
9 | x
10 | x
(10 rows)


Time: 0.920 ms
dbone=#


dbone=# explain select b.id,
case
when b.uname!='' then b.uname
else (select a.uname from tb_test a where a.uname!='' and a.id<b.id order by a.id desc limit 1)
end
from tb_test b order by b.id;
QUERY PLAN
------------------------------------------------------------------------------------
Sort (cost=12.97..12.99 rows=10 width=82)
Sort Key: b.id
-> Seq Scan on tb_test b (cost=0.00..12.80 rows=10 width=82)
SubPlan 1
-> Limit (cost=1.16..1.17 rows=1 width=82)
-> Sort (cost=1.16..1.17 rows=3 width=82)
Sort Key: a.id
-> Seq Scan on tb_test a (cost=0.00..1.15 rows=3 width=82)
Filter: (((uname)::text <> ''::text) AND (id < b.id))
(9 rows)


Time: 1.880 ms
dbone=#
dbone=#
dbone=#
dbone=#
dbone=#
dbone=# explain SELECT
id, uname, uname_partition, first_value(uname) over (partition by uname_partition order by id)
FROM (
SELECT
id,
uname,
sum(case when uname is null then 0 else 1 end) over (order by id) as uname_partition
FROM tb_test
ORDER BY id ASC
) as q;
QUERY PLAN
---------------------------------------------------------------------------------------
WindowAgg (cost=1.71..1.91 rows=10 width=90)
-> Sort (cost=1.71..1.73 rows=10 width=90)
Sort Key: q.uname_partition, q.id
-> Subquery Scan on q (cost=1.27..1.54 rows=10 width=90)
-> WindowAgg (cost=1.27..1.44 rows=10 width=82)
-> Sort (cost=1.27..1.29 rows=10 width=82)
Sort Key: tb_test.id
-> Seq Scan on tb_test (cost=0.00..1.10 rows=10 width=82)
(8 rows)


Time: 0.770 ms

这儿只有十条记录，性能相差不大，如果记录多还是要用窗口函数来的快

内容总结

以上是互联网集市为您收集整理的某列为空时选择前面的非空值全部内容，希望文章能够帮你解决某列为空时选择前面的非空值所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端