【MySQL】 GROUP BY句での論理演算 ～ BIT_OR() 等 ～

■ はじめに

 * テーブルのBOOL(BIT)項目に対して、GROUP BYした後に論理演算 OR をしたかったので調べてみた

【MySQL】解決案

 * BIT_OR() ってのがある

公式サイト
https://dev.mysql.com/doc/refman/5.6/ja/group-by-functions.html

■ 実行環境

 * DB : MySQL5.7

■ サンプルデータ

 * トイレ掃除チェックリストで考える。
  => 一日中のうち、一回でもダメ（NG=true）だったらアウト

テーブル

-- トイレ掃除チェックリスト
CREATE TABLE `toilet_clean_checklist` (
	`check_datetime` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'チェック日時',
	`ng` BIT(1) NOT NULL DEFAULT b'0' COMMENT 'NG：ダメだったら 1(true)',
	`inspector` VARCHAR(10) NULL DEFAULT NULL COMMENT 'チェックした人'
)
ENGINE=InnoDB
;

データ

REPLACE INTO `toilet_clean_checklist` (`check_datetime`, `ng`, `inspector`) VALUES
	('2018-04-10 09:30:37', b'0', 'Mike'),
	('2018-04-10 17:31:08', b'0', 'Tom'),
	('2018-04-10 21:31:29', b'0', 'Sam'),
	('2018-04-11 08:21:52', b'0', 'Mike'),
	('2018-04-11 11:12:34', b'0', 'Tom'),
	('2018-04-11 21:33:35', b'1', 'Sam'),
	('2018-04-12 08:33:48', b'1', 'Tom'),
	('2018-04-12 11:12:17', b'1', 'Mike');

データ表示

check_datetime     | ng | inspector
-------------------+----+-----------
2018-04-10 09:30:37|  0 |      Mike
2018-04-10 17:31:08|  0 |       Tom
2018-04-10 21:31:29|  0 |       Sam
-------------------------------------
2018-04-11 08:21:52|  0 |      Mike
2018-04-11 11:12:34|  0 |       Tom
2018-04-11 21:33:35|  1 |       Sam
-------------------------------------
2018-04-12 08:33:48|  1 |       Tom
2018-04-12 11:12:17|  1 |      Mike

■ サンプル

SQL文

SELECT
 DATE_FORMAT(tcc.check_datetime, '%Y-%m-%d') AS date,
 BIT_OR(tcc.ng) AS result
FROM
 toilet_clean_checklist AS tcc
GROUP BY
 DATE_FORMAT(tcc.check_datetime, '%Y-%m-%d')

出力結果

date       | result
-----------+--------
2018-04-10 |     0
2018-04-11 |     1
2018-04-12 |     1

■ 補足：Null許可の項目でのBIT_OR()の動作について

 * NULLの場合、0 を返すような動きになる
  => NULLだけでも 0 を返す（以下の「出力結果」を参照）
  => NULLを考慮に入れたくない場合は、WHEREで除外しておく

https://dev.mysql.com/doc/refman/5.6/ja/group-by-functions.html#function_bit-or

より抜粋
～～～～～～～～～
一致する行がなかった場合、この関数は 0 を返します。
～～～～～～～～～

テーブル

-- トイレ掃除チェックリスト
CREATE TABLE `toilet_clean_checklist` (
	`check_datetime` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'チェック日時',
	`ng` BIT(1) DEFAULT NULL COMMENT 'NG：ダメだったら 1(true) NULL許可',
	`inspector` VARCHAR(10) NULL DEFAULT NULL COMMENT 'チェックした人'
)
ENGINE=InnoDB
;

データ

REPLACE INTO `toilet_clean_checklist` (`check_datetime`, `ng`, `inspector`) VALUES
	('2018-04-10 09:30:37', b'0', 'Mike'),
	('2018-04-10 17:31:08', b'0', 'Tom'),
	('2018-04-10 21:31:29', NULL, 'Sam'),
	('2018-04-11 08:21:52', b'1', 'Mike'),
	('2018-04-11 11:12:34', b'0', 'Tom'),
	('2018-04-11 21:33:35', NULL, 'Sam'),
	('2018-04-12 08:33:48', NULL, 'Tom'),   -- 全部NULL
	('2018-04-12 11:12:17', NULL, 'Mike'),  -- 全部NULL
	('2018-04-13 09:30:37', b'1', 'Mike'),
	('2018-04-13 17:31:08', b'1', 'Tom'),
	('2018-04-13 21:31:29', NULL, 'Sam'),
	('2018-04-14 08:21:52', b'0', 'Mike'),
	('2018-04-14 11:12:34', NULL, 'Tom'),
	('2018-04-14 21:33:35', NULL, 'Sam'),
	('2018-04-15 08:33:48', b'1', 'Tom'),
	('2018-04-15 11:12:17', NULL, 'Mike');

出力結果

date       | result
-----------+--------
2018-04-10 |     0 -- 0, NULL
2018-04-11 |     1 -- 0, 1, NULL
2018-04-12 |     0 -- 全部NULL
2018-04-13 |     1 -- 1, NULL
2018-04-14 |     0
2018-04-15 |     1