#🔎 #BigQuery #EDA #転置行列

null.sql

#standaardSQL

with
    null_tb as (
        select
            (select count(*) from `pj.ds.weatherAUS` where Location="NA") as Location
            , (select count(*) from `pj.ds.weatherAUS` where MinTemp="NA") as MinTemp
            , (select count(*) from `pj.ds.weatherAUS` where MaxTemp="NA") as MaxTemp
            , (select count(*) from `pj.ds.weatherAUS` where Rainfall="NA") as Rainfall
            , (select count(*) from `pj.ds.weatherAUS` where Evaporation="NA") as Evaporation
            , (select count(*) from `pj.ds.weatherAUS` where Sunshine="NA") as Sunshine
            , (select count(*) from `pj.ds.weatherAUS` where WindGustDir="NA") as WindGustDir
            , (select count(*) from `pj.ds.weatherAUS` where WindGustSpeed="NA") as WindGustSpeed
            , (select count(*) from `pj.ds.weatherAUS` where WindDir9am="NA") as WindDir9am
            , (select count(*) from `pj.ds.weatherAUS` where WindDir3pm="NA") as WindDir3pm
            , (select count(*) from `pj.ds.weatherAUS` where WindSpeed9am="NA") as WindSpeed9am
            , (select count(*) from `pj.ds.weatherAUS` where WindSpeed3pm="NA") as WindSpeed3pm
            , (select count(*) from `pj.ds.weatherAUS` where Humidity9am="NA") as Humidity9am
            , (select count(*) from `pj.ds.weatherAUS` where Humidity3pm="NA") as Humidity3pm
            , (select count(*) from `pj.ds.weatherAUS` where Pressure9am="NA") as Pressure9am
            , (select count(*) from `pj.ds.weatherAUS` where Pressure3pm="NA") as Pressure3pm
            , (select count(*) from `pj.ds.weatherAUS` where Cloud9am="NA") as Cloud9am
            , (select count(*) from `pj.ds.weatherAUS` where Cloud3pm="NA") as Cloud3pm
            , (select count(*) from `pj.ds.weatherAUS` where Temp9am="NA") as Temp9am
            , (select count(*) from `pj.ds.weatherAUS` where Temp3pm="NA") as Temp3pm
            , (select count(*) from `pj.ds.weatherAUS` where RainToday="NA") as RainToday
            , (select count(*) from `pj.ds.weatherAUS` where RainTomorrow="NA") as RainTomorrow
    )
     /*****************************************
     * 以降、縦表示にすための処理 （不要ならコメントアウト）
     ******************************************/
    ,col_tb as (
        select
            column_name as key -- カラム名
            ,data_type         -- データ型
        from 
            `prj-test3.case_study1.INFORMATION_SCHEMA.COLUMNS`
        where 
            table_name = 'weatherAUS'
    )
    ,unpivot_tb as (
        /*****************************************************************************************
         * ■ Unpivot(転置行列)
         *　　BigQueryで「union」やり過ぎると、メモリ的に怒られる。
         * 
         * Resources exceeded during query execution: 
         * Not enough resources for query planning - too many subqueries or query is too complex.
         ******************************************************************************************/
        (select 'Location' as key, Location from null_tb)
        union all (select 'MinTemp' as key, MinTemp from null_tb)
        union all (select 'MaxTemp' as key, MaxTemp from null_tb)
        union all (select 'Rainfall' as key, Rainfall from null_tb)
        union all (select 'Evaporation' as key, Evaporation from null_tb)
        union all (select 'Sunshine' as key, Sunshine from null_tb)
        union all (select 'WindGustDir' as key, WindGustDir from null_tb)
        union all (select 'WindGustSpeed' as key, WindGustSpeed from null_tb)
        union all (select 'WindDir9am' as key, WindDir9am from null_tb)
        union all (select 'WindDir3pm' as key, WindDir3pm from null_tb)
        union all (select 'WindSpeed9am' as key, WindSpeed9am from null_tb)
        union all (select 'WindSpeed3pm' as key, WindSpeed3pm from null_tb)
        union all (select 'Humidity9am' as key, Humidity9am from null_tb)
        union all (select 'Humidity3pm' as key, Humidity3pm from null_tb)
        union all (select 'Pressure9am' as key, Pressure9am from null_tb)
        union all (select 'Pressure3pm' as key, Pressure3pm from null_tb)
        union all (select 'Cloud9am' as key, Cloud9am from null_tb)
        union all (select 'Cloud3pm' as key, Cloud3pm from null_tb)
        union all (select 'Temp9am' as key, Temp9am from null_tb)
        union all (select 'Temp3pm' as key, Temp3pm from null_tb)
        union all (select 'RainToday' as key, RainToday from null_tb)
        union all (select 'RainTomorrow' as key, RainTomorrow from null_tb)
    )

/*****************************
* 処理結果は同じだが、見せ方が異なる
*****************************/
-- ① 横表示
select * from null_tb;

-- ②　縦表示
select * from  col_tb left join unpivot_tb using(key);

Cf.
SQL で縦横変換まとめ（pivot と unpivot） - Qiita