BigQueryにサブクエリのエイリアスを優先されてorder byがエラーになった話

はじめに

とある別DBのSQLをBigQueryで実行した時のこと。
最初からBigQueryで書く場合は、凡ミスとしてすぐ気づき、この事象に悩むことはないかもしれません。

が、今回のように別DBでは動くがBigQueryではエラーが出たケースです。

DBやデータをBigQueryに移した後同じクエリを実行しときは割と対面するようなので
そんな方は参考になるかなと思います。

例題テーブル

こんなテーブルを使います。
dataset: babynames
table_name: names2021

column	type
name	STRING
gender	STRING
cnt	INTEGER

正常クエリ

select
    name
    ,ranking
from (
  select 
    name
    ,cnt as ranking
  from
    babynames.names2021
) as ranking

この場合、クエリは成功しました。

エラーになるクエリ

select
    name
    ,row_number() over(partition by gender order by ranking)  as gender_rank
from (
select
    name
    ,gender
    ,cnt as ranking
from
    babynames.names20121
group by
    name
    ,gender
    ,cnt
) as ranking

すると・・・・

Ordering by expressions of type STRUCT is not allowed at 

こんなエラーが出ました。

原因

cntカラムのエイリアスとサブクエリのエイリアスがrankingという名前で重複しています。
order byで指定した名前は、サブクエリ内のカラム名よりサブクエリのエイリアス名が優先されたため
サブクエリのテーブルをSTRUCT型としてorder byしてるとみなされてエラーになりました。

STRUCT型についてはこちらを参照。

解決策

名前を被らないように変える。という手が一番手っ取り早いです。

select
    name
    ,ranking
    ,row_number() over(partition by gender order by ranking)  as b
from (
    select
        name
        ,gender
        ,count as ranking
    from
        babynames.names2010 
) as ranking_table

が、テーブルとして認識されたことを踏まえて操作したところ
(ちょっと気持ち悪いですが)以下のような書き方をすることでも解決は可能だったので記載しておきます。

select
    name
    ,ranking
    ,row_number() over(partition by gender order by ranking.ranking)  as b
from (
    select
        name
        ,gender
        ,count as ranking
    from
        babynames.names2010 
) as ranking

結論

もしorder by して STRUCT is not allowedって言われたらお名前の付け方が怪しいかもしれません。
参考になれば幸いです。