PostgreSQL技术问答11 - 聚合查询Filter和Having

本文是《PostgreSQL技术问答》系列文章中的一篇。关于这个系列的由来，可以参阅开篇文章：

文章的编号只是一个标识，在系列中没有明确的逻辑顺序和意义。读者进行阅读时，不用太关注这个方面。

本文讨论的问题是如何在聚合查询中使用过滤器(filter)和Having子句。这个内容是对PostgreSQL聚合函数和查询相关内容的扩展，讨论在聚合查询中，如何进行更复杂灵活的数据筛选的操作。

如何理解聚合查询中的Having子句

笔者的理解，它是对一般SQL查询语句中Where过滤机制的一个扩展和补充，来满足聚合查询这种应用的场景。

在标准的聚合查询操作中，Select ... Group By 前面，确实可以像正常方式一样，使用Where子句，来对需要进行聚合计算的记录进行一个筛选。但这里有一个逻辑问题，就是如果要使用聚合计算结果作为筛选条件，对聚合查询进行过滤，那么是不能放在Where里面的，因为那时候，还没有进行聚合计算呢。为了解决这个矛盾，SQL引入了having子句来进行处理。简单的理解，having就是专门用于对聚合计算的结果进行筛选的Where子句，为了区分，改为叫Having，并且只能放在group by 子句后面，表示其是对聚合的结果进行的筛选。

PG官方文档给出的示例是这样的，可以来帮助我们理解这个逻辑关系：

SELECT product_id, p.name, (sum(s.units) * (p.price - p.cost)) AS profit
FROM products p LEFT JOIN sales s USING (product_id)
WHERE s.date > CURRENT_DATE - INTERVAL '4 weeks'
GROUP BY product_id, p.name, p.price, p.cost
HAVING sum(p.price * s.units) > 5000;

这个例子中，既使用了Where，也使用了Having，而且都是标准用法。Where过滤原始记录，Having过滤聚合查询结果。但据文档的说法，Having其实是可以部分包容Where的，也就是说，可以将过滤原始记录的条件，也可以放在Having子句中，但有一个限制条件，就是Where子句中涉及的字段，必须是Group By中的字段。

还有一点就是Having中使用的聚合函数过滤计算，可能和聚合查询中使用的不一致，这也是允许的，并不会产生逻辑冲突。

什么是聚合查询过滤器 filter

聚合函数过滤器，是指在聚合查询语句中，对于聚合计算，可以指定计算所包括的数据的范围，就是可以针对每个聚合计算，都设置过滤条件。

就笔者开发使用的经验而言，这是一个不起眼但非常有用的特性。它可以使用一种非常简洁的方式，实现一些使用其他方法可能会比较繁琐的查询，并使后续的数据处理也比较简单。

如何使用聚合查询过滤器

聚合函数的一般形式就是：

aggfunc() filter( where ...) group by ...

也就是说，在调用聚合函数的时候，可以指定该聚合函数所使用的数据的过滤条件。这个功能有什么作用呢？ 我们可以通过下面的简单例子来理解和体会这一特性的用法和优势：

// 数据表 D 的结构包括员工名称、部门代码和性别
  name  | dep | gender 
--------+-----+--------
 John   |   1 |      1
 Tom    |   2 |      1
 Marry  |   1 |      2
 Season |   2 |      2
 Noah   |   1 |      1
 Smith  |   2 |      1
 Alice  |   1 |      2

// 可以使用CTE如下：
with E(name,dep,gender) as ( values
('John', 1 ,1),
('Tom',    2 ,1),
('Marry',  1 ,2),
('Season', 2 ,2),
('Noah',   1 ,1),
('Smith',  2 ,1),
('Alice',  1 ,2)) 

// 需求是： 需要统计每个部门的员工总数、男员工和女员工数
// 方式1： 传统聚合函数+Union
(select dep,gender,count(1) from E group by 1, 2 order by 1,2)
union all 
(select dep,3 as gender,count(1) from E group by 1 order by 1);
 dep | gender | count 
-----+--------+-------
   1 |      1 |     2
   1 |      2 |     2
   2 |      1 |     2
   2 |      2 |     1
   1 |      3 |     4
   2 |      3 |     3
(6 rows)

// 方式2 cube
select dep,gender,count(1) from E group by cube(1, 2) order by 1,2;
 dep | gender | count 
-----+--------+-------
   1 |      1 |     2
   1 |      2 |     2
   1 |        |     4
   2 |      1 |     2
   2 |      2 |     1
   2 |        |     3
     |      1 |     4
     |      2 |     3
     |        |     7
(9 rows)

// 方式3 filter
select dep, count(1) filter( where gender=1) men, count(1) filter(where gender = 2 ) women, count(1) ecount from E group by 1 order by 1;
 dep | men | women | ecount 
-----+-----+-------+--------
   1 |   2 |     2 |      4
   2 |   2 |     1 |      3

上面的例子可以看到，filter提供了一种更高的灵活性来帮助开发者进行统计信息的查询和组织。虽然cube函数也能提供类似的强大的细分聚合统计功能，但需要在后续做一些数据的处理。而filter如果使用得当，可以大幅度的简化SQL的编写调用和前端再处理的过程。

小结

本文作为Postgres聚合函数和计算的扩展，讨论了在这个过程中对于聚合原始数据和结果数据的过滤和筛选机制，具体而言就是Filter和Having子句，并且简单举例讨论了它们的使用方式和适用的应用场景。