CODEWITHSUNDEEP
sqlgoogle-bigquery
Víctor
Víctor

Reputation: 123

Bigquery - UNION ALL same query with different parameters

I have a very large query and I want to UNION ALL the same query but changing two different parameters. Thus, to make the query more readable I want to avoid using UNION ALL. Here a short example (non-sense but it gives an idea) of what I have:

SELECT DISTINCT Name,'7 days' FROM T WHERE DATE(event_time) BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY) AND CURRENT_DATE())
UNION ALL
SELECT DISTINCT Name, '14 DAYS' FROM T WHERE DATE(event_time) BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 14 DAY) AND CURRENT_DATE())

So the whole point is to avoid this union and make it scalable for more unions, just changing the intervals without creating hundreds of lines of code.

Upvotes: 2

Views: 313

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

You could write this as:

SELECT DISTINCT Name, CONCAT(d, ' days')
FROM T JOIN
     (UNNEST(ARRAY[7, 14]) d
     ON DATE(event_time) BETWEEN DATE_SUB(CURRENT_DATE, INTERVAL d DAY) AND CURRENT_DATE())

This query is basically non-sense, given that it is just going to be repeating recent names for every time period.

But the key idea is to UNNEST() an array with the values you want and then use JOIN instead of WHERE.

Upvotes: 2

Related Questions