主页 / dba / 问题 / 242101
Accepted
OrangeDog
Asked: 2019-07-05 07:21:10 +0800 CST

具有交叉（横向）连接的多个聚合

  • 772

继结合 array_agg 和 unnest之后，给定以下数据：

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

执行此查询：

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

给出了意想不到的结果：

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

这里发生了什么，以及如何获得正确的总和？

答案表现

根据我的实际数据（16642 行、1942 个键、6 个聚合），我得到了每个建议解决方案的这些成本估算。

postgresql join

2 个回答

  1. Best Answer

    取消嵌套为 (key = 1, a = 0, b = 1) 生成 2 行，并且交叉连接删除具有空数组的行。

    因此，您的 group by 对以下集合进行操作：

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

    一种解决方案是组合两个分组查询，每个查询分组不同级别：

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

    另一种方法是仅在聚合中包含每个“未嵌套”组的“第一行”：

    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 ordinality返回原始数组中未嵌套元素的位置。对于具有空数组的行，它将为空。

    • 1

  2. 您的查询的一个问题是CROSS JOIN消除了unnest()不产生行的行（发生在空数组中{}）。

    你可以用 来解决这个LEFT JOIN .. ON true问题，但另一个问题是行在unnest()返回多行的地方相乘（发生在{1,2}）。

    这就是你得到7总和的方式：1 + 1 + 2 + 3。

    加入两个单独的子查询可能是最简单/最快的：

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

    普通连接有效，因为两个子查询都按同一列分组 - 并且只要key是NOT NULL。

    db<>在这里摆弄

    有关的：

    • 0

相关问题