集約関数 / グループ化 / 並べ替えと名前付け


集約関数

集約関数とは、1つの列グループに対して施すことのできる演算機能を言います。
集約関数は列グループ全体につき 1つの値を生成します。

集約関数には次の 5つがあります。

SUM( ) 指定条件によって得られた列の値の合計を求める関数
AVG( ) 指定条件によって得られた列の値の平均値を求める関数
MAX( ) 指定条件によって得られた列の値の中で最大値を返す関数
MIN( ) 指定条件によって得られた列の値の中で最小値を返す関数
COUNT( ) 指定条件によって得られた表の基数、すなわち行数を求める関数

 

/* 合計の算出 */
SELECT SUM(受注個数) FROM 受注表 ;

/* 平均値の算出 */
SELECT AVG(受注個数) FROM 受注表 ;

/* 最大値の算出 */
SELECT MAX(受注個数) FROM 受注表 ;

/* 最小値の算出 */
SELECT MIN(受注個数) FROM 受注表 ;

/* 行数の算出 */
SELECT COUNT(*) FROM 受注表 ;
SELECT COUNT(DISTINCT 顧客コード) FROM 受注表 ;

 

/* 集約関数による出力 */
SELECT COUNT(*) , SUM(受注個数) , AVG(受注個数) , MAX(受注個数) , MIN(受注個数) FROM 受注表 WHERE 顧客コード = '001' ;
/* 実行結果 */

COUNT(*) SUM  AVG  MAX  MIN
──── ── ── ── ──
5        100  20   30   10

グループ化

SQL におけるグループ化とは、同列内の値の中で、同じ値を持つデータごとに集合化することを言います。
グループ化には GROUP BY 句を用います。

また、グループ化された情報に対して条件を設定し、その条件に合致するものだけを抽出することもできます。
これには、GROUP BY 句でグループ化する列名を指定した後、HAVING 句を続けて抽出条件を記述します。

GROUP BY

GROUP BY 句を用いれば,ある列の値の部分集合を定義して,それに集約関数を適用することができます。
この部分集合とは、共通の集約が適用される対象として、ある列に値を持つかのように定義されたグループのことです。

/* グループ化 */
SELECT 顧客コード , MAX(受注個数) FROM 受注表 GROUP BY 顧客コード ;
/* 実行結果 */

顧客コード MAX(受注個数)
───── ──────
001        30
002        20
003        20

GROUP BY 句で顧客コード別に新しいテーブルを一度作成し、それぞれのテーブルの中で受注個数の最大値を集約関数で抽出し、その結果を顧客コード別に表示している、というようなイメージです。

GROUP BY 句は複数の列に使用することもできます。

次のサンプルでは、各顧客コードの受注個数の最大値を商品コード別に参照したい場合のものです。

/* グループ化 */
SELECT 顧客コード , 商品コード , MAX(受注個数) FROM 受注表 GROUP BY 顧客コード , 商品コード ;
/* 実行結果 */
顧客コード 商品コード MAX(受注個数)
───── ───── ──────
001        0101       15
001        0102       30
001        0103       10
002        0101       20
002        0102       20
003        0101       10
003        0103       20

HAVING

GROUP BY 句によりグループ化された情報に対して条件を設定する場合は、HAVING を用います。

/* グループ化 */
SELECT 顧客コード , 商品コード , MAX(受注個数) FROM 受注表 GROUP BY 顧客コード , 商品コード HAVING MAX(受注個数) >=20;
/* 実行結果 */
顧客コード 商品コード MAX(受注個数)
───── ───── ──────
001        0102       30
002        0101       20
002        0102       20
003        0103       20

WHEREとHAVINGは条件を設定するという観点からは非常に似たようなもののように見えますが、WHERE 句と HAVING 句とは全く異なるものあります。

WHERE 句は SELECT 文が行を選ぶときの条件を指定するものですが、HAVING 句は WHERE 句が指定する条件で呼び出された行から、グループを選ぶための条件を与えています。

なので行を選ぶ WHERE 句には集約関数を置くことはできませんが、HAVING 句では集約関数を置くことができます。

並べ替えと名前付け

ORDER BY

テーブルは順序の無い集合であり、テーブルからデータを取り出すとき、データが順番に抽出されるとは限りません。
SQL では ORDER BY 句を使って出力に順番を適用できるようにしています。

列ごとに昇順 (ASC) 降順 (DESC) を指定することができます。
デフォルトでは昇順となっています。

また、複数の列を並べ替えることができます。
このときは、ORDER BY 句に続く列の並んでいる順に並び替えられます。

すなわち、最初に指定された列が最も優先され、2 番目に指定された列が最初の重複値の順番を決定する、といった具合になります。

/* 出力の並べ替え */
SELECT * FROM 顧客表 ORDER BY 顧客コード DESC  ;
/* 実行結果 */
顧客コード 顧客名
───── ───
003        first house
002        onside
001        kurokiya

ORDER BY と GROUP BY を利用すれば、グループを並べ替えることができます。

例えば受注表で商品コードをグループ化して商品コードを降順に並べるときは次の通り SQL を記述します。

/* 出力の並べ替え */
SELECT 商品コード FROM 受注表 GROUP BY 商品コード ORDER BY 商品コード DESC ;
/* 実行結果 */
商品コード
─────
103
102
101

また、列名の代わりに整数を使って列を指定することもできます。

整数は最初の列が 1 で示され、3 番目の列が 3 で示されるといったように SELECT 句での列の位置を示します。

/* 出力の並べ替え */
SELECT 顧客コード , 受注個数 FROM 受注表 ORDER BY 2 DESC ;
/* 実行結果 */
顧客コード 受注個数
───── ────
001        30
001        25
002        20
002        20
002        20
001        20
003        20
003        15
002        15
001        15
001        10
003        10

AS

出力列には、名前を付ける事ができます。

MAX(列名) など集約関数を使って出力された結果などには列名が付かないので、AS 句を用いて名前を指定します。

/* 出力列の名前付け */
SELECT AVG(受注個数) AS 平均 FROM 受注表 ;
/* 実行結果 */
平均
────
18.33333

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です


3 + 1 =

次のHTML タグと属性が使えます: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>