[SQL] 移動平均を算出 〜Window関数を使って変動傾向を掴む〜

2016.08.31

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

はじめに

移動平均の目的は

長期間にわたる基本的な傾向変動を見るのに用いられる

ということだそうです。

季節変動や不規則変動を除去したり、加重移動平均という直近のデータに重みをもたせる方法があったりするようですが、このあたりはよくわかっていないので考えないことにします。
今回やってみるのは単純移動平均というものです。

株価の移動平均線みたいなのもこれでできそうですね! こんなに単純ではないとは思いますが。。

今回もいつものサンプルの中からpaymentテーブルを使います。
手を動かしながらSQLの動作を確認したい方はこちらをどうぞ!

環境

PostgreSQL 9.5.4

今回のSQL

今回のSQLは当日を含む直近4営業日の売り上げの移動平均を算出する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

当日と直前3日間の売り上げ合計を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)を含めた4行の平均値を算出して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を書いていて並び順がうまくいかなくて何度かやり直しました。(具体的にはデータが存在しなくて1営業日の平均、2営業日の平均になってしまう所を直近ではなく、昔にしたかった)

OrderByが2箇所あるのですが、実行される順序は5~6行目のOrderByが先に実行され、10~11行目のOrderByが後で実行されます。
役割としては
- avg()内のOrderByavg()関数をどの並び順で実行するか
- 外側のOrderByは最終的に表示される時にどの順番で並ばせたいか

Window関数を使う際は外側のクエリでOrderByを書くとどの並び順でWindow関数を実行したのかがわからなくなるので

  1. 1度外側のSQLを削除(もしくは最初から書かない)してクエリを実行
  2. 望んだ動作であることを確認
  3. 外側のOrderByで表示したい順番に指定する

とすると迷わずに済みそうですね。

最後に

Window関数は本当に便利ですね。