PostgreSQL數(shù)據(jù)庫(kù)中DISTINCT關(guān)鍵字的四種用法詳解
前言
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)用程序
聚合函數(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|男,男,男,男,男,男,男,男 |男 |
到此這篇關(guān)于PostgreSQL數(shù)據(jù)庫(kù)中DISTINCT關(guān)鍵字的四種用法詳解的文章就介紹到這了,更多相關(guān)PostgreSQL DISTINCT用法內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
PgSQl臨時(shí)表創(chuàng)建及應(yīng)用實(shí)例解析
這篇文章主要介紹了PgSQl臨時(shí)表創(chuàng)建及應(yīng)用實(shí)例解析,文中通過(guò)示例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友可以參考下2020-12-12PostgreSQL教程(七):函數(shù)和操作符詳解(3)
這篇文章主要介紹了PostgreSQL教程(七):函數(shù)和操作符詳解(3),本文講解了序列操作函數(shù)、條件表達(dá)式、數(shù)組函數(shù)和操作符、系統(tǒng)信息函數(shù)、系統(tǒng)管理函數(shù)等內(nèi)容,需要的朋友可以參考下2015-05-05PostgreSQL表操作之表的創(chuàng)建及表基礎(chǔ)語(yǔ)法總結(jié)
在PostgreSQL中創(chuàng)建表命令用于在任何給定的數(shù)據(jù)庫(kù)中創(chuàng)建新表,下面這篇文章主要給大家介紹了關(guān)于PostgreSQL表操作之表的創(chuàng)建及表基礎(chǔ)語(yǔ)法的相關(guān)資料,文中通過(guò)代碼介紹的非常詳細(xì),需要的朋友可以參考下2024-05-05PostgreSQL中ON?CONFLICT的使用及一些擴(kuò)展用法
Postgres?ON?CONFLICT是PostgreSQL數(shù)據(jù)庫(kù)中的一個(gè)功能,用于處理插入或更新數(shù)據(jù)時(shí)的沖突情況,下面這篇文章主要給大家介紹了關(guān)于PostgreSQL中ON?CONFLICT的使用及一些擴(kuò)展用法的相關(guān)資料,需要的朋友可以參考下2024-06-06Debian中PostgreSQL數(shù)據(jù)庫(kù)安裝配置實(shí)例
這篇文章主要介紹了Debian中PostgreSQL數(shù)據(jù)庫(kù)安裝配置實(shí)例,一個(gè)簡(jiǎn)明教程,需要的朋友可以參考下2014-06-06postgresql insert into select無(wú)法使用并行查詢的解決
這篇文章主要介紹了postgresql insert into select無(wú)法使用并行查詢的解決,文中通過(guò)示例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2021-01-01Navicat設(shè)置PostgreSQL數(shù)據(jù)庫(kù)的表主鍵ID自增的方法
這篇文章主要介紹了Navicat設(shè)置PostgreSQL數(shù)據(jù)庫(kù)的表主鍵ID自增的方法,文章通過(guò)圖文結(jié)合的形式給大家介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值,需要的朋友可以參考下2024-06-06使用PostgreSQL為表或視圖創(chuàng)建備注的操作
這篇文章主要介紹了使用PostgreSQL為表或視圖創(chuàng)建備注的操作,具有很好的參考價(jià)值,希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2021-01-01PostgreSQL+GeoHash地圖點(diǎn)位聚合實(shí)現(xiàn)代碼
這篇文章主要介紹了PostgreSQL+GeoHash地圖點(diǎn)位聚合,本文通過(guò)實(shí)例代碼給大家介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值,需要的朋友可以參考下2023-07-07