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 を使っていきたいと思います。

2020 年にお世話になった SQL 関数（for MariaDB）