在BigQuery窗口函数的Partition部分使用别名?

Question

下面显示错误"无法识别的名称:country_alias "。

select         year
, case when g.country = 'Australia' then 'Australia' else 'Other' end as country_alias
, count(distinct g.user_id) OVER (PARTITION BY country_alias) as user_count_country
from           people as g 
where          year = 2021
order by       1,2

我认为这是不可能利用列别名作为分区的一部分在窗口函数?还是我用错了语法?

Answer 1

是的，您可以利用列别名作为窗口的一部分。实际上你可以这样写

select         year,
count(distinct g.user_id) OVER 
(PARTITION BY case when g.country = 'Australia' then 'Australia' else 'Other' end) as user_count_country
from           people as g 
where         year = 2021
order by       1,2
-------------------OR You could also do this--------------------
select *, count(distinct g.user_id) OVER (PARTITION BY country_alias) as user_count_country 
from (
select          year,
case when g.country = 'Australia' then 'Australia' else 'Other' end as country_alias
from           people as g 
where          year = 2021

) as _table
order by       1,2

这里使用子查询的原因是OVER()的执行顺序与SELECT相同。因此，当您尝试在SELECT中别名列并使用OVER()时，它对OVER()是不可见的，并且它会抱怨这一点。这就是你的情况。