DB

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


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

Rowtext_colnum_col1num_col2num_col3
1a101100
2a102100
3a103100
4a104100
5a105100
6a106100
7a107100
8a108100
9a109100
10a1010100
11b1211200
12b1212200
13b1213200
14b1214200
15c1515200
16c1516200
17c1517300
18c1518300
19c151910000



平均値を取得

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

平均値はデータの和をデータの個数で割った値で、 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


結果は以下の通り。

Rowtext_colavg_1avg_2avg_3
1a105.5100
2b1212.5200
3c15172200




中央値を取得

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

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


平均とどう違うの?
平均値は集団の重心の値なので、スケールのあまり変わらない集団だと特徴を掴むには良い指標です
しかしスケールの違う値があると、そちらに引きずられるので意味をつかみにくい指標になるんですよ
うーん、例えば?
成人男性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


結果は以下の通り。

Rowtext_colmedian_1median_2median_3
1a105.5100
2b1212.5200
3c1517300


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


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



最頻値を取得

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

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

値ごとに回数を調べるので 100100.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


結果は以下の通り。

Rowtext_coltop1.valuetop1.counttop2.valuetop2.counttop3.valuetop3.count
1a10101110010
2b1241112004
3c1551512002


見ての通り .value が出現の多い値、 .count でデータ内の出現回数を表している。



まとめ

今回はBigQueryで平均値、中央値、最頻値をSQLで取得する方法について解説した。

各値を取得する方法は以下の通り。

集計値を取得するSQL

  • 平均値:AVG()
  • 中央値:PERCENTILE_CONT()
  • 最頻値:APPROX_TOP_COUNT()

  • 平均値以外はちょっと関数の使い方が複雑だね
    まぁ2,3回実行すれば慣れますよ



    他にもBQ関連のTipsを書いているので、もし気になったら見てみて欲しい。

    役に立ったらシェアしてくれると励みになります。

    にほんブログ村 IT技術ブログへ

    -DB
    -

    Translate »

    © 2021 ITips