PostgreSQL 9.4 引入特性：WITHIN GROUP 和 FILTER 子句

PostgreSQL 9.4扩展SQL标准，增加了两个新的子句：WITHIN GROUP 和 FILTER 。

WITHIN GROUP 子句

WITHIN GROUP 对有序子集执行聚集函数非常有用。PostgreSQL9.0 版本引入窗口函数用于关联数据子集和其对应每个记录，针对任何特定记录定义一组聚集，利用 OVER(PARTITION BY/ORDER BY) 子句分组执行聚集函数。

使用PostgreSQL 9.4 版本的 WITHIN GROUP 子句可以简化之前使用窗口函数聚集有序子集数据实现的功能。

percentile_cont(), percentile_disc() 计算百分位数；mode()计算有序集的众数；rank(), dense_rank(), percent_rank(), cume_dist() 可以和WITHIN GROUP 子句一起使用。

并不是所有聚集函数都可以和WITHIN GROUP 子句一起使用。我们可以通过查询pg_aggregate 表的aggkind 字段进行识别,它包括三个值：

• n 表示正常聚集函数，如 max, min等
• o 表示有序集聚集函数
• h 表示假设集聚集函数，是有序集的子集

test=# SELECT aggfnoid, aggkind
test-#    FROM pg_aggregate
test-#    WHERE aggkind IN ('o', 'h');
          aggfnoid          | aggkind
----------------------------+---------
 pg_catalog.percentile_disc | o
 pg_catalog.percentile_cont | o
 pg_catalog.percentile_cont | o
 pg_catalog.percentile_disc | o
 pg_catalog.percentile_cont | o
 pg_catalog.percentile_cont | o
 mode                       | o
 pg_catalog.rank            | h
 pg_catalog.percent_rank    | h
 pg_catalog.cume_dist       | h
 pg_catalog.dense_rank      | h
(11 行记录)

下面通过示例来说明使用with group的优势。

假设我们需要查询1~20共20个数的四分位数。之前的做法使用OVER (PARTITION BY/ORDER BY)子句把数据分成4个组，然后对每个有序子组求其最大值，使用CTE:

CREATE TABLE t AS SELECT generate_series(1,20) AS val;

 WITH subset AS (
    SELECT val,
       ntile(4) OVER (ORDER BY val) AS tile
    FROM t
  )
  SELECT max(val)
  FROM subset GROUP BY tile ORDER BY tile;

   max
  ------
   5
  10
  15
  20
 (4 rows)

使用PostgreSQL 9.4新的特性可以非常简单，且可读性更好。

$ CREATE TABLE t AS SELECT generate_series(1,20) AS val;

$ SELECT unnest(percentile_disc(array[0.25,0.5,0.75,1])
    WITHIN GROUP (ORDER BY val))
  FROM t;

   max
  ------
   5
  10
  15
  20
 (4 rows)

FILTER子句

filter 子句可以对聚集函数增加过滤功能，仅对符合条件的子集进行聚集。假设我们对一组数据执行count统计，同时需要统计奇数和偶数。我们可以在一个查询中使用filter进行实现：

test=# SELECT count(*) count_all,
test-#          count(*) FILTER(WHERE value % 2=1) count_1,
test-#          count(*) FILTER(WHERE value % 2=0) count_2
test-#   FROM generate_series(1,100,1) as t(value);

 count_all | count_1 | count_2
-----------+---------+---------
       100 |      50 |      50
(1 行记录)

上述实现非常简单，不仅提升了代码可读性，也提升了查询性能。

总结

通过引入这些新子句对SQL标准的扩展可开发人员效率，因为可将更多对数据子集的操作和聚合委托给数据库。通过WITHIN GROUP子句结合使用新的窗口函数，可以更方便管理有序数据子集。

通过使用WITHIN GROUP子句，通过引入新的窗口函数，可以更容易地管理可以排序的数据子集。FILTER子句帮助过滤满足某些条件的数据子集，从而避免不必要的聚合函数。