Distinct Count across Bigquery arrays

Question

I want to concatenate arrays across rows and then do a distinct count. Ideally, this would work:

WITH test AS
(
  SELECT
  DATE('2018-01-01') as date,
  2 as value,
  [1,2,3] as key
  UNION ALL
  SELECT
  DATE('2018-01-02') as date,
  3 as value,
  [1,4,5] as key
)
SELECT
  SUM(value) as total_value,
  ARRAY_LENGTH(ARRAY_CONCAT_AGG(DISTINCT key)) as unique_key_count
FROM test

Unfortunately, the ARRAY_CONCAT_AGG function doesn't support the DISTINCT operator. I can unnest the array but then I get a fanout and the sum of the value column is wrong:

WITH test AS
(
  SELECT
  DATE('2018-01-01') as date,
  2 as value,
  [1,2,3] as key
  UNION ALL
  SELECT
  DATE('2018-01-02') as date,
  3 as value,
  [1,4,5] as key
)

SELECT
  SUM(value) as total_value,
  COUNT(DISTINCT k) as unique_key_count

FROM test
  CROSS JOIN UNNEST(key) k

Is there anything I'm missing that would allow me to avoid joining in the unnested array?

Elliott Brossard · Accepted Answer · 2018-09-24 19:29:11Z

8

Here is an alternative:

CREATE TEMP FUNCTION DistinctCount(arr ANY TYPE) AS (
  (SELECT COUNT(DISTINCT x) FROM UNNEST(arr) AS x)
);

WITH test AS
(
  SELECT
  DATE('2018-01-01') as date,
  2 as value,
  [1,2,3] as key
  UNION ALL
  SELECT
  DATE('2018-01-02') as date,
  3 as value,
  [1,4,5] as key
)

SELECT
  SUM(value) as total_value,
  DistinctCount(ARRAY_CONCAT_AGG(key)) as unique_key_count
FROM test

This avoids having a subquery or needing to join the array with the table (causing duplicate values in the sum).

answered Sep 24, 2018 at 19:29

Elliott Brossard

34k2 gold badges75 silver badges105 bronze badges

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

1 Comment

alan Over a year ago

This is great, thanks. I want to use this in a query generation tool (Looker), so the subquery approach doesn't actually work for the use case. Interestingly, when I try it with a bunch of data, it's a lot slower. Didn't realise UDFs slow things down so much. The HLL++ approach below is much faster so that may be the way to go.

Mikhail Berlyant · Accepted Answer · 2018-09-24 20:04:03Z

5

Below is for BigQuery Standard SQL

#standardSQL
WITH test AS
(
  SELECT DATE('2018-01-01') AS DATE, 2 AS value, [1,2,3] AS key UNION ALL
  SELECT DATE('2018-01-02') AS DATE, 3 AS value, [1,4,5] AS key
)
SELECT 
  total_value,
  COUNT(DISTINCT key) unique_key_count
FROM (
  SELECT
    SUM(value) AS total_value,
    ARRAY_CONCAT_AGG(key) AS all_keys
  FROM test
), UNNEST(all_keys) key
GROUP BY total_value

result :

Row total_value unique_key_count     
1   5           5

In case you you have quite a number of rows in your table - you can easily get to memory/resources issue - in this case you can try using HyperLogLog++ Functions for approximate aggregation - see example below

#standardSQL
WITH test AS
(
  SELECT DATE('2018-01-01') AS DATE, 2 AS value, [1,2,3] AS key UNION ALL
  SELECT DATE('2018-01-02') AS DATE, 3 AS value, [1,4,5] AS key
)
SELECT
  SUM(value) total_value,
  HLL_COUNT.MERGE((SELECT HLL_COUNT.INIT(key) FROM UNNEST(key) key)) AS unique_key_count
FROM test

with result

Row total_value unique_key_count     
1   5           5

Note: this is approximate aggregations - so pay attention to precision parameter in HLL_COUNT.INIT(input [, precision]) function

edited Sep 24, 2018 at 20:04

answered Sep 24, 2018 at 19:31

Mikhail Berlyant

174k10 gold badges173 silver badges251 bronze badges

10 Comments

alan Over a year ago

Thanks. Hadn't played around with the HLL++ functions before

Mikhail Berlyant Over a year ago

That was the main purpose of the answer - to introduce those functions. They are usually overlooked :o)

Rogier Werschkull Over a year ago

Thanks, this is a great example of the usage of HLL++ functions!! I find the official documentation greatly lacking with clear examples (cloud.google.com/bigquery/docs/reference/standard-sql/…)

Mikhail Berlyant Over a year ago

@RogierWerschkull - Take a look at this Blog by one of my most favorite Googlers :o)

Rogier Werschkull Over a year ago

While the HLL approach works, my client wants exact distinct counting. For that, the non-HLL version works of course but is not something that can be generated dynamically with Looker, the BI tool we are using. Is there another way to achieve this?

|

Collectives™ on Stack Overflow

Distinct Count across Bigquery arrays

2 Answers 2

1 Comment

10 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

10 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related