SQLで累積や移動平均を取る方法【Azure SQL Database・SQL Server】

Contents

SQLでデータサイエンス？累積や移動平均
OVER関数を利用する

SQLでデータサイエンス？累積や移動平均

Azure SQL DatabaseのSQLで累積・移動平均を取得する方法を紹介します。

OVER関数を利用する

SELECT 
    date,
    datetime,
    CountingIn as Count,
    -- 累積
    SUM(Count) OVER (
        PARTITION BY date
        ORDER BY datetime
        ROWS UNBOUNDED PRECEDING
    ) AS CountAccumulation,
    -- 移動平均
    AVG(Count) OVER (
        PARTITION BY date   
        ORDER BY datetime
        ROWS 2 PRECEDING
   ) AS CountMovingAvg  
FROM TABLE1

まず結論として、SQLを載せておきます。

ただしこれを見ても一見してよくわからないと思いますので一つ一つ解説していきます。

日付ごとに分割しながら累積と移動平均を取得できる

今回は以下のようなデータを作成しました。

date	datetime	count
2020-12-01	2020-12-01 12:00	1
2020-12-01	2020-12-01 12:05	5
2020-12-02	2020-12-02 13:00	4

大体これが数日分、毎分のデータが存在すると思って下さい。

今回は累積と移動平均をデータ全体全てに渡ってするのではなく、他のデータに依存して分けて累積・移動平均を取る方法も紹介したいと思っています。

累積を計算するSQLとは

    SUM(Count) OVER (
        PARTITION BY date
        ORDER BY datetime
        ROWS UNBOUNDED PRECEDING
    ) AS CountAccumulation

まず累積していきたい対象を1行目のSUMの中に入れます。そしてOVER関数を用意します。
次にPARTITION BYの後に分割するための列を指定します。今回は時間に対して累積し、日毎に分割したいと思っているので、date（日）を指定します。
累積する方向を指定します。3行目のORDER BYの後に時間(datetime)を記載します。こちらはデフォルトでASC(昇順)、2行目の最後にDESC(降順)を指定することもできます。
4行目PRECEDINGで手前方向にUNBOUNDEDで無限大に範囲を指定しています。

3つ分の移動平均を計算するSQLとは

    AVG(Count) OVER (
        PARTITION BY date   
        ORDER BY datetime
        ROWS 2 PRECEDING
   ) AS CountMovingAvg

移動平均の対象を1行目のAVGの中に入れます。前節を読んでくださった方は察せられるかもしれませんが、ここをSUMにすると移動合計にできます。
次にPARTITION BYの後に分割するための列を指定します。今回は時間に対して累積し、日毎に分割したいと思っているので、date（日）を指定します。
累積する方向を指定します。3行目のORDER BYの後に時間(datetime)を記載します。こちらはデフォルトでASC(昇順)、2行目の最後にDESC(降順)を指定することもできます。前節と同様です。
4行目PRECEDINGで手前方向に2つまでを計算の対象に入れるという指定をしています。この2を変更することで移動平均の窓の広さをコントロールします。

※もし移動平均を過去にではなく未来に対して（またはその他数字に対して昇順ではなく降順にしたいとき）ORDER BYの後を変更する方法もありますが、PRECEDINGをFOLLOWINGに変更することでも実現できます。

また前後両方に対して移動平均（累積も同様ですが）範囲を広げたい時には

ROWS BETWEEN <n> PRECEDING AND <n> FOLLOWING（<n>は窓の広さ）

と記載します。