在多列上選擇 DISTINCT

假設我們有一個包含四列(a,b,c,d)相同數據類型的表。

是否可以選擇列中數據中的所有不同值並將它們作為單列返回，還是我必須創建一個函式來實現這一點？

更新：用 100K 行測試了**SQLfiddle**中的所有 5 個查詢（和 2 個單獨的案例，一個有幾個（25）個不同的值，另一個有很多（大約 25K 個值））。

一個非常簡單的查詢是使用UNION DISTINCT. 我認為如果四列中的每一列都有一個單獨的索引將是最有效的如果 Postgres 實現了鬆散索引掃描優化，那麼四列中的每一列都有一個單獨的索引將是有效的，但它沒有。所以這個查詢效率不高，因為它需要對錶進行 4 次掃描（並且不使用索引）：

-- Query 1. (334 ms, 368ms) 
SELECT a AS abcd FROM tablename 
UNION                           -- means UNION DISTINCT
SELECT b FROM tablename 
UNION 
SELECT c FROM tablename 
UNION 
SELECT d FROM tablename ;

另一種方法是先UNION ALL使用DISTINCT. 這也需要 4 次表掃描（並且不使用索引）。當值很少時效率不錯，並且在我的（不是廣泛的）測試中，更多的值成為最快的：

-- Query 2. (87 ms, 117 ms)
SELECT DISTINCT a AS abcd
FROM
 ( SELECT a FROM tablename 
   UNION ALL 
   SELECT b FROM tablename 
   UNION ALL
   SELECT c FROM tablename 
   UNION ALL
   SELECT d FROM tablename 
 ) AS x ;

---

其他答案提供了更多使用數組函式或LATERAL語法的選項。Jack 的查詢 ( 187 ms, 261 ms) 具有合理的性能，但 AndriyM 的查詢似乎更有效 ( 125 ms, 155 ms)。它們都對錶進行一次順序掃描，並且不使用任何索引。

實際上，Jack 的查詢結果比上面顯示的要好一些（如果我們刪除order by），並且可以通過刪除 4 個內部distinct並只保留外部的來進一步改進。

---

最後，當且僅當4 列的不同值相對較少時，您可以使用WITH RECURSIVE上述鬆散索引掃描頁面中描述的 hack/優化並使用所有 4 個索引，結果非常快！使用相同的 100K 行和分佈在 4 列中的大約 25 個不同值進行測試（僅在 2 毫秒內執行！），而對於 25K 不同值，它是最慢的 368 毫秒：

-- Query 3.  (2 ms, 368ms)
WITH RECURSIVE 
   da AS (
      SELECT min(a) AS n  FROM observations
      UNION ALL
      SELECT (SELECT min(a) FROM observations
              WHERE  a > s.n)
      FROM   da AS s  WHERE s.n IS NOT NULL  ),
   db AS (
      SELECT min(b) AS n  FROM observations
      UNION ALL
      SELECT (SELECT min(b) FROM observations
              WHERE  b > s.n)
      FROM   db AS s  WHERE s.n IS NOT NULL  ),
  dc AS (
      SELECT min(c) AS n  FROM observations
      UNION ALL
      SELECT (SELECT min(c) FROM observations
              WHERE  c > s.n)
      FROM   dc AS s  WHERE s.n IS NOT NULL  ),
  dd AS (
      SELECT min(d) AS n  FROM observations
      UNION ALL
      SELECT (SELECT min(d) FROM observations
              WHERE  d > s.n)
      FROM   db AS s  WHERE s.n IS NOT NULL  )
SELECT n 
FROM 
( TABLE da  UNION 
 TABLE db  UNION 
 TABLE dc  UNION 
 TABLE dd
) AS x 
WHERE n IS NOT NULL ;

SQLfiddle

---

總而言之，當不同的值很少時，遞歸查詢是絕對的贏家，而有很多值，我的第二個，Jack 的（下面的改進版本）和 AndriyM 的查詢是表現最好的。

---

後期添加，第一個查詢的變體，儘管有額外的不同操作，但性能比原來的第一個好得多，只比第二個差一點：

-- Query 1b.  (85 ms, 149 ms)
SELECT DISTINCT a AS n FROM observations 
UNION 
SELECT DISTINCT b FROM observations 
UNION 
SELECT DISTINCT c FROM observations 
UNION 
SELECT DISTINCT d FROM observations ;

和傑克的改進：

-- Query 4b.  (104 ms, 128 ms)
select distinct unnest( array_agg(a)||
                       array_agg(b)||
                       array_agg(c)||
                       array_agg(d) )
from t ;

引用自：https://dba.stackexchange.com/questions/102677