SQL 聚合函数中的数据过滤
作者: 不剪发的Tony老师
毕业于北京航空航天大学,十多年数据库管理与开发经验,目前在一家全球性的金融公司从事数据库架构设计。CSDN学院签约讲师以及GitChat专栏作者。csdn上的博客收藏于以下地址:https://tonydong.blog.csdn.net
文章目录
聚合函数中的 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 等数据库。
以下查询则返回了每个部门中拥有奖金的员工的平均月薪,为什么?
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 扩展选项语义更加简单,更容易理解。