Bigquery - UNION ALL same query with different parameters

user_9748410 0
Viewed 147

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.

google-bigquery sql
1 Answers

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.

user_11440350
Related