個(gè)人中心
退出
SQL 聚合函數(shù)中的數(shù)據(jù)過濾
作者: 不剪發(fā)的Tony老師
畢業(yè)于北京航空航天大學(xué)，十多年數(shù)據(jù)庫管理與開發(fā)經(jīng)驗(yàn)，目前在一家全球性的金融公司從事數(shù)據(jù)庫架構(gòu)設(shè)計(jì)。CSDN學(xué)院簽約講師以及GitChat專欄作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net
文章目錄
聚合函數(shù)中的 CASE 表達(dá)式
PostgreSQL/SQLite 中的 FILTER 選項(xiàng)
大家好！我是只談技術(shù)不剪發(fā)的 Tony 老師。
今天給大家介紹一下如何在 SQL 聚合函數(shù)中進(jìn)行數(shù)據(jù)過濾，基于滿足特定條件的部分?jǐn)?shù)據(jù)進(jìn)行匯總。本文描述的方法支持但不限于 Oracle、MySQL/MariaDB、Microsoft SQL Server、PostgreSQL 以及 SQLite 等數(shù)據(jù)庫。
如果覺得文章有用，歡迎評(píng)論??、點(diǎn)贊??、推薦??
??本文使用的示例表可以點(diǎn)此下載。
聚合函數(shù)中的 CASE 表達(dá)式
在 SQL 中，聚合函數(shù)（Aggregate Function）用于對(duì)一組數(shù)據(jù)進(jìn)行匯總計(jì)算，并且返回單個(gè)分析結(jié)果。常見的聚合函數(shù)包括 AVG、SUM、COUNT、MAX/MIN 等。例如，以下查詢返回了按照部門統(tǒng)計(jì)的員工數(shù)量和平均月薪：
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|
財(cái)務(wù)部 | 2| 9000.000000|
研發(fā)部 | 9| 7577.777778|
銷售部 | 8| 5012.500000|
保衛(wèi)部 | 0| |
查詢語句中使用了 LEFT JOIN，因?yàn)椤氨Ｐl(wèi)部”沒有員工。
假如我們想要修改一下平均月薪的計(jì)算方式，只返回月薪大于 10000 的員工的平均月薪，應(yīng)該怎么實(shí)現(xiàn)呢？
我們知道聚合函數(shù)不會(huì)對(duì) NULL 值進(jìn)行統(tǒng)計(jì)，因此可以通過 CASE 表達(dá)式將月薪小于等于 10000 的數(shù)據(jù)轉(zhuǎn)換為 NULL 值后傳遞給 AVG 函數(shù)。例如：
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|
財(cái)務(wù)部 | 2|12000.000000|
研發(fā)部 | 9|15000.000000|
銷售部 | 8| |
保衛(wèi)部 | 0| |
“銷售部”雖然有 8 位員工，但是沒有員工的月薪超過 10000。
??CASE 表達(dá)式是標(biāo)準(zhǔn) SQL 功能，因此以上方法支持但不限于 Oracle、MySQL/MariaDB、Microsoft SQL Server、PostgreSQL 以及 SQLite 等數(shù)據(jù)庫。
以下查詢則返回了每個(gè)部門中擁有獎(jiǎng)金的員工的平均月薪，為什么？
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|
財(cái)務(wù)部 | 2|12000.000000| 1|
研發(fā)部 | 9|15000.000000| 1|
銷售部 | 8| 6033.333333| 3|
保衛(wèi)部 | 0| | 0|
PostgreSQL/SQLite 中的 FILTER 選項(xiàng)
PostgreSQL 和 SQLite 為聚合函數(shù)提供一個(gè) FILTER 擴(kuò)展選項(xiàng)，可以用于匯總滿足特定條件的數(shù)據(jù)。
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|
對(duì)于上文中的問題，我們可以使用查詢語句：
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|
財(cái)務(wù)部 | 2|12000.0000000000000000|
銷售部 | 8| |
人力資源部| 3| 24000.000000000000|
研發(fā)部 | 9|15000.0000000000000000|
保衛(wèi)部 | 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|
財(cái)務(wù)部 | 2|12000.0000000000000000| 1|
銷售部 | 8| 6033.3333333333333333| 3|
人力資源部| 3| 24000.000000000000| 1|
研發(fā)部 | 9|15000.0000000000000000| 1|
保衛(wèi)部 | 0| | 0|
顯然，PostgreSQL 提供的 FILTER 擴(kuò)展選項(xiàng)語義更加簡(jiǎn)單，更容易理解。
分類導(dǎo)航