勉強したことのメモ

webプログラマが勉強したことのメモ。

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

 

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

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

最初よく分からなかったけど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;

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

 

リファレンス

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

 

その他

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

 - MySQL

  関連記事

WordPressサイトのロードアベレージが高い

あるWordPressサイトのロードアベレージが先月ぐらいまでは通常0.5前後で ...

MySQLでWhere句をつけてdumpする

MySQLであるテーブルからWhere句をつけて、絞った結果をダンプさせたかった ...

MySQLでカラム数の異なるテーブルを結合する(UNION)

MySQLでカラム数の異なるテーブル同士をUNIONで結合させたかった。ただ、何 ...

MySQLiでFOUND_ROWS(全件数取得)

MySQLiでFOUND_ROWS(全件数取得)。 $sql = ' SELEC ...

MySQLで歯抜けになっているint型の数値を取得する

MySQLで歯抜けになっているシーケンス番号を 取得したいケースがあった。 ■ソ ...

MySQLで経度緯度から半径○Kmのデータを抽出

経度と緯度をtext型として格納しているMySQLのテーブルがあり、指定した経度 ...

MySQLで1日の起点を変更する方法

MySQLで1日毎の集計を行う際、例えば2020/01/01のデータは2020/ ...

SQLで同じ値を複数のレコードに対してUPDATE

やりたかった事は、複数のレコードに対して 同じ値に更新したいというもの。 $sq ...

MySQL/Apacheの再起動

忘れないようにメモ。 ■MySQL service mysqld restart ...

MySQLで指定の順にデータをソートする方法(ORDER BY FIELD)

MySQLで指定した順にソートしてデータを取り出したい。通常だと主キーだったり何 ...