某列為空白時選擇前面的非空值,某列為選擇

來源:互聯網
上載者:User

某列為空白時選擇前面的非空值,某列為選擇

如題,前端時間群裡有人問的這個問題,正好有空,就關注了一下。

現狀
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


這兒只有十條記錄,效能相差不大,如果記錄多還是要用視窗函數來的快


參考
http://stackoverflow.com/questions/18987791/how-do-i-efficiently-select-the-previous-non-null-value

 

 

 


-----------------
轉載請著明出處:
blog.csdn.net/beiigang

相關文章

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.