Início / dba / Perguntas / 124818
Accepted
timpone
Asked: 2015-12-30 19:20:04 +0800 CST

Agrupar e contar elementos de array usando intarray

  • 772

Estou trabalhando em um projeto Postgres 9.4 com a extensão intarray habilitada. Temos uma tabela que se parece com isso:

items
-------------------------------------
id    name                  tag_ids  
--------------------------------------
1     a car                 {1,4}
2     a room to rent        {1}
3     a boat                {1,2,4,11}
4     a wine                {2}
5     emily                 {3}

Gostaria de agrupar os IDs das tags, se possível. Como obter uma contagem de todos os elementos que têm tag_id'{1,2,4,11}'

tag_id  count
1       3
2       2
4       2
11      1

Isso é possível? Eu pensaria em um cruzamento assim:

select * from items where tag_ids && '{1,2,4,11}'

Mas preciso agrupar pelos elementos do array dentro do resultado da interseção. Se eu agrupar por tag_ids, é apenas o valor único.

Como eu faria isso?

postgresql aggregate

1 respostas

  1. Best Answer

    Mantenha a consulta básica que você já possui para identificar linhas com quaisquer elementos de matriz sobrepostos usando um índice .

    Em seguida, desaninhar apenas a interseção ( tag_ids & '{1,2,4,11}') em uma LATERALjunção. Por fim, agregue:

    SELECT tag_id, count(*) AS ct
FROM   items i
     , unnest(tag_ids & '{1,2,4,11}'::int[]) tag_id
WHERE  tag_ids && '{1,2,4,11}'::int[]
GROUP  BY tag_id
ORDER  BY count(*) DESC;

    Mais uma vez, o operador &de interseção do módulo intarray é instrumental.

    Resposta relacionada anterior:

    Sem intarray

    Se você não tiver intarray instalado, ou para qualquer outro tipo de array, precisamos de outro join:

    SELECT tag_id, count(*) AS ct
FROM  (
   SELECT *
   FROM   items
   WHERE  tag_ids && '{1,2,4,11}'::int[]
   ) i, unnest(tag_ids) t(tag_id)
JOIN unnest('{1,2,4,11}'::int[]) x(tag_id) USING (tag_id)
GROUP BY tag_id
ORDER BY count(*) DESC;

    Diferença sutil: o operador de interseção dobra duplicatas para produzir elementos distintos, enquanto esta consulta não. Só importa se pode haver elementos de matriz duplicados ...

    O violino demonstra ambas as consultas operando em uma tabela com uma linha adicional que mistura elementos correspondentes e não correspondentes para mostrar a necessidade da interseção ou uma junção adicional para eliminar elementos indesejados:

    db<>fiddle aqui
    Old sqlfiddle

    • 8

relate perguntas