Adding a "calculated column" to BigQuery query without repeating the calculations

user_9778284 0
Viewed 1577

I want to resuse value of calculated columns in a new third column. For example, this query works:

select
  countif(cond1) as A,
  countif(cond2) as B,
  countif(cond1)/countif(cond2) as prct_pass
  From 
  Where
  Group By

But when I try to use A,B instead of repeating the countif, it doesn't work because A and B are invalid:

select
  countif(cond1) as A,
  countif(cond2) as B,
  A/B as prct_pass
  From 
  Where
  Group By

Can I somehow make the more readable second version work ? Is this first one inefficient ?

firebase-analytics google-bigquery
1 Answers

You should construct a subquery (i.e. a double select) like

SELECT A, B, A/B as prct_pass 
FROM 
(
SELECT countif(cond1) as A, 
       countif(cond2) as B 
       FROM <yourtable>
)

The same amount of data will be processed in both queries. In the subquery one you will do only 2 countif(), in case that step takes a long time then doing 2 instead of 4 should be more efficient indeed.

Looking at an example using bigquery public datasets:

SELECT 
countif(homeFinalRuns>3) as A,
countif(awayFinalRuns>3) as B,
countif(homeFinalRuns>3)/countif(awayFinalRuns>3) as division 
FROM `bigquery-public-data.baseball.games_post_wide`

or

SELECT A, B, A/B as division FROM 
(
SELECT countif(homeFinalRuns>3) as A, 
       countif(awayFinalRuns>3) as B 
       FROM `bigquery-public-data.baseball.games_post_wide`  
)

we can see that doing all in one (without a subquery) is actually slightly faster. (I ran the queries 6 times for different values of the inequality, 5 times was faster and one time slower)

In any case, the efficiency will depend on how taxing is to compute the condition in your particular dataset.

user_128520900
Related