MySQLのいろいろな平均値の出し方

MySQL

MySQLでは標準でAVG()という関数が用意されています。

次のようなテーブルを用意して確認してみます。

CREATE TABLE mean_test (
num int(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into mean_test values (1),(2),(3),(4),(5);

select * from mean_test;
select avg(num) from mean_test;

(1+2+3+4+5)/5 = 3なので計算あってますね。このAVG関数は数値でしか使えません。

このような出し方を平均だと教わることが多いと思いますが、本来は算術平均と言います。

また、平均はツールによってAverageと言ったりMeanと言ったりしますが、どちらも平均値を表すときに使われます。(ExcelではAVERAGE()、Rではmean()と書く)

また、Meanの場合は算術平均以外の形式をとることもあるので、こちらの方がより広義な意味での平均という意味になるのかと思います。

先にも述べたように、平均というものには実際には他にもいくつか種類があり、それはMySQLサポートされていないようなので、自身で計算する必要があります。

加重平均

例えば、商品の原価が仕入れたタイミングによって異なるときに単価の平均値を出そうとした場合。

わかりやすかったので、以下のサイトから数値を引用しています。

加重平均をマスターしよう | プロフェッショナル簿記
加重平均って何?簿記1級では、さまざまな論点で加重平均が使われています。今回は、加重平均をしっかりマスターしましょう。加重平均とは、要するに単なる平均のことです。なぜ、そんな加重平均なんていう仰々しい言い方をするのでしょうか。次の例を考えてみてください。ある小学校のクラスには男子が3人、女子が2人の計5人います。先日テ...

 (月初100円/kg+当月120円/kg)÷2=110円/kg

と計算する人はまずいないでしょう。ほとんどの方は、次のように計算するはずです。

 (月初100円/kg×200kg+当月120円/kg×300kg)÷(200kg+300kg)=112円/kg

https://pro-boki.com/weighted_average

これをSQLでやってみましょう

CREATE TABLE mean_test_weighted (
単価 int(10) DEFAULT NULL,
購入数量 int(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into mean_test_weighted values (100,100),(120,200);
select * from mean_test_weighted;
select sum(単価*購入数量)/sum(購入数量) from mean_test_weighted

このようになります。sum()の中で計算をする必要がありますね。

加重平均はこのように前提となる数(重み、度数)が異なる場合に使われます。

例でいうと100円/kgというのは1kgあたりの平均値と考えることができます。120円/kgも同様です。

前者が全部で200kgあるときの金額は20,000円、後者は300kgで36,000円なので計56,000円。

総量が500kgなので56,000円/500kg = 112円/kgとなります。

幾何平均

売上など、毎月毎年で推移を見ると思いますが、前年に比べて何倍になったとか、何%増減したなども一緒に集計することが多いはず。

そのとき、例えば1年間で平均して何%増減したのかなどを見るときに使います。

ExcelであればGEOMEAN関数があるので簡単に出せますが、MySQLには実装されていない様子。

算術平均・幾何平均・加重平均の計算 with Excel
Excelによる基本統計量の計算(1):算術平均・幾何平均・加重平均の求め方です。組み込みの関数を利用しない場合の手続きもそれぞれ併記しています。

これをSQLで出すことにメリットがあるのか?と問われれば実際のところ特にありません。ツールは目的さえ達成できればそれで良いのです。

さて次のようなテーブルを用意します。

CREATE TABLE geomean_test (
month date DEFAULT NULL,
num int(10) DEFAULT NULL,
rate double DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into geomean_test values ("2019-01-01",157000,0),("2019-02-01",442000,0),("2019-03-01",1701000,0),("2019-04-01",1700000,0),("2019-05-01",2355000,0),("2019-06-01",4034000,0),("2019-07-01",5416000,0),("2019-08-01",5895000,0),("2019-09-01",7759000,0),("2019-10-01",12913000,0),("2019-11-01",13734000,0),("2019-12-01",16852000,0);

#前月比
update geomean_test a, geomean_test b set b.rate = b.num /a.num
where period_diff(date_format(b.month,"%Y%m") , date_format(a.month,"%Y%m")) = 1 ;

前月比の計算まで行います。幾何平均の出し方は次のようになります。

select pow(pow(10,sum(log10(rate))),(1/11)) from geomean_test

計算したい数値を全部掛け算して、その数のn乗根をとる、それが幾何平均の計算方法になります。

rate列を全て掛け算して、11乗根(rateが0でないところの行数)をとれば出すことができます。

一つずつ説明をしていきます。まずはrate列を掛け算していきますが、sum()のように便利な関数はありません。行数が少なければいいですが、多いと面倒ですよね。

なので次のように計算していきます。

列の掛け算

一番内側にあるlog10()とは10を底としたときの対数、たとえば2.81528…であれば log10(2.81528)という関数が実行され、2.81528は10の何乗かを計算します。

次にsum()の部分です。指数法則というものがあり、同じ数を累乗したものの掛け算は指数を足せばいいという法則があります。

やりたいことは各数値の掛け算なので、10の○乗に変換して、それの指数をたし算していけば結果として同じことが出来るというわけですね。

先にlog10()で計算した対数(指数)は10の○乗という形で計算されているので、それをsum()でたし算します。

pow()はpow(X,Y)と入力することで、XのY乗を求めることが出来ます。ここではpow(10,sum()の結果)を計算することでrate列の掛け算の結果が、10の2.03075…乗となり、107.3376という数字になるはずです。

試しにスプレッドシートで計算してみても同様の結果となります。

n乗根の計算

幾何平均の計算方法はデータを全て掛け合わせた結果のn乗根を取ればいいのでしたが、累乗根も関数は定義されていません。

累乗根は1/n乗するのと同義なので、先ほどのpow関数を用いpow(計算結果,(1/11))を計算すると、出すことができます。107.3376 ^ (1/11) = 1.5297265が幾何平均の計算結果となります。(ツールによって若干の誤差はあります)

まとめ

算術平均、加重平均、幾何平均をMySQLで計算してみました。

全てが関数で実装されていなくても、ある程度準備されている関数や計算の組み合わせで解決できるものも多いので、いろいろ試してみるのも楽しいと思います。

途中にも書いていますが、計算することが目的なので、結果さえ正しくでてしまえはツールはなんでもいいのです。データも計算もすべてExcelであればその方が楽ですし。

ただ、「とりあえず関数使っとけば出せる」よりも理屈を分かった上で関数を使いこなす方が応用が聞くと思いますし、例えばデータはRDBにあって計算はExcel、のような時はツールを分けずに計算できると手間が減るので知っておいて損はないかと思います。

コメント

タイトルとURLをコピーしました