CODEWITHSUNDEEP
google-cloud-platformgoogle-bigquery
Rod0n
Rod0n

Reputation: 1079

Fill missing buckets with 0

I've a query that counts the number of items per bucket, but it's possible to have a missing bucket. I've created the "all_buckets" view in order to join with the mentioned table in order to fill the missing buckets with a 0 value.

I've tried using a CROSS JOIN but that wasn't the solution as I got repeated rows and erroneous data. And with LEFT and RIGHT JOIN I don't end up getting all the columns.

This is what I have as an example:

WITH

all_buckets AS (
  SELECT
    *
  FROM
    UNNEST([
      'A',
      'B',
      'C',
      'D',
      'E'
    ]) AS buckets
),

test_data AS (
SELECT
  'A' as bucket, 10 as items_count

  UNION ALL    

SELECT
  'C' as bucket, 1 as items_count

  UNION ALL

SELECT  
  'E' as bucket, 100 as items_count
)

This is something I've tried without success:

SELECT
  cb.bucket,
  IF(cb.bucket = lb.buckets, cb.items_count, 0) AS items_count
FROM
  test_data cb
cross join
  listings_buckets lb
where
  lb.buckets = cb.bucket

And the following would be the desired output:

A, 10
B, 0
C, 1
D, 0
E, 100

Upvotes: 0

Views: 104

Answers (1)

pmo511
pmo511

Reputation: 619

You should use left join with coalesce (ifnull):

WITH all_buckets AS ( 
   SELECT * 
   FROM
   UNNEST([
     'A',
     'B',
     'C',
     'D',
     'E'
   ]) AS buckets
), test_data AS (
   SELECT 'A' as bucket, 10 as items_count 
   UNION ALL
   SELECT 'C' as bucket, 1 as items_count
   UNION ALL
   SELECT  'E' as bucket, 100 as items_count
)
select b.*, ifnull(d.items_count, 0) as items_count
from all_buckets b
left join test_data d on b.buckets = d.bucket

Upvotes: 2

Related Questions