Aggregate discarding values in one column that haven't a match in another column

Question

Say, I have a table representing colored and labeled items inside numbered boxes.
Each box can not contain more than one item with a particular label, but items with the same label (and the same or a different color) may be unique in other boxes.

Oversimplifying, and using PostgreSQL, we can take the following table:

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

I want to know the label and the color of all the items inside the box number 3, but also all the box numbers where an item with the same label can be found. In other words, I'm trying to:

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

But I also need to return the color column, showing only the color of the item inside the box number 3.

For the example data, the output should be this:

label | color | boxes
------+-------+------
b     | red   | 1,3
d     | green | 2,3

Here's an SQL Fiddle for the example.

Answer 1

You can use a correlated subquery:

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

Or (more modern) with 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;

• What is the difference between LATERAL and a subquery in PostgreSQL?

Using an array constructor since that is faster than array_agg() for a single column in the SELECT list.

You could also just use a self-join and aggregate:

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;

I'd expect that to be a bit slower. Test with EXPLAIN ANALYZE.

SQL Fiddle.

We certainly don't need DISTINCT here, since your data definition says:

Each box can not contain more than one item with a particular label

.. which is surely enforced with a UNIQUE constraint (or equivalent) on (label, box_number)?
This would also automatically provide the second index to make this query fast - besides the obvious index on (box_number). Details:

• Is a composite index also good for queries on the first field?

Answer 2

try this (just working ideas, without any optimisation research):

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)

Test dataset:

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;

result:

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

UPD. after Edit and comment:

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

Think, this is what You need?

Answer 3

Ok, after pondering upon @a_vlad and @erwin-brandstetter answers, I came out with my own solution that I leave here open to discussion:

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

Here's the SQLFiddle : http://sqlfiddle.com/#!15/eb2ac/11/0

As I explained in the comments to the previous answers, the reason why I'm proposing this solution is that it seems much more faster (even from the tests I've done) because it needs only to iterate 2N times instead of NxN times. I'm still convinced that this answer can be further refined, maybe using array constructors instead of array_agg() as Erwin suggested, but I cannot work out how. I would be glad to hear what do you think about.

Here's what is happening on the actual database (column names have been replaced as well as other attributes that needs to be selected/joined/sorted) when I run EXPLAIN SELECT:

Solution 1 (correlated subquery):

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

Solution 2 (using LATERAL):

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

Solution 3 (using CASE):

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

I've repeated the tests several times and they are all alike, and when adding complexity to the query the first two solutions takes so long that I need to stop them and the third always returns within seconds. Furthermore the third query is enough quick that I don't need to touch the indexes since the dataset is not supposed to grow very much.

EDIT:

After checking the self-join solution, it seems to be the faster one when used in a simply case and with only one box_number. When this has to be performed in a more complex case, for example against several box_numbers, it still takes an unacceptable lapse of time compared to the CASE solution

This are the results of the EXPLAIN ANALYZE for the same (logical) query as the other three.

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