テクノロジー：SQLの基礎

記事一覧へ戻る

リレーショナル・データベースとSQLの基本に関するシリーズ記事のパート9

このシリーズ記事のパート8 "状況に合った型の選択"（Oracle Magazine、2012年11/12月）では、よく使用されるSQL日付関数を紹介し、問合せを利用して、日付を表す結果セット・データの表示を変更する方法について説明しました。また、SYSDATE関数と日付計算を紹介し、よりわかりやすい結果を得るために、それらを利用して結果セット・データを操作する方法についても説明しました。このシリーズ記事でこれまでに取り上げた関数はすべて、単一行の結果に基づいて動作するものです。集計関数（別名グループ関数）は複数行に基づいて動作します。集計関数を使用すれば、データベース内での格納方法とは異なる表示になるようにデータを操作できます。この記事では、比較的よく使用されるSQLグループ関数のほか、GROUP BY句とHAVING句について紹介します。

すべての行の合計

すべての集計関数は、データをグループ化して、最終的に単一値の結果を生成します。集計関数は複数行の値に基づいて動作するため、集計関数を使用すれば合計などのサマリー・データを生成できます。たとえば、"全従業員に支払われる年間給与分配額の合計は？"といった予算計画の疑問に回答できます。リスト1の問合せは、この疑問に回答するためのSUM集計関数の使用例を示しています。この問合せは、EMPLOYEE表のSALARY列についてすべての値を合計するもので、その結果の合計値は970000です。

コード・リスト1：EMPLOYEE表のSALARYについて、すべての値の合計を表示 

SQL> set feedback on  SQL> select SUM(salary) from employee;    SUM(SALARY)  —————————————————       970000    1 row selected.     

平均を出そうとする場合

集計関数を使用して回答できる業務上の疑問には、ほかにも“現在の全従業員の年間平均給与は？”というものがあります。リスト1の問合せと同様に、リスト2の問合せでもEMPLOYEE表のSALARY列に集計関数を適用しています。リスト2のAVG関数は、SALARY値を合計した後、その合計値を、SALARY値がNULLではない従業員レコード数で割ります。年間支払額合計の970000を従業員数10で割ると、年間給与の平均値は97000になります。

コード・リスト2：NULL以外のすべてのSALARY値を使用して給与の平均値を計算 

SQL> select AVG(salary) from employee;    AVG(SALARY)  —————————————————        97000    1 row selected.     

EMPLOYEE表には11個のレコードがありますが、リスト2の給与の平均値計算で対象となっているレコードは10個のみです。これは、AVG集計関数ではNULL値が無視されるためです（EMPLOYEE表では、従業員Lori DovichiのSALARY値がNULLです）。NULL値に対してNULL以外の値を代用するために、このシリーズ記事のパート7で紹介したNVL関数・コールを、AVG関数コール内にネストできます（リスト3を参照）。リスト3で返される給与の平均値はリスト2で返される平均値よりも小さくなります。Lori DovichiのNULLのSALARY値が0に置き換えられて、その他のNULL以外のSALARY値とともに評価されるためです。NULL値に対してNULL以外の値を代用する操作は、業務上の観点から意味がある場合に限り使用してください。

コード・リスト3：NULL値に対してNULL以外の値を代用 

SQL> set feedback on
SQL> select SUM(salary) from employee;

SUM(SALARY)
—————————————————
     970000

1 row selected. 

個数の管理

前回までのシリーズ記事で確認したとおり、SQL*Plusのset feedback onコマンドによって、問合せの条件を満たすレコード数が表示されます。この方法は、画面上に容易に表示できる少数のレコードがすぐに返されるような場合には有効です。しかし、数百、数千、あるいは数百万のレコードを評価する場合は不便です。結果セット内のすべてのレコードをデータベースからフェッチしてクライアントに返すまで待機する必要があるからです。もっと効率的な代替手段として、COUNT集計関数を利用できます（リスト4を参照）。

コード・リスト4：COUNT(*)を使用した全従業員数の取得 

SQL> select AVG(salary) from employee;

AVG(SALARY)
—————————————————
      97000

1 row selected. 

COUNT集計関数は、問合せの条件を満たすレコードの個数をカウントします。リスト4の問合せではCOUNT(*)を使用しています。COUNT(*)は、問合せの条件を満たす全行数を返す関数です。この結果、EMPLOYEE表内の全レコード数を取得できます。COUNT(*)ではNULL値が無視されませんが、COUNTに列名を指定した場合はNULL値が無視されます。リスト4とリスト5を比較すると、COUNT(*)によってEMPLOYEE表のすべての列を対象としてカウントする場合でも、COUNT(employee_id)によって主キー列のみを対象としてカウントする場合でも、同じ結果が返されています。

SQL> select AVG(NVL(salary, 0)) avg_salary
  2    from employee;

AVG_SALARY
———————————————
88181.8182

1 row selected.  

一方、COUNT(*)またはCOUNT(employee_id)のコールを、COUNT(manager)のコールと比較してみましょう（リスト6を参照）。EMPLOYEE表には、11レコードのうち5レコードのMANAGER列に値がないため、返される単一の個数値にこれらのレコードは含まれません。

コード・リスト6：NULL値を含む列へのCOUNTの適用 

SQL> select COUNT(*)
  2    from employee;

  COUNT(*)
———————————————
        11

1 row selected. 

  リスト7の問合せは、問合せ条件に一致する行がない場合、COUNT(*)またはCOUNT(column_name)のコールの結果、値0が返されることを示しています。この問合せは、雇用日が本日を表すシステム日付（SYSDATE）と一致するすべての従業員レコードについて、全行数と、すべてのMANAGER値の個数を表示するようにリクエストしています。問合せの実行日に雇用された従業員はいないため、個数の値として0が返されます。

コード・リスト7：一致する行がない場合にCOUNT(*)とCOUNT(column_name)は両方とも0を返すことを示す例 

SQL> select COUNT(employee_id)
  2    from employee;

COUNT(EMPLOYEE_ID)
———————————————————————————
                11

1 row selected. 

重複を除いた値の個数を算出しようとしている場合は、COUNT集計関数とDISTINCTキーワードを組み合わせることができます。リスト8に、EMPLOYEE表内のDISTINCTまたはUNIQUE（DISTINCTの代わりに使用できるキーワード）を適用したMANAGER列の値をカウントする問合せを示します。その結果、3という個数が返されます。複数の従業員のMANAGER列がNULL値ですが、このNULL値は、COUNT集計関数のコールによって得られる重複を除いたMANAGER値の個数としてカウントされません。

コード・リスト8：COUNTとDISTINCTによる、重複を除いた値の個数の取得 

SQL> select COUNT(manager)
  2    from employee;

COUNT(MANAGER)
—————————————————————
             7

1 row selected.
   

最大値と最小値

適切なソートを使用したSQL文によって行の値セットをフェッチした場合、その中の最大値と最小値を見つけることはたしかに可能です。しかし、結果セットが大規模で、最大または最小の結果のみが必要な場合は、結果セットの最上部や最下部までスクロールして結果を参照することは面倒でしょう。そのような場合は、代わりにMINおよびMAXという集計関数を使用できます。リスト9の問合せでは、これらの集計関数を使用して、EMPLOYEE表のSALARYの最大値と最小値を表示します。

コード・リスト9：MAXとMINによる列の最大値と最小値の取得 

SQL> select MAX(salary), MIN(salary)
  2    from employee;

MAX(SALARY)  MIN(SALARY)
———————————  ———————————
     300000        60000

1 row selected.     

1つずつの取得とグループごとの取得

この記事のこれまでの例では、特定の集計条件に一致するすべての行に基づいて動作する集計関数を取り上げました。しかし、場合によっては、データをさらに分類して集計する必要もあります。GROUP BY句を使用すれば、複数のレコードにわたるデータを収集して、1つまたは複数の列に基づいて結果をグループ化できます。集計関数とGROUP BY句は、グループごとの集計値を算出して返す目的で、一緒に使用されます。たとえば、リスト10の問合せでは、各部門の従業員数を取得します。

リスト10で注意すべき点として、EMPLOYEE表内には部門に所属していない従業員が1人いますが、その従業員も1つのグループとして結果に含まれています。また、この問合せではORDER BY句も使用しています。GROUP BY句はデータをグループ化しますが、結果を特定の順序でソートすることはありません。リスト11の問合せは、リスト10の問合せからORDER BY句を除いたものです。

コード・リスト10：GROUP BYによる、グループ化された分類データの生成 

SQL> select COUNT(employee_id), department_id
  2    from employee
  3  GROUP BY department_id
  4  ORDER BY department_id;

COUNT(EMPLOYEE_ID)  DEPARTMENT_ID
——————————————————  —————————————
                 6             10
                 2             20
                 2             30
                 1 

4 rows selected. 

コード・リスト11：ORDER BY句なしでGROUP BY句を使用した場合 

SQL> select COUNT(employee_id), department_id
  2    from employee
  3  GROUP BY department_id;

COUNT(EMPLOYEE_ID)  DEPARTMENT_ID
——————————————————  —————————————
                 2             30
                 1 
                 2             20
                 6             10

4 rows selected. 

GROUP BY句に続いてORDER BY句を記述する場合は、ORDER BY句に記述された列をSELECT構文のリストにも含める必要があります。リスト12の問合せは、SELECT構文の列リストとORDER BYの列リストが一致しない場合にエラーが発生することを示しています。同様に、SELECT構文のリストに含まれるが、集計操作に関係しないすべての列に対してGROUP BYを使用しない場合も、リスト13のようなエラーが発生します。リスト13の問合せはリスト12の問合せからGROUP BY句を除いたものです。

コード・リスト12：ORDER BY句の列リストがSELECT構文のリスト内に含まれない場合のエラー 

SQL> select COUNT(employee_id), department_id
  2    from employee
  3  GROUP BY department_id
  4  ORDER BY hire_date DESC;
ORDER BY hire_date DESC
         *
ERROR at line 4:
ORA-00979: not a GROUP BY expression  

コード・リスト13：GROUP BYに必要な列が含まれない場合のエラー 

SQL> select COUNT(employee_id), department_id
  2    from employee
  3  ORDER BY department_id;
select COUNT(employee_id), department_id
                           *
ERROR at line 1:
ORA-00937: not a single-group group function 

複数のグループを返そうとしている場合は、GROUP BY句が必要になります。そして、複数のグループを返すには、集計操作に関係しない列をSELECTリスト内に含める必要があります。リスト13の問合せで、そのような集計操作に関係しない列は、EMPLOYEE表のDEPARTMENT_IDです。集計関数を使用し、かつGROUP BY句を使用しない問合せでは、問合せの時点で問合せ対象の表に行が存在しないとしても、かならず1行返されます。

HAVING句

SELECT構文のリストでWHERE句を使用して結果セットをフィルタリングし、特定の条件を満たすレコードのみを含めることができるのと同様に、GROUP BY句でも同様の句を使用してグループをフィルタリングできます。そのためのHAVING句はGROUP BY句とともに使用できます。HAVING句により、指定した条件を満たすグループへと結果を絞り込むことができます。リスト14は、リスト10の問合せの拡張版です。リスト10の問合せにHAVING句を追加することで、従業員数が1人以下のグループを結果セットから除外できます。ご覧のように、部門が割り当てられていないグループはリスト14の結果セットには返されません。そのグループには1人の従業員しか含まれないためです。

コード・リスト14：HAVING句によるグループのフィルタリング 

SQL> select COUNT(employee_id), department_id
  2    from employee
  3  GROUP BY department_id
  4  HAVING COUNT(employee_id) > 1
  5  ORDER BY department_id;

COUNT(EMPLOYEE_ID)  DEPARTMENT_ID
——————————————————  —————————————
                 6             10
                 2             20
                 2             30

3 rows selected. 

HAVING句はおもに、集計関数を適用した列に基づいて動作します。一方、WHERE句は集計操作のない列やその他の式に基づいて動作します。ここでおもにと言ったのは、HAVING句ではフィルタリング操作内で複数の演算子を使用できるからです。たとえば、リスト15の問合せでは、次の2つの条件のいずれか一方を満たす部門ごと、給与ごとのグループにおける従業員数を表示しますが、集計関数が利用されているのは最初の条件だけです。

• 部門には2人以上の従業員が属す。

• 部門内の従業員の給与が100000未満である。 

コード・リスト15：HAVING句では複数の演算子を使用可能 

SQL> select COUNT(employee_id), department_id, salary
  2    from employee
  3  GROUP BY department_id, salary
  4  HAVING (COUNT(employee_id) > 1
  5             OR salary < 100000)
  6  ORDER BY department_id, salary desc;

COUNT(EMPLOYEE_ID)  DEPARTMENT_ID      SALARY
——————————————————  —————————————  ——————————
                 1             10       80000
                 1             10       70000
                 2             10       60000
                 1             20       90000
                 1             20       65000
                 1             30       70000
                 1                      75000

7 rows selected. 

その他の話題

SELECT構文のリストに含まれるすべての列をGROUP BY句にも記述する必要がありますが、この制約は数値リテラルと文字列リテラル、定数式（列の値を使用しない式）、およびSYSDATEなどの関数には適用されません。リスト16に、例を示すためにリスト15の問合せを拡張した問合せを示します。この問合せのSELECT構文のリスト内には、リテラル、定数式、SYSDATE関数が含まれますが、これらの項目をGROUP BY句やORDER BY句に記述する必要はありません。

コード・リスト16：GROUP BYやORDER BYに含まれないリテラル、式、関数 

SQL> select COUNT(employee_id), department_id, salary,     
2  SYSDATE, ‘String Literal’, 42*37 Expression    
3    from employee    
4  GROUP BY department_id, salary    
5  HAVING (COUNT(employee_id) > 1    
6             OR salary < 100000)    
7  ORDER BY department_id, salary desc;    
COUNT(EMPLOYEE_ID) DEPARTMENT_ID  SALARY SYSDATE   ‘STRINGLITERAL' EXPRESSION
—————————————————— —————————————  —————— —————————  —————————————— ——————————
                 1            10   80000 29-SEP-12  String Literal       1554
                 1            10   70000 29-SEP-12  String Literal       1554
                 2            10   60000 29-SEP-12  String Literal       1554
                 1            20   90000 29-SEP-12  String Literal       1554
                 1            20   65000 29-SEP-12  String Literal       1554
                 1            30   70000 29-SEP-12  String Literal       1554
                 1                 75000 29-SEP-12  String Literal       1554

7 rows selected.   

このシリーズ記事で学習したほかのタイプの関数と同様に、集計関数も別の集計関数内にネストできます。リスト17の問合せでは、部門ごとに給与の合計を取得します。次に、部門ごとの給与合計の値にMIN集計関数を適用して、部門ごとの給与合計の最小値を取得します。この問合せでは、GROUP BY句に記述したすべての列をSELECT構文のリスト内に記述する必要はないことも示しています（この逆は必須であり、SELECT構文のリスト内に記述したすべての列はGROUP BY句に記述する必要があります）。

コード・リスト17：ネストされた集計関数 

SQL> select MIN(SUM(salary)) min_department_salary_sum
  2    from employee
  3   where department_id is not null
  4  GROUP by department_id;

MIN_DEPARTMENT_SALARY_SUM
———————————————————————————
                   155000

1 row selected.  

結論と次回について

この記事では、よく使用される集計関数の一部と、それらの関数を使用してデータの表示を操作する方法について説明しました。MAX、MIN、AVGなどの単一グループの集計関数や、COUNT、SUMなどの複数グループの関数の使用方法を確認しました。データ内にNULL値が存在する場合のこれらの関数の動作や、その動作による結果への影響について確認しました。さらに、GROUP BY句とHAVING句について紹介し、これらの句を使用してサマリー・データをさらにフィルタリングして分類する方法についても説明しました。最後に重要なこととして、ORDER BY句をGROUP BY句とともに使用する場合に気をつけるべき落とし穴や、SELECT構文のリスト内に記述した列の値をGROUP BY句内にも記述する必要がある点について確認しました。この記事ではすべてのOracle Databaseの集計関数を説明していません。詳しくは、Oracle® Database SQL言語リファレンスのドキュメントを確認してください。このSQLの基礎に関する次回の記事では、分析関数について説明します。 

▲ ページTOPに戻る

記事一覧へ戻る