メインコンテンツへスキップ
見出し画像

第9回:相関スカラサブクエリを使った行間比較

    SQLでデータを比較するとき、同じ行にある複数の列を比較することは比較的簡単です。

    では、ある行の値と、別の行の値を比較したい場合はどうすればよいでしょうか。

    例えば、映画の上映時間について、1つ前のIDの映画より長くなったのか、短くなったのかを判定したいとします。

    このような場合に活用できるのが、相関スカラサブクエリを使った行間比較です。

    今回は、相関サブクエリの条件を工夫して比較対象の行をずらし、CASE式で増減を判定するSQLを練習します。

    単に完成したSQLを覚えるのではなく、「現在の行」と「比較対象の行」を頭の中で結び付けながら、自然にSQLを書けるようになることを目指します。


    1.今回のテーマ

    相関スカラサブクエリを使った行間比較

    今回の目標は、次の3つです。

    • 相関サブクエリを使って、別の行の値を取得する

    • 比較対象の行をIDの条件でずらす

    • CASE式で比較結果を判定する

    今回のSQLでは、映画の上映時間を比較します。

    現在の映画より1つ小さいIDの映画を比較対象とし、上映時間が長ければup、短ければdown、それ以外は-と表示します。

    ポイントは、相関サブクエリによって、別の行にある値を取り出して比較することです。

    2.練習に使用するSQLBolt

    今回も、無料でSQLを実行できる学習サイト「SQLBolt」を使用します。

    SQLBolt Lesson 12:Order of execution of a Query

    今回使用するのは、Lesson 12に登場するmoviesテーブルです。

    SQLBoltのLesson 12自体が今回の行間比較を教えているわけではありません。

    Lesson 12のテーブル構成やデータが、今回の相関サブクエリの練習に使いやすいため、練習環境として利用します。

    3.使用するテーブル

    今回使用するのは、映画情報を管理するmoviesテーブルのみです。

    主な列は次のとおりです。

    • Id:映画ID

    • Title:映画タイトル

    • Director:監督名

    • Year:公開年

    • Length_minutes:上映時間(分)

    今回は、このLength_minutesを使って、異なる行同士の値を比較します。

    例えば、次のようなデータがあります。

    画像

    ID 2の映画はID 1より上映時間が長く、ID 3の映画はID 2より短くなっています。

    このような比較をSQLで行います。

    4.今回の練習問題

    moviesテーブルから映画情報を取得し、次の条件でUpDown列を追加してください。

    現在の映画と、IDが1小さい映画の上映時間を比較し、次の値を表示します。

    • 現在の映画の上映時間が長い場合:up

    • 現在の映画の上映時間が短い場合:down

    • 同じ上映時間、または比較対象が存在しない場合:-

    例えば、ID 2のA Bug's Lifeは95分、ID 1のToy Storyは81分なので、upと表示します。

    一方、ID 3のToy Story 2は93分、ID 2のA Bug's Lifeは95分なので、downと表示します。

    今回は、比較対象となる映画の上映時間を相関スカラサブクエリで取得する方法で解いてみましょう。

    5.解答SQL

    SELECT
        a.*,
        CASE
            WHEN a.length_minutes > (
                SELECT b.length_minutes
                FROM movies b
                WHERE b.id = a.id - 1 /* 行をずらす */
            ) THEN 'up'
    
            WHEN a.length_minutes < (
                SELECT b.length_minutes
                FROM movies b
                WHERE b.id = a.id - 1 /* 行をずらす */
            ) THEN 'down'
    
            ELSE '-'
        END AS UpDown
    FROM movies a;

    実行結果

    実行結果では、映画情報にUpDown列が追加されます。

    画像

    それでは、このSQLがどのように動いているのかを詳しく見ていきましょう。

    6.SQLの考え方を詳しく解説

    ① 外側のSQLで現在の行を取得する

    まず、外側のSQLを確認します。

    SELECT
        a.*
    FROM movies a;

    moviesテーブルにaという別名を付けています。

    ここでのaは、現在処理している映画の行を表します。

    例えば、ID 3のToy Story 2を処理している場合、次のようになります。

    • a.id:3

    • a.title:Toy Story 2

    • a.length_minutes:93

    今回のSQLでは、この現在行の上映時間を、別の行の上映時間と比較します。

    ② 比較対象の行を取得する

    次に、相関スカラサブクエリを確認します。

    SELECT b.length_minutes
    FROM movies b
    WHERE b.id = a.id - 1

    ここでは、同じmoviesテーブルをもう一度参照し、bという別名を付けています。

    • a:現在の映画

    • b:比較対象となる映画

    重要なのは、WHERE句です。

    WHERE b.id = a.id - 1

    例えば、現在の映画がID 3の場合、

    a.id - 1は3 - 1 = 2になります。

    つまり、サブクエリは次のような条件で実行されます。

    SELECT b.length_minutes
    FROM movies b
    WHERE b.id = 2;

    ID 2のA Bug's Lifeの上映時間は95分なので、サブクエリは95を返します。

    このように、外側の行の値を使って、内側のサブクエリが取得する行を決めるのが、相関サブクエリの特徴です。

    ③ なぜ「スカラサブクエリ」なのか

    今回のサブクエリは、b.length_minutesという1つの列を取得しています。

    また、b.id = a.id - 1という条件によって、IDが一意であれば、取得される行は最大1件になります。

    そのため、サブクエリは1つの値を返すスカラサブクエリとして利用できます。

    例えば、ID 3の映画を処理しているときは、比較対象の上映時間である95を返します。

    この値を、外側のa.length_minutesと比較できるわけです。

    なお、比較対象のIDが存在しない場合、スカラサブクエリの結果はNULLになります。

    ④ CASE式で上映時間を比較する

    次に、CASE式の判定部分を確認します。

    CASE
        WHEN a.length_minutes > (
            SELECT b.length_minutes
            FROM movies b
            WHERE b.id = a.id - 1
        ) THEN 'up'
    
        WHEN a.length_minutes < (
            SELECT b.length_minutes
            FROM movies b
            WHERE b.id = a.id - 1
        ) THEN 'down'
    
        ELSE '-'
    END AS UpDown

    CASE式では、条件を上から順番に判定します。

    最初のWHEN

    現在の映画の上映時間が、比較対象より長い場合はupを返します。

    2つ目のWHEN

    現在の映画の上映時間が、比較対象より短い場合はdownを返します。

    ELSE

    どちらの条件にも該当しない場合は-を返します。

    同じ上映時間の場合だけでなく、比較対象が存在せず、サブクエリがNULLを返す場合も、今回のCASE式では-になります。

    ⑤ 実際のデータで動きを確認する

    ID 2のA Bug's Lifeを例に考えてみましょう。

    • 現在の上映時間:95分

    • 比較対象:ID 1のToy Story

    • 比較対象の上映時間:81分

    CASE式の最初の条件は、

    95 > 81

    となるため、結果はupです。

    次に、ID 3のToy Story 2を考えます。

    • 現在の上映時間:93分

    • 比較対象:ID 2のA Bug's Life

    • 比較対象の上映時間:95分

    最初の条件93 > 95は成立しません。

    次の条件93 < 95が成立するため、結果はdownになります。

    このように、現在行が変わるたびに、相関サブクエリが参照する比較対象も変わるのがポイントです。

    7.今回の最大のポイント:比較対象の行をずらす

    今回のトレーニングで最も重要なのは、次の条件です。

    WHERE b.id = a.id - 1

    この条件によって、現在の行とは異なる行を比較対象として取得しています。

    例えば、

    • 現在のIDが2なら、比較対象はID 1

    • 現在のIDが3なら、比較対象はID 2

    • 現在のIDが4なら、比較対象はID 3

    という関係になります。

    つまり、相関サブクエリの中で、比較対象となる集合を現在行から1つずらしているわけです。

    ここが理解できると、行間比較の考え方がぐっと分かりやすくなります。

    ただし、今回のid - 1という方法は、IDが連続しているデータを前提としています。

    例えば、ID 4が存在しない場合、ID 5の比較対象となるID 4の行は取得できません。

    その場合、サブクエリはNULLを返します。

    また、SQLのテーブルには本来、行の並び順という概念はありません。

    今回比較しているのは、あくまで**「IDが1小さい行」**です。

    この点は、実務で応用するときにも意識しておきたいポイントです。

    8.実務ではどのように活用できるか

    行間比較の考え方は、実務でもさまざまな場面に応用できます。

    例えば、製造業のデータであれば、次のようなケースが考えられます。

    ① 生産実績の前回比較

    前回の生産実績と今回の生産実績を比較し、生産数量が増えたか減ったかを判定する。

    ② 在庫数量の増減判定

    前回記録した在庫数量と現在の在庫数量を比較し、増加・減少を判定する。

    ③ 設備測定値の変化確認

    直前の測定値と今回の測定値を比較し、値が上昇したか下降したかを確認する。

    ④ 単価の変動確認

    前回登録した単価と今回の単価を比較し、値上がり・値下がりを判定する。

    いずれも、基本となる考え方は同じです。

    「現在のデータ」と「比較対象のデータ」を対応付け、値を比較する。

    ただし、実務ではIDが連続しているとは限りません。

    そのため、日付や連番などを使って比較対象を特定したり、ウィンドウ関数のLAGを利用したりする方法もあります。

    今回は、まず相関スカラサブクエリを使って、行間比較の基本的な仕組みを理解することに集中しましょう。

    9.「息をするように書ける」ための練習ポイント

    今回のSQLを自然に書けるようになるために、おすすめしたい練習方法があります。

    ① まずは比較対象の値だけを取得する

    最初からCASE式を完成させようとせず、まずは次のSQLを書いてみてください。

    SELECT
        a.id,
        a.title,
        a.length_minutes,
        (
            SELECT b.length_minutes
            FROM movies b
            WHERE b.id = a.id - 1
        ) AS previous_length
    FROM movies a;

    このSQLでは、現在の映画の上映時間と、比較対象の上映時間を並べて表示できます。

    まずは、相関サブクエリがどの行の値を返しているかを確認することが大切です。

    ② CASE式で増減を判定する

    比較対象の値が正しく取得できたら、CASE式を追加します。

    まずはupの判定だけを書き、その後downの判定を追加してみてもよいでしょう。

    ③ 比較する列を変えてみる

    上映時間ではなく、公開年のYearを比較するSQLに書き換えてみてください。

    比較対象の行を取得する考え方は同じです。

    比較する列が変わっても、相関サブクエリの仕組みを使いこなせるかを確認できます。

    ④ 比較対象のIDを変えてみる

    慣れてきたら、次のように条件を変更してみましょう。

    WHERE b.id = a.id - 2

    今度は、IDが2小さい映画との比較になります。

    このように条件を変えることで、相関サブクエリで比較対象の行を自由に指定する感覚を身につけられます。

    ⑤ 最後は何も見ずに書いてみる

    最終的には、完成したSQLを見ずに、次の流れを自分で組み立ててみてください。

    1. 現在の行を取得する

    2. 相関サブクエリで比較対象の行を取得する

    3. CASE式で大小を比較する

    4. 結果をup・down・-で表示する

    繰り返し練習することで、異なる行同士の比較が必要になったときにも、自然にSQLを書き始められるようになることを目指します。

    10.まとめ

    今回は、SQLBolt Lesson 12のmoviesテーブルを使い、相関スカラサブクエリによる行間比較を練習しました。

    今回のポイントは、次の3つです。

    • 相関サブクエリで、現在行とは異なる行の値を取得する

    • b.id = a.id - 1によって、比較対象の行をずらす

    • CASE式を使い、値の増減を判定する

    今回のSQLで特に意識してほしいのは、外側の行と内側の行が、どの条件で対応付けられているかという点です。

    この関係を理解できれば、比較対象を変えたり、比較する列を変えたりして、さまざまな行間比較に応用できます。

    ぜひ、SQLBoltで何度も書き直しながら、「息をするように書ける」状態を目指してみてください。


    講座のご案内・記事一覧

    SQLステップアップ講座|講座のご案内・記事一覧

    前回の記事

    第8回:SQLの実行順序

    次回の記事

    第10回:CASE式の条件に集約関数を使用する

    CASE式の条件に集約関数を組み合わせ、集計結果によって判定を切り替えるSQLを練習します。

     
     
    SQL初心者から、実務で使える中級者へ。SQLBoltを活用した16テーマの実践トレーニングと、製造業の業務データを題材にしたオリジナルSQL演習問題集(全20問)を公開中。SQLを「息をするように書ける」力を身につけることを目指します。

    あなたへのおすすめ