PostgreSQL , 聚合 , filter , order , within group
PostgreSQL的分析功能还是比较强大的，例如支持多维分析，支持4大类聚合，支持窗口查询，支持递归查询等。
4大类聚合的用法请参考
《PostgreSQL aggregate function 1 : General-Purpose Aggregate Functions》
《PostgreSQL aggregate function 2 : Aggregate Functions for Statistics》
《PostgreSQL aggregate function 3 : Aggregate Functions for Ordered-Set》
《PostgreSQL aggregate function 4 : Hypothetical-Set Aggregate Functions》
多维分析请参考
《Greenplum 最佳实践 - 多维分析的使用(CUBE, ROLLUP, GROUPING SETS in GreenPlum and Oracle)》
《PostgreSQL 9.5 new feature - Support GROUPING SETS, CUBE and ROLLUP.》
窗口查询请参考
《时序数据合并场景加速分析和实现 - 复合索引，窗口分组查询加速，变态递归加速》
《快速入门PostgreSQL应用开发与管理 - 4 高级SQL用法》
递归查询请参考
《快速入门PostgreSQL应用开发与管理 - 3 访问数据》
本文主要介绍一下聚合表达式的高级用法
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 ) ] aggregate_name ( [ expression [ , ... ] ] ) WITHIN GROUP ( order_by_clause ) [ FILTER ( WHERE filter_clause ) ]1. 我们在分组后，需要查出分组中复合条件的count，以及分组的count。
postgres=# create table test(id int, c1 int); CREATE TABLE postgres=# insert into test select generate_series(1,10000), random()*10; INSERT 0 10000 postgres=# select * from test limit 10; id | c1 ----+---- 1 | 10 2 | 4 3 | 6 4 | 1 5 | 4 6 | 9 7 | 9 8 | 7 9 | 5 10 | 4 (10 rows) postgres=# select count(*), count(*) filter (where id<1000) from test group by c1; count | count -------+------- 1059 | 118 998 | 109 999 | 101 1010 | 95 468 | 48 544 | 43 964 | 107 956 | 103 1021 | 87 977 | 101 1004 | 87 (11 rows)2. 我们需要按顺序，将多条记录，聚合为一个字符串或数组，同样也可以加filter，只聚合复合条件的记录
postgres=# select string_agg(id::text, '-' order by id) filter (where id<100) from test group by c1; string_agg ------------------------------------------- 35-65-74-97 4-12-19-31-36-40-85-89-90-98-99 17-18-22-42-43-44-58-59-64-70-75-83-84 11-14-15-16-21-30-41-54-62-67-73-80-81-94 2-5-10-51-79-93-96 9-26-45-46-47-61 3-27-28-37-48-55-56-68-69-77-92 8-20-24-33-34-49-50-60-63-66-78-91 25-39-53-57-71-76-82-87-95 6-7-29-32-38-72-86-88 1-13-23-52 (11 rows)3. 我们需要去每个分组，某个字段中值
postgres=# select percentile_cont(0.5) within group (order by id) from test group by c1; percentile_cont ----------------- 4911.5 5210 4698 4699.5 4955 5061.5 5115 5176 4897.5 5087 4973 (11 rows)4. 去过滤条件后的中值
postgres=# select percentile_cont(0.5) within group (order by id) filter (where id<100) from test group by c1; percentile_cont ----------------- 69.5 40 58 47.5 51 45.5 55 49.5 71 35 18 (11 rows)PostgreSQL 的分析方法很全面，建议用户可以多了解一下我在开始时给出的链接，帮助提高生产力。
https://www.postgresql.org/docs/9.6/static/sql-expressions.html#SYNTAX-AGGREGATES
