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

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

    今回のテーマは、CASE式を使った特性関数です。

    「ある条件を満たすデータが何件あるか」を集計したり、「条件を満たすデータが一定数以上存在するグループ」を抽出したりするときに、とても役立つ考え方です。

    今回は、CASE式を使った特性関数を、SELECT句とHAVING句の両方で使う練習をしていきます。

    この2つをセットで練習することで、単なる条件付き集計だけでなく、集合そのものに条件を設定する考え方も身につけていきましょう。

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

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

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

    このLessonで使用する employees テーブルは、職種や勤続年数などの情報が含まれており、今回の「CASE式を使った特性関数」の練習に適しています。

    なお、SQLBoltのLesson 11自体が、今回のSQLパターンを直接教える内容というわけではありません。

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

    今回使用するテーブル

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

    画像

    ※上の画像は、SQLBolt Lesson 11のEmployeesテーブルの一部を表示したものです。実際にはスクロールした先にもデータがあり、今回のSQLはテーブル内のすべてのデータを対象に実行します。

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

    • role:職種

    • years_employed:勤続年数

    今回は、この2つの列を使って、職種ごとの条件付き集計と、HAVING句によるグループの絞り込みを練習します。

    練習①:SELECT句で特性関数を使う

    まずは、次の問題を考えてみましょう。

    問題:職種ごとに、勤続年数が5年以上の従業員が何人いるかを求めてください。

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

    select role,
        sum(case when years_employed>=5 then 1 else 0 end) as ninzu
    from employees
    group by role

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

    実行結果

    画像

    Artistは4人、Engineerは1人、Managerは2人という結果になりました。

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

    まず、次のCASE式に注目してください。

    case when years_employed>=5 then 1 else 0 end

    このCASE式では、勤続年数が5年以上なら1、そうでなければ0を返します。

    つまり、条件を満たす場合に1、満たさない場合に0という「フラグ」を立てています。

    このように、ある条件を満たす場合に1、満たさない場合に0を返す関数を、特性関数といいます。

    次に、このCASE式をSUM関数で囲みます。

    sum(case when years_employed>=5 then 1 else 0 end)

    例えば、ある職種に5人の従業員がいて、CASE式の結果が次のようになったとします。

    • 1人目:1

    • 2人目:0

    • 3人目:1

    • 4人目:1

    • 5人目:0

    これらを合計すると3になります。

    つまり、条件を満たす従業員が3人いるということです。

    ここで覚えておきたいのが、

    「CASE式でフラグを立てて、SUMで数える」

    という考え方です。

    これが、特性関数を使った条件付き集計の基本になります。

    GROUP BYは部分集合を作る

    次に、group by role に注目してください。

    GROUP BYは、指定した列の値が同じ行をまとめて、グループを作ります。

    今回の場合は、従業員全体の集合を、職種ごとのグループに分けています。

    • Artistの集合

    • Engineerの集合

    • Managerの集合

    それぞれを、従業員全体から取り出した「部分集合」と考えてみてください。

    そして、それぞれの部分集合に対して、SUM関数で条件を満たす人数を集計しています。

    つまり、今回のSQLは次のように考えることができます。

    1. GROUP BYで職種ごとの部分集合を作る。

    2. CASE式で、勤続5年以上なら1、そうでなければ0を返す。

    3. SUMで1の数を合計する。

    4. 職種ごとの人数を表示する。

    この流れを理解しておくと、次のHAVING句の練習も理解しやすくなります。

    練習②:HAVING句で特性関数を使う

    続いて、少し発展させてみましょう。

    問題:勤続年数が5年以上の従業員が、3人以上いる職種だけを抽出してください。

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

    select role
    from employees
    group by role
    having
        sum(case when years_employed>=5 then 1 else 0 end)>=3

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

    実行結果

    画像

    抽出されたのは、Artistだけでした。

    先ほどのSELECT句での集計結果を振り返ると、

    • Artist:4人

    • Engineer:1人

    • Manager:2人

    となっていました。

    このうち、勤続5年以上の従業員が3人以上いるのはArtistだけなので、今回の結果になります。

    WHEREとHAVINGの違いを理解する

    ここが、今回の重要なポイントです。

    WHEREは集合の「要素」の性質を調べる道具です。

    例えば、where years_employed>=5 と書けば、従業員一人ひとりについて、勤続年数が5年以上かどうかを判定します。

    一方で、

    HAVINGは「集合自身」の性質を調べる道具です。

    今回のHAVING句では、

    sum(case when years_employed>=5 then 1 else 0 end)>=3

    という条件を設定しています。

    これは、職種ごとの集合について、

    「勤続5年以上の従業員が3人以上含まれているか?」

    を判定しているわけです。

    個々の従業員を調べているのではなく、職種ごとに作られた集合全体の性質を調べているという点が重要です。

    CASE式を使えば、集合に複雑な条件を設定できる

    ここまでの考え方を整理してみましょう。

    1. GROUP BYで部分集合を作る。

    2. CASE式で、各要素が条件を満たすかどうかを1と0で表す。

    3. SUMで、条件を満たす要素の数を求める。

    4. HAVINGで、集合自身が条件を満たすかどうかを判定する。

    つまり、CASE式で特性関数を表現することで、集合に対して、さまざまな条件を設定できるようになります。

    今回の例では「勤続5年以上の従業員が3人以上」という条件でしたが、CASE式の条件を変えることで、より複雑な判定にも応用できます。

    この考え方は、SQLで集合を扱ううえで非常に重要です。

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

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

    • 取引先ごとに、納期遅延している発注が何件あるかを集計する。

    • 担当者ごとに、未完了の案件が3件以上ある人を抽出する。

    • 品目ごとに、不良判定となった検査結果が一定件数以上あるものを抽出する。

    どれも、GROUP BYでグループを作り、CASE式で条件を満たすデータにフラグを立て、SUMで集計するという考え方です。

    このパターンを身につけておけば、実務での集計や抽出条件の幅が大きく広がります。

    今回のまとめ

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

    ① GROUP BYは部分集合を作る。

    指定した列の値によって行をグループ化し、それぞれの集合に対して集約計算を行います。

    ② CASE式で特性関数を表現すれば、集合に複雑な条件を設定できる。

    条件を満たせば1、満たさなければ0というフラグを立て、SUMで合計することで、条件に合うデータを数えられます。

    ③ WHEREは集合の「要素」、HAVINGは「集合自身」の性質を調べる。

    個々の行を判定するのか、グループ全体を判定するのか。この違いを意識することが大切です。

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

    SQLを、息をするように書けるように。

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


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

    講座のご案内・記事一覧

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

    前回の記事

    第3回:NOT EXISTSを使った非存在判定

    次回の記事

    第5回:相関サブクエリ

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


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

    あなたへのおすすめ