For example, the question that someone in the front-end time group asked is just blank and I will pay attention to it. Status quo dbone # select * fromtb_test; id | uname | addr ---- + ------- + ------ 1 | a | 2 | a | 3 | 4 | 5 | 6 | bb | 7 | 8 | 9 | x | 10 | (10 rows) expected results: id | uname ---- + ------- 1 | a2
For example, the question that someone in the front-end time group asked is just blank and I will pay attention to it. Status quo dbone = # select * from tb_test; id | uname | addr ---- + ------- + ------ 1 | a | 2 | a | 3 | 4 | 5 | 6 | bb | 7 | 8 | 9 | x | 10 | (10 rows) expected results: id | uname ---- + ------- 1 | a2
For example, the question that someone in the front-end time group asked is just blank and I will pay attention to it.
Status quo
Dbone = # select * from tb_test; id | uname | addr ---- + ------- + ------ 1 | a | 2 | a | 3 | 4 | 5 | 6 | bb | 7 | 8 | 9 | x | 10 | (10 rows) expected results: id | uname ---- + ------- 1 | a2 | a3 | a4 | a5 | a6 | bb7 | bb8 | bb9 | x10 | x (10 rows) can be as follows: dbone = # select B. id, casewhen B. uname! = ''Then B. unameelse (select a. uname from tb_test a where a. uname! = ''And a. id
You can also use window functions as follows:
dbone=# SELECTdbone-# id, uname, uname_partition, first_value(uname) over (partition by uname_partition order by id)dbone-# FROM (dbone(# SELECTdbone(# id,dbone(# uname,dbone(# sum(case when uname is null then 0 else 1 end) over (order by id) as uname_partitiondbone(# FROM tb_testdbone(# ORDER BY id ASCdbone(# ) as q;id | uname | uname_partition | first_value----+-------+-----------------+-------------1 | a | 1 | a2 | a | 2 | a3 | | 2 | a4 | | 2 | a5 | | 2 | a6 | bb | 3 | bb7 | | 3 | bb8 | | 3 | bb9 | x | 4 | x10 | | 4 | x(10 rows)dbone=# \timingTiming is on.dbone=# SELECTid, uname, uname_partition, first_value(uname) over (partition by uname_partition order by id)FROM (SELECTid,uname,sum(case when uname is null then 0 else 1 end) over (order by id) as uname_partitionFROM tb_testORDER BY id ASC) as q;id | uname | uname_partition | first_value----+-------+-----------------+-------------1 | a | 1 | a2 | a | 2 | a3 | | 2 | a4 | | 2 | a5 | | 2 | a6 | bb | 3 | bb7 | | 3 | bb8 | | 3 | bb9 | x | 4 | x10 | | 4 | x(10 rows)Time: 0.805 msdbone=# select b.id,casewhen b.uname!='' then b.unameelse (select a.uname from tb_test a where a.uname!='' and a.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 msdbone=#dbone=#dbone=#dbone=#dbone=#dbone=# explain SELECTid, uname, uname_partition, first_value(uname) over (partition by uname_partition order by id)FROM (SELECTid,uname,sum(case when uname is null then 0 else 1 end) over (order by id) as uname_partitionFROM tb_testORDER 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
There are only 10 records, with little performance difference. If there are too many records, you should use window functions to speed up.