Window関数についての知識をまとめた(その2)

6848 ワード
Window関数 MySQL SQL MySQL テキストリンク

はじめに

データ分析における重要な関数そうなので、何章かに分けて、Window関数についてまとめています。

前回の記事は👇

今回もいくつかのサンプルを用意して説明します。

今回は、Window関数の機能一覧の紹介と、Window関数の典型的な利用ケースである移動平均を紹介していきます。

今回もSQLの実行は 「DB Fiddle」というWebサービスを使ってみます。

Window関数の機能一覧の紹介

Window関数の機能を1枚絵で俯瞰してみましょう。

以下の論文から引用してます。

Efficient processing of window functions in analytical SQL queries | Request PDF

3つの機能から成り立っていることが分かります

  • PARTITION BY 句によるレコード集合のカット
  • ORDER BY 句による順序づけ
  • フレーム句による現在の行を中心とした範囲の定義

Window関数で移動平均を算出

当日と前日の単純移動平均を計算してみましょう

参考

Schema (MySQL v8.0)

CREATE TABLE temperatures (
  id INT NOT NULL PRIMARY KEY auto_increment,
  date DATE,
  city_name TEXT,
  avg_temp FLOAT
);

INSERT INTO temperatures VALUES
    (1, '2020-12-01', '東京', 10.0),
    (2, '2020-12-02', '東京', 9.0),
    (3, '2020-12-03', '東京', 8.0),
    (4, '2020-12-04', '東京', 7.0),
    (5, '2020-12-01', '愛知', 11.0),
    (6, '2020-12-02', '愛知', 10.0),
    (7, '2020-12-03', '愛知', 9.0),
    (8, '2020-12-04', '愛知', 8.0);

Query

SELECT
    date AS 日付,
    city_name,
    AVG(avg_temp) OVER avg_temp_2days AS '移動平均2日間'
FROM temperatures
WINDOW avg_temp_2days AS (
    PARTITION BY city_name
   ORDER BY id ASC
   ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
);

ウィンドウ関数のフレーム句で使用できるオプション

  • ROWS: 移動単位を行で指定
  • n PRECEDING: nだけ前（小さい方）へ移動する
  • CURRENT ROW: 現在行

Results

日付 city_name 移動平均2日間
2020-12-01 愛知 11
2020-12-02 愛知 10.5
2020-12-03 愛知 9.5
2020-12-04 愛知 8.5
2020-12-01 東京 10
2020-12-02 東京 9.5
2020-12-03 東京 8.5
2020-12-04 東京 7.5

View on DB Fiddle

参照

29 - 35p

アウトプット100本ノック実施中