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

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

    1.今回のテーマ

    今回のテーマは、CASE式の条件に集約関数を組み合わせることです。

    SQLでは、COUNTやSUMなどの集約関数を使って、データの件数や合計を求めることができます。

    また、CASE式を使えば、条件によって表示する値を切り替えることができます。

    では、この2つを組み合わせて、集約した結果によって表示内容を切り替えることはできるでしょうか?

    もちろん、できます。

    例えば、社員が登録されていない建物について、単に人数を「0」と表示するだけでなく、「No Employees」というメッセージを表示することも可能です。

    今回は、このような処理を実現するSQLを練習します。

    さらに、CASE式と集約関数だけでなく、LEFT JOIN、複数列によるGROUP BY、COUNT(*)とCOUNT(列名)の違いなども組み合わせます。

    複数のSQLの知識を、1本のSQLとして自然に組み立てられるようになること。

    これが今回の練習の目的です。

    2.練習に使用するSQLBolt

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

    SQLBolt Lesson 7:OUTER JOINs

    SQLBoltのLesson 7では、建物を管理する buildings テーブルと、社員情報を管理する employees テーブルを利用できます。

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

    なお、SQLBoltのLesson 7自体が「CASE式と集約関数の組み合わせ」を学ぶためのレッスンというわけではありません。

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

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

    3.使用するテーブル

    今回使用するテーブルは、次の2つです。

    buildingsテーブル

    建物の情報を管理するテーブルです。

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

    • building_name:建物名

    • capacity:収容人数

    登録されているデータは、次の4件です。

    画像

    employeesテーブル

    社員の情報を管理するテーブルです。

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

    • role:役職

    • name:社員名

    • building:所属する建物

    • years_employed:勤続年数

    例えば、次のようなデータが登録されています。

    画像

    今回のポイントは、建物は登録されていても、その建物に所属する社員が存在するとは限らないということです。

    このようなデータを扱う際に、LEFT JOINとCOUNTの違いが重要になります。

    4.今回の練習問題

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

    練習問題

    buildingsテーブルとemployeesテーブルを使用し、建物名と役職の組み合わせごとに社員数を集計してください。

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

    1. 社員が存在しない建物も検索結果に表示する。

    2. 建物名と役職の組み合わせごとに集約する。

    3. COUNT(*) と COUNT(b.role) の両方を表示する。

    4. COUNT(b.role) が0の場合は、「No Employees」と表示する。

    5. それ以外の場合は、NULLを表示する。

    今回の練習では、単に集計結果を求めるだけでなく、集約関数の結果をCASE式の条件に直接使用することが重要です。

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

    5.解答SQL

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

    select a.building_name, b.role, count(*), count(b.role),
        case when count(b.role)=0 then 'No Employees' else null end as EmpChk
    from buildings a
    left join employees b on a.building_name=b.building
    group by a.building_name, b.role

    一見すると短いSQLですが、さまざまな要素が組み合わされています。

    それぞれの役割を順番に確認していきましょう。

    実行結果

    今回の実行結果では、次のようになります。

    画像

    特に注目してほしいのは、1wと2eの結果です。

    社員が存在しない建物でも、COUNT(*)は1、COUNT(b.role)は0になっています。

    なぜ、このような違いが生まれるのでしょうか?

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

    ① LEFT JOINで社員が存在しない建物も残す

    まず、FROM句とJOIN句を確認しましょう。

    from buildings a
    left join employees b on a.building_name=b.building

    ここでは、buildingsテーブルを基準に、employeesテーブルをLEFT JOINしています。

    LEFT JOINは、左側のテーブルの行をすべて残す結合方法です。

    そのため、社員が存在しない建物であっても、buildingsテーブルに登録されていれば検索結果に残ります。

    今回のデータでは、1wと2eに所属する社員が存在しません。

    しかし、LEFT JOINを使用しているため、この2つの建物も結果に表示されます。

    社員が存在しない場合、employeesテーブル側の列にはNULLが入ります。

    例えば、1wについては次のようなイメージです。

    • building_name:1w

    • role:NULL

    • name:NULL

    この結合相手が存在しない場合のNULLが、後ほどCOUNTの結果に影響します。

    ② GROUP BYで2つの列を組み合わせて集約する

    次に、GROUP BY句を確認しましょう。

    group by a.building_name, b.role

    今回のSQLでは、建物名と役職の2つの列を指定しています。

    これは、建物名と役職の組み合わせごとにグループを作るという意味です。

    例えば、建物1eには、EngineerとManagerが所属しています。

    この場合、建物1eの社員をまとめて1つのグループにするのではなく、次のように分けて集計します。

    • 1e/Engineer:5人

    • 1e/Manager:2人

    同様に、建物2wも次のように分かれます。

    • 2w/Artist:5人

    • 2w/Manager:1人

    このように、GROUP BYでは複数の列を指定できます。

    実務でも、部署別・役職別、商品別・月別、顧客別・担当者別など、複数の条件を組み合わせた集計は頻繁に登場します。

    複数列のGROUP BYを、迷わず書けるようにしておきましょう。

    ③ COUNT(*)とCOUNT(列名)の違い

    ここは、今回の練習で特に重要なポイントです。

    SQLには、件数を数えるためのCOUNT関数があります。

    しかし、次の2つは同じ意味ではありません。

    count(*)
    count(b.role)

    COUNT(*)

    グループ内の行数を数えます。

    NULLを含む行であっても、行として存在していればカウントします。

    COUNT(b.role)

    指定した列の値がNULLではない行を数えます。

    つまり、b.role がNULLの場合、その行はカウントされません。

    今回のデータで考えてみましょう。

    建物1wには社員が存在しません。

    しかし、LEFT JOINによって、buildingsテーブル側の1wの行は結果に残ります。

    その際、employeesテーブル側の列はNULLになります。

    この状態で集計すると、次のようになります。

    • COUNT(*):1

    • COUNT(b.role):0

    COUNT(*)は、LEFT JOINによって残った1行を数えます。

    一方、COUNT(b.role)は、roleの値がNULLなのでカウントしません。

    この違いを理解することが、社員が存在しない建物を判定するための重要なポイントになります。

    なお、COUNT(列名)はあくまで指定列のNULL以外の値を数える関数です。

    今回のデータでは社員の役職が登録されているため、COUNT(b.role)を社員数の判定に利用できます。

    実務では、NULLにならない社員IDなどを指定する方法も考えられます。

    ④ CASE式の条件にCOUNTを直接書く

    いよいよ、今回の中心となる部分です。

    case when count(b.role)=0 then 'No Employees' else null end as EmpChk

    通常、CASE式は次のように使用します。

    case
        when 条件 then 値
        else 値
    end

    今回のSQLでは、このWHENの条件にCOUNT関数を直接使用しています。

    when count(b.role)=0

    これは、グループごとに集計したCOUNT(b.role)の結果が0かどうかを判定しています。

    COUNT(b.role)が0であれば、社員が存在しない建物と判断し、「No Employees」を表示します。

    それ以外の場合はNULLを表示します。

    例えば、建物1wでは次のようになります。

    • COUNT(b.role) = 0

    • CASE式の条件が成立する

    • EmpChkに「No Employees」を表示する

    一方、建物1eのEngineerでは次のようになります。

    • COUNT(b.role) = 5

    • CASE式の条件が成立しない

    • EmpChkにNULLを表示する

    このように、集約関数の結果をCASE式の条件に組み込むことで、集計結果に応じた判定が可能になります。

    ⑤ なぜWHERE句ではなくCASE式で判定するのか

    ここで、もう一歩理解を深めてみましょう。

    SQLには、WHERE句やHAVING句など、条件を指定するための構文もあります。

    では、今回のようにCOUNTの結果を判定したい場合、なぜCASE式を使用しているのでしょうか?

    それは、行を絞り込むのではなく、判定結果を新しい列として表示したいからです。

    WHERE句は、主に集約前の行を絞り込むために使用します。

    HAVING句は、集約したグループを条件によって絞り込むために使用します。

    一方、今回のCASE式は、集約した結果をもとに表示内容を切り替えています。

    つまり、目的が異なります。

    • WHERE:集約前の行を絞り込む

    • HAVING:集約後のグループを絞り込む

    • CASE:条件に応じて表示する値を切り替える

    この違いを理解しておくと、実務でもSQLを組み立てやすくなります。

    7.今回の最大のポイント:集約関数をCASE式の条件に使う

    今回のSQLで最も覚えてほしいのは、次の書き方です。

    case
        when count(b.role)=0 then 'No Employees'
        else null
    end

    CASE式の条件には、単純な列の比較だけでなく、COUNTなどの集約関数を使った条件も記述できるということです。

    例えば、次のような考え方にも応用できます。

    • COUNTが0なら「未登録」

    • COUNTが10以上なら「人数が多い」

    • SUMが一定金額以上なら「目標達成」

    • AVGが基準値より低ければ「要確認」

    もちろん、実際にどのような条件を使うかは、業務の目的によって異なります。

    大切なのは、集約結果を単に数値として表示するだけでなく、その数値を条件として利用できるという発想を身につけることです。

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

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

    例① 部署別・役職別の人数集計

    社員情報を部署別・役職別に集約し、それぞれの人数を確認します。

    部署に所属する社員が存在しない場合には、「未登録」などのメッセージを表示できます。

    例② 担当者が登録されていない案件の確認

    案件マスタと担当者情報をLEFT JOINし、担当者が登録されていない案件を判定します。

    単に担当者数を数えるだけでなく、担当者が存在しない案件に「担当者未設定」と表示することも可能です。

    例③ 商品別・月別の売上集計

    商品と売上データを組み合わせ、商品別・月別に売上件数を集計します。

    売上が存在しない商品や月について、集計結果をもとに「売上なし」と表示するような処理にも応用できます。

    このように、LEFT JOINによって対象を残し、GROUP BYで集約し、CASE式で判定するという組み合わせは、実務で役立つSQLパターンの1つです。

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

    今回のSQLは、次の順番で組み立てる練習がおすすめです。

    ステップ1:LEFT JOINを書く

    まずは、buildingsとemployeesを結合します。

    select *
    from buildings a
    left join employees b on a.building_name=b.building

    社員が存在しない建物も残っていることを確認しましょう。

    ステップ2:GROUP BYで集約する

    次に、建物名と役職の組み合わせごとに集約します。

    select a.building_name, b.role, count(*)
    from buildings a
    left join employees b on a.building_name=b.building
    group by a.building_name, b.role

    ステップ3:COUNT(b.role)を追加する

    select a.building_name, b.role, count(*), count(b.role)
    from buildings a
    left join employees b on a.building_name=b.building
    group by a.building_name, b.role

    ここでは、社員が存在しない建物のCOUNT(*)とCOUNT(b.role)の違いを確認してください。

    ステップ4:CASE式を追加する

    最後に、COUNT(b.role)が0の場合の判定を追加します。

    select a.building_name, b.role, count(*), count(b.role),
        case when count(b.role)=0 then 'No Employees' else null end as EmpChk
    from buildings a
    left join employees b on a.building_name=b.building
    group by a.building_name, b.role

    このSQLを、何も見ずに自然に書けるようになるまで繰り返してみましょう。

    今回の練習で意識したいのは、構文を1つずつ覚えることだけではありません。

    「社員が存在しない建物も残したいからLEFT JOIN」「建物と役職ごとに集計したいからGROUP BY」「社員がいないことを判定したいからCOUNTとCASE」

    このように、やりたいことから必要な構文を自然に選べるようになることが目標です。

    10.まとめ

    今回は、CASE式の条件に集約関数を組み合わせる方法を練習しました。

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

    • LEFT JOINを使用すると、結合相手が存在しない行も残せる。

    • GROUP BYには複数の列を指定できる。

    • COUNT(*)は行数を数える。

    • COUNT(列名)はNULL以外の値を数える。

    • CASE式のWHEN条件にCOUNTなどの集約関数を使用できる。

    • 複数の構文を組み合わせることで、集計と判定を1本のSQLで実現できる。

    今回のSQLは短いですが、実務でも重要な考え方が詰まっています。

    特に、LEFT JOIN・GROUP BY・COUNT・CASEを組み合わせて自然に書けることは、SQLの実務力を高めるうえで大切です。

    ぜひ、SQLBoltのテーブルを使って繰り返し練習してみてください。

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


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

    前回の記事

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

    次回の記事

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

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

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

    あなたへのおすすめ