わかりやすい JPA(7)
関数と集計クエリ

はじめに

これは「わかりやすいJPA」の7回目です。

SELECT文はデータベースからいろいろな条件でデータを抽出するのでレポートクエリともいいます。SELECT句で、抽出するデータをフィルタリングするためのいろいろな条件を書くのがWhere句です。前回の演算子に続き、今回は、関数と集計クエリについて解説します。

解説で出てくるエンティティやクエリの実行については、「連載:わかりやすいJPA」で必要なツールとドメインモデルについて を参照してください。

日付・時間、数値、文字列を返す関数

以下に示す関数は、日付・時間、数値、文字列を返す関数です。クエリの中で、値を書く場所に、これらの関数を書くことができます。

関数 機 能
CURRENT_DATE 現在の日付を返す
CURRENT_TIME 現在の時間を返す
CURRENT_TIMESTAMP 現在のタイムスタンプを返す
ABS( num ) 数値の絶対値を返す
MOD( num1, num2 ) num1をnum2で割った余り(整数)を返す
SQRT( num ) numの平方根を返す
LENGTH( str ) strの長さを返す
LOWE( str ) strの小文字表現を返す
UPPER( str ) strの大文字表現を返す
CONCAT( str1, str2) str1とstr2を連結した文字列を返す
LOCATE( str1, str2, [,start] ) 文字位置は1から数える。str1のstr2における位置を返す。該当がない場合は0を返す。
SUBSTRING( str, s, e ) 文字位置は1から数える。strの文字位置 s から e までの部分文字列を返す。
TRIM( str ) str から両端の空白を除いた文字列を返す
SIZE( c )  コレクション c のサイズを返す

LOCATE関数

文字位置を1から数えることに注意してください。
LOCATE(‘american’, ‘a’ )  = 1
LOCATE(‘american’, ‘a’, 3 ) = 4
LOCATE(‘chinese’, ‘a’ )   = 0

TRIM関数

取り除く位置の指定ができます。

TRIM(‘  jp  ‘)
TRIM(LEADING FROM  ‘  jp  ‘ )
TRIM(TRAILING FROM  ‘  jp  ‘ )
TRIM(BOTH FROM  ‘  jp  ‘ )
→ ’jp’
→ ’jp  ‘
→ ’  jp’
→ ’jp’

また、取り除く文字として空白以外の文字を指定できます。

TRIM(‘@’ FROM  ‘@@jp@@’ )
TRIM( LEADING ‘@’  FROM  ‘@@jp@@’ )
TRIM( TRAILING ‘@’ FROM  ‘@@jp@@’ )
→ ’jp’
→ ’jp@@’
→ ’@@jp’

集計クエリとは

SELECT文のうち、集計関数(この後解説)があり、GROUP BY、HAVING、ORDER BY句などを持つものを、特に集計クエリといいます。集計クエリでは、データをグループに分けて、グループごとに集計することができます。次のような書き方になります([ ]は省略可能なことを意味します)。

SELECT (集計関数など)
FROM ~
[ WHERE 条件式 ]
[ GROUP BY ~ ]
[ HAVING 条件式]
ORDER BY ~]

集計クエリでは、最初にWHERE句の部分までを解釈して(グループ化しないで)、該当データを集めます。次に、GROUP BY句の指定に従いデータをグループ化し、集計関数によりグループごとに集計します。ただし、集計結果はHAVINGの条件を満たすものだけが返されます。また、ORDER BY句で並び替えが指定してあれば、結果は指定に従って並び変えたものになります。

集計クエリの仕組み

もっとも簡単な集計クエリは、GROUP BY 以下のないクエリで、集計関数により全体を集計します。

次は、管理職を除いた社員の給与の平均を求める集計クエリです。この例では、GROUP BY以下がないので、全体が1つのグループとみなされます。SELECT句に書いたAVGは、グループを対象として、引数に指定された変数の平均を求める集計関数です。

 SELECT AVG(e.salary) 
 FROM Employee e
 WHERE e.directs IS EMPTY
48900.0

前の例は、社員全体を1つのグループとして集計しましたが、所属ごとにグループ化して集計することができます。それには、GROUP BYでグループ化の単位として所属名を指定します。

次の例では、FROM句で対象をDepartment d と所属する社員のJOINに変更していることに注意してください。これは所属ごとに、所属と社員の直積(所属×社員)を作りだします。そして、GROUP BY句で d.name(所属名) を指定したので、所属名 が同じデータが1つのグループとみなされます。この結果、集計は所属ごとに実行されるようになります。

 SELECT d.name, AVG(e.salary) 
 FROM Department d JOIN d.employees e
 WHERE e.directs IS EMPTY
 GROUP BY d.name
[Accounting, 39500.0]
[Engineering, 52400.0]
[QA, 49333.0]

さらに、対象とするグループを制限して、集計結果をフィルタリングするにはHAVING句を使います。HAVING句もWHERE句と同様に条件を指定しますが、グループ化した後の結果についてどのように集計するか、制約をかける点が大きな違いです。

次の例のように、HAVING句では、集計関数を使ってどのように集計するか条件を設定できます。このHAVING句は、グループでの集計について、平均給与が40000以上のものだけに限るという条件を指定しています。

 SELECT d.name, AVG(e.salary) 
 FROM Department d  JOIN d.employees e
 WHERE e.directs IS EMPTY
 GROUP BY d.name
 HAVING AVG(e.salary)>40000
[Engineering, 52400.0]
[QA, 49333.0]

GROUP BY句について

SELECT句の変数に注意

SELECT句にd.nameのような、集計関数でないものを指定する場合、それはGROUP BY句に書かれている変数でなくてはいけません。例えば、次は所属名(d.name)とともに、所属コード(d.id)もレポートに含めようとした例ですが、GROUP BY句に書かれていないので、エラーになります。

※エラーになる
SELECT d.id, d.name, AVG(e.salary)
FROM Department d JOIN d.employees e
WHERE e.directs IS EMPTY
GROUP BY d.name

d.idを結果に含めたい場合は、GROUP BY 句にd.nameではなく、dを指定します。

SELECT d.name, d.id, AVG(e.salary), COUNT(e)
FROM Department d JOIN d.employees e
WHERE e.directs IS EMPTY
GROUP BY d
[Engineering, 1, 52400.0, 5]
[QA, 2, 49333.0, 3]
[Accounting, 3, 39500.0, 2]

複数の集計関数を指定できる

集計関数はいくつでも指定できます。次は平均に加えて、社員の人数を集計する例です。COUNT( )関数は引数に指定した変数の個数を集計する関数です。

SELECT d.name, AVG(e.salary), COUNT(e)
FROM Department d JOIN d.employees e
WHERE e.directs IS EMPTY
GROUP BY d.name
[Accounting, 39500.0, 2]
[Engineering, 52400.0, 5]
[QA, 49333.0, 3]

複数のGROUP BY 指定

GROUP BYには複数のグループ指定を書くことができます。その場合、FROM句での対象の書き方に注意してください。

次の例は、GROUP BY句に、d.nameとe.nameを指定しています。これは各所属ごとに社員を単位としてグループ化するという意味です。集計関数は、各社員が所属しているプロジェクトの数を集計します。これにより、出力は、グループごとにまとめられ、その中で各社員の所属するプロジェクトの数がリストアップされます。

SELECT d.name, e.name, COUNT(p)
FROM Department d JOIN d.employees e JOIN e.projects p
GROUP BY d.name, e.name
[Engineering, Frank, 1]
[Engineering, Jennifer, 2]
[Engineering, Joan, 3]
[Engineering, Scott, 2]
[Engineering, Stephanie, 1]
[Engineering, Sue, 2]
[QA, John, 1]
[QA, Peter, 3]
[QA, Rob, 2]
[QA, Sarah, 2]

使用できる集計関数

集計クエリで使用できるすべての集計関数の一覧を示します。

集計関数 説 明
AVG グループにおける数値型のフィールドの平均を計算して返す。結果はdoubleの値になる
COUNT グループにおける項目の出現回数をカウントする。結果はlongの値になる。引数にDISTINCTを付けて、重複を除く集計ができる。

SELECT e.name, COUNT(p), COUNT(DISTINCT p.type)
FROM Employee e JOIN e.phones p
GROUP BY e
SUM グループにおける数値型のフィールドの合計を計算して返す。結果の型は集計したフィールドの型と同じになる。
MAX グループにおける数値型のフィールドの最大値を返す。
MIN グループにおける数値型のフィールドの最小値を返す。

DBMSネイティブな関数の呼び出し方法

各データベースシステムに固有な関数や、データベースシステム管理者が作成した関数は、FUNCTION 式を使って呼び出すことができます。

FUNCTION ( “function_name” , arg1, arg2, …)

第1引数に関数名、それ以後に必要な引数をコンマで区切って並べます。

読者になる

コメントをどうぞ

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

%d人のブロガーが「いいね」をつけました。