はじめに

Verticaでは、MEDIAN分析関数を使って、代表値の一つである中央値を算出することが出来ます。
中央値は、外れ値を含むデータの実態をつかむのに便利です。

※代表値とは、データセットの特徴を表す統計学上の値のことで、中央値の他にも、平均値、最大/最小値、最頻値があります。

平均値と中央値の違い

中央値は、平均値と混同されることがありますが、これら2つの値には以下の違いがあります。

構文

SELECT MEDIAN(列名) OVER()
※PARTITION BY句を省略すると、全データから一つの中央値を算出します。

パラメータ

※OVER句の詳しい指定方法については、参考情報の「分析関数(ウィンドウ関数)の概要-OVER句」の項目をご確認ください。

使用例

州(state)ごとの店舗(name)の売上(sales)について、MEDIAN分析関数を使って中央値を求める例をご案内します。

1.サンプルデータの準備

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毎の中央値を確認

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句に何も指定しないと、全データから中央値を算出します。

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.平均値と中央値を並べて確認

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.平均値と中央値を並べて確認(ウィンドウ句を使用)

※ウィンドウ句の書き方については、参考情報の「分析関数(ウィンドウ関数)の概要-ウィンドウに名前を付ける」の項目をご確認ください。

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です。
データの変化を捉えたい場合は、全ての値を結果に反映する平均値が最適です。

このように、目的に応じて、複数の代表値を確認するようにしましょう。

参考情報

分析関数(ウィンドウ関数)の概要
https://www.ashisuto.co.jp/cm/analytics-database/window_function.html

MEDIAN [Analytic]
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/Functions/Analytic/MEDIANAnalytic.htm

各列の平均、中央値、最大値/最小値、標準偏差等を一括で確認する(Vertica9.0新機能)
https://www.ashisuto.co.jp/cm/analytics-database/summarize_numcol.html

検証バージョンについて

この記事の内容はVertica 9.2で確認しています。

更新履歴

2019/06/27　本記事を公開