主页 / dba / 问题 / 102677
Accepted
Fabrizio Mazzoni
Asked: 2015-05-29 04:55:08 +0800 CST

在多列上选择 DISTINCT

  • 772

假设我们有一个包含四列(a,b,c,d)相同数据类型的表。

是否可以选择列中数据中的所有不同值并将它们作为单列返回，还是我必须创建一个函数来实现这一点？

postgresql performance

5 个回答

  1. Best Answer

    更新：用 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 ;
    • 27

  2. 你可以使用 LATERAL，就像在这个查询中一样：

    SELECT DISTINCT
  x.n
FROM
  atable
  CROSS JOIN LATERAL (
    VALUES (a), (b), (c), (d)
  ) AS x (n)
;

    LATERAL 关键字允许连接的右侧从左侧引用对象。在这种情况下，右侧是一个 VALUES 构造函数，它根据您要放入单个列的列值构建一个单列子集。主查询仅引用新列，同时对其应用 DISTINCT。

    • 15

  3. 需要明确的是，我会union按照ypercube 的建议使用，但也可以使用数组：

    select distinct unnest( array_agg(distinct a)||
                        array_agg(distinct b)||
                        array_agg(distinct c)||
                        array_agg(distinct d) )
from t
order by 1;

    | 松散 |
| :----- |
| 0 |
| 1 |
| 2 |
| 3 |
| 5 |
| 6 |
| 8 |
| 9 |

    dbfiddle在这里

    • 10

  4. 最短的

    SELECT DISTINCT n FROM observations, unnest(ARRAY[a,b,c,d]) n;

    Andriy 想法的一个不太冗长的版本只是稍长一些，但更优雅、更快。对于许多不同/很少重复的值：

    SELECT DISTINCT n FROM observations, LATERAL (VALUES (a),(b),(c),(d)) t(n);

    最快的

    在每个涉及的列上都有一个索引！
    对于少数不同/许多重复值：

    WITH RECURSIVE
  ta AS (
   (SELECT a FROM observations ORDER BY a LIMIT 1)
   UNION ALL
   SELECT o.a FROM ta t, LATERAL (SELECT a FROM observations WHERE a > t.a ORDER BY a LIMIT 1) o
   )
, tb AS (
   (SELECT b FROM observations ORDER BY b LIMIT 1)
   UNION ALL
   SELECT o.b FROM tb t, LATERAL (SELECT b FROM observations WHERE b > t.b ORDER BY b LIMIT 1) o
   )
, tc AS (
   (SELECT c FROM observations ORDER BY c LIMIT 1)
   UNION ALL
   SELECT o.c FROM tc t, LATERAL (SELECT c FROM observations WHERE c > t.c ORDER BY c LIMIT 1) o
   )
, td AS (
   (SELECT d FROM observations ORDER BY d LIMIT 1)
   UNION ALL
   SELECT o.d FROM td t, LATERAL (SELECT d FROM observations WHERE d > t.d ORDER BY d LIMIT 1) o
   )
SELECT a
FROM  (
         TABLE ta
   UNION TABLE tb
   UNION TABLE tc
   UNION TABLE td
   ) sub
ORDER  BY 1;  -- optional

    这是另一种 rCTE 变体，类似于@ypercube 已经发布的变体，但我使用ORDER BY 1 LIMIT 1它min(a)通常会更快一些。我也不需要额外的谓词来排除 NULL 值。
    而LATERAL不是相关的子查询，因为它更干净（不一定更快）。

    我对此技术的首选答案中的详细说明：

    我将它添加到 ypercube 的sqlfiddle
    ... 现在将其移植到 dbfiddle.uk，因为 sqlfiddle.com 跟不上：

    db<>在这里摆弄

    • 7

  5. 你可以，但是当我编写和测试这个函数时，我感觉不对。这是一种资源浪费。
    请使用联合和更多选择。唯一的优势（如果是的话），从主表进行一次扫描。

    在 sql fiddle 中，您需要将分隔符从$更改为其他内容，例如/

    CREATE TABLE observations (
    id         serial
  , a int not null
  , b int not null
  , c int not null
  , d int not null
  , created_at timestamp
  , foo        text
);

INSERT INTO observations (a, b, c, d, created_at, foo)
SELECT (random() * 20)::int        AS a          -- few values for a,b,c,d
     , (15 + random() * 10)::int 
     , (10 + random() * 10)::int 
     , ( 5 + random() * 20)::int 
     , '2014-01-01 0:0'::timestamp 
       + interval '1s' * g         AS created_at -- ascending (probably like in real life)
     , 'aöguihaophgaduigha' || g   AS foo        -- random ballast
FROM generate_series (1, 10) g;               -- 10k rows

CREATE INDEX observations_a_idx ON observations (a);
CREATE INDEX observations_b_idx ON observations (b);
CREATE INDEX observations_c_idx ON observations (c);
CREATE INDEX observations_d_idx ON observations (d);

CREATE OR REPLACE FUNCTION fn_readuniqu()
  RETURNS SETOF text AS $$
DECLARE
    a_array     text[];
    b_array     text[];
    c_array     text[];
    d_array     text[];
    r       text;
BEGIN

    SELECT INTO a_array, b_array, c_array, d_array array_agg(a), array_agg(b), array_agg(c), array_agg(d)
    FROM observations;

    FOR r IN
        SELECT DISTINCT x
        FROM
        (
            SELECT unnest(a_array) AS x
            UNION
            SELECT unnest(b_array) AS x
            UNION
            SELECT unnest(c_array) AS x
            UNION
            SELECT unnest(d_array) AS x
        ) AS a

    LOOP
        RETURN NEXT r;
    END LOOP;

END;
$$
  LANGUAGE plpgsql STABLE
  COST 100
  ROWS 1000;

SELECT * FROM fn_readuniqu();
    • 3

相关问题