SQLで移動平均　〜指定行数未満はNULL〜

　
SQLで移動平均を求めます。移動平均はウィンドウ関数を使えば１行で書くことができます。ですが、指定した行数未満しかデータがない部分に関しても平均を出してしまうと、正しい移動平均の比較ができません。そこで、指定した行数未満の箇所に関してはNULLとなるようにします。

テーブルの作成

時系列データのテーブルAccountsを作っています。

CREATE TABLE Accounts
(prc_date DATE NOT NULL , 
 prc_amt  INTEGER NOT NULL , 
 PRIMARY KEY (prc_date)) ;

INSERT INTO Accounts VALUES ('2018-10-26',  12000 );
INSERT INTO Accounts VALUES ('2018-10-28',   2500 );
INSERT INTO Accounts VALUES ('2018-10-31', -15000 );
INSERT INTO Accounts VALUES ('2018-11-03',  34000 );
INSERT INTO Accounts VALUES ('2018-11-04',  -5000 );
INSERT INTO Accounts VALUES ('2018-11-06',   7200 );
INSERT INTO Accounts VALUES ('2018-11-11',  11000 );

移動平均を求める

今回は２行前までの移動平均を求めています。移動平均はウィンドウ関数を使えば簡単に求めることができ、中の予約語を少し変えるだけで◯日前までの移動平均の算出も行えます。今回のテーマに関する処理においては、CASE文により場合分けすることで対応しています。指定した行数のときは移動平均、それ以外はNULLとなっています。

SELECT
	prc_date
	, CASE WHEN 
		COUNT(prc_amt) OVER(ORDER BY prc_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) = 3
		THEN AVG(prc_amt) OVER(ORDER BY prc_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) 
        ELSE NULL END AS avg_amt
FROM Accounts;

こちらの本の中では別解で求められていました。

達人に学ぶSQL徹底指南書第2版初級者で終わりたくないあなたへ (CodeZine BOOKS)

作者:ミック
翔泳社

Amazon

SELECT prc_date, prc_amt,
       CASE WHEN cnt < 3 THEN NULL
            ELSE mvg_avg END AS mvg_avg
  FROM (SELECT prc_date, prc_amt,
               AVG(prc_amt)
                 OVER(ORDER BY prc_date
                       ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) mvg_avg,
               COUNT(*)
                 OVER (ORDER BY prc_date
                        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS cnt
          FROM Accounts) TMP;