Postgresql中的filter

2380阅读 0评论2019-01-23 轨迹16
分类:Mysql/postgreSQL

Postgresql中的filter


代码例子


-- 建立表
postgres=# create table t(i int);
CREATE TABLE

-- 插入数据
postgres=# insert into t(i) select i from generate_series(1, 10000) t(i);
INSERT 0 10000

-- 创建索引,为了后面的演示
postgres=# create index on t(i);
CREATE INDEX

-- 使用where 进行统计
postgres=# select count(*) from t where i > 10 and i < 100;
 count 
-------
    89

-- 在where的情况下再做filter
postgres=# select
count(*) cnt,
count(*) filter(where i > 20 and i < 60) cnt2 from t where i > 10 and i < 100;
 cnt | cnt2 
-----+------
  89 |   39 


应用场景

  1. 在一定的条件下统计,然后同时统计另外一个条件。
  2. 譬如:多个班的同学成绩,要统计一个班的统计,也要统计这个班不及格的同学的人数。譬如:在过车数据中,要统计一天的数据(where),也要统计这一天中高峰期的过车数据(filter)


关于索引情况,结论: filter是不走索引的


-- where中使用了索引
postgres=# explain select count(*) from t where i > 10 and i < 100;
                                  QUERY PLAN                                  
------------------------------------------------------------------------------
 Aggregate  (cost=10.29..10.30 rows=1 width=8)
   ->  Index Only Scan using t_i_idx on t  (cost=0.29..10.07 rows=89 width=0)
         Index Cond: ((i > 10) AND (i < 100))

-- 同样是这个条件,filter不走索引
postgres=# explain select count(*) filter(where i > 10 and i < 100) from t;
                         QUERY PLAN                          
-------------------------------------------------------------
 Aggregate  (cost=220.00..220.01 rows=1 width=8)
   ->  Seq Scan on t  (cost=0.00..145.00 rows=10000 width=4) 


以filter等价的功能


-- cnt1, cnt2 等价
postgres=# select 
                count(*) cnt,
                count(*) filter(where i > 10 and i < 100) cnt1,
                count(case when i > 10 and i < 100 then 1 else null end) cnt2
           from 
                t;
---
  cnt  | cnt1 | cnt2 
-------+------+------
 10000 |   89 |   89 

结论:filter 比原来的case when实现更加优雅简单

上一篇:c++ 中0长度的数组
下一篇:PostgreSql中使用python