こんにちは、naoyaです。
10月に入り一気に涼しくなり、ここ数年では早く思える秋の訪れを感じます。
僕は春は花粉症で薬が手放せず、夏は暑さが苦手でエアコンに頼りっきりなので涼しい秋の気候が一番好きです。
はじめに
今回のお題はMySQLにおけるクエリについてとなります。
先日、新たにテスターとして参画した社員向けにMySQLのSELECT文などの説明をする機会がありました。
そんな中、普段何気なく使っている関数などが意外と知られていなかったりするのかな、と感じたものがあり今回は改めて応用的なテクニックをまとめてみました。
CASE句
条件に応じて指定の文字列や異なるカラムを1つの列に出力したい時は
CASE WHEN "条件A" THEN '"条件Aの場合の結果"' ELSE '"条件を満たさない場合の結果"' END
で条件に応じた結果を表示することが出来ます。
（WHEN ~ THEN ~ は複数記述可能）
イメージとしてはJavaScriptやExcelの関数でも使われるIF文のようなものです。
例、注文(order)テーブルの情報から、「納品状態」を納品日(deliveryDate)が過去なら"納品済"、納品日が未来なら"納品予定"、それ以外（≠日付が入っていない状態）は"納品日未定"と出力する
SELECT id,deliveryDate, CASE WHEN deliveryDate <= CURDATE() THEN '納品済' WHEN deliveryDate > CURDATE() THEN '納品予定' ELSE '納品日未定' END AS `納品状況` FROM `order`
四則演算や四捨五入
カラム同士を+ - * / で繋げることで四則演算が出来ます。
例、注文の明細(orderDetail)の価格(price)と数量(amount)から金額の計算を行う
SELECT price * amount AS `金額` FROM `orderDetail`
TRUNCATE("数値","桁数")
で切り捨てが出来ます。
これを応用することで切り上げ/四捨五入も可能です。
（四捨五入はROUND関数でも近い挙動をするが、厳密には処理内容が異なる）
例、単価 * 数量の金額計算で小数点以下を
切り捨ての場合
SELECT price,amount,TRUNCATE(price * amount,0) AS `金額` FROM `orderDetail`;
切り上げの場合
SELECT price,amount,TRUNCATE(price * amount + 0.9,0) AS `金額` FROM `orderDetail`;
対象の数値に0.9 ※1 を足すことで1の位が繰り上がり、
TRUNCATEで切り捨てることで結果が切り上げと同様の結果になります。※2
※1 対象の数値の小数点以下が1桁までの場合は+0.9、
2桁までの場合は+0.99...と切り上げ処理を行う前の桁数に応じて可変させる
※2 処理前の数値 = 1.2 → 1.2+0.9 = 2.1 → 切り捨てを行い結果は2
処理前の数値 = 1.9 → 1.9+0.9 = 2.8 → 切り捨てを行い結果は2
でどちらも結果は切り上げとなる
四捨五入の場合
SELECT price,amount,TRUNCATE(price * amount + 0.5,0) AS `金額` FROM `orderDetail`;
切り上げとほぼ同じ理屈で0.5を足して1の位が繰り上がるかどうかで四捨五入と同様の結果になります。
LIMIT,OFFSET
LIMITとOFFSETを利用して表示する結果の件数を変更できます。
主に検索結果のページングなどで利用します。
例、注文(order)情報を日付(orderDate)が古い順に50件づつ表示する
1ページ目、100件の内の1~50件目
SELECT * FROM `order` ORDER BY orderDate LIMIT 50;
2ページ目、100件の内の51~100件目
SELECT * FROM `order` ORDER BY orderDate LIMIT 50 OFFSET 50;
検索結果の"OFFSET+1"件目から"LIMIT"件のレコードを表示する形になります。
（OFFSETは検索結果の先頭から除外する件数、というイメージ）
1:Nの関係から最大値(最小値)と紐づくデータの取得
テーブル同士の連結を行うLEFT JOINを利用して、1:Nで紐づくデータからN側のテーブルの特定のカラムが最大値(最小値)のデータを抽出することが出来ます。
例、顧客(customer)に紐づく注文(order)の中で、最新(order.createdAtが最も未来日の1件)の注文情報を抽出する
SELECT c.id,c.name,o1.id,o1.createdAt FROM `customer` AS c LEFT JOIN `order` AS o1 ON c.id = o1.customerId LEFT JOIN `order` AS o2 ON c.id = o2.customerId AND o1.createdAt < o2.createdAt WHERE o2.id is null;
o2はo1のレコードより新しいcreatedAtのレコードを探すため、o2に該当するレコードが見つからない(o2.id is nullの)o1のレコードが最新の注文という考え方です。
日付や金額など、比較可能なデータであれば同様に取得可能です。
（比較演算子を逆にすれば最小値も取得可能）
条件付きのUPDATE文
こちらは非常に限定的な使い方をしたので実例に沿って説明します
本番運用中のプロジェクトで一部画面のリスト値に更新が入り、お客さんの要望もあって下記のようなリスト値の変更が発生しました
（変更前の値は残っているが、対応する数値が変わった）
変更前
1:B 2:D 3:H
変更後
1:A 2:B 3:C 4:D 5:E 6:F 7:G 8:H
該当のカラムを 1:B → 2:B、2:D → 4:D、3:H → 8:H にそれぞれ変更したいが、
順番にUPDATE文を実行すると元が1 or 2だったデータが全て4になってしまう
3→8、2→4、1→2の順に１つづつUPDATE文を実行でも良いですが、CASE文を使用すれば複数のUPDATEを一括で実行出来ます。
UPDATE `table` SET `column` = CASE WHEN `coumn` = ‘1’ THEN ‘2’ WHEN `column` = ‘2’ THEN ‘4’ WHEN `column` = ‘3’ THEN ‘8’ ELSE NULL END;
例は限定的ではありますが、WEHN ~の条件に応じて更新が出来るので一括更新が必要な場面では使い所がありそうです。
UPDATE/INSER文
既に存在しているレコードに対して一括で任意の項目を更新したい場合、
INSERT文の後ろにON DUPLICATE KEY UPDATE を付けることでPRIMARY KEYの項目がDBに存在していなければ新規行のINSERT、存在していれば指定の項目のUPDATEを行う
INSERT INTO `table`(`id` , `column1` , `column2`)
VALUES
('1' , 'aaa' , 'あああ'),
('2' , 'bbb' , 'いいい'),
('3' , 'ccc' , 'ううう')
ON DUPLICATE KEY UPDATE
column1 = VALUES(`column1`);
id がPRIMARY KEYであり
id = 1と2が存在するレコードで3は存在しないレコードである場合、
id = 1と2の行はcolumn1のみUPDATEされ、
3はcolumn2も含めて全ての項目がINSERTされます。
対象データ全件をCSVなどで出力しExcelなどでデータを更新し、
INSERT文を生成すれば更新したい項目のみをUPDATE出来ます。
おわりに
普段プログラムを書かない人間なので、システムの内部で具体的にどういった事をしているのかまでそこまで詳しく把握しておらず、これらをテスト時に都度調べながら使っていた頃は「こんな事が出来るのか」程度の認識でした。
DBを扱う処理は実際には今回挙げたようなクエリを実行して検索や更新が行われているので出来て当たり前といえば当たり前でしたね。
