SQL 小計を利用する

Oracle

何かの統計システムなどを作っていると、よく複数項目で小計をとる場面にであう。これを実現するためにGROUP BY句を使用すると、小計単位でSQLを発行しなければならない。また、小計でソートを行いたいときにアプリケーション側で制御するのも面倒だ。OVER関数を利用すれば、これらをひとつのSQLで実現できる。

環境

  • Oracle10g R2

サンプル

例えば以下のようなテーブル「T_SHAIN」があるとする。

JIGYOUSHO_CD BUSHO_CD SHAIN_CD
1 101 50001
1 101 50002
1 102 50003
1 102 50004
1 101 50005
1 101 50006
1 101 50007
1 102 50008
1 102 50009
2 201 50010
3 301 50011
1 101 50012
2 202 50013
2 202 50014
2 202 50015
2 202 50016
2 201 50017
1 101 50018
2 202 50019
1 102 50020

このテーブルを使って「事業所単位」「部署単位」で社員数を集計し、結果を社員数の多い事業所順＆社員数の少ない部署順で取得したい。

GROUP BY句を使用する場合
　SELECT
　　　JIGYOUSHO_CD,
　　　COUNT(SHAIN_CD) AS TOTAL_JIGYOUSHO
　FROM
　　　T_SHAIN
　GROUP BY
　　　JIGYOUSHO_CD
　ORDER BY
　　　TOTAL_JIGYOUSHO DESC
JIGYOUSHO_CD TOTAL_JIGYOUSHO
1 12
2 7
3 1 
　SELECT
　　　JIGYOUSHO_CD,
　　　BUSHO_CD,
　　　COUNT(SHAIN_CD) AS TOTAL_BUSHO
　FROM
　　　T_SHAIN
　GROUP BY
　　　JIGYOUSHO_CD,
　　　BUSHO_CD
　ORDER BY
　　　TOTAL_BUSHO
JIGYOUSHO_CD BUSHO_CD TOTAL_BUSHO
3 301 1
2 201 2
2 202 5
1 102 5
1 101 7

そしてこれらの取得結果をfor文でまわしながら、事業所コードで合致させ、さらに事業所全体の社員数が多く、かつ部署の社員数が少ない順にソート…となるとコーディングもややこしくなる。

OVER関数を使用する場合
　SELECT DISTINCT
　　　JIGYOUSHO_CD,
　　　BUSHO_CD,
　　　COUNT(SHAIN_CD) OVER (PARTITION BY JIGYOUSHO_CD) AS TOTAL_JIGYOUSHO,
　　　COUNT(SHAIN_CD) OVER (PARTITION BY JIGYOUSHO_CD,BUSHO_CD) AS TOTAL_BUSHO
　FROM
　　　T_SHAIN
　ORDER BY
　　　TOTAL_JIGYOUSHO DESC,
　　　TOTAL_BUSHO
JIGYOUSHO_CD BUSHO_CD TOTAL_JIGYOUSHO TOTAL_BUSHO
1 102 12 5
1 101 12 7
2 201 7 2
2 202 7 5
3 301 1 1

OVER関数を使用すると、PARTITION BY句でGROUP BY句と同じように集計関数に対する集合範囲を定義することができる。これはSQL文で複数指定することが可能であるため、事業所単位と部署単位を同時に取得できる。
さらに取得フィールド名として定義すれば、ORDER BY句に指定することができるので、小計単位でソート順を自由に組み合わせられる。

説明


サンプルではCOUNT関数で社員数を求めたが、SUM関数などの集計関数ならば同様に利用することができる。これにより、例えば「営業社員数が少ないのに売上額が高い支社」「生産数が少ないのに不良品数が多い工場」などを簡易に計上できる。