9.1.1 表をグループ化してデータを取り出す

GROUP BY句を指定して、表をグループ化します。

表をグループ化する

最初に、表をグループ化する方法について説明します。

例1

在庫表を製品名の値でグループ化します。

SELECT  …  FROM 在庫管理.在庫表  GROUP BY  製品名
             (B)                     (A)      (1)

(1) グループ化列

(A)～(1) GROUP BY句

(B)～(1) 表式

GROUP BY句によって導出される表を以下に示します。

図9.1 GROUP BY句で導出される表の例

表式の中のFROM句およびWHERE句によって導出された行の集まりをグループ化します。この例では、表式のFROM句には在庫表だけを指定しているので、FROM句の結果は在庫表と同じです。また、WHERE句は省略しているので、すべての行がグループ化の対象になります。

GROUP BY句: GROUP BY句にはグループ化のキーとする列名、またはデータ列値関数や数値関数を指定します。列名を指定した場合、その列を“グループ化列”、データ列値関数または数値関数を指定した場合、その関数を“グループ化関数”と呼びます。例1でグループ化列は製品名です。GROUP BY句の結果により導出される表は、グループ化列の値が同じ行同士を1つのグループとしたグループ表です。また、複数の列をグループ化列とする場合は、列名をコンマ(,)で区切って指定します。この場合、すべてのグループ化列の値が等しい行同士が1つのグループとなります。

グループ化した表からデータを取り出す

問合せ指定では、データを操作するための表を導出します。問合せ指定でグループ表から導出する表は、それぞれのグループを1行とした表です。GROUP BY句を指定した問合せ指定では、選択リストに指定できるのは、グループ化列、グループ化関数、および集合関数です。集合関数を使用することにより、それぞれのグループ内で、列の値の合計、平均、最大値、最小値、および行の数を求めることができます。グループ化列でない列を集合関数以外で指定するとエラーになります。例えば、例1の問合せ指定で、選択リストに在庫数量を指定したとき、製品名が“テレビ”の在庫数量として“85”、“90”および“0”のうちどの値とするかが決定できないからです。一方、これらの値の合計や平均は1つの値として決定できるので、集合関数は指定できます。また、“図9.1 GROUP BY句で導出される表の例”では倉庫番号の値は、それぞれのグループ内で一意となっていますが、この列についても選択リストに直接指定することはできません。データの変動によって、いつ一意でない値が発生するかわからないからです。“図9.1 GROUP BY句で導出される表の例”のデータでは、グループ化列に製品名と倉庫番号の2つの列を指定しておけば、倉庫番号の値を取り出すことができます。なお、選択リストに定数やホスト変数を指定することは可能です。

例2

例1で示したグループ表から、製品名と在庫数量の合計を求めます。

図9.2 グループ化した表からデータを取り出す例

注）集合関数の結果の列には名前がありません。

“図9.1 GROUP BY句で導出される表の例”から、選択リストにグループ化列だけを指定した結果が、問合せ指定で“DISTINCT”を指定したのと同じであることに気が付かれたと思います。例2で選択リストに製品名だけを指定すれば、冗長な行を取り除いた製品名を求めることができます。しかし、冗長な行を取り除くのが目的の場合には、GROUP BY句は使用せず、DISTINCTを指定した方が、SQL文の処理効率がよくなることがあります。

例2のように集合関数の結果と列名を取り出す場合には、表をグループ化することが必要です。選択リストに集合関数と列名を並べて指定し、かつGROUP BY句の指定がないとエラーとなります。エラーとなる例を以下に示します。

例3

集合関数の使用方法の誤りの例です。

SELECT 製品名, SUM(在庫数量) FROM 在庫管理.在庫表

図9.3 集合関数の使用方法の誤りの例

例3の結果の行数は、在庫数量の合計は1行となりますが、製品名はn行となります。したがって、この問合せ指定はエラーになります。

文字列の一部を使用して表をグループ化する

表のデータを列単位でグループ化する方法はこれまでに説明しました。列単位ではなく、文字列の一部を使用して表をグループ化するためには、GROUP BY句に文字部分列関数を指定します。指定例を以下に示します。なお、文字部分列関数の詳細については、“9.4 文字列のデータを操作する”を参照してください。

例4

顧客表の所属の都道府県名が同じ人でグループ化して、各所属の平均年齢を取り出します。

図9.4 文字列の一部を使用して表をグループ化する例

データを場合分けして表をグループ化する

列単位ではなく、列のデータを場合分けして、場合分けしたそれぞれの値ごとに表をグループ化するためには、GROUP BY句にCASE式を指定します。指定例を以下に示します。なお、CASE式の詳細については、“9.7 CASE式を使用してデータを操作する”を参照してください。

例5

小売履歴表を年代別にグループ化して、各年代の売上合計額を取り出します。

SELECT 年代,SUM(単価*数量) AS 売上合計額 FROM 小売履歴表

    GROUP BY CASE WHEN 年齢 < 20 THEN '10代以下'

                    WHEN 年齢 < 30 THEN '20代'

                    WHEN 年齢 < 40 THEN '30代'

                                   ELSE '40代以上' END AS 年代

図9.5 データを場合分けして表をグループ化する例

月ごとに表をグループ化する

DATE型の列の年の単位や月の単位でグループ化するためには、日時値関数を指定します。指定例を以下に示します。なお、日時値関数の詳細については、“9.5 日付のデータを操作する”を参照してください。

例6

小売履歴表の販売日を月ごとにグループ化して、売上合計額を取り出します。

  SELECT 売上月,SUM(単価*数量) AS 売上合計額 FROM  小売履歴表

      GROUP BY TRUNC_DATE(販売日,'MONTH') AS 売上月

図9.6 月ごとに表をグループ化する例