BigQuery query result having variable number of columns?

0
votes

I have a table in this form:

TIME  TYPE COUNT
00:01 DOG  4
00:04 CAT  5
00:07 DOG  2
00:30 BIRD 1
01:04 DOG  2
01:30 BIRD 3
01:40 DOG  1
02:01 BIRD 4

I do a query like this that joins the sum of COUNT of each TYPE based on hour:

select HOUR(`TIME`), `TYPE`, sum(`COUNT`)
from `table`
where `TYPE` = 'DOG'
group by HOUR(`TIME`)
FULL JOIN
...(same query but... where `TYPE` = 'CAT')
ON hour...
FULL JOIN
...(same query again, but where `TYPE` = 'BIRD')
etc.

The purpose being to get a result like this:

HOUR DOG CAT BIRD
00   6   5   1
01   5   0   3
02   0   0   4

My question is: How can you do this without joins? I want a query to work for any distinct TYPE values that happen to the be in the table and are not known upfront, so the table would grow in columns foreach distinct TYPE.

Is this something that could be done with BigQuery script?

2
sqlgoogle-bigquery

2 Answers

1
votes

Use sum(case . . ):

select extract(hour from time), 
       sum(case when type = 'dog' then count else 0 end) as dog,
       sum(case when type = 'cat' then count else 0 end) as cat,
       sum(case when type = 'bird' then count else 0 end) as bird
from t
group by extract(hour from time);

For this version, you need to explicitly list each value. If you want a dynamic SQL, you need to construct the query as a string and then run it.

0
votes

I haven't tried this, but one way I think will work is to interate over the distinct values and generate a query as per Gordon Linoff's answer:

# Get array of distinct types
DECLARE myTypes ARRAY<STRING>;
SET myTypes = ( select distinct(`TYPE`) form `table`);

# get size of array
DECLARE x INT64;
SET x = ARRAY_LENGTH(myTypes);

# start making query
DECLARE myQuery STRING;
SET myQuery = "select extract(hour from time),";

LOOP

    x = x-1;

    # Add column for each type
    SET myQuery = CONCAT(myQuery,format("sum(case when type = '%s' then count else 0 end) as %s,",myTypes[OFFSET(x)]);

    IF x == 0 THEN LEAVE;
    END IF;
END LOOP;

#finish up query
SET myQuery = CONCAT(myQuery,"from table group by extract(hour from time)")


EXECUTE IMMEDIATE myQuery;