個(gè)人中心
退出
PostgreSQL 數(shù)據(jù)庫(kù)中 DISTINCT 關(guān)鍵字的 4 種用法
作者: 不剪發(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
文章目錄
DISTINCT
DISTINCT ON
IS DISTINCT FROM
聚合函數(shù)與 DISTINCT
大家好，我是只談技術(shù)不剪發(fā)的 Tony 老師。PostgreSQL 不但高度兼容 SQL 標(biāo)準(zhǔn)，同時(shí)還對(duì)很多語(yǔ)法進(jìn)行了擴(kuò)展，可以用于實(shí)現(xiàn)一些特殊的功能。今天我們就來(lái)介紹一下 PostgreSQL 數(shù)據(jù)庫(kù)中 DISTINCT 關(guān)鍵字的 4 種不同用法。
DISTINCT
按照 SQL 標(biāo)準(zhǔn)，SELECT DISTINCT可以在返回查詢結(jié)果之前去除重復(fù)的記錄，每個(gè)重復(fù)的數(shù)據(jù)組中只保留一條記錄。例如：
SELECT DISTINCT dept_id, sex
FROM employee;
dept_id|sex|
-------|---|
4|男 |
1|男 |
4|女 |
5|男 |
3|女 |
2|男 |
以上語(yǔ)句中的 DISTINCT 表示返回不同部門 id 和性別的組合值。我們也可以使用 GROUP BY 實(shí)現(xiàn)相同的結(jié)果：
SELECT dept_id, sex
FROM employee
GROUP BY dept_id, sex;
??按照 SQL 標(biāo)準(zhǔn)，多個(gè) NULL 值對(duì)于 DISTINCT 而言屬于相同的分組。
DISTINCT ON
考慮一個(gè)問(wèn)題：每個(gè)部門中月薪最高的員工都是誰(shuí)？這個(gè)問(wèn)題可以使用多種實(shí)現(xiàn)方法：
-- 子查詢
SELECT dept_id, emp_name,salary
FROM employee
WHERE (dept_id, salary) IN ( SELECT dept_id, MAX(salary)
FROM employee
GROUP BY dept_id );
dept_id|emp_name|salary |
-------|--------|--------|
1|劉備 |30000.00|
2|諸葛亮 |24000.00|
3|孫尚香 |12000.00|
4|趙云 |15000.00|
5|法正 |10000.00|
-- 窗口函數(shù)
WITH ranked_employee AS (
SELECT dept_id, emp_name, salary, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) rn
FROM employee
)
SELECT *
FROM ranked_employee
WHERE rn = 1;
dept_id|emp_name|salary |rn|
-------|--------|--------|--|
1|劉備 |30000.00| 1|
2|諸葛亮 |24000.00| 1|
3|孫尚香 |12000.00| 1|
4|趙云 |15000.00| 1|
5|法正 |10000.00| 1|
其中，第一個(gè)語(yǔ)句使用了子查詢；第二個(gè)語(yǔ)句使用了窗口函數(shù)，除了 ROW_NUMBER 之外，也可以使用 RANK 或者 DENSE_RANK 等函數(shù)。這兩者都是 SQL 標(biāo)準(zhǔn)實(shí)現(xiàn)。
除此之外，PostgreSQL 提供了擴(kuò)展的 DISTINCT ON 子句，可以更加方便地實(shí)現(xiàn)以上結(jié)果：
SELECT DISTINCT ON (dept_id) dept_id, emp_name, salary
FROM employee
ORDER BY dept_id, salary DESC;
dept_id|emp_name|salary |rn|
-------|--------|--------|--|
1|劉備 |30000.00| 1|
2|諸葛亮 |24000.00| 1|
3|孫尚香 |12000.00| 1|
4|趙云 |15000.00| 1|
5|法正 |10000.00| 1|
其中，DISTINCT ON (dept_id) 表示部門 id 相同的數(shù)據(jù)組，返回其中的第一條記錄；ORDER BY 子句確保了返回的是每個(gè)部分中月薪最高的記錄。DISTINCT ON 中的字段或表達(dá)式（可能多個(gè)）必須和 ORDER BY 最左側(cè)的幾個(gè)字段或表達(dá)式相同。
IS DISTINCT FROM
空值（NULL）是數(shù)據(jù)庫(kù)中的一個(gè)特殊值，通常用于表示缺失值或者不適用的值?？罩档谋容^是一個(gè)比較容易出錯(cuò)的問(wèn)題。例如：
WITH t AS (
SELECT 1 AS a, 1 AS b
UNION ALL
SELECT 1, 2
UNION ALL
SELECT NULL, 1
UNION ALL
SELECT NULL, NULL )
SELECT a, b, a = b "a=b"
FROM t;
a |b |a=b |
----|----|-----|
1 |1 |true |
1 |2 |false|
NULL|1 |NULL |
NULL|NULL|NULL |
當(dāng)我們使用比較運(yùn)算符（=、<>、<、> 等）與 NULL 進(jìn)行比較時(shí)，結(jié)果既不是真也不是假，而是未知；因?yàn)?NULL 表示未知，也就意味著可能是任何值；我們不能說(shuō)兩個(gè)未知的值相同，也不能說(shuō)它們不相同。
為了比較 NULL 值，SQL 定義了兩個(gè)專用的運(yùn)算符：IS NULL和IS NOT NULL。例如：
SELECT 1 IS NULL "1 IS NULL",
1 IS NOT NULL "1 IS NOT NULL",
NULL IS NULL "NULL IS NULL",
NULL IS NOT NULL "NULL IS NOT NULL";
1 IS NULL|1 IS NOT NULL|NULL IS NULL|NULL IS NOT NULL|
---------|-------------|------------|----------------|
false |true |true |false |
因此，對(duì)于兩個(gè)可能為空的字段進(jìn)行比較的完整方法如下：
WITH t AS (
SELECT 1 AS a, 1 AS b
UNION ALL
SELECT 1, 2
UNION ALL
SELECT NULL, 1
UNION ALL
SELECT NULL, NULL )
SELECT a, b,
(a IS NULL AND b IS NULL)
OR
(a IS NOT NULL AND b IS NOT NULL AND a = b) "a=b"
FROM t;
a |b |a=b |
----|----|-----|
1 |1 |true |
1 |2 |false|
NULL|1 |false|
NULL|NULL|true |
以上語(yǔ)句返回了我們期望的結(jié)果，但是讀寫都很不方便；為此，PostgreSQL 提供了擴(kuò)展的 IS [NOT] DISTINCT FROM 運(yùn)算符，支持 NULL 值的比較。例如：
WITH t AS (
SELECT 1 AS a, 1 AS b
UNION ALL
SELECT 1, 2
UNION ALL
SELECT NULL, 1
UNION ALL
SELECT NULL, NULL )
SELECT a, b, a IS NOT DISTINCT FROM b "a=b"
FROM t;
a |b |a=b |
----|----|-----|
1 |1 |true |
1 |2 |false|
NULL|1 |false|
NULL|NULL|true |
注意，IS NOT DISTINCT FROM 表示判斷兩個(gè)數(shù)據(jù)是否相同，IS DISTINCT FROM 表示判斷兩個(gè)數(shù)據(jù)是否不同；它們都將 NULL 看作已知的一個(gè)特殊值，而不是 SQL 標(biāo)準(zhǔn)中的未知值。顯然這種語(yǔ)法更加言簡(jiǎn)意賅。
另外，PostgreSQL 還提供了一個(gè)配置變量 transform_null_equals，該參數(shù)默認(rèn)為 off；如果設(shè)置為 on，PostgreSQL 會(huì)自動(dòng)執(zhí)行 convert x = NULL 到 x IS NULL 的轉(zhuǎn)換。建議不要依賴這個(gè)參數(shù)的設(shè)置，而是應(yīng)該修改應(yīng)用程序
??關(guān)于 SQL 空值的詳細(xì)討論以及它們?cè)?MySQL、Oracle、SQL Server、PostgreSQL、SQLite 中的具體實(shí)現(xiàn)，可以參考這篇文章。
聚合函數(shù)與 DISTINCT
聚合函數(shù)（aggregate function）針對(duì)一組數(shù)據(jù)行進(jìn)行運(yùn)算，并且返回一條結(jié)果。PostgreSQL 支持的聚合函數(shù)包括 AVG、COUNT、MAX/MIN、SUM、STRING_AGG、ARRAY_AGG 等。例如：
SELECT dept_id, count(*), avg(salary), string_agg(emp_name, ',' ORDER BY salary DESC)
FROM employee
GROUP BY dept_id
ORDER BY dept_id;
dept_id|count|avg |string_agg |
-------|-----|----------------------|--------------------------------------|
1| 3| 26666.666666666667|劉備,關(guān)羽,張飛 |
2| 3|13166.6666666666666667|諸葛亮,黃忠,魏延 |
3| 2| 9000.0000000000000000|孫尚香,孫丫鬟 |
4| 9| 7577.7777777777777778|趙云,周倉(cāng),關(guān)興,關(guān)平,趙氏,廖化,張苞,趙統(tǒng),馬岱|
5| 8| 5012.5000000000000000|法正,簡(jiǎn)雍,孫乾,糜竺,黃權(quán),龐統(tǒng),鄧芝,蔣琬 |
以上語(yǔ)句返回了每個(gè)部門的員工人數(shù)、平均月薪以及所有員工姓名的連接字符串（按照月薪從高到低）。
PostgreSQL 不僅實(shí)現(xiàn)了分組聚合操作，還支持聚合函數(shù)中的 DISTINCT 選項(xiàng)，可以在進(jìn)行匯總之前去除每個(gè)分組中的重復(fù)記錄。例如：
SELECT dept_id, string_agg(sex, ','), string_agg(DISTINCT sex, ',') string_agg_distinct
FROM employee
GROUP BY dept_id
ORDER BY dept_id;
dept_id|string_agg |string_agg_distinct|
-------|-----------------------|-------------------|
1|男,男,男 |男 |
2|男,男,男 |男 |
3|女,女 |女 |
4|男,女,男,男,男,男,男,男,男|女,男 |
5|男,男,男,男,男,男,男,男 |男 |
分類導(dǎo)航