快捷導(dǎo)航

PostgreSQL數(shù)據(jù)庫中DISTINCT關(guān)鍵字的四種用法詳解

更新時間：2024年04月18日 10:01:12 作者：不剪發(fā)的Tony老師

PostgreSQL 不但高度兼容 SQL 標(biāo)準(zhǔn),同時還對很多語法進行了擴展,可以用于實現(xiàn)一些特殊的功能,今天我們就來介紹一下 PostgreSQL 數(shù)據(jù)庫中 DISTINCT 關(guān)鍵字的 4 種不同用法,需要的朋友可以參考下

前言

PostgreSQL 不但高度兼容 SQL 標(biāo)準(zhǔn)，同時還對很多語法進行了擴展，可以用于實現(xiàn)一些特殊的功能。今天我們就來介紹一下 PostgreSQL 數(shù)據(jù)庫中 DISTINCT 關(guān)鍵字的 4 種不同用法。

DISTINCT

按照 SQL 標(biāo)準(zhǔn)，SELECT DISTINCT可以在返回查詢結(jié)果之前去除重復(fù)的記錄，每個重復(fù)的數(shù)據(jù)組中只保留一條記錄。例如：

SELECT DISTINCT dept_id, sex
FROM employee;

dept_id|sex|
-------|---|
      4|男  |
      1|男  |
      4|女  |
      5|男  |
      3|女  |
      2|男  |

以上語句中的 DISTINCT 表示返回不同部門 id 和性別的組合值。我們也可以使用 GROUP BY 實現(xiàn)相同的結(jié)果：

SELECT dept_id, sex
FROM employee
GROUP BY dept_id, sex;

按照 SQL 標(biāo)準(zhǔn)，多個 NULL 值對于 DISTINCT 而言屬于相同的分組。

DISTINCT ON

考慮一個問題：每個部門中月薪最高的員工都是誰？這個問題可以使用多種實現(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|

其中，第一個語句使用了子查詢；第二個語句使用了窗口函數(shù)，除了 ROW_NUMBER 之外，也可以使用 RANK 或者 DENSE_RANK 等函數(shù)。這兩者都是 SQL 標(biāo)準(zhǔn)實現(xiàn)。

除此之外，PostgreSQL 提供了擴展的 DISTINCT ON 子句，可以更加方便地實現(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 子句確保了返回的是每個部分中月薪最高的記錄。DISTINCT ON 中的字段或表達式（可能多個）必須和 ORDER BY 最左側(cè)的幾個字段或表達式相同。

IS DISTINCT FROM

空值（NULL）是數(shù)據(jù)庫中的一個特殊值，通常用于表示缺失值或者不適用的值?？罩档谋容^是一個比較容易出錯的問題。例如：

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)我們使用比較運算符（=、<>、<、> 等）與 NULL 進行比較時，結(jié)果既不是真也不是假，而是未知；因為 NULL 表示未知，也就意味著可能是任何值；我們不能說兩個未知的值相同，也不能說它們不相同。

為了比較 NULL 值，SQL 定義了兩個專用的運算符：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           |

因此，對于兩個可能為空的字段進行比較的完整方法如下：

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 |

以上語句返回了我們期望的結(jié)果，但是讀寫都很不方便；為此，PostgreSQL 提供了擴展的 IS [NOT] DISTINCT FROM 運算符，支持 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 表示判斷兩個數(shù)據(jù)是否相同，IS DISTINCT FROM 表示判斷兩個數(shù)據(jù)是否不同；它們都將 NULL 看作已知的一個特殊值，而不是 SQL 標(biāo)準(zhǔn)中的未知值。顯然這種語法更加言簡意賅。

另外，PostgreSQL 還提供了一個配置變量 transform_null_equals，該參數(shù)默認為 off；如果設(shè)置為 on，PostgreSQL 會自動執(zhí)行 convert x = NULL 到 x IS NULL 的轉(zhuǎn)換。建議不要依賴這個參數(shù)的設(shè)置，而是應(yīng)該修改應(yīng)用程序

聚合函數(shù)與 DISTINCT

聚合函數(shù)（aggregate function）針對一組數(shù)據(jù)行進行運算，并且返回一條結(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|趙云,周倉,關(guān)興,關(guān)平,趙氏,廖化,張苞,趙統(tǒng),馬岱|
      5|    8| 5012.5000000000000000|法正,簡雍,孫乾,糜竺,黃權(quán),龐統(tǒng),鄧芝,蔣琬    |

以上語句返回了每個部門的員工人數(shù)、平均月薪以及所有員工姓名的連接字符串（按照月薪從高到低）。

PostgreSQL 不僅實現(xiàn)了分組聚合操作，還支持聚合函數(shù)中的 DISTINCT 選項，可以在進行匯總之前去除每個分組中的重復(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ù)庫中DISTINCT關(guān)鍵字的四種用法詳解的文章就介紹到這了,更多相關(guān)PostgreSQL DISTINCT用法內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: