みなさん、こんにちは。
クルトンです！
今回は、前回ブログの流れから、「基本統計量をSQLで把握するのはどうしたら良いのか」という疑問を持ったので、確かめる意味でやってみました。 基本統計量とは、本ブログで以下のものを指します。
- 最大値
- 最小値
- 平均値
- 中央値
- 最頻値
- 分散
- 標準偏差
SQLの実行環境を準備
前回ブログと同じ内容ですので、知っている方は飛ばしていただいて大丈夫です。
実行環境はGoogle Colabで、ランタイムはCPUです。
まずは必要なモジュールとデータをダウンロードします。
# IPython用のモジュールをダウンロード
!pip install jupysql duckdb-engine
# データ（csvファイル）のダウンロード
!wget https://raw.githubusercontent.com/mwaskom/seaborn-data/master/penguins.csvここでダウンロードしたpenguins.csvは、ペンギンの種類やどこの島に生息しているかなどのデータが記録されているテーブルデータになります。
次に、Colab上でSQLが使えるように、データ読み込みとDBの設定をします。
%load_ext sql
%sql duckdb://これで、データの用意とSQLクエリが使えるようになりました。
Colabのセル上でSQLを実行するには、 %%sqlと書いて、その直下にSQL文を書きます。
例えば以下のように書きます。
%%sql
SELECT
*
FROM
penguins.csvまた、以下のように書いて、実行結果を変数に入れることも可能です。
%%sql 変数名 <<実際に変数に入れてデータを確認する例は次のようになります。
%%sql all_result <<
SELECT
*
FROM
penguins.csv基本統計量を確認してみた
本ブログ冒頭でも書いたように、以下の内容を確認していきます。
- 最大値
- 最小値
- 平均値
- 中央値
- 最頻値
- 分散
- 標準偏差
確認する対象としては、speciesカラムにある以下のペンギンの種別ごとに値を確認していきます。
- Adelie アデリーペンギン
- Chinstrap ヒゲペンギン
- Gentoo ジェンツーペンギン
最大値
MAX関数を使う事で、最大値を確認していきます。
NULLが帰ってきた時のためにCOALESCE関数を使って、NULLが帰ってきた場合は0を表示するようにしています。
%%sql
SELECT
species
, COALESCE(MAX(bill_length_mm), 0) as max_length
, COALESCE(MAX(bill_depth_mm), 0) as max_depth
, COALESCE(MAX(flipper_length_mm), 0) as max_flipper_length
, COALESCE(MAX(body_mass_g), 0) as max_body_mass
FROM
penguins.csv
GROUP BY
species実行結果は次のようになります。
species max_length max_depth max_flipper_length max_body_mass
Adelie 46.0 21.5 210 4775
Chinstrap 58.0 20.8 212 4800
Gentoo 59.6 17.3 231 6300最小値
MIN関数を使うことで、最小値を確認していきます。
最大値の時と同じく、NULLが帰ってきた時のためにCOALESCE関数を使って、NULLが帰ってきた場合は0を表示するようにしています。
SELECT
species
, COALESCE(MIN(bill_length_mm), 0) as min_length
, COALESCE(MIN(bill_depth_mm), 0) as min_depth
, COALESCE(MIN(flipper_length_mm), 0) as min_flipper_length
, COALESCE(MIN(body_mass_g), 0) as min_body_mass
FROM
penguins.csv
GROUP BY
species実行結果は次のようになります。
species min_length min_depth min_flipper_length min_body_mass
Adelie 32.1 15.5 172 2850
Chinstrap 40.9 16.4 178 2700
Gentoo 40.9 13.1 203 3950平均値
AVG関数を使って、平均値を確認していきます。
最大値の時と同じく、NULLが帰ってきた時のためにCOALESCE関数を使って、NULLが帰ってきた場合は0を表示するようにしています。
SELECT
species
, COALESCE(AVG(bill_length_mm), 0) as avg_length
, COALESCE(AVG(bill_depth_mm), 0) as avg_depth
, COALESCE(AVG(flipper_length_mm), 0) as avg_flipper_length
, COALESCE(AVG(body_mass_g), 0) as avg_body_mass
FROM
penguins.csv
GROUP BY
species実行結果は次のようになります。
species avg_length avg_depth avg_flipper_length avg_body_mass
Adelie 38.79139072847684 18.346357615894032 189.95364238410596 3700.662251655629
Chinstrap 48.83382352941177 18.420588235294115 195.8235294117647 3733.0882352941176
Gentoo 47.504878048780476 14.982113821138206 217.1869918699187 5076.016260162602中央値
MEDIAN関数を使って、中央値を確認していきます。
最大値の時と同じく、NULLが帰ってきた時のためにCOALESCE関数を使って、NULLが帰ってきた場合は0を表示するようにしています。
SELECT
species
, COALESCE(MEDIAN(bill_length_mm), 0) as med_length
, COALESCE(MEDIAN(bill_depth_mm), 0) as med_depth
, COALESCE(MEDIAN(flipper_length_mm), 0) as med_flipper_length
, COALESCE(MEDIAN(body_mass_g), 0) as med_body_mass
FROM
penguins.csv
GROUP BY
species実行結果は次のようになります。
species med_length med_depth med_flipper_length med_body_mass
Adelie 38.8 18.4 190.0 3700.0
Chinstrap 49.55 18.45 196.0 3700.0
Gentoo 47.3 15.0 216.0 5000.0最頻値
最頻値については定義されている関数がないため、自前で実装しました。
%%sql
WITH cnt_tbl AS(
SELECT
species
, bill_length_mm
, COUNT(*) AS cnt
FROM
penguins.csv
WHERE
species='Gentoo'
GROUP BY
bill_length_mm, species
)
, max_cnt_tbl AS(
SELECT
MAX(cnt) as max_cnt
FROM
cnt_tbl
)
SELECT
species
, bill_length_mm
, max_cnt as mode
FROM
cnt_tbl, max_cnt_tbl
WHERE
cnt>=max_cntGROUP BY句を使う都合上、全てのカラムでなく、特定のカラムの最頻値を計算しています。
また、ペンギンの種別ごとに計算する一例として今回はWHERE species='Gentoo'とGentooを指定しています。他種別を確認したい場合はここをGentooの部分を別の種別名に変更してください。
最頻値計算をするカラムを変更したい場合は、cnt_tblテーブルでSELECT文の対象にしているカラム名(ここではbill_length_mm)とGROUP BY句のカラム名(ここではbill_length_mm)を確認したいカラム名に変更すると良いです。
実行結果は次のようになります。4が最頻値の値で、複数該当は全部表示されます。
species bill_length_mm mode
Gentoo 50.0 4
Gentoo 46.5 4
Gentoo 45.5 4
Gentoo 46.2 4
Gentoo 45.2 4分散
VARIANCE関数を使って、分散を確認していきます。
最大値の時と同じく、NULLが帰ってきた時のためにCOALESCE関数を使って、NULLが帰ってきた場合は0を表示するようにしています。
%%sql
SELECT
species
, COALESCE(VARIANCE(bill_length_mm), 0) as var_length
, COALESCE(VARIANCE(bill_depth_mm), 0) as var_depth
, COALESCE(VARIANCE(flipper_length_mm), 0) as var_flipper_length
, COALESCE(VARIANCE(body_mass_g), 0) as var_body_mass
FROM
penguins.csv
GROUP BY
species実行結果は次のようになります。
species var_length var_depth var_flipper_length var_body_mass
Adelie 7.09372538631347 1.4802366445916129 42.76450331125821 210282.89183222956
Chinstrap 11.150629938542583 1.289122036874451 50.86391571553991 147713.45478489902
Gentoo 9.497844862055171 0.9627922164467538 42.05491136878581 254133.18006130887標準偏差
STDDEV関数を使って、標準偏差を確認していきます。
最大値の時と同じく、NULLが帰ってきた時のためにCOALESCE関数を使って、NULLが帰ってきた場合は0を表示するようにしています。
%%sql
SELECT
species
, COALESCE(STDDEV(bill_length_mm), 0) as var_length
, COALESCE(STDDEV(bill_depth_mm), 0) as var_depth
, COALESCE(STDDEV(flipper_length_mm), 0) as var_flipper_length
, COALESCE(STDDEV(body_mass_g), 0) as var_body_mass
FROM
penguins.csv
GROUP BY
species実行結果は次のようになります。
species var_length var_depth var_flipper_length var_body_mass
Adelie 2.6634048483686197 1.216649762500126 6.539457417191293 458.56612591013476
Chinstrap 3.339255895935887 1.1353951016604091 7.1318942585781455 384.3350813871914
Gentoo 3.081857372114286 0.9812197595068873 6.484975818673946 504.1162366570917終わりに
今回は基本統計量をSQLのみを使って、基本統計量を確認してみました。 思った以上に必要な関数が定義されているため、簡単に確認できました。
最頻値についてだけは他にもっと良い実装方法がないか考えたいところです。
今回はここまで。
それでは、また！
5
