はじめに
Verticaでは、MEDIAN分析関数を使って、代表値の一つである中央値を算出することが出来ます。中央値は、外れ値を含むデータの実態をつかむのに便利です。
※代表値とは、データセットの特徴を表す統計学上の値のことで、中央値の他にも、平均値、最大/最小値、最頻値があります。
平均値と中央値の違い
中央値は、平均値と混同されることがありますが、これら2つの値には以下の違いがあります。| 概要 | メリット | デメリット | |
| 平均値 | 対象のデータを全て足して、対象のデータ数で割った数値。 | 全ての値を結果に反映できる | 外れ値の影響を受けやすい |
| 中央値 | データを小さい順に並べたときに、中央に位置する値のこと。 ※データの数が偶数で、1つの中心が決まらない場合は、中心に位置する値を足して2で割った数が中央値となります。 | 外れ値の影響を受けにくい | 全ての値が結果に反映されない データの変化を追うには不向き |
構文
|
1 2 |
SELECT MEDIAN(列名) OVER() ※PARTITION BY句を省略すると、全データから一つの中央値を算出します。 |
パラメータ
| 列名 | 数値型のデータ、あるいは暗黙的に数値データ型に変換できる非数値データ型の列を指定する。 |
| OVER句 | 処理範囲とする列を指定する。 なにも指定しなければ、データ全体を単一のパーティションとして処理を行う。 |
※OVER句の詳しい指定方法については、参考情報の「分析関数(ウィンドウ関数)の概要-OVER句」の項目をご確認ください。
使用例
州(state)ごとの店舗(name)の売上(sales)について、MEDIAN分析関数を使って中央値を求める例をご案内します。1.サンプルデータの準備
|
1 2 3 4 5 6 7 8 9 10 |
CREATE TABLE allsales(state VARCHAR(20), name VARCHAR(20), sales INT); INSERT INTO allsales VALUES('MA', 'A', 60); INSERT INTO allsales VALUES('NY', 'B', 20); INSERT INTO allsales VALUES('NY', 'C', 15); INSERT INTO allsales VALUES('MA', 'D', 20); INSERT INTO allsales VALUES('MA', 'E', 50); INSERT INTO allsales VALUES('NY', 'F', 40); INSERT INTO allsales VALUES('MA', 'G', 10); INSERT INTO allsales VALUES('MA', 'H', 500); COMMIT; |
2.state毎の中央値を確認
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
dbadmin=> SELECT *, MEDIAN(sales) OVER(PARTITION BY state) AS med_sal dbadmin=> FROM allsales; state | name | sales | med_sal -------+------+-------+--------- MA | G | 10 | 50 MA | D | 20 | 50 MA | E | 50 | 50 MA | A | 60 | 50 MA | H | 500 | 50 NY | C | 15 | 20 NY | B | 20 | 20 NY | F | 40 | 20 (8 rows) |
3.データ全体の中央値を確認
OVER句に何も指定しないと、全データから中央値を算出します。|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
dbadmin=> SELECT *, MEDIAN(sales) OVER() AS med_sal dbadmin=> FROM allsales; state | name | sales | med_sal -------+------+-------+--------- MA | G | 10 | 30 NY | C | 15 | 30 MA | D | 20 | 30 NY | B | 20 | 30 NY | F | 40 | 30 MA | E | 50 | 30 MA | A | 60 | 30 MA | H | 500 | 30 (8 rows) |
4.平均値と中央値を並べて確認
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
dbadmin=> SELECT *, AVG(sales) OVER(PARTITION BY state) AS avg_sal, MEDIAN(sales) OVER(PARTITION BY state) AS med_sal dbadmin=> FROM allsales; state | name | sales | avg_sal | med_sal -------+------+-------+---------+--------- MA | G | 10 | 128 | 50 MA | D | 20 | 128 | 50 MA | E | 50 | 128 | 50 MA | A | 60 | 128 | 50 MA | H | 500 | 128 | 50 NY | C | 15 | 25 | 20 NY | B | 20 | 25 | 20 NY | F | 40 | 25 | 20 (8 rows) |
MA stateの平均値(avg_sal)が、極端に大きな値(500)の影響を受けて、高い数値になっていることが分かります。
中央値(med_sal)は、外れ値の存在に関係なく、データセットの真ん中の値を表示しています。
5.平均値と中央値を並べて確認(ウィンドウ句を使用)
※ウィンドウ句の書き方については、参考情報の「分析関数(ウィンドウ関数)の概要-ウィンドウに名前を付ける」の項目をご確認ください。|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
dbadmin=> SELECT *, AVG(sales) OVER(win_state) AS avg_sal, MEDIAN(sales) OVER(win_state) AS med_sal dbadmin-> FROM allsales dbadmin-> WINDOW win_state AS (PARTITION BY state); state | name | sales | avg_sal | med_sal -------+------+-------+---------+--------- MA | G | 10 | 128 | 50 MA | D | 20 | 128 | 50 MA | E | 50 | 128 | 50 MA | A | 60 | 128 | 50 MA | H | 500 | 128 | 50 NY | C | 15 | 25 | 20 NY | B | 20 | 25 | 20 NY | F | 40 | 25 | 20 (8 rows) |
おわりに
中央値は、外れ値(極端な値)の影響を受けずに、データセットの中心となるデータを算出できる便利なものですが、データの変化を追うことには不向きです。なぜなら中央値は、データの中心が一つの値の範囲内にある以上、結果が変化しないためです。
例えば、salesに10000というデータが入っても、データの中心が50であれば結果は50です。
データの変化を捉えたい場合は、全ての値を結果に反映する平均値が最適です。
このように、目的に応じて、複数の代表値を確認するようにしましょう。
参考情報
分析関数(ウィンドウ関数)の概要http://vertica-tech.ashisuto.co.jp/window_function/
MEDIAN [Analytic]
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/Functions/Analytic/MEDIANAnalytic.htm
各列の平均、中央値、最大値/最小値、標準偏差等を一括で確認する(Vertica9.0新機能)
http://vertica-tech.ashisuto.co.jp/summarize_numcol/
