移動平均とは何か
移動平均が何か、というのは大体わからない。
株式の価格とか、時系列データを分析するときに使う項目である、というのが何となくわかったくらいだ。
ただ、移動平均を使うと何が嬉しいかはわかる。
ダイエットしてて、毎日体重を測る人がいるとする。たまたま、飲み会でハメを外して〆の大盛りラーメンを食べちゃった。翌日の体重が怖い。
ただ、毎日毎日大盛りラーメンを食べてるわけではない。長期的に見れば、体重は順調に減っている。
この、長期的に見た傾向を測るのが、移動平均だ。
トレンドラインともいうらしい。たまたまバズッた記事があるとか、そういう特殊なケースを除いた傾向が知りたいときに、移動平均という概念を使うらしい。
実際の求め方
実際のコードは、『達人に学ぶSQL徹底指南書 第2版 初級者で終わりたくないあなたへ』を参考にさせていただいた。
対象テーブル
おそらく、雑貨屋の商品テーブルだろう
| id | name | category | selling_price | purchase_price | created_at |
|---|---|---|---|---|---|
| 0001 | Tシャツ | 衣服 | 1000 | 500 | 2009-09-20 |
| 0002 | 穴あけパンチ | 事務用品 | 500 | 320 | 2009-09-11 |
| 0003 | カッターシャツ | 衣服 | 4000 | 2800 | |
| 0004 | 包丁 | キッチン用品 | 3000 | 2800 | 2009-09-20 |
| 0005 | 圧力鍋 | キッチン用品 | 6800 | 5000 | 2009-01-15 |
| 0006 | フォーク | キッチン用品 | 500 | 2009-09-20 | |
| 0007 | おろしがね | キッチン用品 | 880 | 790 | 2008-04-28 |
| 0008 | ボールペン | 事務用品 | 100 | 2009-11-11 |
抽出するSQL
-- Calc moving average of selling price in time line about item id asc
-- between current row and two precending one.
select
id
, name
, selling_price
, avg (selling_price) over (
order by id
rows between 2 preceding and current row) as moving_avg
from
sales_items;
結果
moving_avgというのが移動平均のことだ。
| id | name | selling_price | moving_avg |
|---|---|---|---|
| 0001 | Tシャツ | 1000 | 1000.0000 |
| 0002 | 穴あけパンチ | 500 | 750.0000 |
| 0003 | カッターシャツ | 4000 | 1833.3333 |
| 0004 | 包丁 | 3000 | 2500.0000 |
| 0005 | 圧力鍋 | 6800 | 4600.0000 |
| 0006 | フォーク | 500 | 3433.3333 |
| 0007 | おろしがね | 880 | 2726.6667 |
| 0008 | ボールペン | 100 | 493.3333 |
解説
以下の箇所が移動平均のコアな実装だ。
avg (selling_price) over (
order by id
rows between 2 preceding and current row)
avg (selling_price)とあるので、販売単価についての平均だとわかる。
order by idとあるので、商品IDが時系列データだとわかる。
rows between 2 precending and current rowとあるので、2つ前のレコードから現在のレコードの間の平均を求める、というのが分かる。precendingは「先行した」という意味で、2 precendingで、2つ先行したということ、すなわち、商品IDの昇順で2つ前のデータということになる。
具体例を見よう。
商品IDが0001のデータの移動平均は、1000である。販売単価と変わらない。なぜなら、0001より前のレコードは存在しないからだ。つまり、条件を満たすレコードが全て存在しなくても、エラーにはならないように実装されていることがわかる。
商品IDが0003のデータの移動平均は、1833である。2つ前のレコード0001から0003までの間の販売単価である1000 + 500 + 4000の平均値を求めている。これは理屈に適っている。浮動小数の桁数は調節可能だろう。当たり前の話だが。
商品IDが0008のデータの移動平均は、493である。0006から0008までの間の平均を求めているからだ。0006より前のレコードが存在しているにも関わらず、指定範囲を超えて計算が行われない、ということがわかる。
まとめ
今回のデータは時系列データとしてはそれほど意味のないものだ。しかし、移動平均が何なのかをざっくりと掴むにはもってこいの素材である。
さあ、楽しくなってきた。ありがとうミックさん。