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

如题，前端时间群里有人问的这个问题，正好有空，就关注了一下。 现状 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!=&#39;&#39; 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!=&#39;&#39; 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!=&#39;&#39; then b.uname
else (select a.uname from tb_test a where a.uname!=&#39;&#39; 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 <> &#39;&#39;::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

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