アナログCPU:5108843109

ゲームと音楽とプログラミング(酒と女とロックンロールのノリで)

('ω') < 転職した

ざっくり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
みかん うんしゅう しらぬい せとか
りんご つがる ふじ