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

第11回:サブクエリを組み合わせた直近データとの差分比較

    1.今回のテーマ

    今回のテーマは、サブクエリを組み合わせて、直近データとの差分を求めることです。

    SQLでは、ある行の値を別の行の値と比較したい場面があります。

    例えば、次のようなケースです。

    • 今回の売上と前回の売上を比較したい

    • 今月の実績と直近の実績を比較したい

    • 最新の単価と、その直前の単価との差額を求めたい

    このような処理では、比較対象となる行をどのように取得するかが重要になります。

    第9回では、IDを1つずらすことで、別の行と比較するSQLを練習しました。

    今回は、さらに一歩進んで、現在行より前のデータの中から、最も直近のデータを探して比較する方法を練習します。

    そのために、サブクエリの中に、さらにサブクエリを記述します。

    特に重要なのは、最も内側のサブクエリから、メインクエリの列を参照するという書き方です。

    少し複雑に見えるかもしれませんが、処理を順番に分解すれば理解できます。

    今回も、SQLを「息をするように書ける」ことを目指して練習していきましょう。

    2.練習に使用するSQLBolt

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

    SQLBolt Lesson 1:SELECT queries 101

    SQLBoltのLesson 1では、映画情報を管理する movies テーブルを利用できます。

    今回も、このテーブルと登録済みのデータを使って、オリジナルのSQLを練習します。

    なお、SQLBoltのLesson 1自体が「サブクエリを組み合わせた直近データとの差分比較」を学ぶためのレッスンというわけではありません。

    今回のテーマを練習するうえで、テーブル構成やデータが使いやすいため、おすすめしています。

    SQLBoltの演習画面を開き、今回紹介するSQLを実際に入力して試してみてください。

    3.使用するテーブル

    今回使用するのは、movies テーブルです。

    映画のタイトル、監督、公開年、上映時間などを管理しています。

    主な列は以下のとおりです。

    • id:映画ID

    • title:映画タイトル

    • director:監督名

    • year:公開年

    • length_minutes:上映時間(分)

    今回の練習で使用するデータは、次のとおりです。

    画像

    今回注目するのは、**公開年(year)と上映時間(length_minutes)**の2つの列です。

    現在行の映画について、公開年がそれより前の映画の中から最も直近のものを探し、上映時間の差を求めます。

    4.今回の練習問題

    次の条件を満たすSQLを作成してください。

    練習問題

    moviesテーブルを使用して、各映画の上映時間と、その映画より前に公開された最も直近の映画の上映時間との差分を求めてください。

    その際、以下の条件を満たすものとします。

    1. moviesテーブルのすべての列を表示する。

    2. 各映画の公開年より前の公開年の中から、最大の年を取得する。

    3. 取得した公開年に該当する映画の上映時間を取得する。

    4. 現在行の上映時間から、直近年度の映画の上映時間を引く。

    5. 計算結果を diff_recent という列名で表示する。

    6. 比較対象が存在しない場合は、NULLを表示する。

    今回のポイントは、単にIDを1つずらすのではなく、公開年を基準に比較対象を探すことです。

    どのようなSQLになるか、まずは考えてみてください。

    5.解答SQL

    今回の解答SQLはこちらです。

    select a.*,
        a.length_minutes - (select b.length_minutes
                           from movies b
                           where b.year=(select max(c.year)  /*直近年度か?*/
                                        from movies c
                                        where c.year<a.year)
                           ) as diff_recent
    from movies a

    サブクエリが入れ子になっているため、少し複雑に見えるかもしれません。

    しかし、処理を順番に分けて考えると、理解しやすくなります。

    実行結果

    画像

    例えば、Finding Nemoは2003年公開、上映時間107分です。

    その直前の公開年は2001年で、該当する映画はMonsters, Inc.(92分)です。

    したがって、

    107 − 92 = 15

    となり、diff_recent には15が表示されます。

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

    ① メインクエリで現在行の映画を取得する

    まず、SQLの一番外側を確認しましょう。

    select a.*
    from movies a

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

    a.* は、moviesテーブルのすべての列を取得するという意味です。

    今回のSQLでは、この a が比較の基準となる現在行を表します。

    例えば、Finding Nemoの行を処理している場合、次のようになります。

    • a.title:Finding Nemo

    • a.year:2003

    • a.length_minutes:107

    この現在行の情報を基準に、比較対象となる映画を探していきます。

    ② 最も内側のサブクエリで直近年度を取得する

    次に、今回の最も重要な部分を確認しましょう。

    select max(c.year)
    from movies c
    where c.year<a.year

    ここでは、moviesテーブルに c という別名を付けています。

    注目してほしいのは、WHERE句です。

    where c.year<a.year

    c.year は、最も内側のサブクエリで参照しているmoviesテーブルの公開年です。

    一方、a.year は、メインクエリの現在行の公開年です。

    つまり、最も内側のサブクエリから、メインクエリの列を参照しています。

    このように、外側のクエリの値を参照するサブクエリを、相関サブクエリと呼びます。

    今回のSQLでは、現在行より前の公開年に絞り込んでいます。

    そして、

    max(c.year)

    によって、その中で最大の公開年を取得しています。

    例えば、現在行がFinding Nemo(2003年)の場合、2003年より前の公開年は次のとおりです。

    • 1995年

    • 1998年

    • 1999年

    • 2001年

    この中で最大の年は2001年です。

    したがって、最も内側のサブクエリは2001を返します。

    現在行の公開年より前の年に限定し、その中で最大の年を取得する。

    これが、今回のSQLの中心となる考え方です。

    ③ 外側のサブクエリで直近年度の上映時間を取得する

    続いて、その1つ外側のサブクエリを確認しましょう。

    select b.length_minutes
    from movies b
    where b.year=(select max(c.year)
                  from movies c
                  where c.year<a.year)

    ここでは、moviesテーブルに b という別名を付けています。

    このサブクエリの役割は、最も内側のサブクエリで取得した公開年に該当する映画の上映時間を取得することです。

    Finding Nemoを例にすると、最も内側のサブクエリは2001を返します。

    すると、考え方としては次のようになります。

    select b.length_minutes
    from movies b
    where b.year=2001

    2001年公開の映画はMonsters, Inc.です。

    上映時間は92分なので、このサブクエリは92を返します。

    つまり、ここまでで、現在行の映画と比較するための上映時間を取得できました。

    なお、今回のSQLは、該当する公開年の映画が1件だけ存在することを前提としています。

    同じ公開年の映画が複数存在する場合、サブクエリが複数行を返してしまう可能性があるため、注意が必要です。

    ④ 現在行の上映時間から差し引く

    最後に、メインクエリの計算部分を確認しましょう。

    a.length_minutes - (select b.length_minutes
                        from movies b
                        where b.year=(select max(c.year)
                                      from movies c
                                      where c.year<a.year)
                        ) as diff_recent

    ここでは、現在行の上映時間から、サブクエリで取得した上映時間を引いています。

    Finding Nemoの場合は、

    • 現在行の上映時間:107分

    • 直近年度の上映時間:92分

    なので、

    107 − 92 = 15

    となります。

    この計算結果を、diff_recent という列名で表示しています。

    ⑤ 比較対象が存在しない場合はNULLになる

    今回のデータで最も古い映画は、1995年公開のToy Storyです。

    この映画より前に公開された映画は、今回のテーブルには存在しません。

    そのため、

    select max(c.year)
    from movies c
    where c.year<1995

    の結果はNULLになります。

    すると、直近年度の上映時間も取得できません。

    最終的に、次のような計算になります。

    81 - NULL

    SQLでは、通常、数値とNULLを計算すると結果はNULLになります。

    そのため、Toy Storyの diff_recent はNULLになります。

    これはエラーではなく、比較対象が存在しないことによる自然な結果です。

    7.今回の最大のポイント:最も内側からメインクエリを参照する

    今回のSQLで最も重要なのは、次の部分です。

    select max(c.year)
    from movies c
    where c.year<a.year

    このサブクエリは、1つ外側の movies b ではなく、さらに外側にあるメインクエリの movies a を参照しています。

    SQL全体の構造を整理すると、次のようになります。

    メインクエリ(movies a)

    現在行の映画を取得する。

    ↓

    外側のサブクエリ(movies b)

    比較対象となる映画の上映時間を取得する。

    ↓

    最も内側のサブクエリ(movies c)

    メインクエリの a.year を参照し、現在行より前の最大の公開年を取得する。

    ここで重要なのは、サブクエリが何段階になっていても、どのテーブルの列を参照しているかを正確に理解することです。

    今回のSQLでは、

    • a:現在行の映画

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

    • c:比較対象の公開年を探すための映画データ

    という役割になっています。

    特に、c.year<a.year の a.year を自然に書けるようになることが、今回の練習の大きな目標です。

    8.第9回の行間比較との違い

    第9回では、次のような条件を使用しました。

    where b.id=a.id-1

    これは、現在行のIDから1を引いたIDの行を比較対象とする方法でした。

    一方、今回は次の条件を使用しています。

    where b.year=(select max(c.year)
                  from movies c
                  where c.year<a.year)

    こちらは、現在行より前の公開年の中から、最大の公開年を取得する方法です。

    違いを整理すると、次のようになります。

    第9回

    • IDを1つずらして比較する

    • 比較対象のIDを直接計算する

    • IDが連続しているデータを前提とした練習

    第11回

    • 公開年を基準に直近のデータを探す

    • MAX関数で比較対象の年を取得する

    • サブクエリを入れ子にして比較対象を決定する

    つまり、第11回は、比較対象の行を探すために、もう1段階サブクエリを使用しているということです。

    この違いを意識すると、SQLの組み立て方の幅が広がります。

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

    今回のような考え方は、実務でもさまざまな場面で活用できます。

    例① 商品単価の前回との差額

    商品ごとに単価の変更履歴を管理している場合、現在の単価と直近の変更前の単価を比較できます。

    例えば、

    • 今回の単価:1,200円

    • 直近の変更前単価:1,100円

    • 差額:100円

    というような処理です。

    例② 設備の点検結果の比較

    設備ごとに点検結果を記録している場合、今回の測定値と直近の測定値との差を求めることができます。

    例えば、今回の温度が85℃、直近の測定値が80℃であれば、差分は5℃です。

    例③ 月次実績の直近データとの差分

    月次の実績データについて、現在月と直近の実績が存在する月を比較することもできます。

    途中にデータが存在しない月があっても、現在月より前の最大の年月を取得することで、直近の実績を探す考え方に応用できます。

    ただし、実務では商品コードや設備番号など、同じ対象のデータ同士を比較するための条件も必要になります。

    また、同じ年月に複数のデータが存在する場合は、比較対象を1件に特定するための工夫が必要です。

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

    今回のSQLは、いきなり全体を書こうとすると複雑に感じるかもしれません。

    そこで、次の順番で組み立てる練習がおすすめです。

    ステップ1:メインクエリを書く

    まずは、moviesテーブルのすべての列を取得します。

    select a.*
    from movies a

    ステップ2:現在行より前の最大の公開年を取得する

    次に、相関サブクエリを使用して、直近の公開年を取得します。

    select a.*,
        (select max(c.year)
         from movies c
         where c.year<a.year) as recent_year
    from movies a

    ここでは、c.year<a.year を自然に書けるようになることを意識してください。

    ステップ3:直近年度の上映時間を取得する

    次に、サブクエリを入れ子にして、直近年度の上映時間を取得します。

    select a.*,
        (select b.length_minutes
         from movies b
         where b.year=(select max(c.year)
                       from movies c
                       where c.year<a.year)
        ) as recent_length
    from movies a

    この段階では、まだ差分を計算せず、比較対象の上映時間が正しく取得できているか確認します。

    ステップ4:差分を計算する

    最後に、現在行の上映時間から、取得した上映時間を引きます。

    select a.*,
        a.length_minutes - (select b.length_minutes
                           from movies b
                           where b.year=(select max(c.year)  /*直近年度か?*/
                                        from movies c
                                        where c.year<a.year)
                           ) as diff_recent
    from movies a

    これで完成です。

    今回の練習で意識したいのは、単にSQLを暗記することではありません。

    「直近の年を探したいからMAX」「現在行より前に限定したいから相関条件」「その年の値を取得したいからサブクエリ」「最後に差分を計算する」

    このように、やりたいことから必要なSQLを自然に組み立てられるようになることが目標です。

    11.まとめ

    今回は、サブクエリを組み合わせて、直近データとの差分を求めるSQLを練習しました。

    今回のポイントを振り返ります。

    • 相関サブクエリでは、外側のクエリの列を参照できる。

    • サブクエリの中に、さらにサブクエリを記述できる。

    • MAX関数を使うことで、現在行より前の最大の公開年を取得できる。

    • 取得した公開年を使って、比較対象の上映時間を取得できる。

    • 現在行の値から比較対象の値を引くことで、差分を求められる。

    • 比較対象が存在しない場合、差分はNULLになる。

    • 同じ公開年のデータが複数存在する場合は、比較対象を1件に特定する工夫が必要になる。

    今回のSQLは、第9回の行間比較から一歩進んだ内容です。

    特に、最も内側のサブクエリからメインクエリを参照する書き方は、ぜひ繰り返し練習して身につけてください。

    SQLを複数の構文に分けて理解し、それらを自然に組み合わせられるようになると、実務で対応できる処理の幅も広がります。

    今回のパターンも、ぜひ自分のSQLの引き出しに加えていきましょう。


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

    前回の記事

    第10回:CASE式の条件に集約関数を組み合わせる

    次回の記事

    第12回:累計和を求めるSQL

    次回は、データを順番に集計し、累計値を求めるSQLを練習します。

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

    あなたへのおすすめ