エイチームライフスタイル アドベントカレンダー2017の4日目は、株式会社エイチームライフスタイルのWebエンジニア@suzuki_shが担当します。
本稿では、まず「相加平均」「相乗平均」「調和平均」の3つの違いについて説明します。そして、PostgreSQLでこれらを算出してみます。
弊社では日々の数値集計などにAmazon Redshift(PostgreSQL互換)を使っていますので、ここで3種類の平均値を使えるようになる事を目指します。
3種類の平均値について
「平均値」にはいくつかの種類があることを知っていますか？
以下、順番に説明していきます。ご存知の方は読み飛ばしてください。
相加平均とは？
下記の例を使います。
| Q | 売上(千円) |
|---|---|
| 1Q | 100.0 |
| 2Q | 130.0 |
| 3Q | 130.0 |
| 4Q | 219.7 |
このとき「四半期ごとの売り上げの平均値」はいくらでしょうか？
簡単です。
\frac{100+130+130+219.7}{4} = 144.925千円
(100+130+130+219.7)/4 = 144.925 になります。
これが相加平均(算術平均)です。
「平均」と言ったときには、この「相加平均」を指すことが多いです。
一般に、n個の要素の相加平均は下記の計算式で求められます。
\frac{a_1+a_2+...+a_n}{n} = \frac{1}{n}\sum^n_{i=1}a_i
相乗平均とは？
先ほどの例にて、前四半期比(Q/Q)の成長率を計算してみましょう。
| Q | 売上(千円) | Q/Q |
|---|---|---|
| 1Q | 100.0 | - |
| 2Q | 130.0 | 1.30 |
| 3Q | 130.0 | 1.00 |
| 4Q | 219.7 | 1.69 |
売り上げのQ/Qが130%、100%(横ばい)、169%という感じで、第3四半期に伸び悩んだものの1年を通して積み重なっていきます。
最終的には219.7千円まで成長しています。
この1年間の「前四半期比成長率」は平均何%でしょうか？
(1.3+1+1.69)/3=1.33より、平均133% となります。
さて売上の前四半期比が平均133%ならば、売上100千円から毎四半期133%の成長をし続ければ、第4四半期は219.7千円になるのでしょうか？
実際に計算してみましょう。
| Q | 売上(千円) | Q/Q | 平均Q/Q | 平均値を掛けた売上(千円) |
|---|---|---|---|---|
| 1Q | 100.0 | - | - | 100.0 |
| 2Q | 130.0 | 1.30 | 1.33 | 133.0 |
| 3Q | 130.0 | 1.00 | 1.33 | 176.89 |
| 4Q | 219.7 | 1.69 | 1.33 | 235.2637 |
第4四半期の売上が、想定していた219.7千円よりも高くなってしまいました。
平均133%という数字を使ったのに、なぜでしょうか。
先に算出した平均133%というのは 相加平均 です。
一方で、ここで使うべきは相加平均ではなく相乗平均(幾何平均)になります。
相乗平均は、下記の計算で求められます。
\sqrt[3]{1.3×1×1.69} = 1.3
(1.3*1*1.69)^(1/3)=1.3なので、相乗平均130% となります。
130％という数字を使って、先ほどと同じ計算をしてみましょう。
| Q | 売上(千円) | Q/Q | Q/Qの相乗平均値 | 平均値を掛けた売上(千円) |
|---|---|---|---|---|
| 1Q | 100.0 | - | - | 100.0 |
| 2Q | 130.0 | 1.30 | 1.30 | 130.0 |
| 3Q | 130.0 | 1.00 | 1.30 | 169.0 |
| 4Q | 219.7 | 1.69 | 1.30 | 219.7 |
「相乗平均130%」を使うと、第4四半期の売り上げが291.7千円と、実際の値に一致しました。
相乗平均を使って「今年は平均130％の成長をしている」と言ったほうが、代表値として適切ですね。
さて、相加平均と相乗平均は、どのように使い分ければよいのでしょうか。
ここで最初の例をよく見てみます。
| Q | 売上(千円) | Q/Q |
|---|---|---|
| 1Q | 100.0 | - |
| 2Q | 130.0 | 1.30 |
| 3Q | 130.0 | 1.00 |
| 4Q | 219.7 | 1.69 |
この時、売上の前四半期比と第1および第4四半期の売上には、下記の関係が成り立ちます。
100千円×1.3×1×1.69 = 219.7千円
足し算ではなく、掛け算を合わせることで第4四半期の売上になります。
このように、掛け算をあわせる関係に対しては相乗平均を用います。
前四半期比や前期比などの平均を求める時が該当します。
一般に、n個の要素の相乗平均は下記の計算式で求められます。
\sqrt[n]{a_1×a_2×...×a_n} = (\prod^n_{i=1}a_i)^\frac{1}{n}
調和平均とは？
3つめの平均値の説明に移る前に、ちょっと例を変えてみます。
四半期売上の予算が「120千円」だったとします。
この予算を達成したら、その四半期の間はもう仕事をしなくてOKだったという事にしましょう。はやく予算を達成したいですね。
このとき、120千円を売り上げるためにかかる日数が、下記だったとします。
| Q | 1日あたり売上 | 予算達成日数 |
|---|---|---|
| 1Q | 2千円 | 60日間 |
| 2Q | 3千円 | 40日間 |
| 3Q | 4千円 | 30日間 |
1Qは1日2千円、対して3Qは1日4千円を売り上げていたとします。予算達成までにかかる期間がどんどん短くなっていますね。
結果、60＋40＋30＝130日間のあいだ仕事をして、120×3 = 360千円を売り上げました。
さてこのとき「1日あたり売り上げ」の平均値はいくらでしょうか？
相加平均では (2+3+4)/3=3より平均3千円 となります。
しかし、これも相加平均ではおかしくなってしまいます。
1日平均3千円を売り上げていたとなると、3千円×130日間 = 390千円となり、上記期間の売上の合計値(360千円)を上回ってしまいます。
ここでは相加平均ではなく調和平均を使います。
調和平均の算出は下記の計算式になります。
\frac{3}{\frac{1}{2} + \frac{1}{3}+\frac{1}{4}} = \frac{36}{13} = 2.769231....
3/(1/2 + 1/3 + 1/4)=36/13より、調和平均$\frac{36}{13}$千円(=2.769231...千円)となります。
実際に36/13 × 130日間 = 360千円 となり、1日の売上の平均値は$\frac{36}{13}$千円という方が代表値として適切です。
このように調和平均を使うのべきなのは、どういうタイミングでしょうか。
ここでも最初の例をよく見てみます。
| Q | 1日あたり売上 | 予算達成日数 |
|---|---|---|
| 2Q | 2千円 | 60日間 |
| 2Q | 3千円 | 40日間 |
| 3Q | 4千円 | 30日間 |
このとき、1日あたり売上と予算、最終的に仕事をした日数の間には下記の関係が成立します。
\frac{1}{2}×120千円 ＋ \frac{1}{3}×120千円 ＋ \frac{1}{4}×120千円 = 130日間
このように、逆数を足し合わせる関係の時は調和平均を使います。
たとえば一定距離を移動するときの速度の平均などは、調和平均を使います。
一般に、n個の要素の調和平均は下記の計算式で求められます。
\frac{n}{\frac{1}{a_1}+\frac{1}{a_2}+...⁺\frac{1}{a_n}} = \frac{n}{\sum^n_{i=1}\frac{1}{a_i}}
まとめ:3種類の平均の使い分け
- 売り上げのように、足し算で合わせるものは相加平均 (普通の平均)
- 成長率のように、掛け算で合わせるものは相乗平均
- 1日あたり売り上げのように、逆数を足し合わせるものは調和平均
また、これら3種類の平均値については下記の関係性が成り立つことが数学的に証明されています。
調和平均\leq相乗平均\leq相加平均
3種類の平均を使い分けず、全て相加平均を使ってしまうと、売上のデータからQ/Q133％の成長、1日あたり売上3千円といった数値を代表値として算出してしまいます。
これらは実際よりも大きい数値情報になってしまっています。そして、この数値を使って来期の予算を立てる等の誤った意思決定をしてしまうかもしれません。
一方で、相加平均がきわめて直感的で使いやすいのに対し、相乗平均や調和平均は非直感的なものです。
そのため特になにも気にしていないと、使いやすい相加平均ばかり使ってしまいがちです。
平均を使うときは、それらの数値が足し合わせる関係なのか、掛け合わせるのか、逆数を足す関係なのか、確認したうえで正しい平均値を使いましょう。そうすることで、すこし幸せになれるかもしれません。
PostgreSQLで3種類の平均値を扱う
ここからはPostgreSQLでそれぞれの平均値を算出してみます。
SQL Fiddleを使って試してみましょう。
スキーマ定義
-- 相加平均・相乗平均用
CREATE TABLE sales(
id SERIAL PRIMARY KEY,
sale NUMERIC,
rate NUMERIC
);
INSERT INTO sales
(id, sale, rate) VALUES
(1, 100, 0),
(2, 130, 1.3),
(3, 130, 1.0),
(4, 219.7, 1.69);
-- 調和平均用
CREATE TABLE daily_sales(
id SERIAL PRIMARY KEY,
daily NUMERIC
);
INSERT INTO daily_sales (id, daily) VALUES
(1, 2),
(2, 3),
(3, 4);
相加平均
これはAVG()関数を使えば、一発で算出できます。
SELECT AVG(sale) AS arithmetic_mean FROM sales;
| arithmetic_mean |
|---|
| 144.925 |
相乗平均
これは複雑です。
まず結論から言うと、下記のSQLで算出できます。
SELECT POWER(EXP(SUM(LN(rate))), 1.0/COUNT(*)) AS geometric_mean FROM sales WHERE rate > 0;
| geometric_mean |
|---|
| 1.3 |
ここでは、項目の総積を求めるを参考に、すべての積をEXP(SUM(LN(rate)))で算出しています。
しかし、ユーザー定義関数を用いて下記のように書いた方が素直かもしれません。
-- 積
CREATE FUNCTION multi(NUMERIC,NUMERIC) RETURNS NUMERIC
AS 'SELECT ($1*$2)'
LANGUAGE SQL;
-- 総積
CREATE AGGREGATE product (NUMERIC)
( sfunc = multi,
stype = NUMERIC,
initcond = 1);
-- Query
SELECT POWER(product(rate), 1.0/COUNT(*)) AS geometric_mean FROM sales WHERE rate > 0;
調和平均
SUM()とCOUNT()を使えば算出できます。
SELECT COUNT(*)/SUM(1.0/daily) AS harmonic_mean FROM daily_sales;
| harmonic_mean |
|---|
| 2.769230769230769 |
これで、3種類の平均値がPostgreSQLでも算出できました。
参考
- 栗原伸一・丸山敦史 著『統計学図鑑(オーム社)』
- PostgreSQL ユーザ定義の集約の追加説明 - Qiita
- 単純な移動平均による季節調整法の練習 with PostgreSQL - Qiita
エイチームライフスタイル アドベントカレンダー2017、明日は@gonjyu121 に書いてもらう予定です。お楽しみに！
株式会社エイチームライフスタイルでは、一緒に働けるチャレンジ精神旺盛な仲間を募集しています。興味を持たれた方はぜひエイチームグループ採用サイトを御覧ください。
http://www.a-tm.co.jp/recruit/