コードロード

エラー討伐

【SQL】ウィンドウ関数でSQL上で行間比較をやってしまう

SQL

達人に学ぶSQL徹底指南所の学習記録です。

できること

  • 行間比較において相関サブクエリに頼っていた部分を、ウィンドウ関数で書けるようになる。
  • 手続き型言語において、「順序づけされたレコード集合をfor文やwhile文のループで回してカレントコードを1行ずつずらしながら処理するという考え方」を、SQL内でやってしまおう！ということ。
  • 異なる行を自分の行に持ってくることで行間比較ができるようになる。

やってること

  • PARTITION BY 句によるレコード集合のカット
  • ORDER BY 句によるレコードの順序付け
  • フレーム句によるカレントコードを中心としたサブセットの定義

f:id:naka_no_mura:20220205153640p:plain

引用先：http://www.vldb.org/pvldb/vol8/p1058-leis.pdf

要点

  • ウィンドウとは「範囲」の意味
  • PARTITION BY句 は、 GROUP BY句 から集約の機能を引いて、カットの機能だけを残し、 ORDER BY はレコードの順序をつける
  • フレーム句はカーソルの機能をSQLに持ち込むことで、カレントコードを中心としたレコード集合の範囲を定義することができる。
  • フレーム句を使うことで、異なる行のデータを1つの行に持ってくることができるようになって行間比較が簡単に行えるようになる。

構文

無名と名前付きの構文がある。

  • 無名ウィンドウ構文
SELECT shohin_id, name, price
AVG (price)
OVER (ORDER BY shohin_id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
FROM Shohin;
  • 名前付きウィンドウ構文
SELECT shohin_id, name, price
AVG (price) OVER W AS moving_avg
FROM Shohin
WINDOW W AS (ORDER BY shohin_id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg;

フレーム句のオプション

  • ROWS
    • 移動単位を行で設定
  • RANGE
    • 移動単位を列の値で設定。基準となるのはORDER BY句で指定された列
  • n PRECEDING
    • nだけ前へ（小さい方）へ移動する。nは正の整数
  • n FOLLOWING
    • nだけ後へ（大きい方）へ移動する。nは正の整数
  • UNBOUNDED PRECEDING
    • nだけ後へ（大きい方）へ移動する。nは正の整数
  • UNBOUNDED FOLLOWING
    • 無制限に下る方へ移動
  • CURRENT ROW
    • 現在行

注意

  • 名前付き構文はPostgreSQL、MySQLでは使用できるが、Oracleではエラーとなる。
  • 名前付きの方が正式な気もするが、無名構文の方が普及してしまった結果
  • 原則として、無名構文の方が無難かも

参考

分析関数（ウインドウ関数）をわかりやすく説明してみた - Qiita

itsakura.com