SQLで移動累計と移動平均

移動累計とは？移動平均とはなんぞや？
それは何がうま味なのかとかそういう話はもっとちゃんとした記事に任せて、とにかく出し方を。

使用するのは PostgreSQL
ドキュメントには

どんな組み込み、またはユーザ定義の集約関数もウィンドウ関数として使用できます

とあるのでSUMやAVGをwindow関数として使う。

サンプルデータ

sales テーブル

id |amount|created_at         |
---|------|-------------------|
  1| 15550|2020-12-07 07:20:18|
  2| 15134|2020-12-24 06:06:43|
  3|  5686|2020-12-06 17:47:16|
  4|  9823|2020-12-14 11:32:55|
  5|  1107|2020-12-26 20:30:30|
~
 995| 17558|2020-12-31 06:22:12|
 996| 10102|2020-12-11 20:23:26|
 997|  6472|2020-12-09 01:05:56|
 998| 16310|2020-12-17 01:12:56|
 999| 13276|2020-12-12 06:39:21|
1000| 13576|2020-12-03 12:32:36|

レコード数 1000
amount(金額) 500 ~ 20,000
created_at(レコード作成日時) 2020-12-01 00:00:00 ~ 2020-12-31 23:59:59

SQL

日別合計

本題の前にとりあえず日別合計

SELECT
  date_trunc('day', created_at)
  ,SUM(amount)
FROM
  public.sales
GROUP BY
  date_trunc('day', created_at)
ORDER BY
  date_trunc('day', created_at)
;

date_trunc         |sum   |
-------------------|------|
2020-12-01 00:00:00|257481|
2020-12-02 00:00:00|250527|
2020-12-03 00:00:00|311574|
2020-12-04 00:00:00|333867|
2020-12-05 00:00:00|350105|
2020-12-06 00:00:00|375064|
2020-12-07 00:00:00|273588|
2020-12-08 00:00:00|282633|
2020-12-09 00:00:00|247449|
2020-12-10 00:00:00|299017|
2020-12-11 00:00:00|338893|
2020-12-12 00:00:00|337359|
2020-12-13 00:00:00|363897|
2020-12-14 00:00:00|421434|
2020-12-15 00:00:00|287354|
2020-12-16 00:00:00|280898|
2020-12-17 00:00:00|287979|
2020-12-18 00:00:00|434503|
2020-12-19 00:00:00|383953|
2020-12-20 00:00:00|372925|
2020-12-21 00:00:00|309810|
2020-12-22 00:00:00|377717|
2020-12-23 00:00:00|323474|
2020-12-24 00:00:00|447995|
2020-12-25 00:00:00|357059|
2020-12-26 00:00:00|285747|
2020-12-27 00:00:00|296992|
2020-12-28 00:00:00|276438|
2020-12-29 00:00:00|342667|
2020-12-30 00:00:00|284884|
2020-12-31 00:00:00|297883|

移動累計

直前３日間の売り上げを合計した3日間移動累計を出す。
OVER句を追加してwindow関数として使っていく。

SELECT
  date_trunc('day', created_at)
  ,SUM(amount) OVER (
    ORDER BY
      date_trunc('day', created_at) ASC
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS mooving_sum
FROM
  public.sales
GROUP BY
  date_trunc('day', created_at)
  ,amount
ORDER BY
  date_trunc('day', created_at)
;

単純にSUMをwindow関数として使っていけば行けるかと思ったが、amount カラムが集約関数ではなくwindow関数で使用されているという判定になるのか、上記のSQLだと

column "sales.amount" must appear in the GROUP BY clause or be used in an aggregate function

とのお咎めをいただいた。
amount を集約関数か GROUP BY にいれんかい！ とのこと。

仕方ないから日別に集約する部分をサブクエリに逃すことにした。

SELECT
  daily.sals_day
  ,SUM(daily.daily_sum) OVER (
    -- PARTITION BY は使わず、SELECT 内容全てを対象に
    -- SELECT 結果テーブルを日付順に並べつつ
    ORDER BY
      daily.sals_day ASC
    -- 現在の行から2行前までをまとめる(自身を含めて3日分)
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS mooving_sum
FROM
  (
    SELECT
      date_trunc('day', created_at) AS sals_day
      ,SUM(amount) AS daily_sum
    FROM
      public.sales
    GROUP BY
      date_trunc('day', created_at)
  ) AS daily
ORDER BY
  date_trunc('day', sals_day)
;

ちょっと行数が増えたがこれで出た結果が以下
(最初の2日は手前の行数が足りず3日分の合計にはなっていない)

sals_day           |mooving_sum|
-------------------|-----------|
2020-12-01 00:00:00|     257481|
2020-12-02 00:00:00|     508008|
2020-12-03 00:00:00|     819582|
2020-12-04 00:00:00|     895968|
2020-12-05 00:00:00|     995546|
2020-12-06 00:00:00|    1059036|
2020-12-07 00:00:00|     998757|
2020-12-08 00:00:00|     931285|
2020-12-09 00:00:00|     803670|
2020-12-10 00:00:00|     829099|
2020-12-11 00:00:00|     885359|
2020-12-12 00:00:00|     975269|
2020-12-13 00:00:00|    1040149|
2020-12-14 00:00:00|    1122690|
2020-12-15 00:00:00|    1072685|
2020-12-16 00:00:00|     989686|
2020-12-17 00:00:00|     856231|
2020-12-18 00:00:00|    1003380|
2020-12-19 00:00:00|    1106435|
2020-12-20 00:00:00|    1191381|
2020-12-21 00:00:00|    1066688|
2020-12-22 00:00:00|    1060452|
2020-12-23 00:00:00|    1011001|
2020-12-24 00:00:00|    1149186|
2020-12-25 00:00:00|    1128528|
2020-12-26 00:00:00|    1090801|
2020-12-27 00:00:00|     939798|
2020-12-28 00:00:00|     859177|
2020-12-29 00:00:00|     916097|
2020-12-30 00:00:00|     903989|
2020-12-31 00:00:00|     925434|

移動平均

AVGにするだけ。

SELECT
  daily.sals_day
  ,AVG(daily.daily_sum) OVER (
    -- PARTITION BY は使わず、SELECT 内容全てを対象に
    -- SELECT 結果テーブルを日付順に並べつつ
    ORDER BY
      daily.sals_day ASC
    -- 現在の行から2行前までをまとめる(自身を含めて3日分)
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS mooving_sum
FROM
  (
    SELECT
      date_trunc('day', created_at) AS sals_day
      ,SUM(amount) AS daily_sum
    FROM
      public.sales
    GROUP BY
      date_trunc('day', created_at)
  ) AS daily
ORDER BY
  date_trunc('day', sals_day)
;

sals_day           |mooving_sum        |
-------------------|-------------------|
2020-12-01 00:00:00|257481.000000000000|
2020-12-02 00:00:00|254004.000000000000|
2020-12-03 00:00:00|273194.000000000000|
2020-12-04 00:00:00|298656.000000000000|
2020-12-05 00:00:00|331848.666666666667|
2020-12-06 00:00:00|353012.000000000000|
2020-12-07 00:00:00|332919.000000000000|
2020-12-08 00:00:00|310428.333333333333|
2020-12-09 00:00:00|267890.000000000000|
2020-12-10 00:00:00|276366.333333333333|
2020-12-11 00:00:00|295119.666666666667|
2020-12-12 00:00:00|325089.666666666667|
2020-12-13 00:00:00|346716.333333333333|
2020-12-14 00:00:00|374230.000000000000|
2020-12-15 00:00:00|357561.666666666667|
2020-12-16 00:00:00|329895.333333333333|
2020-12-17 00:00:00|285410.333333333333|
2020-12-18 00:00:00|334460.000000000000|
2020-12-19 00:00:00|368811.666666666667|
2020-12-20 00:00:00|397127.000000000000|
2020-12-21 00:00:00|355562.666666666667|
2020-12-22 00:00:00|353484.000000000000|
2020-12-23 00:00:00|337000.333333333333|
2020-12-24 00:00:00|383062.000000000000|
2020-12-25 00:00:00|376176.000000000000|
2020-12-26 00:00:00|363600.333333333333|
2020-12-27 00:00:00|313266.000000000000|
2020-12-28 00:00:00|286392.333333333333|
2020-12-29 00:00:00|305365.666666666667|
2020-12-30 00:00:00|301329.666666666667|
2020-12-31 00:00:00|308478.000000000000|

おまけ

ちなみに、私が出したかったのは移動累計や移動平均ではなくて、
24時間以内に〇〇件以上のレコードが登録されてい瞬間がないか？
などというものが傍から見ると利用方法がよくわからんものが出したかったのである。

以下がそんな趣のSQL

SELECT
  created_at
  ,COUNT(1) OVER (
    ORDER BY
      created_at
    RANGE BETWEEN CURRENT ROW AND interval '1 day' - interval '1 second' FOLLOWING
  ) AS moving_total
FROM
  public.sales
ORDER BY
  created_at
;

現在の行から1日-1秒までのcreated_atを持つレコードをカウントする。
あとはこれをサブクエリなりなんなりにしてwhere句でmoving_totalを指定してやるといい感じ。

RENGE を適当に 1 day にすると 2020-12-01 00:00:00 の行のカウントに 2020-12-02 00:00:00 の行も入ってくるのでとりあえず1秒引いている。
もっと良い手はあるかもしれない。

結果は以下

created_at         |moving_total|
-------------------|------------|
2020-12-01 00:07:50|          23|
2020-12-01 00:53:28|          24|
2020-12-01 02:09:41|          23|
2020-12-01 07:22:43|          28|
2020-12-01 07:38:12|          27|
~
2020-12-31 20:18:36|           5|
2020-12-31 20:36:27|           4|
2020-12-31 20:46:53|           3|
2020-12-31 22:40:27|           2|
2020-12-31 23:06:51|           1|

以上、久しぶりにまともにwindow関数と向き合った(当社比)のでちょこっとメモでした。

参考文献

https://dev.classmethod.jp/articles/sql-moving-average/
https://qiita.com/suzuki_sh/items/e470045786ba714dc7d7
https://www.postgresql.jp/document/12/html/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS
https://www.postgresql.jp/document/12/html/functions-window.html