![[SQL] 移動平均を算出 〜Window関数を使って変動傾向を掴む〜](https://devio2023-media.developers.io/wp-content/uploads/2015/07/sql.png)
[SQL] 移動平均を算出 〜Window関数を使って変動傾向を掴む〜
はじめに
移動平均の目的は
長期間にわたる基本的な傾向変動を見るのに用いられる
ということだそうです。
季節変動や不規則変動を除去したり、加重移動平均という直近のデータに重みをもたせる方法があったりするようですが、このあたりはよくわかっていないので考えないことにします。
今回やってみるのは単純移動平均というものです。
株価の移動平均線みたいなのもこれでできそうですね！ こんなに単純ではないとは思いますが。。
今回もいつものサンプルの中からpaymentテーブルを使います。
手を動かしながらSQLの動作を確認したい方はこちらをどうぞ！
環境
PostgreSQL 9.5.4
今回のSQL
今回のSQLは当日を含む直近４営業日の売り上げの移動平均を算出するSQLになります。
元データ
payment_id | customer_id | staff_id | rental_id | amount | payment_date
------------+-------------+----------+-----------+--------+----------------------------
17503 | 341 | 2 | 1520 | 7.99 | 2007-02-15 22:25:46.996577
17504 | 341 | 1 | 1778 | 1.99 | 2007-02-16 17:23:14.996577
17505 | 341 | 1 | 1849 | 7.99 | 2007-02-16 22:41:45.996577
17506 | 341 | 2 | 2829 | 2.99 | 2007-02-19 19:39:56.996577
17507 | 341 | 2 | 3130 | 7.99 | 2007-02-20 17:31:48.996577
17508 | 341 | 1 | 3382 | 5.99 | 2007-02-21 12:33:49.996577
17509 | 342 | 2 | 2190 | 5.99 | 2007-02-17 23:58:17.996577
17510 | 342 | 1 | 2914 | 5.99 | 2007-02-20 02:11:44.996577
17511 | 342 | 1 | 3081 | 2.99 | 2007-02-20 13:57:39.996577
17512 | 343 | 2 | 1547 | 4.99 | 2007-02-16 00:10:50.996577
17513 | 343 | 1 | 1564 | 6.99 | 2007-02-16 01:15:33.996577
17514 | 343 | 2 | 1879 | 0.99 | 2007-02-17 01:26:00.996577
17515 | 343 | 2 | 1922 | 0.99 | 2007-02-17 04:32:51.996577
17516 | 343 | 2 | 2461 | 6.99 | 2007-02-18 18:26:38.996577
17517 | 343 | 1 | 2980 | 8.99 | 2007-02-20 07:03:29.996577
17518 | 343 | 1 | 3407 | 0.99 | 2007-02-21 14:42:28.996577
17519 | 344 | 1 | 1341 | 3.99 | 2007-02-15 10:54:44.996577
17520 | 344 | 2 | 1475 | 4.99 | 2007-02-15 19:36:27.996577
17521 | 344 | 1 | 1731 | 0.99 | 2007-02-16 14:00:38.996577
17522 | 345 | 2 | 1210 | 0.99 | 2007-02-15 01:26:17.996577
17523 | 345 | 1 | 1457 | 4.99 | 2007-02-15 18:34:15.996577
17524 | 345 | 2 | 1550 | 0.99 | 2007-02-16 00:27:01.996577
17525 | 345 | 2 | 2766 | 4.99 | 2007-02-19 16:13:41.996577
SQL
元データを以下のSQLで実行すると
select
date_trunc('day',payment_date) as daily
,sum(amount) as daily_amount
,avg(sum(amount)) over (
order by
date_trunc('day',payment_date) asc
rows between 3 preceding and current row) as moving_average
from
payment
group by
date_trunc('day',payment_date)
order by
daily desc;
以下のような結果が返ってきます。
実行結果
daily | daily_amount | moving_average
---------------------+--------------+-----------------------
2007-05-14 00:00:00 | 514.18 | 2894.6000000000000000
2007-04-30 00:00:00 | 5723.89 | 3434.4475000000000000
2007-04-29 00:00:00 | 2717.60 | 2090.2775000000000000
2007-04-28 00:00:00 | 2622.73 | 1893.4975000000000000
2007-04-27 00:00:00 | 2673.57 | 1722.8950000000000000
2007-04-26 00:00:00 | 347.21 | 1547.7975000000000000
2007-04-12 00:00:00 | 1930.48 | 1977.9600000000000000
2007-04-11 00:00:00 | 1940.32 | 2052.3000000000000000
2007-04-10 00:00:00 | 1973.18 | 2063.2900000000000000
2007-04-09 00:00:00 | 2067.86 | 2089.2800000000000000
2007-04-08 00:00:00 | 2227.84 | 1640.6550000000000000
2007-04-07 00:00:00 | 1984.28 | 1669.3025000000000000
2007-04-06 00:00:00 | 2077.14 | 1819.9300000000000000
2007-04-05 00:00:00 | 273.36 | 2017.7125000000000000
2007-03-23 00:00:00 | 2342.43 | 2616.8450000000000000
2007-03-22 00:00:00 | 2586.79 | 2685.6600000000000000
2007-03-21 00:00:00 | 2868.27 | 2714.4025000000000000
2007-03-20 00:00:00 | 2669.89 | 2607.8750000000000000
2007-03-19 00:00:00 | 2617.69 | 2015.2225000000000000
2007-03-18 00:00:00 | 2701.76 | 1998.3125000000000000
2007-03-17 00:00:00 | 2442.16 | 2024.9325000000000000
2007-03-16 00:00:00 | 299.28 | 1643.8600000000000000
2007-03-02 00:00:00 | 2550.05 | 1873.8125000000000000
2007-03-01 00:00:00 | 2808.24 | 1559.0250000000000000
2007-02-21 00:00:00 | 917.87 | 1175.9600000000000000
2007-02-20 00:00:00 | 1219.09 | 1243.5350000000000000
2007-02-19 00:00:00 | 1290.90 | 1227.3075000000000000
2007-02-18 00:00:00 | 1275.98 | 1201.8125000000000000
2007-02-17 00:00:00 | 1188.17 | 912.0000000000000000
2007-02-16 00:00:00 | 1154.18 | 819.9433333333333333
2007-02-15 00:00:00 | 1188.92 | 652.8250000000000000
2007-02-14 00:00:00 | 116.73 | 116.7300000000000000
当日と直前３日間の売り上げ合計をmoving_averageに表示しています。
下3行を見ると、それ以前のデータが存在しないので、存在しているデータだけを足して平均を出しているようですね。
avg()
,avg(sum(amount)) over (
order by
date_trunc('day',payment_date) asc
rows between 3 preceding and current row) as moving_average
avg()という関数は集約関数としてよく使われますが、多くの集約関数はWindow関数としても使えます。
PartitionBy句がないので全体に対して、5-6行目のOrderBy句に従い営業日順に並んだ集合に対してavg()関数が実行され平均値が算出されます。
7行目の影響範囲の指定では、現在行(current row)と直前3行(3 preceding)を含めた４行の平均値を算出してmoving_averageに表示しています。
Window関数については詳しくはこちら！
OrderByについて
select
date_trunc('day',payment_date) as daily
,sum(amount) as daily_amount
,avg(sum(amount)) over (
order by
date_trunc('day',payment_date) asc
rows between 3 preceding and current row) as moving_average
from
payment
group by
date_trunc('day',payment_date)
order by
daily desc;
今回SQLを書いていて並び順がうまくいかなくて何度かやり直しました。（具体的にはデータが存在しなくて１営業日の平均、２営業日の平均になってしまう所を直近ではなく、昔にしたかった）
OrderByが２箇所あるのですが、実行される順序は5~6行目のOrderByが先に実行され、10~11行目のOrderByが後で実行されます。
役割としては
- avg()内のOrderByはavg()関数をどの並び順で実行するか
- 外側のOrderByは最終的に表示される時にどの順番で並ばせたいか
Window関数を使う際は外側のクエリでOrderByを書くとどの並び順でWindow関数を実行したのかがわからなくなるので
- １度外側のSQLを削除(もしくは最初から書かない)してクエリを実行
- 望んだ動作であることを確認
- 外側のOrderByで表示したい順番に指定する
とすると迷わずに済みそうですね。
最後に
Window関数は本当に便利ですね。
