MySQLでカラム内容によって条件分岐させたい

MySQLでカラムの内容によって条件を分岐させたかった。またPHPで処理するのではなくMySQL文で完結させたかった。調べてみるとCASE文というのが使えそうだったので使い方と検証のメモ。

検証内容

楽天やamazonで買い物をし、その際にポイントが発生したとする。このポイントを性別や購入ジャンル、サイトによって以下の通り増加する。

男性→ポイント1.1倍

女性→ポイント1.2倍

amazonでdrinkを購入→1.2倍

amazonでfoodを購入→1.5倍

楽天でdrinkを購入→1.3倍

楽天でfoodを購入→1.6倍

最終的に性別毎の合計ポイントを算出したい。

テーブルの作成

以下テーブルを作成した。

+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| point | int(11)      | NO   |     | 0       |                |
| sex   | tinyint(4)   | NO   |     | 0       |                |
| site  | varchar(128) | NO   |     | NULL    |                |
| genre | varchar(128) | NO   |     | NULL    |                |
| ymd   | datetime     | NO   |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+

適当にデータを作成する。作成後は以下の通り。

+----+-------+-----+---------+-------+---------------------+
| id | point | sex | site    | genre | ymd                 |
+----+-------+-----+---------+-------+---------------------+
|  1 |   100 |   0 | amazon  | food  | 2016-11-01 00:00:00 |
|  2 |   200 |   0 | amazon  | food  | 2016-11-01 00:00:00 |
|  3 |   300 |   0 | rakuten | food  | 2016-11-01 00:00:00 |
|  4 |   120 |   0 | amazon  | drink | 2016-11-01 00:00:00 |
|  5 |   220 |   0 | rakuten | drink | 2016-11-01 00:00:00 |
|  6 |   220 |   1 | rakuten | drink | 2016-11-01 00:00:00 |
|  7 |   120 |   1 | amazon  | drink | 2016-11-01 00:00:00 |
|  8 |   300 |   1 | rakuten | food  | 2016-11-01 00:00:00 |
|  9 |   200 |   1 | amazon  | food  | 2016-11-01 00:00:00 |
| 10 |   100 |   1 | amazon  | food  | 2016-11-01 00:00:00 |
+----+-------+-----+---------+-------+---------------------+

また、この時点での合計ポイントは以下の通り。

SELECT sex, SUM(point) AS point 
FROM point_table 
GROUP BY sex;

+-----+-------+
| sex | point |
+-----+-------+
|   0 |   940 |
|   1 |   940 |
+-----+-------+

CASE文を使ってみる

まずは男性→1.1倍、女性1.2倍を算出してみる。SQL文と結果は以下の通り。

SELECT sex, SUM(
	point * 
		(
			CASE sex 
				WHEN 0 THEN 1.1 
				WHEN 1 THEN 1.2
			END
		) 
	) AS point 
FROM point_table 
GROUP BY sex;

+-----+--------+
| sex | point  |
+-----+--------+
|   0 | 1034.0 |
|   1 | 1128.0 |
+-----+--------+

さらにサイトと購入ジャンルで分岐させたい場合は入れ子にして使う。以下の通り。

SELECT sex, SUM(
	point * 
		(
			CASE sex 
				WHEN 0 THEN 1.1 
				WHEN 1 THEN 1.2
			END
		) *
		(
			CASE site 
				WHEN 'amazon' THEN 
					CASE genre 
						WHEN 'drink' THEN 1.2 
						WHEN 'food' THEN 1.5 
					END 
				WHEN 'rakuten' THEN 
					CASE genre 
						WHEN 'drink' THEN 1.3 
						WHEN 'food' THEN 1.6 
					END 
			END
		)
	) AS point 
FROM point_table 
GROUP BY sex;

リファレンス

https://dev.mysql.com/doc/refman/5.6/ja/case.html

その他

分かりづらいけど使い道は多そう。Where句とかは普通に追加して検索できる。