BigQuery: How to iterate through list and use variable in where clause-CodePudding

I have a table called t1 and I want to count distinct values for an id column based on a list of known values in that exist in column ìd2.

How can I do this iteratively in BigQuery?

To do this kind of query one at a time, I would do

SELECT COUNT(DISTINCT(id)) FROM t1 WHERE id2 = '113'

SELECT COUNT(DISTINCT(id)) FROM t1 WHERE id2 = '50'

When I try this for multiple values for id2: SELECT COUNT(DISTINCT(id)) FROM t1 WHERE id2 IN ('113', '50')

it counts all of the values in the list. ie. unique ìd for '113' '50'

How do I execute a query that will iterate through a list of ìd2 values?

CodePudding user response：

Use below approach

select id2, count(distinct id)
from t1
join unnest([113, 50]) id2
using(id2)
group by id2

CodePudding user response：

I would do something like:

SELECT DISTINCT id, id2, COUNT(1)
FROM t1
GROUP BY
id, id2

This will return a list of:

etc...

Which will contain all of the unique pairings of id and id2, from here you should be able to extract what you need.

From here you should be able select the values that you want i.e. as in above example:

SELECT * FROM previous_results
WHERE id2 IN ('113', '50')