Início / dba / Perguntas / 242101
Accepted
OrangeDog
Asked: 2019-07-05 07:21:10 +0800 CST

Vários agregados com junção cruzada (lateral)

  • 772

Seguindo combinando array_agg e unnest , dados estes dados:

key |  a | b | c
------------------
 1  |  0 | 1 | {1,2}
 1  |  1 | 2 | {3}
 1  | -1 | 3 | {2}
 1  |  2 | 4 | {}

Executando esta consulta:

SELECT 
  d.key,
  min(d.a) AS a,
  sum(d.b) AS b,
  array_agg(DISTINCT x.c) AS c 
FROM data AS d
  CROSS JOIN LATERAL unnest(d.c) AS x(c)
GROUP BY d.key

Dá o resultado inesperado:

key |  a | b | c
------------------
 1  | -1 | 7 | {1,2,3}

O que está acontecendo aqui, e como obter a soma correta?

Desempenho da resposta

Em meus dados reais (16.642 linhas, 1.942 chaves, 6 agregados), recebo essas estimativas de custo para cada solução sugerida.

postgresql join

2 respostas

  1. Best Answer

    O desaninhamento gera 2 linhas para (chave = 1, a = 0, b = 1) e a junção cruzada remove a linha com a matriz vazia.

    Portanto, seu grupo opera no seguinte conjunto:

    key | a  | b | c
----+----+---+--
  1 |  0 | 1 | 1
  1 |  0 | 1 | 2
  1 |  1 | 2 | 3
  1 | -1 | 3 | 2

    Uma solução é combinar duas consultas agrupadas por consultas, cada uma agrupando em um nível diferente:

    select *
from (
  select d1."key", min(d1.a), sum(d1.b)
  from data d1
  group by d1."key"
) m
  join (
    select "key", array_agg(DISTINCT x.c) AS c 
    from data d2
      left join lateral unnest(d2.c) as x(c) on true
    where x.c is not null
    group by "key"
  ) a on a."key" = m."key";

    Outra abordagem é incluir apenas a "primeira linha" para cada grupo "não aninhado" nos agregados:

    select d."key", 
       min(d.a) filter (where idx = 1 or idx is null), 
       sum(d.b) filter (where idx = 1 or idx is null),
       array_agg(distinct x.c)
from data AS d
  left join lateral unnest(d.c) with ordinality  AS x(c,idx) on true
group by d."key";

    with ordinalityretorna a posição do elemento não aninhado na matriz original. Para a linha com a matriz vazia, isso será nulo.

    • 1

  2. Um problema com sua consulta é que CROSS JOINelimina linhas onde unnest()não produz linhas (acontece para o array vazio {}).

    Você pode corrigir isso com LEFT JOIN .. ON true, mas o outro problema é que as linhas são multiplicadas onde unnest()retorna várias linhas (acontece para {1,2}).

    É assim que você obtém 7a soma: 1 + 1 + 2 + 3.

    Unir duas subconsultas separadas pode ser mais simples/rápido:

    SELECT *
FROM (
   SELECT key, min(a) AS min_a, sum(b) AS sum_b
   FROM   data
   GROUP  BY 1
   ) x
JOIN  (
   SELECT key, array_agg(DISTINCT e) AS dist_c 
   FROM   data, unnest(c) e
   GROUP  BY 1
   ) y USING (key);

    A junção simples funciona porque ambas as subconsultas foram agrupadas pela mesma coluna - e contanto que keyseja NOT NULL.

    db<>fique aqui

    Relacionado:

    • 0

relate perguntas