SQL 聚合函数中的数据过滤

作者: 不剪发的Tony老师
毕业于北京航空航天大学，十多年数据库管理与开发经验，目前在一家全球性的金融公司从事数据库架构设计。CSDN学院签约讲师以及GitChat专栏作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net

 

文章目录

        聚合函数中的 CASE 表达式
        PostgreSQL/SQLite 中的 FILTER 选项

大家好！我是只谈技术不剪发的 Tony 老师。

今天给大家介绍一下如何在 SQL 聚合函数中进行数据过滤，基于满足特定条件的部分数据进行汇总。本文描述的方法支持但不限于 Oracle、MySQL/MariaDB、Microsoft SQL Server、PostgreSQL 以及 SQLite 等数据库。

如果觉得文章有用，欢迎评论📝、点赞👍、推荐🎁

    📝本文使用的示例表可以点此下载。

聚合函数中的 CASE 表达式

在 SQL 中，聚合函数（Aggregate Function）用于对一组数据进行汇总计算，并且返回单个分析结果。常见的聚合函数包括 AVG、SUM、COUNT、MAX/MIN 等。例如，以下查询返回了按照部门统计的员工数量和平均月薪：

SELECT d.dept_name, count(e.emp_id), avg(e.salary)
FROM department d
LEFT JOIN employee e ON (e.dept_id = d.dept_id)
GROUP BY d.dept_name;

dept_name|count(e.emp_id)|avg(e.salary)|
---------|---------------|-------------|
行政管理部|              3| 26666.666667|
人力资源部|              3| 13166.666667|
财务部    |              2|  9000.000000|
研发部    |              9|  7577.777778|
销售部    |              8|  5012.500000|
保卫部    |              0|             |


查询语句中使用了 LEFT JOIN，因为“保卫部”没有员工。

假如我们想要修改一下平均月薪的计算方式，只返回月薪大于 10000 的员工的平均月薪，应该怎么实现呢？

我们知道聚合函数不会对 NULL 值进行统计，因此可以通过 CASE 表达式将月薪小于等于 10000 的数据转换为 NULL 值后传递给 AVG 函数。例如：

SELECT d.dept_name, count(e.emp_id), avg(CASE WHEN e.salary <= 10000 THEN NULL ELSE e.salary END)
FROM department d
LEFT JOIN employee e ON (e.dept_id = d.dept_id)
GROUP BY d.dept_name;

dept_name|count(e.emp_id)|avg         |
---------|---------------|------------|
行政管理部|              3|26666.666667|
人力资源部|              3|24000.000000|
财务部    |              2|12000.000000|
研发部    |              9|15000.000000|
销售部    |              8|            |
保卫部    |              0|            |


“销售部”虽然有 8 位员工，但是没有员工的月薪超过 10000。

    📝CASE 表达式是标准 SQL 功能，因此以上方法支持但不限于 Oracle、MySQL/MariaDB、Microsoft SQL Server、PostgreSQL 以及 SQLite 等数据库。

以下查询则返回了每个部门中拥有奖金的员工的平均月薪，为什么？

SELECT d.dept_name, count(e.emp_id), avg(e.salary + e.bonus - e.bonus) avg, count(e.salary + e.bonus - e.bonus) count
FROM department d
LEFT JOIN employee e ON (e.dept_id = d.dept_id)
GROUP BY d.dept_name;

dept_name|count(e.emp_id)|avg         |count|
---------|---------------|------------|-----|
行政管理部|              3|26666.666667|    3|
人力资源部|              3|24000.000000|    1|
财务部    |              2|12000.000000|    1|
研发部    |              9|15000.000000|    1|
销售部    |              8| 6033.333333|    3|
保卫部    |              0|            |    0|

PostgreSQL/SQLite 中的 FILTER 选项

PostgreSQL 和 SQLite 为聚合函数提供一个 FILTER 扩展选项，可以用于汇总满足特定条件的数据。

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|


对于上文中的问题，我们可以使用查询语句：

SELECT d.dept_name, count(e.emp_id), avg(e.salary) FILTER (WHERE e.salary > 10000)
FROM department d
LEFT JOIN employee e ON (e.dept_id = d.dept_id)
GROUP BY d.dept_name;

dept_name|count|avg                   |
---------|-----|----------------------|
行政管理部|    3|    26666.666666666667|
财务部    |    2|12000.0000000000000000|
销售部    |    8|                      |
人力资源部|    3|    24000.000000000000|
研发部    |    9|15000.0000000000000000|
保卫部    |    0|                      |


SELECT d.dept_name, count(e.emp_id),
       avg(e.salary) FILTER (WHERE e.bonus IS NOT NULL),
       count(e.salary ) FILTER (WHERE e.bonus IS NOT NULL)
FROM department d
LEFT JOIN employee e ON (e.dept_id = d.dept_id)
GROUP BY d.dept_name;

dept_name|count|avg                   |count|
---------|-----|----------------------|-----|
行政管理部|    3|    26666.666666666667|    3|
财务部    |    2|12000.0000000000000000|    1|
销售部    |    8| 6033.3333333333333333|    3|
人力资源部|    3|    24000.000000000000|    1|
研发部    |    9|15000.0000000000000000|    1|
保卫部    |    0|                      |    0|


显然，PostgreSQL 提供的 FILTER 扩展选项语义更加简单，更容易理解。