Início / dba / Perguntas / 117913
Accepted
Alexandros
Asked: 2015-10-14 09:52:53 +0800 CST

Fusão eficiente (remoção de duplicatas) de arrays

  • 772

Eu tenho duas tabelas left2e right2. Ambas as tabelas serão grandes (1-10 milhões de linhas).

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);

Vou realizar este tipo de consulta:

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;

Onde para agregação de arrays utilizo a função:

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

Após concatenar os arrays, utilizo a UNIQfunção do intarraymódulo. Existe uma maneira mais eficiente de fazer isso? Existe algum índice no arrcampo para acelerar a mesclagem (com remoção de duplicatas)? A função agregada pode remover duplicatas diretamente? As matrizes originais podem ser consideradas classificadas (e são únicas) se isso ajudar.

O SQL Fiddle está aqui :

postgresql postgresql-9.3

2 respostas

  1. Best Answer

    Resultados corretos?

    Em primeiro lugar: correção. Você quer produzir uma variedade de elementos únicos? Sua consulta atual não faz isso. A função uniq()do módulo intarray apenas promete:

    remover duplicatas adjacentes

    Conforme instruído no manual , você precisaria de:

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

    Também fornece matrizes classificadas - supondo que você queira isso, você não esclareceu.

    Vejo que você tem sort() em seu violino , então isso pode ser apenas um erro de digitação em sua pergunta.

    Postgre 9.5 ou posterior

    De qualquer forma, já que o Postgres 9.5 array_agg()tem os recursos do meu array_agg_mult()integrado pronto para uso e muito mais rápido também:

    Também houve outras melhorias de desempenho para manipulação de array.

    Consulta

    O principal objetivo do array_agg_mult()é agregar arrays multidimensionais, mas você só produz arrays unidimensionais de qualquer maneira. Então, eu tentaria pelo menos esta consulta alternativa:

    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;

    O que também responde à sua pergunta:

    A função agregada pode remover duplicatas diretamente?

    Sim, pode, com DISTINCT. Mas isso não é mais rápido do que uniq()para arrays inteiros, que foi otimizado para arrays inteiros, enquanto DISTINCTé genérico para todos os tipos de dados qualificados.

    Não requer o intarraymódulo. No entanto , o resultado não é necessariamente classificado. Postgres usa algoritmos variados para arquivos DISTINCT. Grandes conjuntos geralmente são hash, o que deixa o resultado não classificado, a menos que você adicione explícito ORDER BY. Se você precisar de matrizes classificadas, poderá adicionar ORDER BYdiretamente à função agregada:

    array_agg(DISTINCT elem ORDER BY elem)

    Mas isso é normalmente mais lento do que alimentar dados pré-classificados array_agg()(uma classificação grande versus muitas classificações pequenas). Então, eu classificaria em uma subconsulta e agregaria :

    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;

    Esta foi a variante mais rápida em meu teste superficial no Postgres 9.4.

    SQL Fiddle com base no que você forneceu.

    Índice

    Não vejo muito potencial para nenhum índice aqui. A única opção seria:

    CREATE INDEX ON right2 (t1, arr);

    Só faz sentido se você obtiver varreduras somente de índice - o que acontecerá se a tabela subjacente right2for substancialmente mais larga do que apenas essas duas colunas e sua configuração se qualificar para varreduras somente de índice. Detalhes no Wiki do Postgres.

    • 11

  2. Estou muito desapontado, isso é uma coisa fácil de fazer no Microsoft Access. Você pode criar uma consulta "remover duplicatas" e, em seguida, examinar o SQL para ver como está sendo feito. Vou ter que ligar uma máquina Windows para olhar. Eles variam, o assistente de consulta faz isso.

    Acho que uma coisa que funciona é carregar todos os seus dados em uma tabela e fazer SELECT DISTINCT em uma nova tabela. Você também pode manter uma cláusula order by enquanto estiver fazendo isso. Eu fiz isso de alguma forma há um ano, deve ser isso.

    Estou combinando 2 anos de dados de temperatura, o sensor envia 2 cópias do mesmo ponto de dados a cada minuto como uma proteção redundante. Às vezes, um fica no lixo, mas eu só quero manter um. Eu também tenho sobreposições entre arquivos.

    Se os dados tiverem exatamente o mesmo formato durante toda a execução, em uma máquina unix você pode fazer algo como

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

    Mas o uniq compara linhas como strings e, por exemplo, 18.7000 não é o mesmo que 18.7. Mudei meu software durante os 2 anos, então tenho os dois formatos.

    • 0

relate perguntas