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

第7回:CASE式を使った行から列への展開

    SQLを書くとき、「集計結果を項目ごとに横並びで表示したい」と思ったことはありませんか?

    例えば、部署別・商品区分別・担当者別の集計結果を、1行の中にそれぞれ独立した列として表示したい場合です。

    そんなときに便利なのが、集約関数の中にCASE式を組み込む書き方です。

    今回は、SUM関数とCASE式を組み合わせて、縦方向に並んでいるデータを横方向の集計列に展開する方法を練習します。

    この書き方は、私自身、製造業の社内SEとしてAccessなどの帳票を作成する際にも役立ってきたものです。

    SQLの書き方だけでなく、実務でどのような場面に使えるのかも紹介します。


    1.今回のテーマ

    CASE式を使った行から列への展開

    通常、GROUP BYを使って集計すると、グループごとの結果が縦方向に並びます。

    一方、SUM関数の中にCASE式を組み込むと、条件ごとの集計結果をそれぞれ独立した列として横並びに表示できます。

    今回身につけたいのは、次の考え方です。

    「CASE式で集計対象を振り分け、SUM関数で列ごとに集計する」

    このパターンを覚えると、帳票や集計表に必要なデータをSQL側で作りやすくなります。

    2.練習に使用するSQLBolt

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

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

    このLessonに登場するemployeesテーブルを使います。

    なお、SQLBoltのLesson 11自体が、今回の「CASE式を使った横方向への展開」を直接教える教材というわけではありません。

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

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

    3.使用するテーブル

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

    主な列は次のとおりです。

    • Role:職種(Engineer、Artist、Manager)

    • Name:従業員名

    • Building:所属する建物

    • Years_employed:勤続年数

    テーブルには13人の従業員データが登録されています。

    画像

    今回は、このうちRoleとYears_employedの2列を使います。

    4.今回の練習問題

    Roleごとに勤続年数(Years_employed)の合計を求め、その結果を1行の中に横並びで表示してください。

    出力する列は、次の3つです。

    • Sum_Engineer:Engineerの勤続年数合計

    • Sum_Artist:Artistの勤続年数合計

    • Sum_Manager:Managerの勤続年数合計

    ここでのポイントは、Roleごとに3行を出力するのではなく、3つの集計結果を1行の中に並べることです。

    まずは、自分ならどのようなSQLを書くか考えてみてください。

    5.解答SQL

    SELECT
        SUM(CASE WHEN role = 'Engineer' THEN years_employed ELSE 0 END) AS Sum_Engineer,
        SUM(CASE WHEN role = 'Artist'   THEN years_employed ELSE 0 END) AS Sum_Artist,
        SUM(CASE WHEN role = 'Manager'  THEN years_employed ELSE 0 END) AS Sum_Manager
    FROM employees;

    実行結果

    画像
    • Sum_Engineer:17

    • Sum_Artist:30

    • Sum_Manager:18

    Role別の勤続年数合計が、1行の中に3つの列として表示されました。

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

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

    SUM(CASE WHEN role = 'Engineer' THEN years_employed ELSE 0 END)

    一見すると複雑に見えるかもしれませんが、内側のCASE式と外側のSUM関数を分けて考えると理解しやすくなります。

    ① CASE式で集計対象を振り分ける

    まず、内側のCASE式を見てみましょう。

    CASE
        WHEN role = 'Engineer' THEN years_employed
        ELSE 0
    END

    このCASE式は、各行について次の判定を行います。

    • RoleがEngineerなら、その従業員の勤続年数を返す

    • Engineer以外なら、0を返す

    例えば、Engineerの勤続年数が4年なら4を返します。

    一方、Artistの勤続年数が8年でも、Engineer用のCASE式では0を返します。

    つまり、Engineerだけを集計できるように、各行の値を振り分けているわけです。

    ② SUM関数で合計する

    次に、CASE式全体をSUM関数で囲みます。

    SUM(
        CASE WHEN role = 'Engineer'
             THEN years_employed
             ELSE 0
        END
    )

    CASE式によってEngineer以外の行は0になるため、SUM関数で合計するとEngineerの勤続年数だけが集計されます。

    今回のEngineerの勤続年数は、次のとおりです。

    • 4年

    • 2年

    • 6年

    • 4年

    • 1年

    したがって、合計は17年です。

    Artist、Managerについても同じ考え方で、それぞれ条件を変えたCASE式を作ります。

    ③ 集計式をSELECT句に横並びで配置する

    最後に、3つのSUM関数をSELECT句に並べます。

    SELECT
        SUM(CASE WHEN role = 'Engineer' THEN years_employed ELSE 0 END) AS Sum_Engineer,
        SUM(CASE WHEN role = 'Artist'   THEN years_employed ELSE 0 END) AS Sum_Artist,
        SUM(CASE WHEN role = 'Manager'  THEN years_employed ELSE 0 END) AS Sum_Manager
    FROM employees;

    今回はGROUP BYを指定していないため、テーブル全体が1つの集計対象になります。

    その結果、Engineer、Artist、Managerの合計値が、それぞれ独立した列として1行に表示されます。

    CASE式で対象を選び、SUM関数で集計し、SELECT句で横に並べる。

    これが今回のSQLの基本的な考え方です。

    7.GROUP BYによる縦方向の集計との違い

    ここで、通常のGROUP BYによる集計と比較してみましょう。

    例えば、次のSQLです。

    SELECT
        role,
        SUM(years_employed) AS Sum_Years
    FROM employees
    GROUP BY role;

    このSQLでは、Roleごとに集計結果が縦方向に表示されます。

    • Engineer:17

    • Artist:30

    • Manager:18

    それぞれのRoleが独立した行になるため、結果は3行です。

    一方、今回のCASE式を使ったSQLでは、結果が次のように横方向に並びます。

    • 1行目:Engineer=17、Artist=30、Manager=18

    つまり、集計したい内容は同じでも、出力するデータの形が異なるのです。

    通常のGROUP BYは、グループの種類が増えても行として結果を追加できるため、一覧表示などに向いています。

    一方、CASE式を使った横方向の集計は、出力する項目があらかじめ決まっている集計表や帳票などに向いています。

    なお、今回のように列を固定する方法では、新しいRoleが増えた場合、そのRole用の集計列を追加する必要があります。

    この違いも覚えておくと、実務で使い分けやすくなります。

    8.実務での活用例:Accessなどの帳票作成

    今回の書き方は、実務でも活躍する場面があります。

    私自身、製造業の社内SEとしてAccessなどの帳票を作成する際に、集計結果を項目別に横並びで表示したいという場面で、この考え方を活用してきました。

    例えば、製造業の集計帳票で、次のような項目を横並びに表示したい場合です。

    • 部品Aの入荷数量

    • 部品Bの入荷数量

    • 部品Cの入荷数量

    元データでは部品区分ごとに行が分かれていても、CASE式とSUM関数を組み合わせることで、それぞれの部品区分を独立した集計列として出力できます。

    また、部署別・担当者別・月別など、あらかじめ集計区分が決まっている場合にも応用できます。

    SQL側で帳票に必要な形のデータを用意できれば、Accessのフォームやレポート側で複雑な集計処理を行わずに済む場合があります。

    「帳票に必要な形を、SQLで先に作っておく」

    この考え方は、実務で覚えておくと便利です。

    なお、上記の部品別入荷数量は活用方法を説明するための例です。実際の業務では、対象データや帳票の仕様に合わせてSQLを組み立てます。

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

    今回のSQLも、完成形を暗記するだけではなく、考え方を自然に使えるようになることが目標です。

    おすすめの練習は、次の順番です。

    ① CASE式だけを理解する

    まずは、Roleによって勤続年数を返すか0を返すかを考えます。

    ② SUM関数を組み合わせる

    CASE式が返した値を合計すると、特定のRoleだけを集計できることを確認します。

    ③ 条件を変えて列を増やす

    Engineerだけでなく、Artist、Managerの集計式も自分で書いてみます。

    ④ 何も見ずにSQLを書き直す

    最後に、完成したSQLを見ずに、最初から書いてみてください。

    「条件ごとに横並びで集計したい」と思った瞬間に、

    SUM(CASE WHEN ... THEN ... ELSE 0 END)

    という形が自然に浮かぶようになれば、今回の練習は大きな成果です。

    10.まとめ

    今回は、CASE式とSUM関数を組み合わせて、行方向に並んでいるデータを列方向の集計結果に展開する方法を練習しました。

    重要なポイントは、次の3つです。

    • CASE式で条件に応じた値を返す

    • SUM関数で条件ごとの値を集計する

    • SELECT句に複数の集計式を並べて横方向に展開する

    通常のGROUP BYによる縦方向の集計と、CASE式を使った横方向の集計を使い分けられるようになると、SQLで作成できる集計表の幅が広がります。

    特にAccessなどの帳票作成では、覚えておくと役立つ書き方です。

    今回も、SQLBoltで繰り返し実行しながら、「息をするように書ける」状態を目指して練習してみてください。


    講座のご案内・記事一覧

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

    前回の記事

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

    次回の記事

    第8回:SQLの実行順序

    SQLの実行順序を意識することで、複雑なSQLも整理して考えられるようになることを目指します。


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

    あなたへのおすすめ