【相関サブクエリの動作イメージが掴みにくい方へ…】

■前置き

こんにちは。しがないプログラマのryuji62でございます。
最近転職して、どちらかというとバックエンドを中心（今現在はがっつりフロントエンドコーディングしていますが）にコードを書いております。
バックエンドでは、Laravelを使い始めました。前職ではこういったフレームワークを使用することはなく、普通のHTMLでコーディングするというコーダーを担当しておりました。時々普通のHTMLでは具合が悪い案件に当たった場合は、タスクランナーを使用することもありましたが、それもGulpとかHexoくらいでした。

さて、今の会社ではフレームワークも使用し、バックエンドを中心にコードを書いておりますが、PHP以外でSQLも書く機会が増えてきております。前職ではPHPすら書かなかったので、驚きです。もともとPHPとSQLが言語的には好き（関心がある）だったので、少しやりがいを感じていたりします。

SQLに関しては2つ前の会社で少しテーブル連結とかをやるくらいで別段ややこしいSQLを書く機会はそれほどでもなかったのですが、今の会社では自社プロジェクトの都合で割と複雑なSQLを書く機会がありました。
どういったSQLかというと、「相関サブクエリ」でございます。熟練者の方からすると、「何をほざいておられるのですか？笑」と思われそうですが、いわゆる普通のWebサイトくらいだとシンプルなSQLで済むことが多いんですよ。

しかし、自社プロジェクトで複雑なデータ取得をするときにそこそこハマりましたね…。
そのときはタイトルにあるような相関サブクエリではなかったのですが…。

備忘録も兼ねて、勉強した内容を共有しようと思います！

もしよろしければご参考にしてやってください。

■今回の内容

今回は、『相関サブクエリの動作イメージが掴みにくい方へ…』というテーマなので、そのときのクエリの処理の追い方をご説明しようと思います。完全に正解ではないかもしれませんが、ご参考にはなるかと思われます。

〇お題

例えば、以下のようなテーブルがあったとします。
テーブル名は「sales2」です。

このテーブルから以下の内容を取得しようと思います。

一行前のデータと比較して、もしsale値が同じであれば取得する。

さて、これの答えを先に書くと（先に書くんかいっ！！）、

SELECT
  s1.year,
  s1.sale
FROM sales2 AS s1
WHERE s1.sale = (
  SELECT
    s2.sale
  FROM sales2 AS s2
  WHERE s2.year = s1.year - 1
)

という感じになります。
実はこのお題自体は「達人に学ぶSQL徹底指南書第2版初級者で終わりたくないあなたへ (CodeZine BOOKS)」という本であったお題（レコードは少し変えてますが）なんです。この本を最近よく読んでいて、「なぜそうなるの？」っていう動作のイメージがわかり辛かったんですね。

こういう「行間比較」っていうのは今時だと、ウィンドウ関数を使用して、シンプルなSQLで取得できることがだいたいだと思うのですが、使用中のサーバーにインストールされているRDBMSがウィンドウ関数をサポートしていないことがあります。そういうときに、行間比較の必要性が出てくると中々冷や汗もので、普通にPHPでループさせながら無理やりアプリケーション側でなんとかするっていうのも一つの手段ですが、できればプログラムはスマートな処理で完結させたいっていうときに、SQLでなんとかするっていう手段にたどり着くのかなと思います。

さてさて、上記SQLを読んでみて、みなさんはすぐに動作を頭の中でイメージできるでしょうか？
僕は無理です。いや、無理と言うと「諦めたらそこで～」というようなセリフが過りますよね。

このSQLの、

WHERE s2.year = s1.year - 1 ←ここがポイント①

ここなんですね。これがループクエリ（SQL）の大事なポイントで、これがないと目的は果たせなくなりますな。

これは日本語にそのまま直すと、「s2.year と s1.year - 1 が等しいとき、そのときのs1.yearとs1.saleを、sales AS s1テーブルから取得する」という意味になります。

ポイント①が2つのテーブルを紐づけ（バインドって言い方をします）して、プログラムでいうところのループ処理みたいに、1行ずつ比較するという動作をしてくれてるんですな。

この場合、2つのテーブルというのは、どっちもsalesテーブルです。スクショを貼ったテーブルです。
普通、複数のテーブルの紐づけというと、LEFT OUTER JOINとかINNER JOINとかの結合を想像される方も多いかと思います。

僕もそうでした。
まったく違うテーブルだと比較的想像しやすいのですが、ウィンドウ関数なしで行間比較しようとすると、どうしても相関サブクエリ（それも自己相関サブクエリというややこい名前）になってしまいます。

同じテーブルだと紐づけの仕方の動作イメージが付きにくいですが、これを違うテーブルと考えると良いですよ。って本には書いてあります。なるほど、確かにそうである。相関サブクエリなんていうややこい名前に縛られてる（まさにバインド）と、欲しい君が求められないっていう事態になり、今日も残業やっほい！ってなりますね。

本を読みながら、「どう考えるとわかりやすいか？」を考えるというある意味哲学的発想に至りながら、絞り出した答えが、「紙に書き出す or エディタにメモしながら」という非常に原始的な方法でした笑

今回はその方法でご説明したいと思います。

ここでもう一回お題のテーブルを貼っておきます。

お題は、

一行前のデータと比較して、もしsale値が同じであれば取得する。

で、つまり求めたい結果は、

になります。

では、まずはテーブルを本当に2つ並べてみましょうか（考えるときにわかりやすくするために）。

この２つのテーブル（実際には1つのテーブルを自己相関してますが）で行間比較してみるということですね。

で、行間比較のイメージ図ですが、

▼1回目のループ

S1 2005 : S2 2005

でサブクエリ内のWHEWE句の条件を判定しています。

で、この1回目のループでは、「s2.year = s1.year - 1」ではないので（2005 = 2005 - 1ではない）のでこのときは何もSELECTされない。

▼2回目のループ

S1 2005 : S2 2004

このときは、「s2.year = s1.year - 1」（2004 = 2005 - 1）で条件が一致しているので、S1が2005のときはOKという意味になるので、S1テーブルから2005と71がSELECTされる。

▼3回目のループ

S1 2005 : S2 2002

このときは、「s2.year = s1.year - 1」ではないので（2002 = 2005 - 1ではない）のでこのときは何もSELECTされない。

というように、一つずつループしていますよね？
こうやって紙に書くなり、エディタにメモしながら追いかけるとわかりやすいですよ。

長々となってしまいましたが、こうやっていくと相関サブクエリも少しづつ慣れていくだろうと思います。

以上になります。
どうもありがとうございました。
（前置き長い…）