BigQueryで平均値、中央値、最頻値をSQLで取得する方法

BigQueryで平均値、中央値、最頻値ってどうやって取得するんだっけ？

データの分析や処理に便利なBigQuery(BQ)。

大規模データの処理に優れており、テーブルに保存した大きなデータでも短時間で集計することができる。

そして集計時によく使うのが平均値、中央値、最頻値。

これらの値はBigQueryではどうやって計算するのか。

そこで今回はBigQueryで平均値、中央値、最頻値をSQLで取得する方法について紹介する。

この記事を書いている人

システムエンジニア、AIエンジニアと、IT業界で10年以上働いている中堅。PythonとSQLが得意。

記事を読むメリット

BigQueryで平均値、中央値、最頻値をSQLで取得する方法がわかり、データ集計が楽になる

データ準備

ではまずデータを準備する。

以下のCSVを avg_median_sample というテーブルとして取り込む。

text_col,num_col1,num_col2,num_col3
a,10,1,100
a,10,2,100
a,10,3,100
a,10,4,100
a,10,5,100
a,10,6,100
a,10,7,100
a,10,8,100
a,10,9,100
a,10,10,100
b,12,11,200
b,12,12,200
b,12,13,200
b,12,14,200
c,15,15,200
c,15,16,200
c,15,17,300
c,15,18,300
c,15,19,10000

すると以下のようなテーブルとなる。

Row	text_col	num_col1	num_col2	num_col3
1	a	10	1	100
2	a	10	2	100
3	a	10	3	100
4	a	10	4	100
5	a	10	5	100
6	a	10	6	100
7	a	10	7	100
8	a	10	8	100
9	a	10	9	100
10	a	10	10	100
11	b	12	11	200
12	b	12	12	200
13	b	12	13	200
14	b	12	14	200
15	c	15	15	200
16	c	15	16	200
17	c	15	17	300
18	c	15	18	300
19	c	15	19	10000

平均値を取得

データが準備できたら、平均値を取得してみる。

平均値はデータの和をデータの個数で割った値で、 AVG() 関数を使うと取得できる。

SELECT
text_col,
avg(num_col1) as avg_1,
avg(num_col2) as avg_2,
avg(num_col3) as avg_3
FROM test.avg_median_sample
GROUP BY text_col

結果は以下の通り。

Row	text_col	avg_1	avg_2	avg_3
1	a	10	5.5	100
2	b	12	12.5	200
3	c	15	17	2200

参考

標準 SQL の式、関数、演算子 | BigQuery | Google Cloud

中央値を取得

次は中央値を取得してみる。

中央値は平均値とは違って計算で求める値ではなく、単純にデータを順番に並べて真ん中の位置にある値のことだ。

平均とどう違うの？

平均値は集団の重心の値なので、スケールのあまり変わらない集団だと特徴を掴むには良い指標です

しかしスケールの違う値があると、そちらに引きずられるので意味をつかみにくい指標になるんですよ

うーん、例えば？

成人男性100人とウルトラマンがいたとして、そこで体重の平均を取るとどうなるか

あー、ウルトラマン一人が重すぎるせいで平均意味無いわ・・・

中央値は PERCENTILE_CONT() 関数を使うと取得できる。

SELECT distinct
text_col,
PERCENTILE_CONT(num_col1, 0.5) OVER(PARTITION BY text_col) AS median_1,
PERCENTILE_CONT(num_col2, 0.5) OVER(PARTITION BY text_col) AS median_2,
PERCENTILE_CONT(num_col3, 0.5) OVER(PARTITION BY text_col) AS median_3
FROM test.avg_median_sample
ORDER BY text_col

結果は以下の通り。

Row	text_col	median_1	median_2	median_3
1	a	10	5.5	100
2	b	12	12.5	200
3	c	15	17	300

text_col="c" の num_col3 の平均は2200だったのに対して、中央値は単純にデータを並べた真ん中を取っているので300となっているのがわかる。

ちなみに PERCENTILE_CONT() の第2引数を0.5から 0.1 に変更すると下から10％の位置にある値、 0.75 なら75％の位置にある値が取得できる。

参考

標準 SQL の式、関数、演算子 | BigQuery | Google Cloud

最頻値を取得

次は最頻値を取得してみる。

最頻値はデータの値の中で最も多く出現した値のことである。

値ごとに回数を調べるので 100 と 100.1 は別の値扱いになる為、連続する数値に対してはあまり調べる意味はない。

グループ分けしてどのグループが多いか調べるなり、テキストデータに対してどの滝ストの出現が多いか調べると良いだろう。

最頻値を調べるには APPROX_TOP_COUNT() を用いる。

SELECT
text_col,
APPROX_TOP_COUNT(num_col1, 1) top1,
APPROX_TOP_COUNT(num_col2, 1) top2,
APPROX_TOP_COUNT(num_col3, 1) top3
FROM test.avg_median_sample
GROUP BY text_col
ORDER BY text_col

結果は以下の通り。