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

Advent Calendar 20 日目の記事です。

# はじめに
ここ最近、素の SQL でデータを触ることが増えてきました。
便利な関数のおかげで SQL だけで処理が完結できるので、データ取得後にゴリゴリとコードを書かずにすんでいます。
ちょうど年末でもあるので、お世話になった関数に感謝しつつ、この 1 年をふりかえってみたいと思います。

# 前提
説明のしやすさから、前後関係を意識している箇所はありますが、関数の並びに深い意味はありません。
SQL 実行後のデータの扱いについて補足するために、一部 Python のコードが登場します。

## 動作環境

```shell

$ 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 |


テーブルの内容は以下のとおり。

```sql

> 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 埋め

```sql

> 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 桁で * 埋め

```sql

> 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）から空白を除去

```sql

> 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）を抽出

```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.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 文字列を扱うことができます。（詳細は [こちら](https://mariadb.com/kb/en/json-data-type/)）

実際に型を確認してみると、result は LONGTEXT 型となっています。
（冒頭の「使用するテーブル」では result の型を JSON と表記していますが、厳密には LONGTEXT が正しいです）

```sql

> 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 を流してみると、

```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 周りに違いがあるようです。（詳細は[こちら](https://mariadb.com/kb/en/text/#difference-between-varcharvarchar-and-text)）

> VARCHAR columns can be fully indexed. TEXT columns can only be indexed over a specified length.
> （Google 翻訳： VARCHAR 列は完全に索引付けできます。 TEXT 列は、指定された長さでのみ索引付けできます。）

## JSON_UNQUOTE 関数
JSON 文字列のダブルクォートを除去します。

* 例） 健康診断の結果（result）から血液型（blood）を抽出

```sql

> 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 関数を使って、こんな風に対応していました。

```sql

> 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)の曜日を取得

```sql

> 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 文と組み合わせるとこんな風にもできます。
（長くなるので、必要な曜日だけ条件分岐させています）

```sql

> 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)の曜日を取得

```sql

> 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 文と組み合わせるときはインデックスの値に注意します。

```sql

> 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)が平日のデータを取得

```sql

> 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)が平日のデータを取得

```sql

> 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）の目安を求める

```sql

> 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)
```

マイナスの値を指定すると減算もできます。

```sql

> 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）から何日前に受診したかを調べる

```sql

> 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 を）減算します。
以下のように値を入れ替えると負の値になります。

```sql

> 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 型の減算も可能です。
（これに気づくまで型を合わせる必要があるのかと、かなり苦戦しました）

```sql

> 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）を日付のみの書式に変換

```sql

> 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] として扱われる、ということです。

以下のサンプルコードで実験してみます。

```python: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 句に追加しました。


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