DB

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

2021-05-30

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




中央値を取得

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

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


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



最頻値を取得

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

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

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


結果は以下の通り。

Row text_col top1.value top1.count top2.value top2.count top3.value top3.count
1 a 10 10 1 1 100 10
2 b 12 4 11 1 200 4
3 c 15 5 15 1 200 2


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



まとめ

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

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

集計値を取得するSQL

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

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



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

    ITipsと同じようなブログを作る方法

    ブログに興味がありますか?

    もしブログに興味がある場合は↓このページ↓を参考にすれば、ITipsと同じ構成でブログを作ることができます

    サーバー、ドメイン、ASPと【ブログに必要なものは全て】このページに書きました。
    同じ構成でブログ作るのはいいけど、記事はマネしないでネ (TДT;)

    ランキング参加中

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

    他にもブログやSNSで紹介してくれると励みになります。

    はてブのコメントで酷評されると泣きます(´;ω;`)

    -DB
    -

    © 2024 ITips