SQL：移動平均をwindow関数で求める

移動平均をwindow関数を利用してSQLで求めてみました。DBMSはPostgreSQLのバージョン12を利用しました。

移動平均とは

移動平均は連続するデータの不規則な変化を滑らかにするために用いられます。

不規則なデータの変化を滑らかにすることでそのデータの変化の傾向がつかみやすくなります。

連続するデータのある時点のデータの移動平均はそのデータを含む一定の区間の平均をとることで求められます。

移動平均には区間のとり方によって中央移動平均、後方移動平均、前方移動平均などの種類があり、それぞれの区間のとり方は以下です。

  • 中央移動平均 … ある時点のデータとその前後の区間のデータの平均値を使う
  • 後方移動平均 … ある時点のデータとその後の区間のデータの平均値を使う
  • 前方移動平均 … ある時点のデータとその前の区間のデータの平均値を使う

移動平均の求め方

後方移動平均、前方移動平均については単純にそれぞれの区間の平均値を計算することで求めることができます。

ただし中央移動平均の求め方については区間数を奇数にするか偶数にするかで計算方法が変わってきます。

区間数が奇数の場合（区間数=5の場合）
$$ma_i = \cfrac{x_{i-2} + x_{i-1} + x_i + x_{i+1} + x_{i + 2}}{5}$$

中央移動平均の区間数が偶数の場合は分子のデータ数が区間数より1つ多くなってしまうので両端の値についてはそれぞれ0.5をかけることで調整を行います。

区間数が偶数の場合（区間数=6の場合）
$$ma_i = \cfrac{0.5 \times x_{i-3} + x_{i-2} + x_{i-1} + x_i + x_{i+1} + x_{i+2} + 0.5 \times x_{i+3}}{6}$$
参考リンク

サンプルデータ

東京電力ホールディングスのサイトからダウンロードできる電力使用実績データの2021年の5月中旬～6月中旬までのデータの使用実績を日ごとに集計してサンプルデータとしました。

SQLで移動平均を求める前にエクセルで計算してみます。

C列の移動平均列は区間数7の中央移動平均です。C6のセルに=ROUND(AVERAGE(B3:B9),0)を入力してオートフィルでC31まで求めました。

このC列の移動平均が今回SQLで求める答えとなります。

5月中旬～6月中旬までの電力使用実績データ C列は中央移動平均(n=7)
日付実績(万kW)移動平均
2021/5/152607
2021/5/162471
2021/5/172966
2021/5/1830182847
2021/5/1929332851
2021/5/2029192854
2021/5/2130182846
2021/5/2226292845
2021/5/2324982845
2021/5/2429062847
2021/5/2530142828
2021/5/2629282828
2021/5/2729352834
2021/5/2828842830
2021/5/2926302823
2021/5/3025402828
2021/5/3128762840
2021/6/129662866
2021/6/229622869
2021/6/330232872
2021/6/430622893
2021/6/526542931
2021/6/625602963
2021/6/730222993
2021/6/832333010
2021/6/931843027
2021/6/1032333047
2021/6/1131823055
2021/6/1227783055
2021/6/132695
2021/6/143077
2021/6/153239

移動平均のグラフでは土日に減少する使用実績の変化が滑らかになり6月に入ってから使用実績が少しずつ増加する傾向があることが読み取りやすくなりました。

電力使用量実績

移動平均をSQLで求める

まず電力使用実績データを入れるテーブルを作成します。

テーブル作成のSQL 
CREATE TABLE 電力使用実績 (
  日付 DATE PRIMARY KEY,
  使用実績 NUMERIC NOT NULL,
  移動平均 NUMERIC
)

作成した電力使用実績テーブルに先ほどのエクセルのデータを投入します。

テストデータ投入用のSQL 
INSERT 
INTO 電力使用実績(日付, 使用実績, 移動平均) 
VALUES
('2021-05-15', 2607, NULL), 
('2021-05-16', 2471, NULL), 
('2021-05-17', 2966, NULL), 
('2021-05-18', 3018, 2847), 
('2021-05-19', 2933, 2851), 
('2021-05-20', 2919, 2854), 
('2021-05-21', 3018, 2846), 
('2021-05-22', 2629, 2845), 
('2021-05-23', 2498, 2845), 
('2021-05-24', 2906, 2847), 
('2021-05-25', 3014, 2828), 
('2021-05-26', 2928, 2828), 
('2021-05-27', 2935, 2834), 
('2021-05-28', 2884, 2830), 
('2021-05-29', 2630, 2823), 
('2021-05-30', 2540, 2828), 
('2021-05-31', 2876, 2840), 
('2021-06-01', 2966, 2866), 
('2021-06-02', 2962, 2869), 
('2021-06-03', 3023, 2872), 
('2021-06-04', 3062, 2893), 
('2021-06-05', 2654, 2931), 
('2021-06-06', 2560, 2963), 
('2021-06-07', 3022, 2993), 
('2021-06-08', 3233, 3010), 
('2021-06-09', 3184, 3027), 
('2021-06-10', 3233, 3047), 
('2021-06-11', 3182, 3055), 
('2021-06-12', 2778, 3055), 
('2021-06-13', 2695, NULL), 
('2021-06-14', 3077, NULL), 
('2021-06-15', 3239, NULL);

それではSQLで先ほどのエクセルの数式と同じく区間数7の中央移動平均を求めます。

区間数7の中央移動平均を求めるSQL 
SELECT
  日付, 
  使用実績, 
  CASE 
    WHEN (count(*) OVER 移動平均区間) = 7 
      THEN round(avg(使用実績) OVER 移動平均区間) 
    ELSE NULL 
    END AS 移動平均
FROM
  電力使用実績 
WINDOW
  移動平均区間 AS ( 
    ORDER BY
      日付 ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING
  ) 
ORDER BY
  日付

区間数7の中央移動平均を求めるので対象のデータから前後に各3個のデータをとる区間をWINDOW句で定義しています。

WINDOW句で定義した区間に対してavg関数をwindow関数として呼び出して平均を求めます。

対象の区間の区間数が7にならない場合はCASE式でNULLを返すようにしました。

データを区間ごとに集計したい場合にwindow関数が便利です。

参考リンク