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

7741 ワード
メモ SQL 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

[C++]STL-関数オブジェクトアダプタ
練習問題5.13 n文字列を入力し、アルファベットを小さい順に並べて出力します.