omachizura

SQLの基本的なことからスタートし、高度な検索・集計方法などの応用テクニックまでできるスキルを紹介します。


ROLLUP文で小計行・合計行を出力する

「社員マスタ」と「売上明細」というテーブルがあります。売上明細テーブルには、社員の売上情報が格納されています。

社員マスタ
社員コード社員名
1テスト社員1
2テスト社員2
3テスト社員3
売上明細
売上NO社員コード売上日売上金額
1120150401200
2120150402300
3220150408100
4220150501150
5320150505550

課題

社員ごとに、会社ごとの売上合計を表示し、社員ごとにすべての会社の小計行も出力したい、また最終行に、全社員の全会社の合計行も表示したい。

課題を読んだだけではイメージがわかないと思うので、今回は出力したい結果から示します。課題のSQL文は本ページの最後に記載してあります。

実行結果
社員名会社名売上金額
テスト社員1会社名1500
テスト社員1小計500
テスト社員2会社名1100
テスト社員2会社名2250
テスト社員2小計350
テスト社員3会社名2550
テスト社員3小計550
全社員合計1400

ROLLUPという文をGROUP BY句で指定すると、会社計と社員計の合計行を出力することができます。言葉で説明するのは難しいので、1ステップずつSQLを示しながら、上記結果を出力するSQLを作っていきます。

まずは小計行と合計行の出力は考えず、社員ごとかつ会社ごとに売上明細のデータを集計してみます。

SQLは下記のようになります。

SELECT MAX(T1.社員名) AS 社員名
  ,MAX(T3.会社名) AS 会社名
  ,SUM(T2.売上金額) AS 売上金額
FROM 社員マスタ AS T1
  LEFT JOIN 売上明細 AS T2
  ON T1.社員コード = T2.社員コード
  LEFT JOIN 会社マスタ AS T3
  ON T2.会社コード = T3.会社コード
GROUP BY T1.社員コード, T3.会社コード
実行結果
社員名会社名売上金額
テスト社員1会社名1500
テスト社員2会社名1100
テスト社員2会社名2250
テスト社員3会社名2550

次に、GROUP BY句の最後に、ROLLUP文を指定します。

わかりやすくするために、社員コードや会社コードなども出力します。

SQLは下記のようになります。

SELECT MAX(T1.社員名) AS 社員名
  ,MAX(T3.会社名) AS 会社名
  ,SUM(T2.売上金額) AS 売上金額
  ,T1.社員コード AS 社員コード
  ,T3.会社コード AS 会社コード
  ,GROUPING(T1.社員コード) AS 社員計
  ,GROUPING(T3.会社コード) AS 会社計
FROM 社員マスタ AS T1
  LEFT JOIN 売上明細 AS T2
  ON T1.社員コード = T2.社員コード
  LEFT JOIN 会社マスタ AS T3
  ON T2.会社コード = T3.会社コード
GROUP BY ROLLUP(T1.社員コード, T3.会社コード)
実行結果
社員名会社名売上金額社員コード会社コード社員計会社計
テスト社員1会社名15001100
テスト社員1会社名15001null01
テスト社員2会社名11002100
テスト社員2会社名22502200
テスト社員2会社名23502null01
テスト社員3会社名15503100
テスト社員3会社名15503null01
テスト社員3会社名21300nullnull11

紫色の行がROLLUP文によって出力された集計行です。

ROLLUP文で指定した集計単位の上位の集計単位で集計行が出力されます。今回の例だと、ROLLUP(T1.社員コード, T3.会社コード)なので、会社コードの上位の集計単位は社員コードで、社員コードの上位の集計単位はSQL全体になります。よって、社員コードが変わるごとに社員コードごとの集計行が出力され、SQLの最終行に表全体の集計行が出力されます。

集計対象が変わるだけで、集計方法は変わりません。集計行の社員名や会社名が上記のように表示されるのは、集計対象の中のMAX値を取得しているためです。売上金額は集計対象の値をSUMしているため、合計値として表示されています。

SELECT句でGROUPINGという関数を使用すれば、どの行が集計行かどうかがわかります。集計対象の列の場合は1が出力され、それ以外は0が出力されます。

CASE文でGROUPINGの値によって場合分けをすれば、課題の実行結果を得ることができます。

SQLは下記のようになります。

SELECT CASE WHEN GROUPING(T1.社員コード) = 1 THEN '全社員'
    ELSE MAX(T1.社員名) END AS 社員名
  ,CASE WHEN GROUPING(T1.社員コード) = 1 THEN '合計'
    GROUPING(T1.会社コード) = 1 THEN '小計'
    ELSE MAX(T3.会社名) END AS 会社名
  ,SUM(T2.売上金額) AS 売上金額
FROM 社員マスタ AS T1
  LEFT JOIN 売上明細 AS T2
  ON T1.社員コード = T2.社員コード
  LEFT JOIN 会社マスタ AS T3
  ON T2.会社コード = T3.会社コード
GROUP BY ROLLUP(T1.社員コード, T3.会社コード)

また、今回はたまたま綺麗な並び順で表示されましたがORDER BYを指定して、明示的に並び順を指定する場合は、下記のようになります。

SELECT CASE WHEN GROUPING(T1.社員コード) = 1 THEN '全社員'
    ELSE MAX(T1.社員名) END AS 社員名
  ,CASE WHEN GROUPING(T1.社員コード) = 1 THEN '合計'
    GROUPING(T1.会社コード) = 1 THEN '小計'
    ELSE MAX(T3.会社名) END AS 会社名
  ,SUM(T2.売上金額) AS 売上金額
FROM 社員マスタ AS T1
  LEFT JOIN 売上明細 AS T2
  ON T1.社員コード = T2.社員コード
  LEFT JOIN 会社マスタ AS T3
  ON T2.会社コード = T3.会社コード
GROUP BY ROLLUP(T1.社員コード, T3.会社コード)
ORDER BY T1.社員コード
  ,GROUPING(T1.社員コード) --同一の社員コードで通常行は0、合計行は1なので、合計行が同一社員の一番最後の行に出力される
  ,T1.会社コード
  ,GROUPING(T3.会社コード)





  • CASE文で効率よく集計する

    CASE文を使い、効率よく集計するテクニックを説明します。SUMで集計する際に集計したいデータの場合のみ、そのデータの売上金額を足し、それ以外は0を足すという方法で効率よく集計できます。


  • ROLLUP文で小計行・合計行を出力する

    ROLLUP文で小計行・合計行を出力する方法を説明します。ROLLUPという文をGROUP BY句で指定すると、小計行や合計行を出力することができます。


  • 副問い合わせを理解する

    副問い合わせを説明します。副問い合わせとはSQL文の中に入れ子でSQL文を指定することをいいます。


  • DISTINCT文で重複行を1行で表示する方法

    DISTINCT文で重複行を1行で表示する方法を説明します。SELECT句でSELECTのあとにDISTINCTを指定すると、重複した列は1行のみ出力するようになります。


  • WITH句を使い重複するSQL文をまとめる方法

    WITH句でVIEWを作成し重複するSQL文をまとめる方法を説明します。WITH句を使えば1つの副問い合わせ(SQL)を複数の箇所で使いまわすことができます。


  • ROWNUMで行番号を取得する際の注意点

    oracleで行番号を取得する際の基本的な考え方と注意点を紹介します。行番号はROWNUMで取得することができます。注意しなければならないのは、ORDER BYでソートする場合です。ORDER BYは...


  • OVER句の分析関数で効率よくデータを集計する

    分析関数を使用すれば、効率よく簡単にデータを集計することができます。集計関数COUNTの後にOVERを指定して、分析関数として集計します。OVERの中のPARTITION BYとORDER BYで分析...


  • PARTITION BYで効率よくデータを取得する

    PARTITION BYをうまく使用すれば、効率よく簡単にデータを集計だけでなく、取得することができます。PATITION BYで部署コード単位で集計することができるようになります。売上累計の降順で並...


  • IN句をEXISTS句に変換する方法

    IN句をEXISTS句に変換する方法を紹介します。IN句よりもEXISTS句のほうがパフォーマンスが良くなる場合が多いので、主にレスポンス対策としてこの書き換えを行うことが多いと思います。


  • チューニングでレスポンス改善に効果のあったポイント

    1回のSQLで一気にまとめて取得したほうがレスポンスは早いイメージがありますが、あまり大量のデータを扱うSQLを実行するとサーバーに負荷がかかってしまい、遅くなってしまうことがあります。