【SQL】ウィンドウ関数で行間比較を行う（相関サブクエリとはバイバイして可読性＆パフォーマンスUP）

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

達人に学ぶSQL徹底指南書第2版初級者で終わりたくないあなたへ (CodeZine BOOKS)

作者:ミック
翔泳社

Amazon

SQLで同一行内の列同士の値を比較するのはWHERE句で記述すればいいだけだが、異なる行を比較するのは工夫が必要。

SQLで行間比較を行いたいときは、相関サブクエリを使うのが常套手段だったが、相関サブクエリのコードはパフォーマンスと可読性が悪いという欠点がある。

そこで、「行の順列」に基づいて、手続き型言語のループの動作をSQLに持ち込んだのが、ウィンドウ関数。

概要

昔は、SQLで行同士を比較するときは、比較対象のテーブルを追加して、相関サブクエリを行なっていた。
しかし相関サブクエリは、パフォーマンスと可読性が悪い。
ウィンドウ関数は可読性が高く、パフォーマンス改善も見込める。
手続き型のループの動作をSQLに持ち込んだのがウィンドウ関数。

具体例

ある会社の年商を記録するテーブルを考えてみる。

前年と比較する

f:id:naka_no_mura:20220213092210p:plain

このテーブルから「前年と比べて年商が増えたのか、減ったのか、変化なしなのか」をSQLで出力してみる。

まずは、「変化なし」の年を取得してみる。

もし手続き型言語で計算するなら、

年度で昇順にソートする
ループさせて1行ずつ直前の行のsale列と比較する

というやり方になるはず。

SQLだとこんな感じ。

// 相関サブクエリを利用
SELECT year, sale
  FROM sales S1
 WHERE sale = (SELECT sale FROM sales S2 where S2.yaer = S1.year - 1)
 ORDER BY year;

year  sale
----  ----
1993  52
1995  50

f:id:naka_no_mura:20220213092222p:plain

相関サブクエリで2つのテーブルにおける比較対象の行をズラすことで手続き型のループの代役をしている。

ウィンドウ関数ならこうなる。

// ウィンドウ関数を利用
SELECT year, current_sale
  FROM (SELECT year,
               sale AS current_sale,
               SUM(sale) OVER (ORDER BY year
                               RANGE BETWEEN 1 PRECEDING
                                         AND 1 PRECEDING) AS pre_sale
          FROM sales) AS TMP
 WHERE current_sale = pre_sale
 ORDER BY year;

year  sale
----  ----
1993  52
1995  50

ポイントは、サブクエリ内部のウィンドウ関数単体で実行するとわかりやすい。

// ウィンドウ関数のみで実行
SELECT year,
       sale AS current_sale,
       SUM(sale) OVER (ORDER BY year
                       RANGE BETWEEN 1 PRECEDING
                                 AND 1 PRECEDING) AS pre_sale
 　FROM sales;

year  current_sale  pre_sale
----  ------------  --------
1990            50
1991            51        50
1992            52        51
1993            52        52
1994            50        52
1995            50        50
1996            49        50
1997            55        49

RANGE BETWEEN 1 PRECEDING AND 1 PRECEDING で、「カレント行の年よりも１年前に限定する」という条件で、列を一時的に追加している（フレーム句）。

時系列に歯抜けがない場合

次に、各年について、前年に比べて成長したのか、後退したのか、現状維持なのかを一度に求めてみる（音楽や映画の週間ランキングみたいな）。

// 相関サブクエリを利用
SELECT year, current_sale AS sale,
       CASE WHEN current_sale = pre_sale
            THEN '→'
            WHEN current_sale > pre_sale
            THEN '↑'
            WHEN current_sale < pre_sale
            THEN '↓'
       ELSE 'ー' END AS var
  FROM (SELECT year,
               sale AS current_sale,
               (SELECT sal
                  FROM sales S2
                 WHERE S2.year = S1.year - 1) AS pre_sale
          FROM sales S1) AS TMP
 ORDER BY year;

year  sale  var
----  ----  ---
1990     50  ー
1991     51  ↑
1992     52  ↑
1993     52  →
1994     50  ↓
1995     50  →
1996     49  ↓
1997     55  ↑

// ウィンドウ関数を利用
SELECT year, current_sale AS sale,
       CASE WHEN current_sale = pre_sale
            THEN '→'
            WHEN current_sale > pre_sale
            THEN '↑'
            WHEN current_sale < pre_sale
            THEN '↓'
       ELSE 'ー' END AS var
  FROM (SELECT year,
               sale AS current_sale,
               SUM(sale) OVER (ORDER BY year
                               RANGE BETWEEN 1 PRECEDING
                                         AND 1 PRECEDING) AS pre_sale
          FROM sales S1) AS TMP
 ORDER BY year;

year  sale  var
----  ----  ---
1990     50  ー
1991     51  ↑
1992     52  ↑
1993     52  →
1994     50  ↓
1995     50  →
1996     49  ↓
1997     55  ↑

時系列に歯抜けがある場合

f:id:naka_no_mura:20220213092240p:plain

年に歯抜けがなければこれでいいが、歯抜けがあると、「今年 - 1」の条件設定ではうまくいかなくなる。より一般化して、「直近」の行を比較対象にする必要がある。

まずは相関サブクエリから。

ある年から見て、「過去の直近の年」ということは、「条件1：自分より前のとしてあること」「条件2：条件1を満たす年の中で最大であること」。

// 相関サブクエリ
SELECT year, sale
  FROM sales2 S1
 WHERE sale = (SELECT sale
                 FROM sales2 S2
                WHERE S2.year = (SELECT MAX(year)  // 条件2：条件1を満たす年の中で最大
                                   FROM sales2 S3
                                  WHERE S1.year > S3.year)) // 条件1：自分より過去
 ORDER BY year;

year  sale
----  ----
1992   50
1997   55

相関サブクエリだとネストが深くなり、パフォーマンスは劣化する。