BigQuery / SQL aggregate data from one field, many records

user_1744727 0
Viewed 14

I have the following tables:

rooms
    +--------+------------+
    | room_id| room_name  |
    +--------+------------+
    | 1      |  Kitchen   |
    | 2      |  Bedroom   |
    +--------+------------+

room_products
    +--------+------------+
    | room_id| product_id |
    +--------+------------+
    | 1      |    101     |
    | 1      |    102     |
    | 1      |    103     |
    | 1      |    104     |
    | 2      |    105     |
    | 2      |    106     |
    | 2      |    107     |
    +--------+------------+

products
    +------------+--------------+
    | product_id | product_name |
    +------------+--------------+
    | 101        |  Kettle      |
    | 102        |  Toaster     |
    | 103        |  Microwave   |
    | 104        |  Cooker      |
    | 105        |  Bed         |
    | 106        |  Lamp        |
    | 107        |  Clock       |
    +------------+--------------+

The output I'm trying to get is:

+----------+-------------------------------------+
| room_name| product_name_aggregated             |
+----------+-------------------------------------+
| Kitchen  |  Kettle, Toaster, Microwave, Cooker |
| Bedroom  |  Bed, Lamp, Clock                   |
+----------+-------------------------------------+

The code I have so far is:

SELECT r.room_name, STRING_AGG(p.product_name)
FROM rooms r
JOIN room_products rp ON rp.room_id = r.room_id
JOIN products p ON p.product_id = rp.product_id
WHERE r.room_id = 1 OR r.room_id = 2;

Think I'm getting all kinds of confused. Would really appreciate any help... Thank you.

google-bigquery aggregate join sql
1 Answers

You are almost there:

SELECT r.room_name, STRING_AGG(p.product_name, ',')
FROM rooms r JOIN
     room_products rp
     ON rp.room_id = r.room_id JOIN
     products p
     ON p.product_id = rp.product_id
WHERE r.room_id IN (1, 2)
GROUP BY r.room_name;

The changes are:

  • Added a second argument to STRING_AGG().
  • Changed the WHERE to use IN (more convenient; OR also works).
  • Added GROUP BY.

Note that I would recommend ARRAY_AGG() rather than STRING_AGG() -- arrays are quite useful in BigQuery.

user_11440350
Related