ちょっと前にデータサイエンス100本ノック（構造化データ加工編）というのが公開されていた。
github.com機械学習で必要となるデータ加工の演習ができるという。必要なデータが予め用意されておりdockerを利用するので演習をするためのデータベースや言語のインストールも不要だ。
Pythonの勉強がてらちょっとやってみるかなと思っていたら演習にはSQLもあった。肩慣らしに先にSQLでもやるかな。そんな感じで気楽にノックを始めたのだった。
……だが甘かった。最初こそスイスイ進んでいたのだが見る見る勢いは止まり滅多打ちにあってしまった。
まあ最終的にはズタボロになりながらもなんとかやり終えたのでその過程で得たことをちょっとメモとしてまとめたみた。
統計用語
- 中央値
- 有限個のデータを小さい順に並べたとき中央に位置する値
- 最頻値
- データ群や確率分布で最も頻繁に出現する値。
- 標本分散
- 確率変数の分布が期待値（平均）からどれだけ散らばっているかを示す非負の値。
要素のすべてが平均値を取る場合は値が0となり最大値と最小値しかない場合に最も大きな値となる。
要素がすべての取りうる値である場合は母分散といい、サンプル的に取得した場合は標本分散という。 - 標本標準偏差
- データや確率変数の散らばり具合（ばらつき）を表す指標。
標本分散の平方根。分散の値は元の値を二乗しているので要素の単位と合わせるために平方根を取る。 - ダミー変数化
- 数値ではないものを数値(0/1)に変換する。
取りうる値が2値以外の場合は取りうる値-1分の変数を定義する。 - データの標準化
- 異なる基準のデータ同士を比較するときに使う。指定された指針の値になるようにする。
一般的な標準化の指針は平均値：0 標準偏差：1。この結果になるようにデータを変換する。
データの変換式は(対象のデータ - 対象データの平均値) / 対象データとなる。
なぜこの変換式で平均値：0 標準偏差：1となるかは統計学における標準化でよくわかる。
標準化の方針が最大値：1 最小値：0の場合、データの変換式は(対象のデータ - 対象データの最小値) / (対象データの最大値 - 対象データの最小値)となる。 - 外れ値の検出
- 外れ値とは他の値から大きく外れた値のこと。指定された指針にしたがって外れ値を定義する。
- σ（標準偏差）による外れ値
- (値 - 平均値) / σ で計算しこの値が3σ以上離れた～などの条件で外れ値を定義する
- IQRによる外れ値
- IQR=75パーセンタイル（第三四分位数）－ 25パーセンタイル（第一四分位数）
75パーセンタイルは有限個のデータを小さい順に並べたとき3/4の位置にある値のこと。中央値は50パーセンタイル。
条件として第一四分位数-1.5×IQRを下回るもの～などの条件で外れ値を定義する。
- アンダーサンプリング
- 少数派のデータ件数に合うように多数派データからランダムに抽出する方法
データの比率を変えないようにサンプリングする
関数
100本ノックで利用するデータベースはpostgresqlだがoracleとmysqlも使う機会があるので調べてみた。それ以外のデータベースは対象外ということで。
統計用途
- 中央値の取得
-
- postgresql
- PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY カラム名)
- oracle
- MEDIAN ( カラム名 )
- mysql
- なし
- 最頻値の取得
-
- postgresql
- MODE() WITHIN GROUP(ORDER BY カラム名)
- oracle
- STATS_MODE(カラム名)
- mysql
- なし
- 標本分散
-
- postgresql,oracle,mysql
- VAR_SAMP(カラム名)
- 標本標準偏差
-
- postgresql,mysql
- STDDEV_SAMP(カラム名)
- oracle
- STDDEV(カラム名)
汎用
- 代替表示
- NULLでない自身の最初の引数を返す。
- postgresql,oracle,mysql
- COALESCE(A,B,..)
- 指定したoffset行の評価
-
- postgresql,oracle,mysql(8.0以上)
- LAG(カラム名, [offset, default]) OVER(ORDER BY カラム名)
省略時はoffset=1, default=NULL
- 型変換
-
- postgresql,oracle,mysql
- CAST(対象の列名 as 変換する型)
- UNIX時間(数値)を時刻に変換
-
- postgresql
- TO_TIMESTAMP(カラム名/数値)
- oracle
- なし
- mysql
- FROM_UNIXTIME(カラム名/数値)
- 文字列切り取り
-
- postgresql,oracle,mysql
- SUBSTR(カラム名/対象. 開始位置, 終了位置)
計算
SQL上では0による除算はnullとなる。また集合関数はnull値を集計対象から除外する。なのでわざわざ条件指定して値を除外する必要はない。
- 常用対数化（底=10）
- 自然対数化 (底=e)
-
- postgresql,oracle,mysql
- ln(カラム名/値)
- 切り捨て
-
- postgresql,oracle
- TRUNC(カラム名/値)
- mysql
- TRUNCATE(カラム名/値, 桁数)
- 四捨五入
-
- postgresql,oracle,mysql
- ROUND(カラム名/値)
- 切り上げ
-
- postgresql,oracle,mysql
- CEIL(カラム名/値)
- 日付差分
-
- postgresql
- age(カラム名/値(timestamp), [カラム名/値(timestamp)])
日付差分(interval型)を返す。interval型の内容は何年何月何日の差分。extract(YEAR/MONTH/DAY FROM interval)で値を取り出す。
例) age('2020-03-12', timestamp '2020-02-11') → 1 month 1day ※きちんと2月の日数で計算している。 - oracle
- 日数の差分はdate型同士の計算で得られる。
月数差分はMONTHS_BETWEEN(カラム名/値(date), カラム名/値(date))
値は小数点付きで返る。 - mysql
- TIMESTAMPDIFF(YEAR/MONTH/DAY, カラム名/値(timestamp), カラム名/値(timestamp))
指定した単位の差分を返す。
集合演算
差集合
- postgresql
- EXCEPT
- oracle
- MINUS
- mysql
- なし
