主页 / dba / 问题 / 117913
Accepted
Alexandros
Asked: 2015-10-14 09:52:53 +0800 CST

高效合并（删除重复）数组

  • 772

我有两张桌子，left2和right2。两个表都很大（1-10M 行）。

CREATE TABLE left2(id INTEGER, t1 INTEGER, d INTEGER);
ALTER TABLE left2 ADD PRIMARY KEY (id,t1);

CREATE TABLE right2( t1 INTEGER, d INTEGER, arr INTEGER[] );
ALTER TABLE right2 ADD PRIMARY KEY(t1,d);

我将执行这种类型的查询：

SELECT l.d + r.d,
       UNIQ(SORT((array_agg_mult(r.arr)))
FROM left2 l,
     right2 r
WHERE l.t1 = r.t1
GROUP BY l.d + r.d
ORDER BY l.d + r.d;

我在哪里聚合数组我使用函数：

CREATE AGGREGATE array_agg_mult(anyarray) (
SFUNC=array_cat,
STYPE=anyarray,
INITCOND='{}');

连接数组后，我使用模块的UNIQ功能intarray。有没有更有效的方法来做到这一点？字段上是否有任何索引arr来加快合并（删除重复项）？聚合函数可以直接删除重复项吗？如果有帮助，原始数组可能被认为是排序的（并且它们是唯一的）。

SQL Fiddle 在这里：

postgresql postgresql-9.3

2 个回答

  1. Best Answer

    结果正确？

    首先：正确性。您想生成一组独特的元素吗？您当前的查询不这样做。intarray模块uniq()中的函数只承诺：

    删除相邻的重复项

    按照手册中的说明，您需要：

    SELECT l.d + r.d, uniq(sort(array_agg_mult(r.arr)))
FROM   ...

    还为您提供排序数组 - 假设您需要，您没有澄清。

    我看到你有 sort()你的小提琴，所以这可能只是你问题中的一个错字。

    Postgres 9.5 或更高版本

    无论哪种方式，由于 Postgres 9.5array_agg()具有我array_agg_mult()内置的开箱即用功能，而且速度也更快：

    数组处理也有其他性能改进。

    询问

    的主要目的array_agg_mult()是聚合多维数组，但无论如何你只生成一维数组。所以我至少会尝试这个替代查询：

    SELECT l.d + r.d AS d_sum, array_agg(DISTINCT elem) AS result_arr
FROM   left2  l
JOIN   right2 r USING (t1)
     , unnest(r.arr) elem
GROUP  BY 1
ORDER  BY 1;

    这也解决了您的问题：

    聚合函数可以直接删除重复项吗？

    是的，它可以，与DISTINCT. 但这并不比uniq()整数数组快，整数数组已经针对整数数组进行了优化，同时DISTINCT对所有合格的数据类型都是通用的。

    不需要intarray模块。但是，结果不一定是排序的。Postgres 对DISTINCT. 大集合通常是散列的，除非您添加显式，否则结果未排序ORDER BY。如果需要排序数组，可以直接添加ORDER BY到聚合函数中：

    array_agg(DISTINCT elem ORDER BY elem)

    但这通常比将预先排序的数据提供给array_agg()（一个大排序与许多小排序）要慢。所以我会在一个子查询中排序，然后聚合：

    SELECT d_sum, uniq(array_agg(elem)) AS result_arr
FROM  (
   SELECT l.d + r.d AS d_sum, elem
   FROM   left2  l
   JOIN   right2 r USING (t1)
        , unnest(r.arr) elem
   ORDER  BY 1, 2
   ) sub
GROUP  BY 1
ORDER  BY 1;

    这是我对 Postgres 9.4 的粗略测试中最快的变体。

    SQL Fiddle基于您提供的那个。

    指数

    我认为这里的任何索引都没有多大潜力。唯一的选择是：

    CREATE INDEX ON right2 (t1, arr);

    仅当您从中获得仅索引扫描时才有意义 - 如果基础表right2比这两列宽得多并且您的设置符合仅索引扫描的条件，就会发生这种情况。Postgres Wiki 中的详细信息。

    • 11

  2. 我真的很失望，这在 Microsoft Access 中很容易做到。您可以创建一个“删除重复项”查询，然后查看 SQL 以了解它是如何执行的。我必须启动一台 Windows 机器才能查看。它们各不相同，查询向导可以做到。

    我认为可行的一件事是将所有数据加载到一个表中，然后将 SELECT DISTINCT 执行到一个新表中。您也可以在使用 order by 子句时坚持使用它。一年前我以某种方式做到了，一定是这样。

    我正在结合 2 年的温度数据，传感器每分钟发送 2 个相同数据点的副本作为冗余保护。有时一个会被扔掉，但我只想保留一个。我也有文件之间的重叠。

    如果数据在整个运行过程中的格式完全相同，那么在 unix 机器上，您可以执行类似的操作

    cat *.tab > points.txt
sort -n < points.txt > sorted.txt
uniq -u sorted.txt unique.txt

    但是 uniq 将行作为字符串进行比较，例如 18.7000 与 18.7 不同。我在 2 年内改变了我的软件，所以我有两种格式。

    • 0

相关问题