SQL 聚合函数中的数据过滤

文章目录

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

今天给大家介绍一下如何在 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 等数据库。

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

更多请见：http://www.mark-to-win.com/tutorial/51599.html