SQL 聚合函數(shù)中的數(shù)據(jù)過(guò)濾

作者: 不剪發(fā)的Tony老師
畢業(yè)于北京航空航天大學(xué)，十多年數(shù)據(jù)庫(kù)管理與開(kāi)發(fā)經(jīng)驗(yàn)，目前在一家全球性的金融公司從事數(shù)據(jù)庫(kù)架構(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ù)過(guò)濾，基于滿足特定條件的部分?jǐn)?shù)據(jù)進(jìn)行匯總。本文描述的方法支持但不限于 Oracle、MySQL/MariaDB、Microsoft SQL Server、PostgreSQL 以及 SQLite 等數(shù)據(jù)庫(kù)。

如果覺(jué)得文章有用，歡迎評(píng)論??、點(diǎn)贊??、推薦??

    ??本文使用的示例表可以點(diǎn)此下載。

聚合函數(shù)中的 CASE 表達(dá)式

在 SQL 中，聚合函數(shù)（Aggregate Function）用于對(duì)一組數(shù)據(jù)進(jìn)行匯總計(jì)算，并且返回單個(gè)分析結(jié)果。常見(jiàn)的聚合函數(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|             |


查詢語(yǔ)句中使用了 LEFT JOIN，因?yàn)椤氨Ｐl(wèi)部”沒(méi)有員工。

假如我們想要修改一下平均月薪的計(jì)算方式，只返回月薪大于 10000 的員工的平均月薪，應(yīng)該怎么實(shí)現(xiàn)呢？

我們知道聚合函數(shù)不會(huì)對(duì) NULL 值進(jìn)行統(tǒng)計(jì)，因此可以通過(guò) 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 位員工，但是沒(méi)有員工的月薪超過(guò) 10000。

    ??CASE 表達(dá)式是標(biāo)準(zhǔn) SQL 功能，因此以上方法支持但不限于 Oracle、MySQL/MariaDB、Microsoft SQL Server、PostgreSQL 以及 SQLite 等數(shù)據(jù)庫(kù)。

以下查詢則返回了每個(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ì)于上文中的問(wèn)題，我們可以使用查詢語(yǔ)句：

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)語(yǔ)義更加簡(jiǎn)單，更容易理解。