If a column is empty, select non-null. If a column is empty, select

If a column is empty, select non-null. If a column is empty, select

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 |
2 |
3 |
4 |
5 |
6 | bb
7 | bb
8 | bb
9 | x
10 | x
(10 rows)

It can be as follows:
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 |
2 |
3 |
4 |
5 |
6 | bb
7 | bb
8 | bb
9 | x
10 | x
(10 rows)

You can also use window functions as follows:
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 |
2 | a | 2 |
3 | 2 |
4 | 2 |
5 | 2 |
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 |
2 | a | 2 |
3 | 2 |
4 | 2 |
5 | 2 |
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 |
2 |
3 |
4 |
5 |
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
Bytes ------------------------------------------------------------------------------------
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
Bytes ---------------------------------------------------------------------------------------
Windowless (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)
-> Windowse (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.

Reference
Http://stackoverflow.com/questions/18987791/how-do-i-efficiently-select-the-previous-non-null-value

 

 

 

-----------------
Please refer to the following source for reprinting:
Blog.csdn.net/beiigang