新しいことにはウェルカム

技術 | 電子工作 | ガジェット | ゲーム のメモ書き

SQLのウィンドウ関数(分析関数)の覚え方

SQLを学び始めた時、一番分かりにくかったのがウィンドウ関数(分析関数)です。

データベースを、単にデータを出し入れする場所として利用する分には、ウィンドウ関数を使うことは無いのですが、データを分析しようとする場合、あるととても便利な機能です。

ウィンドウ関数を使わないでも同様のクエリを書くことは可能なのですが、ウィンドウ関数を使うとクエリが短く書けることが多いです。

私も完全にマスターしているわけでは無いのですが、ここでは、ざっくりしたウィンドウ関数についての考え方をまとめたいと思います。

通常のSQLでできないこと

通常のSQLでは、FROMでデータを取得する対象テーブルを指定し、そこからレコードを1行取り出し、SELECTでその1行のレコードのデータを使って演算して結果を出力します。

サンプルとして、都市別のデイリーのコロナ新規感染者数データを見てみます。

SELECT
  dt,
  city,
  cases
FROM tbl_covid
dt city cases
2022-01-01 Tokyo 86
2022-01-01 Osaka 70
... ... ...

各行で出力されるデータは、取り出した1行のレコードの中に入っているものだけで、他の行のデータを使うことはできません。

例えば、今日の人数と昨日の人数を取得して、その差分を出すといったようなことはできないのです。

他の行のデータを使う方法

しかし、他の行のデータを使いたいケースがあります。

例えば、全データ平均値よりどれだけ多い・少ないを出したい場合などです。

そういった場合は、クエリの中で更にクエリ(サブクエリ)を呼び出すことによってできます。

SELECT
  tbl_base.dt,
  tbl_base.city,
  tbl_base.cases -
    (
      SELECT
        AVG(tbl_sub.cases)
      FROM tbl AS tbl_sub
    )
  AS diff_avg_cases
FROM tbl AS tbl_base

これをウィンドウ関数を使って書くと下記のようになります。

SELECT
  tbl_base.dt,
  tbl_base.city,
  tbl_base.cases -
    (
      AVG(tbl_base.cases) OVER()
    )
  AS diff_avg_cases
FROM tbl AS tbl_base

「集計関数」+「OVER()」でウィンドウ関数となります。

ウィンドウ関数のイメージ

ウィンドウ関数が呼び出される様子を図にしてみました。

  1. FROMより対象テーブルが指定されます
  2. テーブルから1行のレコードが取り出されます
  3. レコードからカラムの値が取り出され、SELECTで演算され出力されます
  4. SELECTの中のウィンドウ関数により、対象テーブルが再度呼び出されます
  5. テーブルから対象範囲(ウィンドウ)のデータが取り出されます
  6. 対象範囲のデータを元に、集計関数で演算し、その結果値がSELECTに渡ります

対象テーブルから対象範囲(ウィンドウ)を取り出して演算することから、ウィンドウ関数と呼ばれます。

また、ウィンドウ関数は行集合に対して演算を行う集計関数で、演算結果は必ず単一の値になります。

最初のSELECTの時は1行のレコードが取り出されるのに対し、ウィンドウ関数では複数のレコードがウィンドウとして取り出される点が大きく違いますね。

PARTITION BY

PARTITION BYでウィンドウ関数の対象範囲カラムを指定することができます。

例えば、前述の例ではテーブルの全データの平均値を求めたのですが、当日の全都市平均との差分を求めるには、PARTITION BYdtを固定することにより集計できます。

SELECT
  tbl_base.dt,
  tbl_base.city,
  tbl_base.cases -
    AVG(tbl_base.cases) OVER(
      PARTITION BY tbl_base.dt
    )
  AS diff_avg_cases
FROM tbl AS tbl_base

また、他の都市の平均ではなく、その都市の他の日の平均との差分を求める場合は、PARTITION BYcityを固定することにより集計できます。

SELECT
  tbl_base.dt,
  tbl_base.city,
  tbl_base.cases -
    AVG(tbl_base.cases) OVER(
      PARTITION BY tbl_base.city
    )
  AS diff_avg_cases
FROM tbl AS tbl_base

フレームとORDER BY

前述のとおり、PARTITION BYでウィンドウ関数のテーブル内の集計対象範囲が決まります。

そして、「フレーム」を使うと、その範囲内での開始と終了位置を指定することができ、更に対象範囲を絞り込むことができます。

例えば、ニュースなどでよく見る、各都市の7日移動平均は下記のクエリになります。

SELECT
  tbl_base.dt,
  tbl_base.city,
  AVG(tbl_base.cases) OVER(
    PARTITION BY tbl_base.city
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS mov_avg_7d
FROM tbl AS tbl_base

ROWS BETWEENの後、「開始行」と「終了行」を指定します。

6 PRECEDINGで現在行の6行前、つまり6日前を示し、CURRENT ROWが現在行を示し、それの平均値を取るので7日平均が集計されます。

実は、このクエリは間違っています。6行前と現在行で7日移動平均を取るには、データの並びが日付で昇順になっている必要があります。

そして、そのデータの並び順を指定する方法としてORDER BYを使います。

以上を踏まえた、正しいクエリは下記になります。

SELECT
  tbl_base.dt,
  tbl_base.city,
  AVG(tbl_base.cases) OVER(
    PARTITION BY tbl_base.city
    ORDER BY tbl_base.dt ASC
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS mov_avg_7d
FROM tbl AS tbl_base

ウィンドウのイメージ

ウィンドウ関数が呼び出され、対象範囲が決まる様子を図にしてみました。

  1. PARTITION BYでグループが固定される
  2. ORDER BYでデータの並びが決まる
  3. ROWS BETWEENで範囲の開始行と終了行が決まる

PARTITION BYはカラムを指定し、ROWS BETWEENで開始・終了を指定し、その開始・終了を指定するには、ORDER BYの並び指定が必要」という構造になります。

まとめ

繰り返しになりますが、ウィンドウ関数は下記のような流れになります。

  1. SELECTの中から再度テーブルを呼び出す
  2. テーブルの中から指定範囲のデータを取り出す
  3. 取り出されたデータを集計関数に渡して演算する

「テーブルが再び呼び出される」という所と「取り出されたレコードグループに対して集計関数が実行される」という所を抑えておけば、ウィンドウ関数のイメージは概ね理解できるかと思います。

応用

集計関数と行の開始・終了位置の指定方法は色々あるので、どういったものがあるかザッと目を通しておくとよいかと思います。

参考までに、ウィンドウ関数のよくある使い方例を記載しました。

各都市別、月初からの累計数

SELECT
  tbl_base.*,
  SUM(tbl_base.cases) OVER(
    PARTITION BY tbl_base.city, DATE_TRUNC(tbl_base.dt, MONTH)
    ORDER BY tbl_base.dt ASC
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS val
FROM tbl AS tbl_base
  • ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWで、グループの最初から現在行までを指定しています

行番号

SELECT
  tbl_base.*,
  ROW_NUMBER() OVER(
    ORDER BY tbl_base.dt ASC, tbl_base.city ASC
  ) AS val
FROM tbl AS tbl_base

一つ前の値

SELECT
  tbl_base.*,
  LAG(tbl_base.cases) OVER(
    PARTITION BY tbl_base.city
    ORDER BY tbl_base.dt ASC
  ) AS val
FROM tbl AS tbl_base

月末の行

WITH

tbl_num AS
(
SELECT
  tbl_base.*,
  ROW_NUMBER() OVER(
    PARTITION BY tbl_base.city, DATE_TRUNC(tbl_base.dt, MONTH)
    ORDER BY tbl_base.dt DESC
  ) AS num
FROM tbl AS tbl_base
)

SELECT
  * EXCEPT(num)
FROM tbl_num
WHERE num=1
  • 月でグループ化し、日付降順で並べてナンバリングし、num=1で並びの最初の行を抽出しています

ROW_NUMBER()LAG()関数など、いくつかの関数は、開始・終了位置を指定できないものがあるので、都度ドキュメントを読んで使い方を確認してください。

その他感想など

SELECTからSELECTを呼び出すサブクエリを使用していて、クエリが重くなったりエラーになるような場合でも、ウィンドウ関数を使ったクエリに書き換えると軽く動作するケースがあります。

SELECTの中で、他の行の値を使いたいケースがあれば、ウィンドウ関数のことを思い出してあげてください。

関連カテゴリー(SQL)記事

www.kwbtblog.com

www.kwbtblog.com

www.kwbtblog.com

www.kwbtblog.com