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

第13回:全称文を集合的に解く

    はじめに

    今回のテーマは、**「全称文を集合的に解く」**です。

    全称文とは、「すべての〇〇が条件を満たす」という条件のことです。

    例えば、

    • ある部署の社員が全員、指定された研修を修了している

    • ある注文に含まれる商品がすべて出荷済みである

    • ある担当者が受け持つ案件がすべて完了している

    といった条件です。

    第6回では、NOT EXISTSを二重に使って、このような全称条件を表現する方法を学びました。

    今回は、別のアプローチを使います。

    GROUP BYで集合を作り、その集合に含まれるすべての行が条件を満たしているかをHAVINGで判定する方法です。

    同じ「すべて」という条件でも、SQLの組み立て方によって、取得できる結果が変わります。

    その違いも含めて、理解していきましょう。

    今回使用するSQLBolt

    今回の演習には、SQLBoltのLesson 11に登場するEmployeesテーブルを使用します。

    SQLBoltのLesson 11が今回の全称条件を直接教えているわけではありません。

    Employeesテーブルには、社員の役職(Role)と勤続年数(Years_employed)があり、役職ごとにグループを作って条件を判定する演習に適しているため、利用しています。

    使用するテーブル

    Employeesテーブル

    画像

    主な列は次の4つです。

    • Role:役職

    • Name:社員名

    • Building:所属する建物

    • Years_employed:勤続年数

    今回は、Roleごとに社員をグループ化します。

    今回の問題

    「所属する社員全員の勤続年数が3の倍数であるRoleを取得してください」

    条件Pは、

    「Years_employedが3の倍数である」

    です。

    求めたいのは、そのRoleに所属する社員が、1人残らず条件Pを満たしている集合です。

    1人でも条件Pを満たさない社員がいれば、そのRoleは対象外になります。

    解答SQL

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

    実行結果

    画像

    取得されるRoleは、次の1つです。

    Manager

    なぜManagerだけが取得されるのでしょうか。

    SQLを順番に分解して確認していきます。

    SQLの考え方①:GROUP BYで集合を作る

    まず、次の部分です。

    group by role

    これにより、Employeesテーブルの13行が、Roleごとに3つの集合に分かれます。

    • Engineer:5人

    • Artist:5人

    • Manager:3人

    ここで重要なのは、社員1人ひとりを判定するのではなく、Roleという集合を判定することです。

    今回のSQLでは、集合の中にいる社員全員が条件Pを満たすかどうかを調べます。

    SQLの考え方②:CASEで条件を満たす人数を数える

    次に、この部分です。

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

    years_employed % 3 = 0 は、勤続年数を3で割った余りが0になる、つまり3の倍数であるという条件です。

    CASE式は、社員ごとに次の値を返します。

    • 条件Pを満たす社員:1

    • 条件Pを満たさない社員:0

    そしてSUM関数で、Roleごとにその値を合計します。

    つまり、この式が求めているのは、そのRoleの中で、条件Pを満たす社員の人数です。

    実際にManagerを確認してみましょう。

    • Scott K.:9年 → 1

    • Shirlee M.:3年 → 1

    • Daria O.:6年 → 1

    3人とも3の倍数なので、

    SUM = 1 + 1 + 1 = 3

    となります。

    一方、Engineerは次のようになります。

    • Becky A.:4年 → 0

    • Dan B.:2年 → 0

    • Sharon F.:6年 → 1

    • Dan M.:4年 → 0

    • Malcom S.:1年 → 0

    したがって、

    SUM = 0 + 0 + 1 + 0 + 0 = 1

    です。

    SQLの考え方③:COUNT(*)と比較する

    最後に、HAVING句全体を確認します。

    having
        sum(case when years_employed%3=0 then 1 else 0 end) = count(*)

    左辺は、条件Pを満たす人数。

    右辺のCOUNT(*)は、そのRoleに所属する全社員の人数です。

    この2つが等しいということは、どういうことでしょうか。

    例えばManagerの場合、

    • 条件Pを満たす人数:3人

    • 所属する全社員の人数:3人

    なので、3 = 3 が成立します。

    Engineerの場合は、

    • 条件Pを満たす人数:1人

    • 所属する全社員の人数:5人

    なので、1 = 5 は成立しません。

    Artistについても、条件Pを満たさない社員がいるため、成立しません。

    このように、

    条件を満たす人数 = 集合全体の人数

    という条件を使えば、

    「集合内のすべての要素が条件Pを満たす」

    という全称条件を表現できます。

    第6回との違い:結果として何を取得できるのか

    ここが、今回特に理解しておきたいポイントです。

    第6回では、NOT EXISTSを二重に使って、全称条件を表現しました。

    NOT EXISTSによる二重否定は、

    「条件を満たさないものが1つも存在しない」

    という考え方です。

    一方、今回の方法は、

    「条件を満たす件数が、集合全体の件数と一致する」

    という考え方です。

    どちらも、全称条件を表現するための方法です。

    ただし、取得する結果の粒度に違いがあります。

    第6回:NOT EXISTSによる二重否定

    NOT EXISTSを使った方法では、SQLの組み立て方によって、条件を満たす社員の行をそのまま取得できます。

    つまり、Roleだけでなく、社員名や勤続年数などの詳細情報も返せます。

    第13回:GROUP BYとHAVINGによる集合的な判定

    今回のSQLでは、Roleごとに集合を作って判定しています。

    そのため、結果として返されるのは、条件を満たした集合の名前であるRoleです。

    今回なら、Managerだけが返されます。

    Scott K.、Shirlee M.、Daria O.という個々の社員名は、このSQLの結果には表示されません。

    これは、GROUP BYによって集計単位がRoleになっているためです。

    もちろん、取得したRoleを使って元のEmployeesテーブルを再検索するなどすれば、個々の社員情報を取得することも可能です。

    つまり、GROUP BYを使うと個々の社員情報が絶対に取得できないわけではありません。

    今回のようにRole単位で集計して、そのRoleをSELECTしているSQLでは、結果が集合単位になる、ということです。

    この違いを理解しておくと、実務でSQLを書くときに役立ちます。

    実務ではどう使う?

    例えば、製造業の受注・出荷管理を考えてみましょう。

    ある受注番号に対して、複数の明細行が登録されているとします。

    それぞれの明細には、出荷状況が記録されています。

    このとき、

    「すべての明細が出荷済みになっている受注番号だけを抽出したい」

    という要求があったとします。

    この場合、受注番号ごとにGROUP BYし、

    having
        sum(case when 出荷状況 = '出荷済' then 1 else 0 end) = count(*)

    とすれば、全明細が出荷済みになっている受注番号を抽出できます。

    一方で、「該当する受注の明細をすべて一覧表示したい」という要求なら、抽出した受注番号を元の明細テーブルと結合する方法などが考えられます。

    このように、集合を探したいのか、それとも集合に属する個々の行を取得したいのかによって、SQLの組み立て方を考えることが大切です。

    今回のまとめ

    今回は、GROUP BYとHAVINGを組み合わせて、全称条件を集合的に解く方法を学びました。

    重要なのは、次の3点です。

    1. GROUP BYで、判定対象となる集合を作る。

    2. SUMとCASEで条件Pを満たす件数を数え、COUNT(*)と比較する。

    3. 集合単位で判定するため、今回のSQLでは結果も集合名(Role)になる。

    第6回のNOT EXISTSによる二重否定と比較することで、全称条件には複数の表現方法があること、そしてSQLの組み立て方によって取得できる情報が異なることを理解できたと思います。

    今回のSQLも、構造を見ただけで意味が自然に分かり、必要なときに**「息をするように書ける」**状態を目指して、繰り返し練習してみてください。


    SQLステップアップ講座の記事一覧はこちら

    前回の記事

    第12回:相関サブクエリを使った累計和

    次回の記事

    第14回:自己結合を段階的に理解する(CROSS JOIN・結合条件・DISTINCT)


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

    あなたへのおすすめ