Bigquery - Grouping by an array and summing

Question

Say I have these 2 tables, and I want the third table as the output (left joining on the animal, array on the campaigns).

    animal     sound  spend clicks      animal  campaign
    +---------+------+---+---+          +-----+-----------+      
    | cat     | meow | 1 | 2 |          | cat | campaign1 |      
    +---------+------+---+---+          +-----+-----------+      
    | cat     | hss  | 2 | 4 |          | cat | campaign2 |      
    +---------+------+---+---+          +-----+-----------+      
    | dog     | woof | 3 | 6 |          | dog | campaign3 |      
    +---------+------+---+---+          +-----+-----------+      
    | bear    | roar | 4 | 8 |          | pig | campaign4 |      
    +---------+------+---+---+          +-----+-----------+    

    +---------+------+---+---+------------------------+
    | cat     | meow | 1 | 2 | [campaign1, campaign2] |
    +---------+------+---+---+------------------------+
    | cat     | hss  | 2 | 4 | [campaign1, campaign2] |
    +---------+------+---+---+------------------------+
    | dog     | woof | 3 | 6 | [campaign3]            |
    +---------+------+---+---+------------------------+
    | bear    | roar | 4 | 8 | []                     |
    +---------+------+---+---+------------------------+

This query duplicates the numbers (which makes it incorrect):


    WITH X AS (
    SELECT  "campaign2" AS campaign, "cat" AS animal,
    UNION ALL SELECT "campaign1", "cat"
    UNION ALL SELECT "campaign3" AS X, "dog" AS animal
    UNION ALL SELECT "campaign4" AS X, "pig"
    ),

    Z AS (
    SELECT "cat" AS animal, "meow" AS noise, 1 AS spend, 2 AS clicks
    UNION ALL SELECT "cat", "hss", 2, 4
    UNION ALL SELECT "dog", "woof", 3, 6
    UNION ALL SELECT "bear", "roar", 4, 8
    )
    SELECT Z.animal, noise, SUM(spend) AS spend, SUM(clicks) AS clicks, ARRAY_AGG(DISTINCT campaign IGNORE NULLS) AS campaign
    FROM Z
    LEFT JOIN X ON Z.animal = X.animal
    GROUP BY animal, noise

    +---------+------+---+---+------------------------+
    | cat     | meow | 2 | 4 | [campaign1, campaign2] |
    +---------+------+---+---+------------------------+
    | cat     | hss  | 4 | 8 | [campaign1, campaign2] |
    +---------+------+---+---+------------------------+
    | dog     | woof | 3 | 6 | [campaign3]            |
    +---------+------+---+---+------------------------+
    | bear    | roar | 4 | 8 | []                     |
    +---------+------+---+---+------------------------+

But this query should give the result I want, but is not possible in BigQuery

WITH X AS (
SELECT  "campaign2" AS campaign, "cat" AS animal,
UNION ALL SELECT "campaign1", "cat"
UNION ALL SELECT "campaign3" AS X, "dog" AS animal
UNION ALL SELECT "campaign4" AS X, "pig"
),
Y AS ( SELECT ARRAY_AGG(campaign) AS campaign, animal, 
        FROM X
        GROUP BY animal ),
Z AS (
SELECT "cat" AS animal, "meow" AS noise, 1 AS spend, 2 AS clicks
UNION ALL SELECT "cat", "hss", 2, 4
UNION ALL SELECT "dog", "woof", 3, 6
UNION ALL SELECT "bear", "roar", 4, 8
)
SELECT Z.animal, noise, SUM(spend) AS spend, SUM(clicks) AS clicks, Y.campaign
FROM Z
LEFT JOIN Y ON Z.animal = Y.animal
GROUP BY animal, noise, campaign

as it gives the error Grouping by expressions of type ARRAY is not allowed at [19:25]

It is possible to do it with TO_JSON_ARRAY, then with the next cte statement do JSON_EXTRACT_ARRAY to convert it back to an array — A H
– A H, Commented Nov 27, 2020 at 14:47

Mikhail Berlyant · Accepted Answer · 2020-11-27 17:59:57Z

1

Below is for BigQuery Standard SQL

#standardSQL
SELECT 
  animal, 
  noise, 
  spend, 
  clicks, 
  campaign
FROM Z
LEFT JOIN (
  SELECT animal, ARRAY_AGG(campaign) AS campaign
  FROM X 
  GROUP BY animal
)
USING(animal)

if to apply to sample data from your question - output is

answered Nov 27, 2020 at 17:59

Mikhail Berlyant

174k10 gold badges173 silver badges251 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Bigquery - Grouping by an array and summing

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related