How to get around binding order in WHERE clause in BigQuery-CodePudding

Take the following query which produces an error:

with tbl as (
    select 'david' name, 10 age union all select 'tom', 20
) select
    name,
    age,
    extract(year from CURRENT_TIMESTAMP())-age as birthyear
from
    tbl
where birthyear > 2010 # binding order occurs before SELECT so invalid

One way to get around this is to throw it in a subselect:

with tbl as (
select 'david' name, 10 age union all select 'tom', 20
) 
select * from (select
    name,
    age,
    extract(year from CURRENT_TIMESTAMP())-age as birthyear
from
    tbl)
where birthyear > 2010

But that feels like such a hack-ish approach. Is there a better way to deal with this 'deferred-binding' of the select list?

CodePudding user response：

This is obviously matter of preferences
Below is my way of addressing such cases

select
  name,
  age,
  birthyear
from tbl, 
unnest([struct(extract(year from CURRENT_TIMESTAMP()) as birthyear)])
where birthyear > 2010 # birthyear is calculated before SELECT so WHERE is valid

CodePudding user response：

You can also use query like below:

with tbl as (
    select 'david' name, 10 age union all select 'tom', 20
) select
    name,
    age,
    extract(year from CURRENT_TIMESTAMP())-age as birthyear
from
    tbl
where extract(year from CURRENT_TIMESTAMP())-age > 2010