LoginSignup
1
0

More than 1 year has passed since last update.

@ds_334

MySQLのCOALESCE関数が素晴らしかった話

Last updated at Posted at 2022-11-16

はじめに

webサービスを運用に関わっているとビジネスサイドから過去の長期間の集計レポート等を求められる事が往々にしてあります。
逆にエンジニアサイドはデータの肥大化を防ぐ為、古いデータをバックアップ用のテーブルに回したり過去データの削除をしている場合が多いです。（重くなるし）
完全に削除してしまっていた場合はどうしようもないですが、バックアップのテーブルが複数ある場合、クエリが煩雑になってしまいがちです。
今回の件はある意味テーブル設計が特殊だったので汎用性があるかは微妙かもしれませんが。

COALESCE関数に関して

COALESCE関数は与えられた引数のうち、"NULL"でない最初の引数を返してくれます。
データを表示する目的で取り出す際、A列に無ければB列を表示する場合や、1カラムに対してマスターが複数ある場合等に便利です。
CASE関数やIFNULL関数を駆使する事で同様の結果を導く事が出来ますが、COALESCE関数を使えばより簡単に記述する事が可能です。

単純な使用法だとこんな感じになります。

-- 電話番号が存在すれば電話番号、電話番号が存在しなければメールアドレスを表示する
SELECT COALESCE(電話番号, メールアドレス) AS 連絡先
FROM ユーザ情報

以下、業務で使用した際の実装例を限りなく限界まで噛み砕き備忘録として残します。

前提のデータを用意

売上データです。今回はsalesは閲覧される回数が多いとの事で、各年度のテーブルには回して居ません。
日付系のカラムは今回使用しないので特に作っていません。
本来であればsalesデータ毎、年度別のテーブルに放り込まれていそうですが、今回は業務的にその作りになって居なかったので助かりました。
どっちが正しいとかは解らん。

sales（売上基本データ）

id year sales
1 2021 2500
2 2021 1000
3 2021 3000
4 2021 4000
5 2021 3000
6 2021 2000
7 2021 1500
8 2021 3000
9 2021 4000
10 2021 2000
11 2022 3000
12 2022 4000
13 2022 1000
14 2022 1000
15 2022 1500
16 2022 3500
17 2022 4000
18 2022 2000
19 2022 5000
20 2022 6000

再現用にクエリ

-- テーブル作成
CREATE TABLE `sales` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `year` int(10) unsigned NOT NULL,
  `sales` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8mb4;

-- データ作成
INSERT INTO `sales` (`id`, `year`, `sales`) VALUES
(1,	2021,	2500),
(2,	2021,	1000),
(3,	2021,	3000),
(4,	2021,	4000),
(5,	2021,	3000),
(6,	2021,	2000),
(7,	2021,	1500),
(8,	2021,	3000),
(9,	2021,	4000),
(10,	2022,	2000),
(11,	2022,	3000),
(12,	2022,	4000),
(13,	2022,	1000),
(14,	2022,	1000),
(15,	2022,	1500),
(16,	2022,	3500),
(17,	2022,	4000),
(18,	2022,	2000),
(19,	2022,	5000),
(20,	2022,	6000);

続いてsalesに対応する年次毎に作られているテーブルを2個。

sales_2022（売上詳細データ2022年分）

sales_id user_id type margin
11 6 1 1000
12 5 1 1500
13 4 2 1000
14 4 2 2000
15 3 1 3000
16 8 1 1500
17 9 2 1000
18 9 2 2000
19 1 1 2000
20 4 1 2000 
-- テーブル作成
CREATE TABLE `sales_2022` (
  `sales_id` int(10) unsigned NOT NULL,
  `user_id` int(10) unsigned NOT NULL,
  `type` tinyint(3) unsigned NOT NULL,
  `margin` int(10) unsigned NOT NULL,
  PRIMARY KEY (`sales_id`),
  KEY `idx_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- データ作成
INSERT INTO `sales_2022` (`sales_id`, `user_id`, `type`, `margin`) VALUES
(11,	3,	1,	1000),
(12,	2,	1,	1500),
(13,	1,	2,	1000),
(14,	1,	2,	2000),
(15,	5,	1,	3000),
(16,	9,	1,	1500),
(17,	1,	2,	1000),
(18,	8,	2,	2000),
(19,	7,	1,	2000),
(20,	5,	1,	2000);

sales_2021（売上詳細データ2021年分）

sales_id user_id type margin
1 6 1 2000
2 5 1 500
3 4 2 2000
4 4 2 2000
5 3 1 2000
6 8 1 1500
7 9 2 1000
8 9 2 2000
9 1 1 2000
10 4 1 2000 
-- テーブル作成
CREATE TABLE `sales_2021` (
  `sales_id` int(10) unsigned NOT NULL,
  `user_id` int(10) unsigned NOT NULL,
  `type` tinyint(3) unsigned NOT NULL,
  `margin` int(10) unsigned NOT NULL,
  PRIMARY KEY (`sales_id`),
  KEY `idx_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- データ作成
INSERT INTO `sales_2021` (`sales_id`, `user_id`, `type`, `margin`) VALUES
(1,	6,	1,	2000),
(2,	5,	1,	500),
(3,	4,	2,	2000),
(4,	4,	2,	2000),
(5,	3,	1,	2000),
(6,	8,	1,	1500),
(7,	9,	2,	1000),
(8,	9,	2,	2000),
(9,	1,	1,	2000),
(10,	4,	1,	2000);

sales_users（ユーザデータ）

最後にそれっぽくなるようにユーザの名前が入っているマスター的なテーブルも用意しておきます。

user_id user_name
1 佐藤
2 鈴木
3 高橋
4 田中
5 渡辺
6 伊藤
7 山本
8 中村
9 小林
10 加藤 
-- テーブル作成
CREATE TABLE `sales_users` (
  `user_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_name` varchar(32) NOT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4;

-- データ作成
INSERT INTO `sales_users` (`user_id`, `user_name`) VALUES
(1,	'佐藤'),
(2,	'鈴木'),
(3,	'高橋'),
(4,	'田中'),
(5,	'渡辺'),
(6,	'伊藤'),
(7,	'山本'),
(8,	'中村'),
(9,	'小林'),
(10,	'加藤');

データ抽出

前提のテーブルの説明が長くなりましたが、ここからが本題。
早速脳死でJOINしてSELECTしてみましょう。

SELECT
    *
FROM sales AS s
LEFT JOIN sales_2022 AS s1
ON s.id = s1.sales_id
LEFT JOIN sales_2021 AS s2
ON s.id = s2.sales_id

結果

id year sales sales_id user_id type margin sales_id user_id type margin
1 2021 2500 NULL NULL NULL NULL 1 6 1 2000
2 2021 1000 NULL NULL NULL NULL 2 5 1 500
3 2021 3000 NULL NULL NULL NULL 3 4 2 2000
4 2021 4000 NULL NULL NULL NULL 4 4 2 2000
5 2021 3000 NULL NULL NULL NULL 5 3 1 2000
6 2021 2000 NULL NULL NULL NULL 6 8 1 1500
7 2021 1500 NULL NULL NULL NULL 7 9 2 1000
8 2021 3000 NULL NULL NULL NULL 8 9 2 2000
9 2021 4000 NULL NULL NULL NULL 9 1 1 2000
10 2022 2000 NULL NULL NULL NULL 10 4 1 2000
11 2022 3000 11 3 1 1000 NULL NULL NULL NULL
12 2022 4000 12 2 1 1500 NULL NULL NULL NULL
13 2022 1000 13 1 2 1000 NULL NULL NULL NULL
14 2022 1000 14 1 2 2000 NULL NULL NULL NULL
15 2022 1500 15 5 1 3000 NULL NULL NULL NULL
16 2022 3500 16 9 1 1500 NULL NULL NULL NULL
17 2022 4000 17 1 2 1000 NULL NULL NULL NULL
18 2022 2000 18 8 2 2000 NULL NULL NULL NULL
19 2022 5000 19 7 1 2000 NULL NULL NULL NULL
20 2022 6000 20 5 1 2000 NULL NULL NULL NULL

ちょっとこのままでは使いにくい形式。
これをCOALESCE関数を使用すると、優先度を付けてSELECTしてくれました。

SELECT
    s.id,
    s.year,
    COALESCE(s1.user_id, s2.user_id) AS user_id,
    COALESCE(s1.type, s2.type) AS type,
    COALESCE(s1.margin, s2.margin) AS margin
FROM sales AS s
LEFT JOIN sales_2022 AS s1
ON s.id = s1.sales_id
LEFT JOIN sales_2021 AS s2
ON s.id = s2.sales_id
id year sales user_id type margin
1 2021 2500 6 1 2000
2 2021 1000 5 1 500
3 2021 3000 4 2 2000
4 2021 4000 4 2 2000
5 2021 3000 3 1 2000
6 2021 2000 8 1 1500
7 2021 1500 9 2 1000
8 2021 3000 9 2 2000
9 2021 4000 1 1 2000
10 2022 2000 4 1 2000
11 2022 3000 3 1 1000
12 2022 4000 2 1 1500
13 2022 1000 1 2 1000
14 2022 1000 1 2 2000
15 2022 1500 5 1 3000
16 2022 3500 9 1 1500
17 2022 4000 1 2 1000
18 2022 2000 8 2 2000
19 2022 5000 7 1 2000
20 2022 6000 5 1 2000

こんな感じでSELECTされる!!素晴らしい。
例にしたテーブルはsales_2021,sales_2022のみの結合でしたが、実際にコーディングしていた時はテーブルが7,8個あったので本当に助かりました。

各々に結び付く、マスター的なテーブルがあっても解決可能です。

SELECT
    s.id,
    s.year,
    COALESCE(s1.user_id, s2.user_id) AS user_id,
    COALESCE(u1.user_name, u2.user_name) AS user_name,
    COALESCE(s1.type, s2.type) AS type,
    COALESCE(s1.margin, s2.margin) AS margin
FROM sales AS s
LEFT JOIN sales_2022 AS s1
ON s.id = s1.sales_id
LEFT JOIN sales_users AS u1
ON s1.user_id = u1.user_id
LEFT JOIN sales_2021 AS s2
ON s.id = s2.sales_id
LEFT JOIN sales_users AS u2
ON s2.user_id = u2.user_id
id year sales user_id user_name type margin
1 2021 2500 6 伊藤 1 2000
2 2021 1000 5 渡辺 1 500
3 2021 3000 4 田中 2 2000
4 2021 4000 4 田中 2 2000
5 2021 3000 3 高橋 1 2000
6 2021 2000 8 中村 1 1500
7 2021 1500 9 小林 2 1000
8 2021 3000 9 小林 2 2000
9 2021 4000 1 佐藤 1 2000
10 2022 2000 4 田中 1 2000
11 2022 3000 3 高橋 1 1000
12 2022 4000 2 鈴木 1 1500
13 2022 1000 1 佐藤 2 1000
14 2022 1000 1 佐藤 2 2000
15 2022 1500 5 渡辺 1 3000
16 2022 3500 9 小林 1 1500
17 2022 4000 1 佐藤 2 1000
18 2022 2000 8 中村 2 2000
19 2022 5000 7 山本 1 2000
20 2022 6000 5 渡辺 1 2000

こんな感じに。

SELECT結果のuser_idからJOINすればJOINが一度で済むかもしれませんが、user_idのINDEXが効かなくなるのでレコード数が増えてくると重くなるので推奨は出来ないんじゃないかと。

使用するシチュエーションは限定されそうですが、1つのカラムに違うマスターのidが入っている事が横行しているプロダクトに携わる事が多かったのでもっと早く知りたかったコマンドでした。

おわりに

いかがでしたか？

今回は、MySQLのCOALESCEコマンドに付いて調べてみました！

例にも挙げましたがもっと早く知りたかったコマンドでした。
今まで関わってきたプロダクト達、すまんな…。
何なら今回も無理矢理CASEかUNIONで実装するところだったし

ここまでお読みいただきありがとうございました！

1
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
1
0