【SQL】表示条件・計算など:定義された関数での計算や表示順(各種関数, CASE, HAVING, GROUP, ORDER)

はじめに

SQLはデータベースを操作するためのプログラムです。
プログラムをどのように分解するかは賛否あると思いますが、
ここでは以下の４つに分類して基本の構文をまとめます。

• 処理内容:検索/更新/挿入/削除のいずれの処理をおこなうか
• 対象テーブル:処理対象のテーブルと、テーブル間の関係
• 絞り込み条件:対象レコードをどのように絞り込むか
• 表示条件・計算など:定義された関数での計算や表示順など

このページでは表示条件の基本構文をまとめます。
表示条件とは書いたもののかなり雑多な内容になっています。各種集約関数とGROUP/HAVING句、条件式のCASE、表示順を決めるORDER句をまとめています。

例として、こちらに掲載しているテーブルを使って、各条件の動きを解説します。

集約関数(GROUP BY, HAVING)

集約関数はDBMSによっていくつかありますが、主だったものは5つです。

集約関数	概要
SUM	条件に一致するデータを集約して列の合計値を取得
AVG(AVERAGE)	条件に一致するデータを集約して列の平均値を取得
COUNT	条件に一致するデータの件数を取得
MAX	条件に一致するデータのうち最大値を取得する
MIN	条件に一致するデータのうち最小値を取得する
参考資料：逆引きSQL構文集 - 集計関数を使用する(基本)

GROUP BYは集約の単位を指定することができ、これはSELECT句で指定する項目(集約関数は除く)と一致します。
*SELECT句の指定が集約関数のみの場合は、GROUP BY句を省略することができます。

数値を計算する関数(SUM, AVG, MAX, MIN)

個人的にはこの4つは使用方法が同じです。
集約単位を指定して、主に数値項目のカラムを関数内に指定する使い方です。(MAXとMINは日付型や文字列型でも利用できます)

例えば、数学テストの成績リスト(MATH_TEST)の平均点を抽出したい時は以下の形になります。

MATH_TEST

ID	SCORE
0001	70
0002	80
0003	50
0004	100
0005	80

AVG

SELECT AVG(SCORE) AS 'AVERAGE' FROM MATH_TEST

実行結果は1レコードです。

AVERAGE
76

男女別で表示したい時は、GROUP BY句を利用します。

AVGbyGender

SELECT T02.GENDER, AVG(T01.SCORE) AS 'AVERAGE' FROM MATH_TEST T01
   INNER JOIN MEMBER_LIST_2ND T02
   ON T01.ID = T02.ID
   GROUP BY T02.GENDER

実行結果は2レコードになります。

GENDER	AVERAGE
M	66.66
F	90

データの件数を取得する(COUNT)

COUNT関数を分けたのは、上記の計算する関数とは異なる動きがあるためです。
COUNT関数でカウントされるのはNULL以外のデータが保持されている場合です。
そのため、あるテーブルのレコード数を取得したい場合は""を指定する、またはNULLが許容されていないカラムを指定することで実現できます。
""を指定すると対象テーブルのレコード数を取得します。

COUNT_ALL

SELECT COUNT(*) AS 'MEMBER_COUNT' FROM MEMBER_LIST_2ND

MEMBER_COUNT
5

特定のカラムを指定した場合には、NULL以外のデータ件数を取得します。

COUNT_NOTE

SELECT COUNT(ID) AS 'ID_COUNT',  COUNT(NOTE) AS 'NOTE_COUNT' FROM MEMBER_LISET_2ND

ID_COUNT	NOTE_COUNT
5	0

条件式(CASE WHEN)

CASE式は条件分岐をおこなって条件に対応する結果を抽出します。
*2つ目の条件分岐(GENDER = '2')やELSE条件は省略することもできます。条件に合致しないデータはNULLになります。

CASE_1

SELECT ID,
   CASE 
      WHEN GENDER = '1' THEN '男性'
      WHEN GENDER = '2' THEN '女性'
      ELSE '不明'
   END AS '性別'
   FROM MEMBER_LIST_2ND

ID	性別
0001	男性
0002	男性
0003	男性
0004	女性
0005	女性

表示順(ORDER BY)

並び順決定の優先カラムとその昇順、降順を指定することができます。
書き方は2通りあり、カラム名を指定する方法と左から何番目のカラムかを指定する方法です。
降順を指定する場合はカラム名(カラム番号)の後ろに"DESC"をつけます、昇順はカラム名だけでOKです。

以下の参考SQLは同じ結果が抽出されます。

SORT_1

SELECT ID, BIRTH_DAY, GENDER FROM MEMBER_LIST_2ND 
   ORDER BY GENDER , BIRTH_DAY DESC

SORT_2

SELECT ID, BIRTH_DAY, GENDER FROM MEMBER_LIET_2ND
   ORDER BY 3 , 2 DESC

ID	BIRTH_DAY	GENDER
0005	1980/12/14	F
0004	1980/8/2	F
0002	1981/3/1	M
0001	1980/10/25	M
0003	1980/4/2	M