MySQL GROUP BY 文
GROUP BY 文は、1つまたは複数の列に基づいて結果セットをグループ化します。
グループ化した列に対して、COUNT、SUM、AVG などの関数を使用できます。
GROUP BY 文は、SQLクエリでデータを集計・分析するための重要なツールであり、特に大量のデータを扱う際に有用な集計情報を提供します。
GROUP BY 構文
SELECT column1, aggregate_function(column2) FROM table_name WHERE condition GROUP BY column1;
column1:グループ化する列を指定します。aggregate_function(column2):グループ化された各グループに対して実行される集計関数。table_name:クエリ対象のテーブル名。condition:オプションで、結果を絞り込むための条件。
orders という名前のテーブルに、以下の列があるとします。order_id、customer_id、order_date、order_amount。
customer_id ごとにグループ化し、各顧客の注文合計金額を計算したい場合、SQL文は次のようになります。
例
FROM orders
GROUP BY customer_id;
上記の例では、GROUP BY customer_id を使用して結果を customer_id 列でグループ化し、SUM(order_amount) を使用して各グループの order_amount 列の合計を計算しています。
AS total_amount は、計算結果に別名を付けてクエリ結果を読みやすくするためのものです。
注意事項:
GROUP BY句は通常、集計関数と一緒に使用されます。グループ化後、各グループに対して集計操作を行う必要があるためです。SELECT句内の列は通常、グループ化列または集計関数の引数のいずれかです。- 複数の列でグループ化できます。その場合は、
GROUP BY句内で列名をカンマで区切るだけです。
例
FROM TABLE_NAME
WHERE condition
GROUP BY column1, column2;
例のデモ
この章の例では、以下のテーブル構造とデータを使用します。使用前に、以下のデータをデータベースにインポートしておいてください。
例
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for `employee_tbl`
-- ----------------------------
DROP TABLE IF EXISTS `employee_tbl`;
CREATE TABLE `employee_tbl` (
`id` INT(11) NOT NULL,
`name` CHAR(10) NOT NULL DEFAULT '',
`date` datetime NOT NULL,
`signin` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'ログイン回数',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of `employee_tbl`
-- ----------------------------
BEGIN;
INSERT INTO `employee_tbl` VALUES ('1', '小明', '2016-04-22 15:25:33', '1'), ('2', '小王', '2016-04-20 15:25:47', '3'), ('3', '小丽', '2016-04-19 15:26:02', '2'), ('4', '小王', '2016-04-07 15:26:14', '4'), ('5', '小明', '2016-04-11 15:26:40', '4'), ('6', '小明', '2016-04-04 15:26:54', '2');
COMMIT;
SET FOREIGN_KEY_CHECKS = 1;
インポートが成功したら、次のSQL文を実行します:
mysql> set names utf8; mysql> SELECT * FROM employee_tbl; +----+--------+---------------------+--------+ | id | name | date | signin | +----+--------+---------------------+--------+ | 1 | 小明 | 2016-04-22 15:25:33 | 1 | | 2 | 小王 | 2016-04-20 15:25:47 | 3 | | 3 | 小丽 | 2016-04-19 15:26:02 | 2 | | 4 | 小王 | 2016-04-07 15:26:14 | 4 | | 5 | 小明 | 2016-04-11 15:26:40 | 4 | | 6 | 小明 | 2016-04-04 15:26:54 | 2 | +----+--------+---------------------+--------+ 6 rows in set (0.00 sec)
次に、GROUP BY 文を使用してデータテーブルを名前でグループ化し、各人のレコード数を集計します:
mysql> SELECT name, COUNT(*) FROM employee_tbl GROUP BY name; +--------+----------+ | name | COUNT(*) | +--------+----------+ | 小丽 | 1 | | 小明 | 3 | | 小王 | 2 | +--------+----------+ 3 rows in set (0.01 sec)
WITH ROLLUP を使用する
WITH ROLLUP を使用すると、グループ化された統計データに基づいて、さらに同じ統計(SUM、AVG、COUNT...)を実行できます。
例えば、上記のデータテーブルを名前でグループ化し、各人のログイン回数を集計します:
mysql> SELECT name, SUM(signin) as signin_count FROM employee_tbl GROUP BY name WITH ROLLUP; +--------+--------------+ | name | signin_count | +--------+--------------+ | 小丽 | 2 | | 小明 | 7 | | 小王 | 7 | | NULL | 16 | +--------+--------------+ 4 rows in set (0.00 sec)
ここで、記録 NULL は全員のログイン回数を表します。
coalesce を使用して NULL の代わりになる名前を設定できます。coalesce の構文:
select coalesce(a,b,c);
パラメータの説明:a==null の場合は b を選択;b==null の場合は c を選択;a!=null の場合は a を選択;a、b、c がすべて null の場合は null を返します(意味がありません)。
次の例では、名前が空の場合は合計数で代用します:
mysql> SELECT coalesce(name, '总数'), SUM(signin) as signin_count FROM employee_tbl GROUP BY name WITH ROLLUP; +--------------------------+--------------+ | coalesce(name, '总数') | signin_count | +--------------------------+--------------+ | 小丽 | 2 | | 小明 | 7 | | 小王 | 7 | | 总数 | 16 | +--------------------------+--------------+ 4 rows in set (0.01 sec)その他の拡張