楽しいSQL 3を学ぶ.GROUP BY

この記事は、朴載浩のYouTube講座(リンク)を見て整理した内容の一部だ.
使用されるすべての材料およびサンプルデータは、SQLite Tutorialにおいて提供される.

1. GROUP BY

groupbyは、選択したバーで同じ行をグループ化します.
また、各グループの情報は、集約関数(MIN、MAX、AVG、SUM等)を用いてさらに表示してもよい.

SELECT 
    column_1,
    aggregate_function(column_2) 
FROM 
    table
GROUP BY 
    column_1,
    column_2;

groupbyは上記のように使用でき、WHERE節がある場合はgroupbyが後ろに表示されなければならない.
まず簡単な例から始めます.

SELECT
	albumid,
	COUNT(trackid)
FROM
	tracks
GROUP BY
	albumid;

上記のクエリにより、アルバムidごとにデータをグループ化し、各アルバムのトラック数を算出することができる.

さらに、2つのテーブルがある場合はgroupbyとinner joinを使用できます.

上記のデータ構造で、既存のアルバムやCOUNTの他に、アルバムのタイトルを一緒に確認したい場合は、どうすればよいでしょうか.

SELECT
	tracks.albumid,
	title,
	COUNT(trackid)
FROM
	tracks
INNER JOIN albums ON albums.albumid = tracks.albumid
GROUP BY
	tracks.albumid;

Albuidのように、2つのテーブルのデータには、前にテーブル名を付けてデータを限定する必要があります.
アーティストのテーブルをもう一枚置いて、歌手の名前を見たいなら、JOIN Postingでやったように、INNER JOIN artists ON artists.artistid = albums.artistid inner joinをもう一回つければいいです.
COUNTだけ書くのに飽きたので、他の集約関数も試してみましょう.
使い方はもちろん、countのように希望するカラム名をパラメータに加算することで、自分で計算することができます.
次に、先ほど使用したデータからミリ秒列の最大、最小、平均値を求めるクエリ文を示します.

SELECT
	tracks.albumid,
	title,
	min(milliseconds),
	max(milliseconds),
	round(avg(milliseconds),2)
FROM
	tracks
INNER JOIN albums ON albums.albumid = tracks.albumid
GROUP BY
	tracks.albumid;

groupbyは必ずしも標準としてカラムを必要としない.
複数のカラムを基準にgroupbyを行うこともできます.この場合、ターゲットカラムをペアにするtupleが作成されます.

SELECT
   MediaTypeId, 
   GenreId, 
   COUNT(TrackId)
FROM
   tracks
GROUP BY
   MediaTypeId, 
   GenreId;

結果はMediaTypeIdとGenreIdのデータ対(1,1),(1,2)…そこから分かる.

2. HAVING

selectクエリ文でwhereセクションで条件を制限しているように、groupbyはhaveセクションとともに使用することで、条件でグループをフィルタできます.
使い方は簡単で、whereのようにgroupbyの後ろにフィルタリングしたい条件を書けばいいです.

現在、albuidが1のグループのみのtrack数を表すために、よく知られているtracksテーブルが使用されています.以下に示します.

SELECT
	albumid,
	COUNT(trackid)
FROM
	tracks
GROUP BY
	albumid
HAVING albumid = 1;

もちろんgroupbyを使用して内部結合を行うテーブルではhaveセクションも使用できます.
Albuidベースのtracksテーブルのミリ秒当たりの合計が一定数より大きいデータのみが表示される場合は、次のようになります.

SELECT
	tracks.AlbumId,
	title,
	SUM(Milliseconds) AS length
FROM
	tracks
INNER JOIN albums ON albums.AlbumId = tracks.AlbumId
GROUP BY
	tracks.AlbumId 
HAVING
	length > 60000000;