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子句帮助过滤满足某些条件的数据子集，从而避免不必要的聚合函数。

本文参考链接：https://blog.csdn.net/neweastsun/article/details/116764660