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

2016/11/28

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

見出し

1 検証内容
2 テーブルの作成
3 CASE文を使ってみる
4 リファレンス
5 その他

検証内容

楽天や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 | int(11) | NO | PRI | NULL | auto_increment |

| point | int(11) | NO | | 0 | |

| sex | tinyint(4) | NO | | 0 | |

| ymd | datetime | NO | | NULL | |

+-------+--------------+------+-----+---------+----------------+

idは通し番号、 pointは発生したポイント、sexは0が男性で1が女性、siteは購入サイト、genreは購入ジャンル、ymdは購入日とする。

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

+----+-------+-----+---------+-------+---------------------+
| 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 |
+----+-------+-----+---------+-------+---------------------+

+----+-------+-----+---------+-------+---------------------+

| 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 |
+-----+-------+

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

)

) AS point

FROM point_table

GROUP BY sex;

+-----+--------+

| sex | point |

+-----+--------+

| 0 | 1034.0 |

| 1 | 1128.0 |

+-----+--------+

最初よく分からなかったけどCASE後のカラムが○○だった場合××に置き換えるみたいな形っぽい。上記だとsexカラムが0だった場合1.1に置き換えて、その前のpointと掛け算するという形。

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

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;

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

)

) AS point

FROM point_table

GROUP BY sex;

凄く分かりづらい。CASEでsiteがamazonだった場合で尚且つgenreがdrinkだった場合は1.2倍、foodだった場合は1.5倍という形になる。

リファレンス

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

その他

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

- MySQL

: 文字コードのutf8_general_ciとutf8_unicode_ciの違い

MySQLで設定する時に使っているサーバーによってデフォルトの文字コードがutf ...

: MySQLでテーブルとデータの複製(コピー)

MySQLでテーブルとデータを複製したかった。ダンプとかではなく、簡単なSQL文 ...

: MySQLでホスト指定しても正常に接続できない場合

MySQL自体は動いており、ユーザーも登録されているのに、ホスト名を指定しても正 ...

: MySQLのintのカラムで2147483647の値

MySQLでintのカラムに数値を入れると「2147483647」という値になっ ...

: MySQLのエラーメール送付

MySQLエラーを取得してエラーメール送付。 <? error_repor ...

: MySQLでランダムに取得

SELECT カラム名 FROM テーブル名 ORDER BY RAND() L ...

: MySQLでIPアドレスを数値として入れると変になる

MySQLでIPアドレスをINET_ATON関数を用いてINT型に変換し格納する ...

: MySQLで大文字小文字

半角英数字のみのフォームで入力した値を検索した際、データに差異が発生。 [Aa ...

: MySQLでテーブル構造とデータをコピーする

MySQLで既にあるテーブルとデータに対して実験を行う必要があった。本番テーブル ...

: MySQLで速度改善

よく見るけど実際に対面してみて難しさが分かった。 150万件ほどのデータがあって ...

PREV: formで送信後にアンカータグをつけたい
NEXT: PHPでソーシャル(SNS)ログインする方法

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

検証内容

テーブルの作成

CASE文を使ってみる

リファレンス

その他

関連記事