1 日ごとの数日間の平均をとる SQL #SQL

先日、同僚の人に教えてもらった単純移動平均。

例えば「1 月 1 日～ 1 月 5 日」「1 月 2 日～ 1 月 6 日」という感じで範囲をずらしながら平均をとるもので、日付の重み付けがないから「単純」らしい。

なんとなく気になって SQL での求め方を考えてみました。そのメモ。

環境

Oracle 11g

サンプルデータ

テーブル作成

create_table

CREATE TABLE STOCKS_1 (
  ID        VARCHAR2(1)
 ,DAY       DATE        NOT NULL
 ,VALUE     INTEGER     NOT NULL
 ,PRIMARY KEY(ID, DAY)
);

データ流し込み

insert_samples

INSERT INTO STOCKS_1
    SELECT
      CHR(ASCII('A') + MOD(ROWNUM - 1, 26))                  -- [A-Z]
     ,TO_DATE('20170101', 'YYYYMMDD') + FLOOR(ROWNUM / 26)
     ,ROWNUM
    FROM
     (SELECT 0 FROM ALL_CATALOG WHERE ROWNUM <= (26 * 3))    -- 3 日分
;

取得

SQL

avg_of_continuous_days

SELECT
  STOCKS_1.ID            AS ID
 ,AVG_RANGE.DAY_BEFORE   AS DAY_BEFORE
 ,AVG_RANGE.DAY_AFTER    AS DAY_AFTER
 ,AVG(STOCKS_1.VALUE)    AS AVG_VALUE
 ,MIN(STOCKS_1.VALUE)    AS MIN_VALUE   -- 参考用
 ,MAX(STOCKS_1.VALUE)    AS MAX_VALUE   -- 参考用
FROM
  STOCKS_1
 ,(
      SELECT
        ID        AS ID
       ,DAY - 1   AS DAY_BEFORE   -- 日付の幅 - 1
       ,DAY       AS DAY_AFTER
      FROM
        STOCKS_1
  ) AVG_RANGE
WHERE
  1 = 1
  AND STOCKS_1.ID = AVG_RANGE.ID
  AND STOCKS_1.DAY BETWEEN AVG_RANGE.DAY_BEFORE AND AVG_RANGE.DAY_AFTER
GROUP BY
  STOCKS_1.ID, AVG_RANGE.DAY_BEFORE, AVG_RANGE.DAY_AFTER
HAVING
  1 = 1
  AND COUNT(*) = 2   -- 日付の幅
ORDER BY
  STOCKS_1.ID, AVG_RANGE.DAY_BEFORE
;

メモ

日付もちのテーブルをもとに、平均をとりたい日付の幅を提供するサブクエリテーブルを作成。
HAVING 句は日付の幅を満たしているものだけを平均の対象としたい場合のみ記述。

結果

ID	DAY_BEFORE	DAY_AFTER	AVG_VALUE	MIN_VALUE	MAX_VALUE
A	2017-01-01	2017-01-02	14	1	27
A	2017-01-02	2017-01-03	40	27	53
B	2017-01-01	2017-01-02	15	2	28
B	2017-01-02	2017-01-03	41	28	54
C	2017-01-01	2017-01-02	16	3	29
C	2017-01-02	2017-01-03	42	29	55
D	2017-01-01	2017-01-02	17	4	30
D	2017-01-02	2017-01-03	43	30	56
E	2017-01-01	2017-01-02	18	5	31
E	2017-01-02	2017-01-03	44	31	57
F	2017-01-01	2017-01-02	19	6	32
F	2017-01-02	2017-01-03	45	32	58
G	2017-01-01	2017-01-02	20	7	33
G	2017-01-02	2017-01-03	46	33	59
H	2017-01-01	2017-01-02	21	8	34
H	2017-01-02	2017-01-03	47	34	60
I	2017-01-01	2017-01-02	22	9	35
I	2017-01-02	2017-01-03	48	35	61

取得 (ウィンドウ関数使用版)

SQL

avg_of_continuous_days_with_over_clause

SELECT
  *
FROM
  (
      SELECT
        ID
       ,DAY - 1   AS DAY_BEFORE
       ,DAY       AS DAY_AFTER
       ,AVG(VALUE) OVER(
          PARTITION BY ID
          ORDER BY DAY
          RANGE 1 PRECEDING   -- (日付の幅 - 1) 日前から
        ) AS AVG_VALUE

        -- 参考用
       ,MIN(VALUE) OVER(
          PARTITION BY ID
          ORDER BY DAY
          RANGE 1 PRECEDING   -- (日付の幅 - 1) 日前から
        ) AS MIN_VALUE

        -- 参考用
       ,MAX(VALUE) OVER(
          PARTITION BY ID
          ORDER BY DAY
          RANGE 1 PRECEDING   -- (日付の幅 - 1) 日前から
        ) AS MAX_VALUE

        -- 日付の幅を満たしているだけを集計対象にしたいとき用
       ,COUNT(*) OVER(
          PARTITION BY ID
          ORDER BY DAY
          RANGE 1 PRECEDING   -- (日付の幅 - 1) 日前から
        ) AS RANGE_VALUE
      FROM
        STOCKS_1
      ORDER BY
        ID, DAY
  )
WHERE
  RANGE_VALUE = 2   -- 日付の幅
;

メモ

OVER 句は使い回しが効かないので同じ条件で集計したい列が複数あると記述が面倒。
- 変数を使える環境なら問題なし。
ウィンドウ関数の結果を条件として扱うには別テーブルとして外側から取り込む必要があり、日付の幅を厳格にしたい場合は少し面倒。

結果

見づらかったので RANGE_VALUE 列は省略。上記条件の通り、全部 2 です。

ID	DAY_BEFORE	DAY_AFTER	AVG_VALUE	MIN_VALUE	MAX_VALUE
A	2017-01-01	2017-01-02	14	1	27
A	2017-01-02	2017-01-03	40	27	53
B	2017-01-01	2017-01-02	15	2	28
B	2017-01-02	2017-01-03	41	28	54
C	2017-01-01	2017-01-02	16	3	29
C	2017-01-02	2017-01-03	42	29	55
D	2017-01-01	2017-01-02	17	4	30
D	2017-01-02	2017-01-03	43	30	56
E	2017-01-01	2017-01-02	18	5	31
E	2017-01-02	2017-01-03	44	31	57
F	2017-01-01	2017-01-02	19	6	32
F	2017-01-02	2017-01-03	45	32	58
G	2017-01-01	2017-01-02	20	7	33
G	2017-01-02	2017-01-03	46	33	59
H	2017-01-01	2017-01-02	21	8	34
H	2017-01-02	2017-01-03	47	34	60
I	2017-01-01	2017-01-02	22	9	35
I	2017-01-02	2017-01-03	48	35	61