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

第5回:相関サブクエリ

    今回のテーマは、相関サブクエリです。

    「カテゴリーごとに平均以上のデータを抽出したい」

    このような条件をSQLで表現するときに役立つ考え方です。

    今回は、職種ごとの平均勤続年数を求め、その平均以上の勤続年数を持つ従業員を抽出する練習をしていきます。

    ポイントは、メインクエリの1行ごとに、サブクエリがどのような条件で評価されるのかをイメージすることです。

    今回の練習におすすめのSQLBolt

    今回使用するのは、SQLBoltのLesson 11です。

    SQLBolt Lesson 11:Queries with aggregates (Pt. 2)

    このLessonで使用する employees テーブルには、従業員の職種や勤続年数などが登録されています。

    職種ごとの平均勤続年数を求め、各従業員の勤続年数と比較するという、今回の相関サブクエリの練習に適しています。

    なお、SQLBoltのLesson 11自体が、今回紹介する「相関サブクエリ」を直接教える内容というわけではありません。

    Lesson 11のテーブル構成やデータが、今回のSQLパターンを練習するのに使いやすいため、おすすめしています。

    今回使用するテーブル

    使用するのは、employees テーブルです。

    画像

    主に使用する列は次の2つです。

    • role:職種

    • years_employed:勤続年数

    今回は、職種が同じ従業員を一つのグループとして考え、そのグループの平均勤続年数と、個々の従業員の勤続年数を比較します。

    ※画像はEmployeesテーブルの一部を表示したものです。実際のSQLはテーブル内のすべてのデータを対象に実行します。

    今回の練習問題

    問題:職種(Role)ごとに、同じ職種の平均勤続年数以上の従業員を抽出してください。

    例えば、EngineerであればEngineer全員の平均勤続年数、ArtistであればArtist全員の平均勤続年数を基準にします。

    全従業員の平均ではなく、それぞれの従業員が所属する職種の平均と比較することがポイントです。

    今回使用するSQLはこちらです。

    select a.*
    from employees a
    where a.years_employed >=
        (select avg(b.years_employed)
         from employees b
         where b.role=a.role
        )

    SQLBoltのエディターに貼り付けて、実行してみてください。

    実行結果

    画像

    職種ごとの平均勤続年数以上の従業員が抽出されました。

    SQLの考え方を詳しく見てみましょう

    まず、外側のSELECT文に注目する

    今回のメインクエリは、次の部分です。

    select a.* from employees a

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

    この外側のクエリでは、Employeesテーブルの従業員を対象に、一人ひとりが条件を満たすかどうかを判定します。

    その判定に使うのが、WHERE句の中に書かれたサブクエリです。

    サブクエリで平均勤続年数を求める

    サブクエリでは、次の集約計算を行っています。

    avg(b.years_employed)

    AVGは平均値を求める集約関数です。

    ただし、今回求めたいのは、Employeesテーブル全体の平均勤続年数ではありません。

    外側で現在判定している従業員と、同じ職種の従業員だけの平均勤続年数です。

    そこで重要になるのが、次の条件です。

    where b.role=a.role

    ここで、

    • b.role:サブクエリ側の従業員の職種

    • a.role:メインクエリ側で現在判定している従業員の職種

    を表しています。

    サブクエリの中から、外側のクエリの列 a.role を参照しています。

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

    メインクエリの1行ごとの動きをイメージする

    ここが、今回最も大切なポイントです。

    相関サブクエリを理解するときは、メインクエリの行を一つずつ取り出し、それぞれに対してどのような判定が行われるのかを考えてみてください。

    ① Becky A.を判定する場合

    Becky A.の職種はEngineer、勤続年数は4年です。

    この行を判定するとき、外側の a.role は Engineer になります。

    そのため、サブクエリは、考え方として次のような内容になります。

    「Engineerの従業員だけを対象に、平均勤続年数を求める」

    そして、Becky A.の勤続年数4年が、その平均以上であれば抽出されます。

    ② Sharon F.を判定する場合

    Sharon F.もEngineerですが、勤続年数は6年です。

    この場合も、比較する相手はEngineerの平均勤続年数です。

    つまり、同じEngineerの従業員同士では、比較に使う平均値は共通です。

    ③ Sherman D.を判定する場合

    次に、ArtistのSherman D.を考えてみましょう。

    Sherman D.の勤続年数は8年です。

    このとき、外側の a.role は Artist になります。

    したがって、サブクエリの集計対象もArtistに変わります。

    「Artistの従業員だけを対象に、平均勤続年数を求める」

    そして、その平均とSherman D.の勤続年数8年を比較します。

    外側の行が変わると、サブクエリが参照する職種も変わる。

    これが相関サブクエリの重要な特徴です。

    ※ここで説明した「1行ずつサブクエリを評価する」という流れは、SQLを理解するための考え方です。実際のデータベースが必ず各行に対してサブクエリを個別実行するとは限りません。

    なぜ相関サブクエリが必要なのか?

    今回の問題を、もう一度整理してみましょう。

    求めたいのは、単に「勤続年数が5年以上の従業員」ではありません。

    **「その従業員が所属する職種の平均勤続年数以上であること」**です。

    つまり、比較に使う基準値が、従業員の職種によって変わります。

    通常のWHERE句で固定値と比較するだけでは、この条件を表現できません。

    そこで、サブクエリを使って比較する基準値を計算します。

    さらに、そのサブクエリを外側の職種と結びつけることで、従業員ごとに適切な平均値と比較できるようになります。

    このように、外側の行の値に応じて、内側のクエリの条件を変えられることが、相関サブクエリの強みです。

    相関サブクエリの注意点

    相関サブクエリは便利ですが、注意点もあります。

    ① パフォーマンスに注意する

    相関サブクエリは、書き方やデータ量、データベースの実行計画によっては、処理が遅くなることがあります。

    特に、大量のデータを扱う場合は注意が必要です。

    ただし、相関サブクエリだから必ず遅いというわけではありません。

    データベースによっては、効率のよい処理に最適化される場合もあります。

    ② 可読性に注意する

    外側のクエリと内側のクエリが関連しているため、慣れないうちはSQLの処理内容を理解しにくいことがあります。

    特に、サブクエリが複雑になったり、入れ子が深くなったりすると、読みづらくなりがちです。

    そのため、a と b のようなテーブルの別名が、どちらのクエリを表しているのかを意識することが大切です。

    なお、今回の問題は、集約結果とのJOINやウィンドウ関数を使って表現する方法もあります。

    それでも、相関サブクエリで考えられるようになることには、十分な学習価値があります。

    実務ではどんな場面で使う?

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

    • 品目ごとに、平均発注数量以上の発注データを抽出する。

    • 取引先ごとに、平均購入金額以上の取引を抽出する。

    • 担当者ごとに、平均処理日数以上かかった案件を抽出する。

    いずれも、カテゴリーごとに計算した値を、個々のデータと比較するという考え方です。

    実務では、データをグループごとの基準と比較したい場面が出てきます。

    そんなときに、今回のSQLパターンを思い出せるようにしておきましょう。

    今回のまとめ

    今回覚えてほしいポイントは、次の3つです。

    ① 相関サブクエリは、外側のクエリの値を参照する。

    今回の b.role=a.role のように、外側と内側のクエリを関連付けることで、行に応じた条件を設定できます。

    ② メインクエリの1行ごとの動きをイメージする。

    現在判定している行がEngineerならEngineerの平均、ArtistならArtistの平均を求める、という流れを頭の中で追えるようにしましょう。

    ③ パフォーマンスと可読性に注意しながら使う。

    相関サブクエリは便利なSQLパターンですが、実務では処理効率や読みやすさも意識する必要があります。

    まずは今回のSQLを、何も見ずに書けるようになるまで繰り返してみてください。

    特に、b.role=a.role の意味を理解し、外側の行と内側の集合の関係を自然にイメージできることが大切です。

    一つひとつのSQLパターンを、自分のものにしていきましょう。


    SQLステップアップ講座では、初級から中級へ進むためのSQLパターンを、全16回で紹介しています。

    講座のご案内・記事一覧

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

    前回の記事

    第4回:CASE式を使った特性関数

    次回の記事

    第6回:NOT EXISTSの二重否定による全称判定

    次回も、SQLBoltのテーブルを使いながら、実際にSQLを書いて練習していきます。


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

    あなたへのおすすめ