努力したWiki

推敲の足りないメモ書き多数

ユーザ用ツール

サイト用ツール


documents:database:sql-0011

以前のリビジョンの文書です


SQLで移動平均を算出

2020/01/08
職場のTRSHM君に煽られたんでちょっと書く。

方針

ウインドウ関数を利用する。サブクエリを連ねるのはさすがに古い。

データベース準備

データベースの状態が実行中になれば利用可能。
データベース TRSMを用意

テーブルとデータの用意

sqlcmdでインスタンスパイプ名を指定して接続。あとは普通にSQLを実行してデータを定義する。

CREATE TABLE sample (
  saledate   DATE
 ,code       VARCHAR(2)
 ,val        NUMERIC(5,3)
);
GO
INSERT INTO sample VALUES('2020-01-01','0A', 50);
GO
INSERT INTO sample VALUES('2020-01-02','0A', 50);
INSERT INTO sample VALUES('2020-01-04','0A', 30);
INSERT INTO sample VALUES('2020-01-03','0A', 40);
INSERT INTO sample VALUES('2020-01-05','0A', 20);
INSERT INTO sample VALUES('2020-01-02','0B', 20);
INSERT INTO sample VALUES('2020-01-04','0B', 30);
INSERT INTO sample VALUES('2020-01-05','0B', 10);
GO

OVER句に対象レコードの範囲を指定する

PARTITION BY句で範囲を決め、ORDER BY句でレコードの並びを決めたのち、ROWS句で処理対象レコードを明示する。
以下のクエリだと、

  • 2つ前のレコード
  • 1つ前のレコード
  • 今選択されているレコード

の3レコードが処理対象になる。日付でソートして“n日前”をとれるようにすること。

SELECT saledate
      ,code
      ,AVG(val) OVER(PARTITION BY code ORDER BY saledate ROWS BETWEEN 2 preceding AND CURRENT ROW) AS "moved_avg"
  FROM sample
GO

ただ、単純にこんなのを書くとはまる。
AVG()で指定する項目がNULLだった場合、そのレコードのカウントが入らないので、例えばcode='0A'の2020-01-01 の“moved_avg”は 16.666 ではなく50.000になってしまう。
※NULL, NULL, 50 となるので、50÷3ではなく50÷1になる。

code='0B'なんか歯抜けがあるのでこれも正しくない。

NULL値の対応を入れる

欠損レコードを補いNULL値の影響を消し込む。下手な小細工するより早い。

INSERT INTO sample VALUES('2019-12-31','0A',0);
INSERT INTO sample VALUES('2019-12-30','0A',0);
INSERT INTO sample VALUES('2019-12-31','0B',0);
INSERT INTO sample VALUES('2019-12-30','0B',0);
INSERT INTO sample VALUES('2020-01-01','0B',0);
INSERT INTO sample VALUES('2020-01-03','0B',0);
GO
SELECT saledate
      ,code
      ,AVG(val) OVER(PARTITION BY code ORDER BY saledate ROWS BETWEEN 2 preceding AND CURRENT ROW) AS "moved_avg"
  FROM sample
GO

不要な行が出るのを嫌うなら、テーブルを拡張して対応すればいいと思う。

DROP TABLE sample
GO
CREATE TABLE sample (
  saledate   DATE
 ,code       VARCHAR(2)
 ,val        NUMERIC(5,3)
 ,disp       VARCHAR(1)
);
GO
INSERT INTO sample VALUES('2020-01-01','0A', 50, 'Y');
INSERT INTO sample VALUES('2020-01-02','0A', 50, 'Y');
INSERT INTO sample VALUES('2020-01-04','0A', 30, 'Y');
INSERT INTO sample VALUES('2020-01-03','0A', 40, 'Y');
INSERT INTO sample VALUES('2020-01-05','0A', 20, 'Y');
INSERT INTO sample VALUES('2020-01-02','0B', 20, 'Y');
INSERT INTO sample VALUES('2020-01-04','0B', 30, 'Y');
INSERT INTO sample VALUES('2020-01-05','0B', 10, 'Y');
INSERT INTO sample VALUES('2019-12-31','0A',  0, 'N');
INSERT INTO sample VALUES('2019-12-30','0A',  0, 'N');
INSERT INTO sample VALUES('2019-12-31','0B',  0, 'N');
INSERT INTO sample VALUES('2019-12-30','0B',  0, 'N');
INSERT INTO sample VALUES('2020-01-01','0B',  0, 'N');
INSERT INTO sample VALUES('2020-01-03','0B',  0, 'N');
GO
WITH V AS (
  SELECT saledate
        ,code
        ,AVG(val) OVER(PARTITION BY code ORDER BY saledate ROWS BETWEEN 2 preceding AND CURRENT ROW) AS "moved_avg"
        ,disp
    FROM sample
)
SELECT saledate
      ,code
      ,moved_avg
  FROM V
  WHERE disp='Y'
GO

documents/database/sql-0011.1578425920.txt.gz · 最終更新: 2020/01/07 19:38 by k896951

ページ用ツール