【小ネタ】Window関数で移動平均を算出する【SQL】

はじめに

SQLのWindow関数について、移動平均を算出する方法をメモします。

メモ

利用するデータ構造

以下のように、日付・地名ごとに記録されている気温データがあるものとします。
各地名・日付ごとに、気温の平均値や最大値などを集計したいものとします。

テーブル定義

CREATE TABLE temperature (record_date date, place_name text, celsius_degree float);

record_date	place_name	celsius_degree
2019-12-01	東京	10.1
2019-12-02	東京	11.2
2019-12-03	東京	8.2
2019-12-01	福岡	13.5
2019-12-02	福岡	12.4
2019-12-03	福岡	11.7
:	:	:
:	:	:

SQLのサンプル

1.当日を含む過去5日と過去15日の単純移動平均を計算する

WINDOW句で、2つのframeを定義する場合のサンプルとなります。
なおこのクエリが意図したとおりに動くためには、各日のレコードが常に1件ずつ存在する必要があります。
これはWINDOW句のROWS句にて、現在処理中の行（？）を含めた5件/15件を集約関数の処理対象とするように指定しているからです。
日付を指定する場合は、RANGE句を用いる必要があります。

SELECT
	record_date AS 日付
	, city_name
	, AVG(celsius_degree) OVER w_latest5days AS 移動平均5日間
	, AVG(celsius_degree) OVER w_latest15days AS 移動平均15日間
FROM temperature
WINDOW
	w_latest5days AS (
		PARTITION BY city_name
		ORDER BY record_date ASC
		ROWS BETWEEN 4 PRECEDING AND CURRENT ROW
	)
	, w_latest15days AS (
		PARTITION BY city_name
		ORDER BY record_date ASC
		ROWS BETWEEN 14 PRECEDING AND CURRENT ROW
	)
;

2.移動平均の対象日の日付を付与する

上記の抽出結果に対し、平均を算出したレコードの日付情報を付加します。

SELECT
	record_date AS 日付
	, city_name
	, AVG(celsius_degree) OVER w_latest5days AS 移動平均5日間
	, min(record_date) OVER w_latest5days AS 移動平均5日間初日
	, max(record_date) OVER w_latest5days AS 移動平均5日間末日
	, AVG(celsius_degree) OVER w_latest15days AS 移動平均15日間
	, min(record_date) OVER w_latest15days AS 移動平均15日間初日
	, max(record_date) OVER w_latest15days AS 移動平均15日間末日
FROM temperature
WINDOW
	w_latest5days AS (
		PARTITION BY city_name
		ORDER BY record_date ASC
		ROWS BETWEEN 4 PRECEDING AND CURRENT ROW
	)
	, w_latest15days AS (
		PARTITION BY city_name
		ORDER BY record_date ASC
		ROWS BETWEEN 14 PRECEDING AND CURRENT ROW
	)
;

参考資料

達人に学ぶSQL徹底指南書 第2版 初級者で終わりたくないあなたへ
https://www.amazon.co.jp/dp/4798157821