関数とSELECT文の句

(2012/05　更新)

レポートフォルダから、第5回の資料をコピーし、各自で演習に取り組む。

今回の内容：

　・テーブルのレコードの特定のフィールドを集計する関数
　・SQLの SELECT文の文法の全て

SELECT による処理は、今後紹介するSQLの他の文（insert や delect など）でも利用でき、応用範囲が広い。十分な理解が求められる。

式と関数

SELECT文では、テーブルのレコードからフィールドの値を直接取得する他、フィールドの値を使用した計算式の計算結果や、文字列の処理（結合など）が可能である。
また、式では、集計関数　や　文字列関数　、算術関数　が使用できる。

例）
・式
select A*2 from T;
select A*B , A+3 , A+B+C from T;
select '\ '&A , A&' + '&B from T;

・集計関数
select sum(A) from T;
select sum(A) , avg(A) , MAX(A) from T;

・算術関数と文字列関数


（復習） select文では、表示するフィールドはコンマ記号「,」で区切る。
以下の差は何か？
select A,B,C from T;
select A+B+C from T;

・集計関数

テーブルのレコードについて、複数のレコードの集計結果を計算して表示します。
集計するレコードの範囲は、テーブル中の全レコード、WHEREで指定されたレコード、後述するGROUP BY　句によるレコードの分類ごとなどがあります。

SQL関数名	機能	ＭＳ-Ａｃｃｅｓｓの関数名
COUNT	レコードの数を数える	COUNT(フィールド名)
SUM	合計を求める	SUM(フィールド名)
AVG	平均	AVG(フィールド名)
MAX	最大値	MAX(フィールド名)
MIN	最小値	MIN(フィールド名)

テーブルの指定したフィールドについて、全データを集計します。
集計するフィールドのデータ型はCOUNT以外の集計関数では、数値型でなければなりません。

例）

テーブルＴ

A	B	C
1	10	5
2	9	4
	8	3
4	7	5

SELECT COUNT(*) AS NO FROM T;

結果：

例）　フィールドを指定してレコード数を数える

SELECT COUNT(A) AS NO FROM T;

A	B	C
1	10	5
2	9	4
	8	3
4	7	5

３番目のレコードで、フィールドAの値は、NULL（空値）である。

結果：

フィールドを指定した場合COUNTは、NULL（空値）のデータは数えない。

例）　条件に合ったレコードの値を合計する

SELECT SUM(B) FROM T
WHERE C >= 5;

A	B	C
1	10	5
2	9	4
	8	3
4	7	5

結果：

平均・合計の求め方についてはCOUNTと同様であるの省略

平均の求め方に関する注意： 平均の平均を求めると？

・文字列関数

文字列関数では、指定したフィールドの値について、文字の変換、追加、削除、抽出などの処理を行うことができる。
MS-ACCESSで使用可能な関数については、ACCESSのHELPを参照のこと。

※集計関数と異なりレコードを１件づつ処理する。

SQL関数名	機能	ＭＳ-Ａｃｃｅｓｓ
UPPER	文字列を大文字に変換	UCASE
LOWER	文字列を小文字に変換	LCASE
LTRIM	左側の空白文字を除去
RTRIM	右側の空白文字を除去
SUBSTR	文字列の一部を取り出す（部分文字列）	MID	例題で使用するのでよく覚えておく事

部分文字列を求める：　SUBSTR　の文法

SUBSTR(NAME,4,4)　は、

NAMEが'ABCDEFGHIJKLMN'のとき、'DEFG'になる

・算術関数

数学の関数のほか、端数処理などを行うことができる。
MS-ACCESSで使用可能な関数については、ACCESSのHELPを参照のこと。

※集計関数と異なりレコードを１件づつ処理する。

SQL関数名	機能	ＭＳ-Ａｃｃｅｓｓ
ABS	絶対値を求める
CEIL	端数の処理	なし
FLOOR	端数の処理	INT
SIGN	符号を求める	SGN
SQRT	平方根（ルート）を求める	SQR

端数処理について：

端数を切り上げるのが、 CEIL

例）
0.5 → 1.0
-1.5 → -1.0

端数を切り下げるのが、 FLOOR

例）
0.5 →0.0
-1.5 → -2.0

端数処理については、マイナスの値の扱いに注意が必要。

SELECT文の句

SELECT FROM に加えて、以下のＳＱＬキーワードを利用できる。

MS-ACCESS SQL 構文

SELECT

 [all | distinct] { * | table.* | [table.]field1 [AS alias1] [, [table.]field2 [AS alias2] [, ...]]}

FROM tableexpression [, ...]

[WHERE... ]

[GROUP BY... ]

[HAVING... ]

[ORDER BY... ] ;

読み方：
　[ aaaa ] とある箇所は省略もしくは記述なしでもOKな部分。
　[ aaaa | bbbb ] とある箇所は、　aaaa または bbbb のどちらかを選んで記述する部分。
　tableexpression の部分は、今のところ　テーブル名をコンマで区切って記したもののみ。次回以降で、拡張された文法について解説する。

・　ORDER　BY

ORDERとBYは、空白で区切る。

SQLの実行結果を、指定したフィールドのデータの順に並べ替える。

重要：　ORDER BYでは、元のテーブルが並び替わるのではない。あくまで、ＳＱＬで求めたテーブルを並べ替えるだけである。

SELECT 名前
FROM 名簿 ORDER BY クラス番号 ASC, 名前 DESC;

名簿の名前を、クラス番号の昇順に並べ替え、同じクラスでは名前の辞書の逆順に並べ替えるて表示する。

ASC →昇順（小さい順・ａｂｃ・五十音順）
DESC →降順（大きい順・昇順の逆順）

ASCは省略可能。

以下の説明では、テーブル　T　を使用すする。

A	B	C
1	1	4
2	2	6
3	1	5
4	2	7
5	1	4
6	3	8

SELECT A,B,C FROM T ORDER BY B;

A	B	C
1	1	4
3	1	5
5	1	4
2	2	6
4	2	7
6	3	8

5.4　GROUP　BY

テーブルのレコードを　グループ　ごとに分類する。　さらに分類した項目ごとに　集計関数　で結果を求める事も出来る。

GROUPとBYは、空白で区切る。

以下の説明では、テーブル　T　を使用すする。

A	B	C
1	1	4
2	2	6
3	1	5
4	2	7
5	1	4
6	3	8

SELECT A,B,C FROM T GROUP BY B;

A	B	C
1	1	4
3	1	5
5	1	4
2	2	6
4	2	7
6	3	8

Bについて３グループに分れた。

SELECT A,B,C FROM T GROUP BY B,C;

A	B	C
1	1	4
5	1	4
3	1	5
2	2	6
4	2	7
6	3	8

Bを更にＣで分類し、５グループに分れた。

(コメント)

データベースによっては、SQLで、GROUP BYの句で、ORDER BY と同様、分類に従った並べ替えが可能なものもある。

例） select B from T group by B desc

また、group by でグループ分けをした結果、並べ替えが行われている場合もあるが、必ずそうなるとは限らない。（グループ分け作業の処理の都合で並べ変わる場合が有る）

集計関数を利用した場合、ORDER BY と異なり、グループごとに集計が計算される。

SELECT B,SUM(C) FROM T GROUP BY B;

B	C
1	13
2	13
3	8

■ 集計関数を、通常のフィールド（列）と同時に指定する場合の注意

SELECT A,SUM(B) FROM T;

上記はエラー（集計関数SUMと、列Aを同時に指定している為。）

Aについてグループ化が必要となる。

SELECT A,SUM(B) FROM T GROUP BY A;

5.5　HAVING

WHEREと同様、ＳＱＬで条件指定を行う。
違いは、
「集計関数を条件に指定できる」
「グループ化して集計関数を計算後、選択条件で判定する」
点である。
例）
　HAVING　AVG(A)>50

WHEREでは、グループ化の前に条件判定を行い、さらに集計関数を条件に利用できない。

重要：句を解釈する順序について

SQLの実行では、記述したSQL文の先頭から順にデータベースが操作されるのではない。
以下に、SQLで作成したクエリーの処理がどのような順でDBMSに実行されるのかの概要を記す。

解釈順序	句	処理内容
1	FROM	テーブルの選択
2	WHERE	条件によるレコードの選択
3	GROUP BY	グループ化
4	HAVING	条件によるレコードの選択
5	SELECT	式・関数・集計関数の計算、表示データの指定
6	ORDER BY	並べ替え

演習課題：

以下の問題についてクエリーを作成する
（ヒント）　空欄部分をマウスでなぞると文字が表示されます。
問題１：　クエリー１
TEAMSTATS表を使用して、打率が、２割５分未満の選手を検索する

・バッターの打率は、　安打数／打数　で求める。 TEAMSTATSテーブルでいうと、 HITS/AB となる。
・打率が2割5分未満の選手を検索するには、　WHERE　でその条件のデータを指定すればよい。
　WHEREの条件式は、 HITS/AB<0.25 となる。
問題２：　クエリー2
CHARACTERS2表を使用して、

INITIALS CODE

K. A. P. 32

のように出力するクエリーを作成する

・クエリーの出力が１件だけになっている。これはWHEREで条件を　CODE=32　または CODE='32' とすればよい。どちらにすべきかは、テーブルのデータ型に依存する。
・イニシャルの表示には、 MID(FIRSTNAME,1,1) &'.'& M &'.'&MID(LASTNAME,1,1)&'.' AS INITIALS を利用する。
問題３：　クエリー３
ORGCHART表を使用して、病気欠勤日数が３０日以上の社員が、　部署ごとに　何人いるかを求めてください。

・部署ごとにレコードをまとめるには　GROUP BY TEAM とする。
・人数を集計するには　COUNT(NAME)　とする。
・SELECTで表示する項目は、　TEAM と人数　の２項目とする。　人数は AS SICKLEAVE30 とフィールド名を付け替える
・病欠が３０日以上であるデータを検索するには　WHERE SICKLEAVE>=30 とする
問題４：　クエリー４
CHECKS表を使用して、次のように出力するクエリーを作成してください。

CHECK# PAYEE AMOUNT

1 Ma Bell 150

・基本的な問題です。　WHERE　で [CHECK#] = 1 とし、SELECTで必要なフィールド名だけを指定してください。
※フィールド名　CHECK# には特殊記号#があるためAccessのSQLでは [CHECK#] と括弧を付ける必要があります。
問題５：　クエリー５
「CHECKS2」テーブルで、
PAYEE が、Ma Bell 以外のPAYEEについて、
各PAYEE毎に、AMOUNTの合計を求めて表示しなさい。
ただし、AMOUNTの平均が30以下の場合は、表示しなくてよい。
表示は、PAYEEと、合計の２つのフィールドからなる表とする。
表示は、PAYEEのアルファベット順に表示すること。

（ヒント）　SELECT / FROM / WHERE / GROUP BY / HAVING / ORDER BY　の全てを使用してSQLを作成します。
いきなり完成形を目指すのでなく以下の順序で少しずつ作成し、動作を確認しながら完成を目指します。
第１段階　PAYEEとAMOUNTを表示します。ただし、ＰＡＹＥＥが Ma Bell でないと言う条件を付けます。
SELECT PAYEE, AMOUNT
FROM
WHERE PAYEE <> 'Ma Bell';

第２段階　AMOUNT を合計します。ただし、PAYEEごとにまとめてから合計します。
SELECT PAYEE, SUM(AMOUNT)
FROM
WHERE
GROUP BY PAYEE;

第３段階　AMOUNT の平均が30 より大きな場合だけ表示する事にします。HAVINGを使います。
SELECT
FROM
WHERE
GROUP BY
HAVING AVG(AMOUNT) > 30;

第４段階並べ替えを指定します
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY PAYEE;

注意：ＡｃｃｅｓｓのＳＱＬの文法に注意する。
テーブル名　→　名前に特殊文字が含まれる場合、[ ]で囲む。例）　[CHECK#]

文字の結合　→　＆　または　＋　を使用する
例）　FIRSTNAME & LASTNAME

列名の名前の付け替え　→　列名　ＡＳ　別名　のＡＳは省略できない。　付け替えた名前には、元の名前は利用できない

ワイルドカード　→　ＬＩＫＥによるパターン検索で、％→＊　＿→？　を使用する。

ファイル名を「第5回#学籍番号」として、レポートフォルダに提出する。
※ファイル名で、#、数字、番号は全て半角文字を使用すること。

INITIALS	CODE
K. A. P.	32

CHECK#	PAYEE	AMOUNT
1	Ma Bell	150