【SQL】クエリの結果に入れ歯をしてあげよう

昨日は @__tomotomon さんの`UNION`, `UNION ALL` に関する記事でした。
実行速度について計測してくれている記事は意外と少ないので、自分にとっても勉強になる記事でした。読んでね！

突然ですが、みなさんは最近SQLを書いていますか？
最近では[Redash](https://redash.io)のような便利なツールもあり、SQLを一度書いてしまえば非エンジニアの方にすぐにデータを見てもらって分析などが素早くできるようになりました。

今回はそんな分析周りで役立つ（かもしれない）SQL関連の小ネタです。

## やりたいこと

分析に関するSQLを書くときに、日付別の売上やイベント数を取得するSQLを書くことも多いかと思います。
例えば商品毎の日付別の売上を分析するため、下記のようなデータを取得することがあるとしましょう。

```sql
select * from sales;
 item_id | sales_date | amount
---------+------------+--------
       1 | 2022-01-01 |   1000
       1 | 2022-01-02 |   2000
       1 | 2022-01-03 |   4000
       2 | 2022-01-01 |   2000
       2 | 2022-01-03 |   5000
       3 | 2022-01-02 |   3000
```

このときに、商品によっては1日のうちに売上がない場合もありえます。
この状態を本記事では「歯抜け」と表現します。

本来であれば歯抜けのない下記のような状態で取得したいのですが、そのためには一工夫してクエリを書く必要があります。

```sql
SELECT ???
 item_id | sales_date | amount
---------+------------+--------
       1 | 2022-01-01 |   1000
       1 | 2022-01-02 |   2000
       1 | 2022-01-03 |   4000
       2 | 2022-01-01 |   2000
       2 | 2022-01-02 |      0
       2 | 2022-01-03 |   5000
       3 | 2022-01-01 |      0
       3 | 2022-01-02 |   3000
       3 | 2022-01-03 |      0
```

ということで、歯抜けを改善するための方法を探っていきたいと思います。
いいシステムにはいい命名が大事だとおばあちゃんに教わったので、本記事ではこの歯抜けを埋めることを「入れ歯」と呼称します。

## 環境

今回はPostgreSQL, MySQLで検証していきます。

- PostgreSQL
```
select version();
                                                   version
--------------------------------------------------------------------------------------------------------------
 PostgreSQL 15.1 on x86_64-pc-linux-musl, compiled by gcc (Alpine 11.2.1_git20220219) 11.2.1 20220219, 64-bit
```

- MySQL
```
select version();
+-----------+
| version() |
+-----------+
| 8.0.31    |
+-----------+
```

## データ準備

```sql
CREATE TABLE items (
    id INT NOT NULL PRIMARY KEY,
    name VARCHAR(255)
);

CREATE TABLE sales (
    item_id INT NOT NULL,
    sales_date DATE NOT NULL,
    amount INT NOT NULL,
    PRIMARY KEY (item_id, sales_date)
);

INSERT INTO items VALUES
(1, '商品１'),
(2, '商品２'),
(3, '商品３');

INSERT INTO sales VALUES
(1,'2022-01-01',1000),
(1,'2022-01-02',2000),
(1,'2022-01-03',4000),
(2,'2022-01-01',2000),
(2,'2022-01-03',5000),
(3,'2022-01-02',3000);
```

## 入れ歯をどう作るか

### STEP 1. `LEFT JOIN` を利用する

まずは`sales`のデータを改めて見ていきましょう。

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/221479/25a2349f-d646-bb9d-ab15-077fd04aa462.png" width=400>

`item_id = 2, 3`のレコードは一部の日付のレコードが足りていないですね。
この取得結果の歯抜けを埋めるために`LEFT JOIN`を利用していこうと思います。

:::note info
`LEFT JOIN`ってなんぞ！という方は下記の記事で解説されています。
図がめっちゃわかりやすいのでおすすめです。
:::

https://zenn.dev/suzuki_hoge/articles/2022-12-table-join-1f145b243166f9

イメージで言うと、下記のように「商品」✕「日付」を網羅した表（A）を用意し、`LEFT JOIN` することで歯抜けをなくそう、というアプローチです。

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/221479/4eb77f8f-83b7-da72-9617-af70049ee55d.png" width=700>

この表Aが「入れ歯」というわけですね。
SQLで表現すると下記のようになります。

```sql
SELECT
    A.item_id,
    A.sales_date,
    COALESCE(s.amount, 0) AS amount
FROM A
LEFT JOIN sales ON A.item_id = sales.item_id AND A.sales_date = s.sales_date;
```

なお、SQL内で使用されている `COALESCE` はPostgreSQLで使用できる関数で、値が `NULL` のときに代わりに第二引数で指定した値を返すものです。
今回は `LEFT JOIN` をしたときに `sales` テーブルにレコードが無い（＝歯抜けの行）場合、`amount` が `NULL`になるので、代わりに 0 になるように使用しています。

https://www.postgresql.jp/document/14/html/functions-conditional.html#FUNCTIONS-COALESCE-NVL-IFNULL

~~MySQLで実行する場合は `COALESCE` → `IFNULL` に置き換える必要があります。~~ 

コメントで教えていただきましたが、MySQLでも `COALESCE` を同じ用途で使えました。
知らんかった。。。

### STEP 2. `CROSS JOIN`を利用する

では表Aをどうやって作るか考えていきましょう。

表Aは「商品」✕「日付」の組み合わせを網羅したテーブルとなっていますね。
この全ての組み合わせを取得するときに利用できるのが、`CROSS JOIN` です。

`CROSS JOIN` を使用すると、下記のようなイメージで全ての組み合わせを取得できます。

![cross_join.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/221479/c32a552b-d183-7a25-c7d9-f8a56fe35803.png)

SQLで表現すると下記のようになります。

```sql
SELECT
    items.id AS item_id,
    B.sales_date
FROM items
CROSS JOIN B;
```

このSQLを先程のSQLに組み込むと下記のようになります。

```sql
SELECT
    A.item_id,
    A.sales_date,
    COALESCE(sales.amount, 0) AS amount
FROM (
    SELECT
        items.id AS item_id,
        B.sales_date
    FROM items
    CROSS JOIN B
) AS A
LEFT JOIN sales ON A.item_id = sales.item_id AND A.sales_date = sales.sales_date;
```

文字が多くて頭がグワングワンしますが、下記がイメージできていればOKです。

- A の部分が入れ歯で、`LEFT JOIN`することによって歯抜けを埋めている
- A は「商品」✕「日付」の組み合わせで、商品の一覧と日付の一覧を`CROSS JOIN`することで取得している

それでは、最後に日付の一覧（B）を取得する方法を考えていきましょう。

### STEP 3. 日付の一覧を取得する

日付の一覧（B）が保存されているテーブルが既に存在すればいいのですが、存在しない場合は新たにテーブルを作成するのは面倒です。
ここはいい感じのクエリを考えていきましょう。

まず、ぱっと思いつくのは`UNION`です。

:::note info
`UNION`ってなんぞ！という方は昨日の @__tomotomon さんの記事で説明してくれてます。見てね！
:::

https://qiita.com/__tomotomon/items/7050a80089d141f65e60

下記のようなSQLを作れば日付の一覧が取得できます。

```sql
SELECT '2022-01-01' AS sales_date 
UNION
SELECT '2022-01-02'
UNION
SELECT '2022-01-03';

 sales_date
------------
 2022-01-02
 2022-01-01
 2022-01-03
```

しかし、期間が 1 ヶ月、 1 年となってきた場合に `UNION` で書いていると時間がいくらあっても足りません。
しかも分析期間を変更する度に多大な負荷がかかるので、できれば別の方法を採用したいものです。

もっといい方式を紹介していきますが、PostgreSQL, MySQLでそれぞれ独自の関数や機能を使う必要があるので、ここからはDB別に見ていきます。

#### PostgreSQL の場合 ... `generate_series` を使おう

PostgreSQL では `generate_series` 関数を使うと便利です。

https://www.postgresql.jp/document/14/html/functions-srf.html

これは連番のレコードを作成する際などに便利な関数です。

```sql
SELECT generate_series(1, 5);

 generate_series
-----------------
               1
               2
               3
               4
               5
```

今回は連続した日付を取得する必要があるので、開始日時に `generate_series` で生成した連番を加算することで連続した日付を取得できます。

```sql
SELECT CAST('2022-01-01' AS DATE) + generate_series(0, 30) AS sales_date;

 sales_date
------------
 2022-01-01
 2022-01-02
 2022-01-03
 ...
 2022-01-30
 2022-01-31
```

これで B に当たる部分を取得できるので、`CROSS JOIN`をすることで入れ歯が作成できます。

```sql
SELECT
    items.id AS item_id,
    B.sales_date
FROM items
CROSS JOIN (SELECT CAST('2022-01-01' AS DATE) + generate_series(0, 2) AS sales_date) AS B;

 item_id | sales_date
---------+------------
       1 | 2022-01-01
       1 | 2022-01-02
       1 | 2022-01-03
       2 | 2022-01-01
       2 | 2022-01-02
       2 | 2022-01-03
       3 | 2022-01-01
       3 | 2022-01-02
       3 | 2022-01-03
```

いい感じですね。

#### MySQL の場合 ... `Recursive Common Table Expressions` を使おう

続いては、MySQL でも B を作っていきましょう。
MySQL には `generate_series` という便利な関数がないので、再帰を使うことで連番を取得していきます。

https://dev.mysql.com/doc/refman/8.0/ja/with.html#common-table-expressions-recursive

:::note warn
再帰的な共通テーブル式は MySQL 8.0.1 から追加された機能です。
もしMySQL 5.7 系を使用している方は、、、一応下記の記事で連番取得する方法を紹介しているので参考にしてみてください(T_T)
:::

https://qiita.com/Nyokki/items/da4518f7836bc8dac9be#4-union-all%E7%9B%B4%E7%A9%8D%E3%83%A6%E3%83%BC%E3%82%B6%E3%83%BC%E5%AE%9A%E7%BE%A9%E5%A4%89%E6%95%B0%E3%81%A7%E9%A0%91%E5%BC%B5%E3%82%8B

下記のようなSQLを書くと `generate_series` と同じような連番のレコードが取得できます。
細かい解説は省きますので、これはもうコピペしましょうｗ（自分もコピペしまくりです）。

```sql
WITH RECURSIVE seq AS (SELECT 1 AS value UNION ALL SELECT value + 1 FROM seq WHERE value < 5)
SELECT * FROM seq;

+-------+
| value |
+-------+
|     1 |
|     2 |
|     3 |
|     4 |
|     5 |
+-------+
```

これを利用して、連続する日付を取得していきます。
日付の加算には `DATE_ADD` 関数を使用しましょう。

```sql
WITH RECURSIVE seq AS (SELECT 0 AS value UNION ALL SELECT value + 1 FROM seq WHERE value < 30)
SELECT DATE_ADD('2022-01-01', INTERVAL value DAY) AS sales_date FROM seq;

+------------+
| sales_date |
+------------+
| 2022-01-01 |
| 2022-01-02 |
| 2022-01-03 |
| ...        |
| 2022-01-30 |
| 2022-01-31 |
+------------+
```

これで B に当たる部分を取得できるので、`CROSS JOIN`をすることで入れ歯が作成できます。

```sql
SELECT
    items.id AS item_id,
    B.sales_date
FROM items
CROSS JOIN (
    WITH RECURSIVE seq AS (SELECT 0 AS value UNION ALL SELECT value + 1 FROM seq WHERE value < 2)
    SELECT DATE_ADD('2022-01-01', INTERVAL value DAY) AS sales_date FROM seq
) AS B
ORDER BY item_id, sales_date; -- 表示を見やすくするためにORDER BYを指定

+---------+------------+
| item_id | sales_date |
+---------+------------+
|       1 | 2022-01-01 |
|       1 | 2022-01-02 |
|       1 | 2022-01-03 |
|       2 | 2022-01-01 |
|       2 | 2022-01-02 |
|       2 | 2022-01-03 |
|       3 | 2022-01-01 |
|       3 | 2022-01-02 |
|       3 | 2022-01-03 |
+---------+------------+
```

PostgreSQL に比べて複雑ですね。 `generate_series` ほちい。
よいこのみんなはサンタさんにお願いしておいてください🎅

### 最終形態

ということで、最後に PostgreSQL, MySQL の歯の治療が完成したSQLを見ていきたいと思います。

#### PostgreSQL

```sql
SELECT
    A.item_id,
    A.sales_date,
    COALESCE(sales.amount, 0) AS amount
FROM (
    SELECT
        items.id AS item_id,
        B.sales_date
    FROM items
    CROSS JOIN (SELECT CAST('2022-01-01' AS DATE) + generate_series(0, 2) AS sales_date) AS B
) A
LEFT JOIN sales ON A.item_id = sales.item_id AND A.sales_date = sales.sales_date
ORDER BY A.item_id, A.sales_date; -- 表示を見やすくするためにORDER BYを指定

 item_id | sales_date | amount
---------+------------+--------
       1 | 2022-01-01 |   1000
       1 | 2022-01-02 |   2000
       1 | 2022-01-03 |   4000
       2 | 2022-01-01 |   2000
       2 | 2022-01-02 |      0
       2 | 2022-01-03 |   5000
       3 | 2022-01-01 |      0
       3 | 2022-01-02 |   3000
       3 | 2022-01-03 |      0
```

#### MySQL

```sql
SELECT
    A.item_id,
    A.sales_date,
    IFNULL(sales.amount, 0) AS amount
FROM (
    SELECT
        items.id AS item_id,
        B.sales_date
    FROM items
    CROSS JOIN (
        WITH RECURSIVE seq AS (SELECT 0 AS value UNION ALL SELECT value + 1 FROM seq WHERE value < 2)
        SELECT DATE_ADD('2022-01-01', INTERVAL value DAY) AS sales_date FROM seq
    ) AS B
) A
LEFT JOIN sales ON A.item_id = sales.item_id AND A.sales_date = sales.sales_date
ORDER BY A.item_id, A.sales_date; -- 表示を見やすくするためにORDER BYを指定

+---------+------------+--------+
| item_id | sales_date | amount |
+---------+------------+--------+
|       1 | 2022-01-01 |   1000 |
|       1 | 2022-01-02 |   2000 |
|       1 | 2022-01-03 |   4000 |
|       2 | 2022-01-01 |   2000 |
|       2 | 2022-01-02 |      0 |
|       2 | 2022-01-03 |   5000 |
|       3 | 2022-01-01 |      0 |
|       3 | 2022-01-02 |   3000 |
|       3 | 2022-01-03 |      0 |
+---------+------------+--------+
```

無事歯抜けが解消されていますね。
長くなってしまいましたが、治療お疲れ様でした。

## おしまい

というわけでクエリの結果が歯抜けにならないような方法のご紹介でした。
集計や分析のクエリを書いていると、意外と歯抜けに気づかないことも多いので意識してみるといいことあるかもしれません。

今回は順序立てて説明しやすい方式を採用しましたが、SQLの書き方は1つではないので興味があれば他の書き方も探してみてください。

明日は @tkek321 さんの記事となります。
どんな記事を書いてくれるのか自分も知らないので、ドキドキしながらお待ち下さいｗ