主页 / dba / 问题 / 121095
Accepted
Claudio Floreani
Asked: 2015-11-15 09:41:59 +0800 CST

汇总一列中在另一列中没有匹配项的丢弃值

  • 772

比方说，我有一张表格，代表编号框内的彩色和标记项目。
每个箱子不能包含超过一件带有特定标签的物品，但具有相同标签（以及相同或不同颜色）的物品在其他箱子中可能是独一无二的。

过度简化并使用 PostgreSQL，我们可以采用下表：

CREATE TABLE items (
    label character varying,
    color character varying,
    box_number integer
);
INSERT INTO items VALUES
  ('a','red',1),
  ('b','blue',1),
  ('c','blue',1),
  ('a','red',2),
  ('c','green',2),
  ('d','blue',2),
  ('b','red',3),
  ('d','green',3);

我想知道 3 号箱子内所有物品的标签和颜色，以及可以找到具有相同标签的物品的所有箱子号。换句话说，我正在尝试：

SELECT label, boxes
FROM (
  SELECT label, array_agg(DISTINCT box_number) AS boxes
  FROM items
  GROUP BY label
) AS sub1
WHERE 3 = ANY(boxes);

但我还需要返回该color列，仅显示 3 号框内项目的颜色。

对于示例数据，输出应该是这样的：

标签 | 颜色 | 盒子
------+--------+-----
乙 | 红色 | 1,3
d | 绿色 | 2,3

这是示例的 SQL Fiddle。

postgresql aggregate

3 个回答

  1. Best Answer

    您可以使用相关子查询：

    SELECT label, color
    , (SELECT ARRAY (SELECT box_number FROM items WHERE label = i.label)) AS boxes
FROM   items i
WHERE  box_number = 3;

    或者（更现代）LEFT JOIN LATERAL：

    SELECT i.label, i.color, b.boxes
FROM   items i
LEFT   JOIN LATERAL (
   SELECT ARRAY (SELECT box_number FROM items WHERE label = i.label) AS boxes
   ) b ON true
WHERE  box_number = 3;

    使用数组构造函数，因为它比列表array_agg()中的单个列更快SELECT。

    您也可以只使用自连接和聚合：

    SELECT i.label, i.color, array_agg(b.box_number) AS boxes
FROM   items i
JOIN   items b USING (label)
WHERE  i.box_number = 3
GROUP  BY i.label, i.color;

    我希望那会慢一点。测试EXPLAIN ANALYZE。

    SQL小提琴。

    我们当然不需要DISTINCT这里，因为您的数据定义说：

    每个盒子不能包含超过一件带有特定标签的物品

    .. 这肯定是通过UNIQUE约束（或等效的）强制执行的(label, box_number)？
    这也会自动提供第二个索引来使这个查询更快——除了明显的索引(box_number)。细节：

    • 3

  2. 试试这个（只是可行的想法，没有任何优化研究）：

    SELECT t1.label, t1.color, array_agg(t1.box_number)
FROM items t1

WHERE t1.box_number = 3
GROUP BY t1.label, t1.color

union all
select 'LABEL' as label, 'NOT FOUND' as color, array_agg(t1.box_number)
FROM items t1 
where t1.label not in (SELECT t2.label
FROM items t2

WHERE t2.box_number = 3)

    测试数据集：

    BEGIN;
INSERT INTO "dbo"."items" VALUES ('aaa', '1', 'yellow');
INSERT INTO "dbo"."items" VALUES ('bbb', '3', 'green');
INSERT INTO "dbo"."items" VALUES ('ccc', '3', 'black');
INSERT INTO "dbo"."items" VALUES ('ddd', '4', 'green');
COMMIT;

    结果：

    bbb green   {3}
ccc black   {3}
LABEL   NOT FOUND   {1,4}

    更新。编辑和评论后：

    SELECT label, color, boxes
FROM (
  SELECT t1.label, (select t2.color from items t2 where t2.box_number =3 and t2.label = t1.label LIMIT 1) as color,array_agg(DISTINCT t1.box_number) AS boxes
  FROM items t1
  GROUP BY label
) AS sub1
WHERE 3=ANY(boxes);

    想一想，这就是你需要的吗？

    • 1

  3. 好的，在思考@a_vlad 和@erwin-brandstetter 的回答后，我提出了我自己的解决方案，我把它留在这里供讨论：

    SELECT
    label,
    array_to_string(array_agg(color),',') AS color,
    array_agg(box_number) AS boxes
FROM (SELECT
        label,
        CASE WHEN box_number=3 THEN color END AS color,
        box_number
    FROM items
    ) AS sub1
GROUP BY label
HAVING 3=ANY(array_agg(box_number))

    这是SQLFiddle：http ://sqlfiddle.com/#!15/eb2ac/11/0

    正如我在之前答案的评论中所解释的那样，我提出这个解决方案的原因是它看起来更快（即使从我所做的测试来看）因为它只需要迭代 2N 次而不是 NxN 次。我仍然相信这个答案可以进一步完善，也许使用数组构造函数而不是array_agg()Erwin 建议的那样，但我不知道如何做。我很高兴听到你的想法。

    这是我运行时实际数据库中发生的情况（列名以及其他需要选择/加入/排序的属性）EXPLAIN SELECT：

    方案一（关联子查询）：

    Seq Scan on items m  (cost=0.00..1552688.65 rows=355 width=21) (actual time=22.883..5813.845 rows=355 loops=1)
"  Filter: ((box_number)::text = '3'::text)"
"  Rows Removed by Filter: 27123"
"  SubPlan 2"
"    ->  Result  (cost=4361.48..4361.49 rows=1 width=0) (actual time=16.312..16.312 rows=1 loops=355)"
"          InitPlan 1 (returns $1)"
"            ->  Seq Scan on items  (cost=0.00..4361.48 rows=2 width=4) (actual time=4.958..16.271 rows=18 loops=355)"
"                  Filter: ((label)::text = (m.label)::text)"
"                  Rows Removed by Filter: 27460"
Planning time: 0.200 ms
Execution time: 5814.197 ms

    解决方案 2（使用横向）：

    Nested Loop Left Join  (cost=4361.48..1552695.75 rows=355 width=53) (actual time=31.448..5693.496 rows=355 loops=1)
"  ->  Seq Scan on items m  (cost=0.00..4361.48 rows=355 width=21) (actual time=11.945..24.483 rows=355 loops=1)"
"        Filter: ((box_number)::text = '3'::text)"
"        Rows Removed by Filter: 27123"
"  ->  Result  (cost=4361.48..4361.49 rows=1 width=0) (actual time=15.962..15.963 rows=1 loops=355)"
"        InitPlan 1 (returns $1)"
"          ->  Seq Scan on items  (cost=0.00..4361.48 rows=2 width=4) (actual time=4.837..15.920 rows=18 loops=355)"
"                Filter: ((label)::text = ($0)::text)"
"                Rows Removed by Filter: 27460"
Planning time: 0.258 ms
Execution time: 5693.929 ms

    解决方案 3（使用案例）：

    GroupAggregate  (cost=6318.73..7231.95 rows=15417 width=25) (actual time=881.221..951.466 rows=340 loops=1)
"  Group Key: items.label"
"  Filter: ('3'::text = ANY ((array_agg(items.box_number))::text[]))"
"  Rows Removed by Filter: 15077"
"  ->  Sort  (cost=6318.73..6387.43 rows=27478 width=25) (actual time=881.066..885.931 rows=27478 loops=1)"
"        Sort Key: items.label"
"        Sort Method: quicksort  Memory: 2906kB"
"        ->  Seq Scan on items  (cost=0.00..4292.78 rows=27478 width=25) (actual time=0.012..21.602 rows=27478 loops=1)"
Planning time: 0.696 ms
Execution time: 952.184 ms

    我重复了几次测试，它们都很相似，当增加查询的复杂性时，前两个解决方案花费的时间太长，以至于我需要停止它们，而第三个总是在几秒钟内返回。此外，第三个查询足够快，我不需要触及索引，因为数据集不应该增长太多。

    编辑：

    在检查了self-join solution之后，在简单的情况下并且只有一个时，它似乎是更快的一个box_number。当这必须在更复杂的情况下执行时，例如针对几个s，与CASE 解决方案box_number相比，它仍然需要不可接受的时间流逝

    这是与其他三个相同（逻辑）查询的 EXPLAIN ANALYZE 的结果。

    HashAggregate  (cost=9637.72..9640.22 rows=200 width=25) (actual time=63.424..64.045 rows=340 loops=1)
"  Group Key: i.label, i.color"
"  ->  Hash Join  (cost=4636.26..9588.61 rows=6548 width=25) (actual time=41.224..57.156 rows=6494 loops=1)"
"        Hash Cond: ((i.label)::text = (b.label)::text)"
"        ->  Seq Scan on items i  (cost=0.00..4361.48 rows=355 width=21) (actual time=6.033..18.434 rows=355 loops=1)"
"              Filter: ((box_number)::text = '3'::text)"
"              Rows Removed by Filter: 27123"
"        ->  Hash  (cost=4292.78..4292.78 rows=27478 width=18) (actual time=35.145..35.145 rows=27478 loops=1)"
"              Buckets: 4096  Batches: 1  Memory Usage: 1363kB"
"              ->  Seq Scan on items b  (cost=0.00..4292.78 rows=27478 width=18) (actual time=0.007..19.538 rows=27478 loops=1)"
Planning time: 2.236 ms
Execution time: 64.579 ms
    • 0

相关问题