CODEWITHSUNDEEP
sqlgoogle-bigquery
samuelbrody1249
samuelbrody1249

Reputation: 4767

How to determine if a NULL value of a GROUPING value in BigQuery?

BigQuery supports ROLLUP, such as:

WITH Sales AS (
  SELECT 123 AS sku, 1 AS day, 9.99 AS price UNION ALL
  SELECT 123, 1, 8.99 UNION ALL
  SELECT 456, 1, 4.56 UNION ALL
  SELECT 123, NULL, 9.99 UNION ALL
  SELECT 789, 3, 1.00 UNION ALL
  SELECT 456, 3, 4.25 UNION ALL
  SELECT 789, 3, 0.99
)
SELECT
  sku,
  day,
  SUM(price) AS total
FROM Sales
GROUP BY ROLLUP(sku, day)
ORDER BY sku, day;

And we get:

enter image description here

My question is if there is a way to do GROUPING or GROUPING_ID in BigQuery? Yes, I know the workaround is 'clean-up the null-value before the query', but supposing there are tons of columns and I have no idea which ones have null values, is there a UDF or some other way to accomplish the GROUPING() concept in a BQ Aggregation?

Upvotes: 1

Views: 2354

Answers (1)

Rajat
Rajat

Reputation: 5803

There might not be an off-the-shelf solution to your problem, but this is what I use when I am trying to do multiple aggregations in one sweep. Note that I use this when I -- either don't care about quantifying nulls or have already imputed nulls.

select concat(if(sku is null,'','(sku)'),
              if(day is null,'','(day)'),
              if(coalesce(sku,day) is not null,'','(overall)')) as agg_level,
        sku,
        day,
        sum(price) as total
from sales
group by rollup(sku, day)
order by sku,day;

Upvotes: 2

Related Questions