1. 云图网首页
  2. 技术专区
  3. 数据库

PostgreSQL9.4: 聚合函数新增 FILTER 属性

• 数据库

Release Note

Add control over which values are passed into aggregate functions using the FILTER clause (David Fetter)

手册中提到聚合函数增加 FILTER 属性， 也就是说在聚合函数 avg(), min(), max(), sum() 等可以使用 FILTER 属性过滤数据， 之前可能用到到以下方式实现:

1
 
select sum(  case  when  ...  then  1  else  0  end  )  from  ..

例如：

1
2
3
4
5
6
7
8
 
francs=> select 
     count(*) as unfiltered,
     sum(case  when a >6  then  1  else  0  end)  as filtered
FROM generate_series(1,10)  as a;
unfiltered | filtered
------------+----------
10  | 4
(1 row)

使用 FILTER 属性

1
2
3
4
5
6
7
8
 
francs=> SELECT
  count(*) AS unfiltered,
  count(*) FILTER (WHERE a >  6) AS filtered
FROM generate_series(1,10) AS a;
unfiltered | filtered
------------+----------
10  | 4
(1 row)

备注：这样方便很多，方便平常 SQL 写得多的同学们。

使用 Sum 函数

1
2
3
4
5
6
7
8
 
francs=> SELECT
sum (a)AS unfiltered,
sum (a)FILTER (WHERE a >  6) AS filtered
FROM generate_series(1,10) AS a;
unfiltered | filtered
------------+----------
55  |  34
(1 row)

1 到 20 按条件分组

1
2
3
4
5
6
7
8
9
 
francs=> SELECT array_agg(i) FILTER (WHERE i %  2  =  0) AS twos,
   array_agg(i) FILTER (WHERE i %  3  =  0) AS threes,
   array_agg(i) FILTER (WHERE i %  5  =  0) AS fives,
   array_agg(i) FILTER (WHERE i %  7  =  0) AS sevens
 FROM generate_series(1,  20) AS g(i);
twos | threes| fives | sevens
-----------------------------+------------------+--------------+--------
{2,4,6,8,10,12,14,16,18,20}  |  {3,6,9,12,15,18}  |  {5,10,15,20}  |  {7,14}
(1 row)

参考

原创文章，作者：ItWorker，如若转载，请注明出处：https://blog.ytso.com/238068.html

赞 (0)
0
上一篇 2022年1月30日
下一篇 2022年1月30日

相关推荐

发表回复

登录后才能评论