#🔎 #BigQuery #要約統計量

statistics_value.sql

#standaardSQL

select
/**********************************************
 * Google BigQueryでの要約統計量
 **********************************************/
    count(passenger_count) as n
    , avg(passenger_count) as mean
    , stddev(passenger_count) as std
    , min(passenger_count) as min
    , (select q from(select percentile_cont(passenger_count, 0.25) over() as q from `project.dataset.table`) group by q) as first_quartile
    , (select q from(select percentile_cont(passenger_count, 0.5) over() as q from `project.dataset.table`) group by q) as median
    , (select q from(select percentile_cont(passenger_count, 0.75) over() as q from `project.dataset.table`) group by q) as thrd_quartile
    , max(passenger_count) as max
from 
    `project.dataset.table`
where 
    passenger_count is not null
;