SQL中級者になる！（その07/12）

初めに

　データベースとSQLは現在のデータサイエンスに必須な知識・スキルの1つ。その基礎を

power-of-awareness.com

を基に学んできた。
　この知識をより昇華させて「SQL中級者になりたい！」ため、同じ著者の

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

を参考に、更なるSQLの知識を拡充していこう。

前回

power-of-awareness.com

7.　ウィンドウ関数で行間比較を行う

　において異なる行を比較するには工夫が必要で、そのためにウィンドウ関数を用いる。

　かつては相関サブクエリを用いるのが常套手段であった。しかし相関サブクエリはコードが複雑で動作の理解が難しく、パフォーマンス上の問題を引き起こしやすいという問題があった。これに対してウィンドウ関数を用いることで行間比較を簡潔な文で記述できるようになった。

7.1　成長・後退・現状維持

　たとえば年次データが行方向に存在していたときに、時系列での比較を行いたかった場合、相関サブクエリを用いることで列方向で当該値を比較することができた。これを今ではウィンドウ関数を用いて行う。

　ウィンドウ関数は元のテーブルに対して変更を加えずに新たな列を付け加えた結果を表示しているだけである点が特徴的である。

　たとえば歯抜けのある(=入力データの無い年(の行)が存在する)データに対して直近の年と同じ年商の年を選択することを考える。

year(年度)	sale(年商)
1990	50
1992	50
1993	52
1994	55
1997	55

-- 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

-- 2. ウィンドウ関数を用いる場合
SELECT year,sale
FROM (SELECT year,
                       sale AS current_sale,
                       SUM(sale) OVER (ORDER BY year
                                                ROWS BETWEEN 1 PRECEEDING
                                                                  AND 1 PRECEEDING) AS pre_sale
             FROM Sales2) TMP
WHERE current_sale = pre_sale
ORDER BY year

上記の事例では、相関サブクエリはネストが深くなりパフォーマンスが劣化する。これに対してウィンドウ関数ではそのような憂慮は不要である。

7.2　ウィンドウ関数と相関サブクエリ

　ウィンドウ関数と相関サブクエリには以下のような相違がある。

• ウィンドウ関数は、サブクエリは使っているものの、「相関」サブクエリではない。そのためサブクエリ単体で実行することができるので、可読性が高く動作も理解しやすい。サブクエリ内部だけを実行することでデバッグも容易に行うことができる。
• テーブルに対するスキャンも一度のみで済むためパフォーマンスが良い。

　相関サブクエリは複数のテーブルを結合することで行間比較を実現させていた一方で、ウィンドウ関数は行の順序に基づいた操作を行うことで手続型言語のループの動作をより直接的にに持ち込んだ。

7.3　まとめ

• 昔は、で行同士の比較を行う場合、比較対象のテーブルを追加して相関サブクエリを行なっていた。
• しかし相関サブクエリはパフォーマンスと可読性が悪く、ユーザーから不評だった。
• ウィンドウ関数の登場により、相関サブクエリを使う必要はなくなり、可読性が高くパフォーマンスが改善できた。

次回

power-of-awareness.com