[MySQL] データマイニングのための検索処理でよく使うテクニックまとめ
こんにちは、@yoheiMuneです。
今日は、仕事でデータベースを検索する際によく使うテクニックをブログに残しておきたいと思います。ちょっとしたことなんですけど、自分でも何回も調べてしまって効率が悪いので、まとめてみたい次第です。
MySQL :: MySQL 5.7 Reference Manual :: 13.20.1 GROUP BY (Aggregate) Function Descriptions
MySQL :: MySQL 5.7 Reference Manual :: 12.7 Date and Time Functions
MySQL :: MySQL 5.7 Reference Manual :: 13.5 String Functions
MySQL :: MySQL 5.7 Reference Manual :: 12.7 Date and Time Functions
最後になりますが本ブログでは、フロントエンド・Python・機械学習など雑多に情報発信をしていきます。自分の第２の脳にすべく、情報をブログに貯めています。気になった方は、本ブログのRSSやTwitterをフォローして頂けると幸いです ^ ^。
最後までご覧頂きましてありがとうございました！
今日は、仕事でデータベースを検索する際によく使うテクニックをブログに残しておきたいと思います。ちょっとしたことなんですけど、自分でも何回も調べてしまって効率が悪いので、まとめてみたい次第です。
目次
今回使うデータ
今回は以下のデータを使いたいと思います。
# ユーザー
CREATE TABLE user (
id INT,
name VARCHAR(20),
age TINYINT,
regist_date DATE,
salary INT
);
# スキル
CREATE TABLE skill (
id INT,
name VARCHAR(20)
);
# ユーザーの保持するスキル
CREATE TABLE user_skill (
user_id INT,
skill_id INT
);
# ユーザー
insert into user values (1, "ゆーじ", 22, "2015-04-04", 300000);
insert into user values (2, "ぴょんた", 56, "2016-02-12", 500000);
insert into user values (3, "ななえ", 29, "2016-03-31", 235000);
insert into user values (4, "すすえ", 38, "2015-12-21", 900000);
# スキル
insert into skill values (1, "エンジニア"), (2, "デザイナー"), (3, "ディレクター");
# ユーザーの保持するスキル
insert into user_skill values (1,1), (1,2), (2,2), (3,1), (3,3), (4,1);
これらのデータを使ってサンプルを示しつつ、進めていきたいと思います。GoupByしたのちに配列で取得する
GROUP BYで行をまとめたのちに、GROUP_CONCATを使ってまとめた内容を配列で取得することができます。
SELECT
MAX(a.name) "ユーザー名",
GROUP_CONCAT(DISTINCT c.name) "保有スキル"
FROM
user a
INNER JOIN user_skill b ON a.id = b.user_id
INNER JOIN skill c ON b.skill_id = c.id
GROUP BY
a.id
;
+-----------------+------------------------------------+
| ユーザー名 | 保有スキル |
+-----------------+------------------------------------+
| ゆーじ | エンジニア,デザイナー |
| ぴょんた | デザイナー |
| ななえ | ディレクター,エンジニア |
| すすえ | エンジニア |
+-----------------+------------------------------------+
ただし、GROUP_CONCATでまとめる内容が多すぎると勝手に省略されちゃいますので、それを許容できるところで使うと良いと思います。リファレンスは以下です。MySQL :: MySQL 5.7 Reference Manual :: 13.20.1 GROUP BY (Aggregate) Function Descriptions
複数行を複数列で表現する
登録年毎のスキル数を把握したい場合に、「登録年」と「スキル」でGROUP BYすると、登録年毎スキル毎の数が行ごとに表示されます。
SELECT
DATE_FORMAT(a.regist_date, "%Y") year,
c.name "スキル名",
COUNT(1) "スキル数"
FROM
user a
INNER JOIN user_skill b ON a.id = b.user_id
INNER JOIN skill c ON b.skill_id = c.id
GROUP BY
year, c.name
;
+------+--------------------+--------------+
| year | スキル名 | スキル数 |
+------+--------------------+--------------+
| 2015 | エンジニア | 2 |
| 2015 | デザイナー | 1 |
| 2016 | エンジニア | 1 |
| 2016 | ディレクター | 1 |
| 2016 | デザイナー | 1 |
+------+--------------------+--------------+
プログラムで処理する場合はこのフォーマットで全く問題ないのですが、出力結果をそのまま人に見せる場合には、以下のようにまとめると綺麗な表になります。
SELECT
DATE_FORMAT(a.regist_date, "%Y") year,
SUM(skill_01) "エンジニア",
SUM(skill_02) "デザイン",
SUM(skill_03) "ディレクション"
FROM
user a
INNER JOIN (
SELECT
user_id,
CASE WHEN skill_id = 1 THEN 1 ELSE 0 END skill_01,
CASE WHEN skill_id = 2 THEN 1 ELSE 0 END skill_02,
CASE WHEN skill_id = 3 THEN 1 ELSE 0 END skill_03
FROM
user_skill
) b ON a.id = b.user_id
GROUP BY
year
;
+------+-----------------+--------------+-----------------------+
| year | エンジニア | デザイン | ディレクション |
+------+-----------------+--------------+-----------------------+
| 2015 | 2 | 1 | 0 |
| 2016 | 1 | 1 | 1 |
+------+-----------------+--------------+-----------------------+
これは仕事でよく使ってます。上記のデータを出して、ペタッとChatworkなどに貼り付ける使い方が多いです。ただし、本気で分析するなら前半部分のSQLとExcelのピボットなどを使うこともあります。日付のフォーマット（読みやすくする）
日付は読みやすいように変換すると、その後のアウトプットがラクになることが多いです。
SELECT
name,
DATE_FORMAT(regist_date, "%Y年%m月%d日") "登録日"
FROM
user
;
+--------------+-------------------+
| name | 登録日 |
+--------------+-------------------+
| ゆーじ | 2015年04月04日 |
| ぴょんた | 2016年02月12日 |
| ななえ | 2016年03月31日 |
| すすえ | 2015年12月21日 |
+--------------+-------------------+
日付のフォーマット文字列は、以下で参照できます。MySQL :: MySQL 5.7 Reference Manual :: 12.7 Date and Time Functions
数値のフォーマット（3桁区切り、小数のケタ数）
これもフォーマットの話ですが、カンマ区切りや小数の桁数など、出力を指定できると見やすくて素敵です。
SELECT
FORMAT(AVG(salary), 0) "平均年収",
FORMAT(AVG(age), 2) "平均年齢"
FROM
user
;
+--------------+--------------+
| 平均年収 | 平均年齢 |
+--------------+--------------+
| 483,750 | 36.25 |
+--------------+--------------+
数値のフォーマットは、以下にリファレンスがあります。MySQL :: MySQL 5.7 Reference Manual :: 13.5 String Functions
OracleのMinus句のような動き
MySQLには残念ながらminus句は存在しないようで、「◯◯スキルを保有しない人」を取得するために、以下のようなコードを書いています（他に良い書き方あったら知りたい）。
# デザイナースキルを持っていない人
SELECT
a.id "ユーザーID",
a.name "ユーザー名"
FROM
(
SELECT
id,
name
FROM
user
) a
LEFT JOIN (
SELECT
user_id
FROM
user_skill
WHERE
skill_id = 2 # デザイナー
) b ON a.id = b.user_id
WHERE
b.user_id IS NULL
;
+----------------+-----------------+
| ユーザーID | ユーザー名 |
+----------------+-----------------+
| 3 | ななえ |
| 4 | すすえ |
+----------------+-----------------+
ただデータの持ち方によって実行計画がひどいことになり、クエリが帰ってこない場合もあるかもしれません。クエリの速度を気にしながら実行したいところです（クエリが返ってこない場合には、分割したのちにPythonなどで処理します）。行番号をつける
行番号に通しの番号をつけたい場合があります。資料に印刷した時に「◯番目のデータだけど・・・」のようなコミュニケーションを円滑にするためです。その時には以下のように行います。
SET @rownum=0;
SELECT
@rownum:=@rownum+1 as ROW_NUM,
id,
name
FROM
user
;
+---------+------+--------------+
| ROW_NUM | id | name |
+---------+------+--------------+
| 1 | 1 | ゆーじ |
| 2 | 2 | ぴょんた |
| 3 | 3 | ななえ |
| 4 | 4 | すすえ |
+---------+------+--------------+
rownumという変数を定義して、それをSelect句でインクリメントしながら使うことで、行番号を表現しています。日付の差分を出す
「何日経過したのか」など日付の差分を出す場合があります。例えば「30日以内の人」などを知るためです。
SELECT
name "名前",
DATEDIFF(now(), regist_date) "登録からの経過日数"
FROM
user
;
+--------------+-----------------------------+
| 名前 | 登録からの経過日数 |
+--------------+-----------------------------+
| ゆーじ | 377 |
| ぴょんた | 63 |
| ななえ | 15 |
| すすえ | 116 |
+--------------+-----------------------------+
日付の加減算
「1ヶ月前」など、日付の計算をしながら抽出条件を指定する場合があります。わざわざ計算して指定しなくて良いので、便利です。
SELECT
name "名前",
regist_date "登録日"
FROM
user
WHERE
-- 登録日1ヶ月以内の人
regist_date >= date_add(now(), interval -1 month)
;
+-----------+------------+
| 名前 | 登録日 |
+-----------+------------+
| ななえ | 2016-03-31 |
+-----------+------------+
date_addなどの関数は、以下リファレンスで詳細を見ることができます。MySQL :: MySQL 5.7 Reference Manual :: 12.7 Date and Time Functions
最後に
今日は、日々の業務でデータを見る時に使っている色々な関数をブログに書きました。色々と関数やテクニックなどあり、まだまだお勉強中です。これからも残しておきたいものはブログに書いていけたらと思います。最後になりますが本ブログでは、フロントエンド・Python・機械学習など雑多に情報発信をしていきます。自分の第２の脳にすべく、情報をブログに貯めています。気になった方は、本ブログのRSSやTwitterをフォローして頂けると幸いです ^ ^。
最後までご覧頂きましてありがとうございました！
