17 July 2013

在PostgreSQL 9.4开发版的git上,今天提交了一个特性:支持聚合函数的filter子句。这个功能支持在同一个group里面,根据filter选择要作用聚合函数的行。在过去,类似的功能只能通过复杂的case when去表达,或者使用窗口函数,这个功能将简化这类功能的实现。

语法形式如下:

aggregate_name (expression [ , ... ] [ order_by_clause ] ) [ FILTER ( WHERE filter_clause ) ]
aggregate_name (ALL expression [ , ... ] [ order_by_clause ] ) [ FILTER ( WHERE filter_clause ) ]
aggregate_name (DISTINCT expression [ , ... ] [ order_by_clause ] ) [ FILTER ( WHERE filter_clause ) ]
aggregate_name ( * ) [ FILTER ( WHERE filter_clause ) ]

应用举例:

SELECT
count(*) AS unfiltered,
count(*) FILTER (WHERE i < 5) AS filtered
FROM generate_series(1,10) AS s(i);
unfiltered | filtered
------------+----------
10 | 4
(1 row)

在过去没有FILTER支持的时候,需要这样写:

SELECT
count(*) AS unfiltered,
sum(case when i < 5 then 1 else 0 end) AS filtered
FROM generate_series(1,10) AS s(i);
unfiltered | filtered
------------+----------
10 | 4
(1 row)

可以看到,用FILTER子句,写法简单直观了很多。

参考连接:

PostgreSQL聚合表达式

PostgreSQL SELECT语法