ざっくりSQL入門 #4：クエリを書く＜集計処理編＞

※MySQLを前提に書いています。一部関数などは方言差がある場合もあるのでご注意ください。

今回は集計系の処理について。

集計処理とは

簡単に言うと「○○ごとの○○」を集計することです。
例えば以下のようなケース。

• 商品テーブルについて、カテゴリごとの商品登録件数を知りたい
• 商品ごとの売り上げ金額合計を集計したい
• 1か月ごとの、会員登録件数の推移を見たい

基本の書き方

構文

SELECT   【件数や合計など】
FROM ... WHERE ...
GROUP BY 【「○○ごと」のカラム名、複数ならカンマ区切り】

例文

-- 商品ID(product_id)ごとに、売上人数と売上金額の合計を集計する
SELECT
  `product_id`
 ,COUNT(*) AS `count`   -- 売上人数：レコード数のカウント
 ,SUM(`price`) AS `sum` -- 売上金額：priceカラムの値の合計
FROM
  `order_product`
GROUP BY
  `product_id` -- 「商品IDごと」にまとめる

補足

このように、「GROUP」句で「○○ごと」にまとめ、
「SELECT」句にて必要に応じ集計関数を用いつつ計算する、という形になります。
もし、「○○ごと」にまとめるわけではなく全件に対して集計を行いたい場合、
単純にGROUP句ごと省略してOKです。

-- userテーブルの件数（＝会員総件数）を求める
-- 何らかの条件をつけたい場合は通常通りWHERE句で指定すればOK
SELECT COUNT(*) AS `user_count`
FROM   `user`

GROUP句の応用

複数指定する

例えば以下のように複数カラムを指定することで、組み合わせ条件ごとにまとめることができます。

-- 「都道府県と職業の組み合わせ」ごとの人数
SELECT `pref`, `job`, COUNT(*) AS `count`
FROM ... WHERE ...
GROUP BY `pref`, `job`

この場合の結果セットは以下のような形になります。

pref	job	count
北海道	会社員	5
北海道	自営業	2
東京都	会社員	15

複雑な条件で集計する

GROUP句には、計算式や関数を含めることもできます。
（パフォーマンスが落ちる場合もあるので注意してください）

-- 年ごとのユーザー登録数
SELECT   YEAR(`registry`) AS `registry_year`, COUNT(*) AS `count`
FROM     `user`
GROUP BY YEAR(`registry`)

集計関数いろいろ

公式マニュアルはこちら。
MySQL :: MySQL 5.6 リファレンスマニュアル :: 12.19.1 GROUP BY (集約) 関数

ここでは主なものを紹介します。

件数を数える（COUNT）

COUNT(*) -- 結果に含まれる全レコード数を返す
COUNT(【カラム名】) -- 結果に含まれるレコードのうち、指定カラムがNULLでないレコード数を返す
COUNT(DISTINCT 【カラム名】) -- 結果に含まれるレコードのうち、指定カラムがNULLでなく、かつ重複を除いた件数を返す

合計や平均（SUM / AVG）

SUM(【カラム名】) -- 結果に含まれるレコードについて、指定カラムの合計を返す
AVG(【カラム名】) -- 結果に含まれるレコードについて、指定カラムの平均を返す

最大や最小（MAX / MIN）

MAX(【カラム名】) -- 結果に含まれるレコードについて、指定カラムの最大値を返す
MIN(【カラム名】) -- 結果に含まれるレコードについて、指定カラムの最小値を返す

文字列を連結する（GROUP_CONCAT）

GROUP_CONCAT(【カラム名】) -- 結果に含まれるレコードについて、指定カラムの値を連結して文字列として返す

こちらは少し特殊というか、あまり頻繁に使うものではないので詳しく補足します。
普段はあまり使わないのですが、慣れれば便利です。

例えば以下のテーブル（fruit）について考えることとします。

id	category	name
1	みかん	うんしゅう
2	みかん	せとか
3	みかん	しらぬい
4	りんご	ふじ
5	りんご	つがる
6	みかん	うんしゅう

以下のクエリを実行することで、下記のような結果セットが得られます。

SELECT   `category`, GROUP_CONCAT(`name`) AS `name_list`
FROM     `fruit`
GROUP BY `category`

category	name_list
みかん	うんしゅう,せとか,しらぬい,うんしゅう
りんご	ふじ,つがる

重複を除くこともできます。

SELECT   `category`, GROUP_CONCAT(DISTINCT `name`) AS `name_list`
FROM     `fruit`
GROUP BY `category`

category	name_list
みかん	うんしゅう,せとか,しらぬい
りんご	ふじ,つがる

並び順を指定することもできます。

SELECT   `category`, GROUP_CONCAT(DISTINCT `name` ORDER BY `name` ASC) AS `name_list`
FROM     `fruit`
GROUP BY `category`

category	name_list
みかん	うんしゅう,しらぬい,せとか
りんご	つがる,ふじ

区切り文字を指定することもできます。

SELECT   `category`, GROUP_CONCAT(DISTINCT `name` ORDER BY `name` ASC SEPARATOR ' ') AS `name_list`
FROM     `fruit`
GROUP BY `category`

category	name_list
みかん	うんしゅう しらぬい せとか
りんご	つがる ふじ