Advent Calendar 20 日目の記事です。
はじめに
ここ最近、素の SQL でデータを触ることが増えてきました。
便利な関数のおかげで SQL だけで処理が完結できるので、データ取得後にゴリゴリとコードを書かずにすんでいます。
ちょうど年末でもあるので、お世話になった関数に感謝しつつ、この 1 年をふりかえってみたいと思います。
前提
説明のしやすさから、前後関係を意識している箇所はありますが、関数の並びに深い意味はありません。
SQL 実行後のデータの扱いについて補足するために、一部 Python のコードが登場します。
動作環境
$ mysqld --version
mysqld Ver 10.5.5-MariaDB for osx10.15 on x86_64 (Homebrew)
使用するテーブル
- 健康診断テーブル（checkup）
| カラム名 | 説明 | 型 |
|---|---|---|
| id | 健康診断ID | INT |
| no | 受診者番号 | INT |
| sex | 性別 | VARCHAR |
| date | 受診日 | DATETIME |
| result | 健康診断の結果 | JSON |
| created_at | レコード作成日時 | TIMESTAMP |
テーブルの内容は以下のとおり。
> SELECT * FROM checkup;
+----+----+--------+---------------------+----------------------------------------+---------------------+
| id | no | sex | date | result | created_at |
+----+----+--------+---------------------+----------------------------------------+---------------------+
| 1 | 1 | MALE | 2018-12-15 09:00:00 | {"height":159,"weight":52,"blood":"o"} | 2018-12-16 09:00:00 |
| 2 | 1 | MALE | 2019-12-23 11:00:00 | {"height":160,"weight":50,"blood":"o"} | 2019-12-25 11:00:00 |
| 3 | 1 | MALE | 2020-12-20 10:00:00 | {"height":161,"weight":49,"blood":"o"} | 2020-12-20 10:00:00 |
+----+----+--------+---------------------+----------------------------------------+---------------------+
3 rows in set (0.003 sec)
※ 上記はあくまで SQL 関数を紹介するためのものです。（テーブルの作りがイケてなくてもあしからず）
文字列に関する関数
LPAD 関数
指定した文字数分、指定した文字列で左からパディングします。
- 例） 受診者番号（no）を 3 桁に 0 埋め
> SELECT no, LPAD(no, 3, '0') AS after_no FROM checkup;
+----+----------+
| no | after_no |
+----+----------+
| 1 | 001 |
| 1 | 001 |
| 1 | 001 |
+----+----------+
3 rows in set (0.000 sec)
桁数を揃えたい、固定長で扱いたい場合などに便利です。
- 例） 5 桁で 0 埋めした受診者番号（no）を 3 桁で * 埋め
> SELECT no, LPAD(no, 5, '0') AS before_no, LPAD(LPAD(no, 5, '0'), 3, '*') AS after_no FROM checkup;
+----+-----------+----------+
| no | before_no | after_no |
+----+-----------+----------+
| 1 | 00001 | 000 |
| 1 | 00001 | 000 |
| 1 | 00001 | 000 |
+----+-----------+----------+
3 rows in set (0.000 sec)
元の文字数より小さい値を文字数に指定すると、左からその文字数でカットされます。
類似としては、右からパディングする RPAD 関数というのもあります。
TRIM 関数
先頭や末尾から指定した文字列を除去します。
- 例： 性別（sex）から空白を除去
> SELECT sex, TRIM(BOTH ' ' FROM sex) AS after_sex FROM checkup;
+--------+-----------+
| sex | after_sex |
+--------+-----------+
| MALE | MALE |
| MALE | MALE |
| MALE | MALE |
+--------+-----------+
3 rows in set (0.000 sec)
分かりづらいですが、sex の先頭と末尾にあった空白が除去されました。
BOTH の代わりに LEADING を指定すると先頭の空白、TRAILING を指定すると末尾の空白がそれぞれ除去されます。
JSON に関する関数
JSON_EXTRACT 関数
JSON 文字列からデータを抽出します。
- 例） 健康診断の結果（result）から身長（height）と体重（weight）と血液型（blood）を抽出
> SELECT result, JSON_EXTRACT(result, '$.height') AS height, JSON_EXTRACT(result, '$.weight') AS weight, JSON_EXTRACT(result, '$.blood') AS blood FROM checkup;
+----------------------------------------+--------+--------+-------+
| result | height | weight | blood |
+----------------------------------------+--------+--------+-------+
| {"height":159,"weight":52,"blood":"o"} | 159 | 52 | "o" |
| {"height":160,"weight":50,"blood":"o"} | 160 | 50 | "o" |
| {"height":161,"weight":49,"blood":"o"} | 161 | 49 | "o" |
+----------------------------------------+--------+--------+-------+
3 rows in set (0.000 sec)
こんな風に JSON 文字列から簡単に値が抽出できます。
ちなみに MariaDB で JSON 型が使用できるようになったのはバージョン 10.2 からです。
それ以前は JSON 文字列が扱えないかというとそうではなく、
JSON is an alias for LONGTEXT introduced for compatibility reasons with MySQL's JSON data type.
（Google 翻訳： JSON は、MySQL の JSON データ型との互換性の理由で導入された LONGTEXT のエイリアスです。 ）
とのことなので、文字列向けの型であれば JSON 文字列を扱うことができます。（詳細は こちら）
実際に型を確認してみると、result は LONGTEXT 型となっています。
（冒頭の「使用するテーブル」では result の型を JSON と表記していますが、厳密には LONGTEXT が正しいです）
> show columns from checkup;
+------------+-------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------------------+----------------+
| id | int(3) | NO | PRI | NULL | auto_increment |
| no | int(3) | NO | | NULL | |
| sex | varchar(10) | NO | | NULL | |
| date | datetime | YES | | NULL | |
| result | longtext | YES | | NULL | |
| created_at | timestamp | NO | | current_timestamp() | |
+------------+-------------+------+-----+---------------------+----------------+
6 rows in set (0.005 sec)
少し実験をしてみます。
> ALTER TABLE checkup MODIFY result varchar(1000);
Query OK, 3 rows affected (0.065 sec)
Records: 3 Duplicates: 0 Warnings: 0
> show columns from checkup;
+------------+---------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------------------+----------------+
| id | int(3) | NO | PRI | NULL | auto_increment |
| no | int(3) | NO | | NULL | |
| sex | varchar(10) | NO | | NULL | |
| date | datetime | YES | | NULL | |
| result | varchar(1000) | YES | | NULL | |
| created_at | timestamp | NO | | current_timestamp() | |
+------------+---------------+------+-----+---------------------+----------------+
5 rows in set (0.012 sec)
result の型を VARCHAR(1000) に変更しました。
この状態で、先ほどと同じ SQL を流してみると、
> SELECT result, JSON_EXTRACT(result, '$.height') AS height, JSON_EXTRACT(result, '$.weight') AS weight, JSON_EXTRACT(result, '$.blood') AS blood FROM checkup;
+----------------------------------------+--------+--------+-------+
| result | height | weight | blood |
+----------------------------------------+--------+--------+-------+
| {"height":159,"weight":52,"blood":"o"} | 159 | 52 | "o" |
| {"height":160,"weight":50,"blood":"o"} | 160 | 50 | "o" |
| {"height":161,"weight":49,"blood":"o"} | 161 | 49 | "o" |
+----------------------------------------+--------+--------+-------+
3 rows in set (0.001 sec)
特に問題なく VARCHAR(1000) に変更した result からも値が抽出できました。
余談ですが、VARCHAR 型と TEXT 型には index 周りに違いがあるようです。（詳細はこちら）
VARCHAR columns can be fully indexed. TEXT columns can only be indexed over a specified length.
（Google 翻訳： VARCHAR 列は完全に索引付けできます。 TEXT 列は、指定された長さでのみ索引付けできます。）
JSON_UNQUOTE 関数
JSON 文字列のダブルクォートを除去します。
- 例） 健康診断の結果（result）から血液型（blood）を抽出
> SELECT result, JSON_EXTRACT(result, '$.blood') AS before_blood, JSON_UNQUOTE(JSON_EXTRACT(result, '$.blood')) AS after_blood FROM checkup;
+----------------------------------------+--------------+-------------+
| result | before_blood | after_blood |
+----------------------------------------+--------------+-------------+
| {"height":159,"weight":52,"blood":"o"} | "o" | o |
| {"height":160,"weight":50,"blood":"o"} | "o" | o |
| {"height":161,"weight":49,"blood":"o"} | "o" | o |
+----------------------------------------+--------------+-------------+
3 rows in set (0.001 sec)
こんな風にダブルクォーテーションの除去が簡単にできます。
ちなみに JSON_UNQUOTE 関数を知る前は TRIM 関数を使って、こんな風に対応していました。
> SELECT result, JSON_EXTRACT(result, '$.blood') AS before_blood, TRIM(BOTH '\"' FROM JSON_EXTRACT(result, '$.blood')) AS after_blood FROM checkup;
+----------------------------------------+--------------+-------------+
| result | before_blood | after_blood |
+----------------------------------------+--------------+-------------+
| {"height":159,"weight":52,"blood":"o"} | "o" | o |
| {"height":160,"weight":50,"blood":"o"} | "o" | o |
| {"height":161,"weight":49,"blood":"o"} | "o" | o |
+----------------------------------------+--------------+-------------+
3 rows in set (0.003 sec)
JSON_UNQUOTE 関数の方がスッキリしていますね。
日付・時間に関する関数
DAYOFWEEK 関数
曜日のインデックス（1： 日曜日、2： 月曜日 ... 7： 土曜日）を取得します。
- 例） 受診日（date)の曜日を取得
> SELECT date, DAYOFWEEK(date) AS day_of_week FROM checkup;
+---------------------+-------------+
| date | day_of_week |
+---------------------+-------------+
| 2018-12-15 09:00:00 | 7 |
| 2019-12-23 11:00:00 | 2 |
| 2020-12-20 10:00:00 | 1 |
+---------------------+-------------+
3 rows in set (0.006 sec)
date の曜日が取得できました。
CASE 文と組み合わせるとこんな風にもできます。
（長くなるので、必要な曜日だけ条件分岐させています）
> SELECT date, DAYOFWEEK(date) AS day_of_week, (CASE WHEN DAYOFWEEK(date) = 1 THEN '日曜日' WHEN DAYOFWEEK(date) = 2 THEN '月曜日' WHEN DAYOFWEEK(date) = 7 THEN '土曜日' END) AS day_of_week_jp FROM checkup;
+---------------------+-------------+----------------+
| date | day_of_week | day_of_week_jp |
+---------------------+-------------+----------------+
| 2018-12-15 09:00:00 | 7 | 土曜日 |
| 2019-12-23 11:00:00 | 2 | 月曜日 |
| 2020-12-20 10:00:00 | 1 | 日曜日 |
+---------------------+-------------+----------------+
3 rows in set (0.003 sec)
WEEKDAY 関数
曜日のインデックス（0： 月曜日、1： 火曜日 ... 6： 日曜日）を取得します。
DAYOFWEEK 関数と似ていますが、こちらは月曜日始まりで、インデックスは 0 からスタートです。
- 例） 受診日（date)の曜日を取得
> SELECT date, WEEKDAY(date) AS week_day FROM checkup;
+---------------------+----------+
| date | week_day |
+---------------------+----------+
| 2018-12-15 09:00:00 | 5 |
| 2019-12-23 11:00:00 | 0 |
| 2020-12-20 10:00:00 | 6 |
+---------------------+----------+
3 rows in set (0.004 sec)
CASE 文と組み合わせるときはインデックスの値に注意します。
> SELECT date, WEEKDAY(date) AS week_day, (CASE WHEN WEEKDAY(date) = 6 THEN '日曜日' WHEN WEEKDAY(date) = 0 THEN '月曜日' WHEN WEEKDAY(date) = 5 THEN '土曜日' END) AS week_day_jp FROM checkup;
+---------------------+----------+-------------+
| date | week_day | week_day_jp |
+---------------------+----------+-------------+
| 2018-12-15 09:00:00 | 5 | 土曜日 |
| 2019-12-23 11:00:00 | 0 | 月曜日 |
| 2020-12-20 10:00:00 | 6 | 日曜日 |
+---------------------+----------+-------------+
3 rows in set (0.000 sec)
単純に曜日のインデックスを取得したいだけなら、DAYOFWEEK 関数と WEEKDAY 関数のどちらでも可能です。
では、以下のような場合はどうでしょうか。
- 例） DAYOFWEEK 関数で受診日（date)が平日のデータを取得
> SELECT * FROM checkup WHERE DAYOFWEEK(date) IN (2, 3, 4, 5, 6);
+----+----+--------+---------------------+----------------------------------------+---------------------+
| id | no | sex | date | result | created_at |
+----+----+--------+---------------------+----------------------------------------+---------------------+
| 2 | 1 | MALE | 2019-12-23 11:00:00 | {"height":160,"weight":50,"blood":"o"} | 2019-12-23 11:00:00 |
+----+----+--------+---------------------+----------------------------------------+---------------------+
1 row in set (0.003 sec)
- 例） WEEKDAY 関数で受診日（date)が平日のデータを取得
> SELECT * FROM checkup WHERE WEEKDAY(date) <= 4;
+----+----+--------+---------------------+----------------------------------------+---------------------+
| id | no | sex | date | result | created_at |
+----+----+--------+---------------------+----------------------------------------+---------------------+
| 2 | 1 | MALE | 2019-12-23 11:00:00 | {"height":160,"weight":50,"blood":"o"} | 2019-12-23 11:00:00 |
+----+----+--------+---------------------+----------------------------------------+---------------------+
1 row in set (0.001 sec)
平日のみ、休日のみ、といった区分でデータを取得したい場合は WEEKDAY 関数が向いています。
DATE_ADD 関数
指定した日付に指定した時間を加算します。
- 例） 1 年後の受診日（date）の目安を求める
> SELECT MAX(date) AS recently_date, DATE_ADD(date, interval 1 YEAR) AS next_date FROM checkup GROUP BY no;
+---------------------+---------------------+
| recently_date | next_date |
+---------------------+---------------------+
| 2020-12-20 10:00:00 | 2019-12-15 09:00:00 |
+---------------------+---------------------+
1 row in set (0.003 sec)
マイナスの値を指定すると減算もできます。
> SELECT '2020-12-20' AS today, DATE_ADD('2020-12-20', interval - 1 YEAR) AS 1_year_ago;
+------------+------------+
| today | 1_year_ago |
+------------+------------+
| 2020-12-20 | 2019-12-20 |
+------------+------------+
1 row in set (0.000 sec)
TIMESTAMPDIFF 関数
指定した単位で指定した 2 つの日付を減算します。
- 例） 今日（2020-12-20）から何日前に受診したかを調べる
> SELECT date, TIMESTAMPDIFF(DAY, date, '2020-12-20') AS diff_days FROM checkup;
+---------------------+-----------+
| date | diff_days |
+---------------------+-----------+
| 2018-12-15 09:00:00 | 735 |
| 2019-12-23 11:00:00 | 362 |
| 2020-12-20 10:00:00 | 0 |
+---------------------+-----------+
3 rows in set (0.005 sec)
関数の引数を後ろから順に（'2020-12-20' から date を）減算します。
以下のように値を入れ替えると負の値になります。
> SELECT date, TIMESTAMPDIFF(DAY, '2020-12-20', date) AS diff_days FROM checkup;
+---------------------+-----------+
| date | diff_days |
+---------------------+-----------+
| 2018-12-15 09:00:00 | -735 |
| 2019-12-23 11:00:00 | -362 |
| 2020-12-20 10:00:00 | 0 |
+---------------------+-----------+
3 rows in set (0.000 sec)
TIMESTAMP とありますが、DATETIME 型と TIMESTAMP 型の減算も可能です。
（これに気づくまで型を合わせる必要があるのかと、かなり苦戦しました）
> SELECT date, created_at, TIMESTAMPDIFF(DAY, date, created_at) AS diff_days FROM checkup;
+---------------------+---------------------+-----------+
| date | created_at | diff_days |
+---------------------+---------------------+-----------+
| 2018-12-15 09:00:00 | 2018-12-16 09:00:00 | 1 |
| 2019-12-23 11:00:00 | 2019-12-25 11:00:00 | 2 |
| 2020-12-20 10:00:00 | 2020-12-20 10:00:00 | 0 |
+---------------------+---------------------+-----------+
3 rows in set (0.003 sec)
DATE_FORMAT 関数
日付を指定したフォーマットに変換します。
- 例） レコード作成日時（created_at）を日付のみの書式に変換
> SELECT created_at, DATE_FORMAT(created_at, '%Y-%m-%d') AS created_date FROM checkup;
+---------------------+--------------+
| created_at | created_date |
+---------------------+--------------+
| 2018-12-16 09:00:00 | 2018-12-16 |
| 2019-12-25 11:00:00 | 2019-12-25 |
| 2020-12-20 10:00:00 | 2020-12-20 |
+---------------------+--------------+
3 rows in set (0.005 sec)
SEC_TO_TIME 関数
秒を HH:MM:SS 形式に変換します。
- 例） TIMESTAMPDIFF 関数で求めた秒を HH:MM:SS 形式に変換
> SELECT TIMESTAMPDIFF(SECOND, '2020-12-20 01:23:45', '2020-12-20 12:34:56') AS sec, SEC_TO_TIME(TIMESTAMPDIFF(SECOND, '2020-12-20 01:23:45', '2020-12-20 12:34:56')) AS time;
+-------+----------+
| sec | time |
+-------+----------+
| 40271 | 11:11:11 |
+-------+----------+
1 row in set (0.004 sec)
秒のままに比べて、どれくらい差があるのか分かりやすくなりました。
おまけ： Python で扱うときにハマった点
Python でデータを扱おうとしてハマったのが、SEC_TO_TIME 関数で変換したものは timedelta64[ns] として扱われる、ということです。
以下のサンプルコードで実験してみます。
sample.py
from typing import Any, Dict
import mysql.connector
import pandas as pd
from mysql.connector import MySQLConnection
from mysql.connector.cursor import MySQLCursor
from pandas import DataFrame
settings: Dict[str, Any] = {
"database": "sample",
"user": "user",
"password": "password",
"host": "localhost",
"port": 3306,
"raise_on_warnings": True
}
def main() -> None:
conn: MySQLConnection = mysql.connector.connect(**settings)
cur: MySQLCursor = conn.cursor()
query: str = (
"""
SELECT
TIMESTAMPDIFF(SECOND, '2020-12-20 01:23:45', '2020-12-20 12:34:56') AS sec,
SEC_TO_TIME(TIMESTAMPDIFF(SECOND, '2020-12-20 01:23:45', '2020-12-20 12:34:56')) AS time;
"""
)
cur.execute(query)
df: DataFrame = pd.DataFrame(cur.fetchall(), columns=["sec", "time"])
print(df["time"])
if __name__ == "__main__":
main()
上記を実行すると、dtype が timedelta64[ns] と出力されます。
'11:11:11'（HH:MM:SS）の部分だけが欲しかったのですが、このままではうまくいきません。
$ python sample.py
0 0 days 11:11:11
Name: time, dtype: timedelta64[ns]
Python で型変換処理を行うこともできますが、せっかくなので SQL を少し変更してみます。
DATE_FORMAT 関数に '%T' を指定すると 24 時間表記の時間 (HH:MM:SS)に変換できるので、time を DATE_FORMAT 関数で変換した time_string という項目を SELECT 句に追加しました。
sample2.py
from typing import Any, Dict
import mysql.connector
import pandas as pd
from mysql.connector import MySQLConnection
from mysql.connector.cursor import MySQLCursor
from pandas import DataFrame
settings: Dict[str, Any] = {
"database": "sample",
"user": "user",
"password": "password",
"host": "localhost",
"port": 3306,
"raise_on_warnings": True
}
def main() -> None:
conn: MySQLConnection = mysql.connector.connect(**settings)
cur: MySQLCursor = conn.cursor()
query: str = (
"""
SELECT
TIMESTAMPDIFF(SECOND, '2020-12-20 01:23:45', '2020-12-20 12:34:56') AS sec,
SEC_TO_TIME(TIMESTAMPDIFF(SECOND, '2020-12-20 01:23:45', '2020-12-20 12:34:56')) AS time,
DATE_FORMAT(SEC_TO_TIME(TIMESTAMPDIFF(SECOND, '2020-12-20 01:23:45', '2020-12-20 12:34:56')), '%T') AS time_str;
"""
)
cur.execute(query)
df: DataFrame = pd.DataFrame(cur.fetchall(), columns=["sec", "time", "time_string"])
print(df["time_string"])
if __name__ == "__main__":
main()
修正したコードを実行してみます。
$ python sample2.py
0 11:11:11
Name: time_string, dtype: object
HH:MM:SS 形式のまま、object（文字列）として出力されました。
まとめ
2020 年をふりかえると、JSON や日付に関する関数に特にお世話になった 1 年でした。
次のステップとしては、便利な SQL 関数を活用しつつ、パフォーマンス面もより意識して SQL を使っていきたいと思います。