［SQL］GROUP BY 句を使いこなす

実務において、SQLの［GROUP BY］ 句で様々な処理を行うケースがあった。自分の備忘録としてメモしておく。

なお、例として以下のレコードが保存されている order というテーブルを例に考えていきます。

mysql> SELECT * FROM `order`;
+------+---------+------------------------------+-------+
| id   | user_id | order_name                   | price |
+------+---------+------------------------------+-------+
|    1 |       1 | ビッグマック                     |   500 |
|    2 |       3 | チーズバーガー                  |   350 |
|    3 |       5 | フィレオフィッシュバーガー          |   400 |
|    4 |      10 | テリヤキバーガー                |   450 |
|    5 |       2 | ビッグマック                    |   500 |
|    6 |       4 | チーズバーガー                  |   350 |
+------+---------+------------------------------+-------+

STEP1.重複している order_name の抽出

まずはシンプルにorder_nameの値が重複しているものを抽出してみます。

SELECT * FROM `order` GROUP BY order_name;

結果は以下のとおり

mysql> SELECT order_name FROM `order` GROUP BY order_name;
+-----------------------+
| order_name            |
+-----------------------+
| ビッグマック              |
| チーズバーガー           |
+-----------------------+

STEP2.order_nameが重複しているレコードの数も抽出する

次は重複しているビッグマックとチーズバーガーのレコードがそれぞれ何件あるかを確認します。
STEP1のsqlに ［HAVING］ 句を追加して、以下の記述します。

SELECT 
  order_name, 
  COUNT(*) as '注文件数' 
FROM
  `order`
GROUP BY
  order_name 
HAVING 
  COUNT(*) > 1;

結果は以下のとおり

mysql> SELECT order_name, COUNT(*) as '注文件数' FROM `order` GROUP BY order_name HAVING COUNT(*) > 1;
+-----------------------+--------------+
| order_name            | 注文件数      |
+-----------------------+--------------+
| ビッグマック              |            2 |
| チーズバーガー           |            2 |
+-----------------------+--------------+

STEP3.order_nameが重複しているレコード情報をすべて出力

重複しているレコードのすべての情報を出力する場合、少し工夫が必要になります。
［GROUP BY］ 句を使用するとき、SELECT句で 【*】 を併用することはできません。
この理由から、少し遠回りですが、テーブルの自己結合を使用して以下のような形式で抽出することになります。

SELECT
  *
FROM
  `order`
JOIN
  (SELECT
     order_name 
   FROM 
     `ORDER` 
   GROUP BY 
     order_name 
   HAVING 
     COUNT(order_name) > 1
  ) sub_query_order
ON
  sub_query_order.order_name = order.order_name;

JOIN句の部分はサブクエリとなっており、まずはこの部分から処理が実行されます。
このサブクエリの処理はSTEP2のsqlとほぼ同じです。

ORDERテーブルから重複しているorder_nameを抜き出し、その結果を ［sub_query_order］ という名前の別テーブルとして抽出する形式になっています。

この ［sub_query_order］ テーブルを、order_nameカラムを介して、元の ［order］ テーブルと結合します。元は同じテーブルなので自己結合と言われます。
この処理を介することで、アスタリスクを使用してすべてのカラム情報を出力できるようになります。

結果は以下のようになります。

mysql> SELECT
    ->   *
    -> FROM
    ->   `order`
    -> JOIN
    ->   (SELECT order_name FROM `ORDER` GROUP BY order_name HAVING COUNT(order_name) > 1) sub_query_order
    -> ON
    ->   sub_query_order.order_name = order.order_name;
+------+---------+-----------------------+-------+-----------------------+
| id   | user_id | order_name            | price | order_name            |
+------+---------+-----------------------+-------+-----------------------+
|    1 |       1 | ビッグマック              |   500 | ビッグマック             |
|    2 |       3 | チーズバーガー           |   350 | チーズバーガー           |
|    5 |       2 | ビッグマック              |   500 | ビッグマック             |
|    6 |       4 | チーズバーガー           |   350 | チーズバーガー           |
+------+---------+-----------------------+-------+-----------------------+

GROUP BY句とアスタリスクを併用する時は上記のように一手間必要となります。