LoginSignup
6
2

More than 1 year has passed since last update.

ミライトデザインAdvent Calendar 2022Day 21
@Nyokkiin株式会社ミライトデザイン

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

Last updated at Posted at 2022-12-21

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

突然ですが、みなさんは最近SQLを書いていますか？
最近ではRedashのような便利なツールもあり、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日のうちに売上がない場合もありえます。
この状態を本記事では「歯抜け」と表現します。

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

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

データ準備

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のデータを改めて見ていきましょう。

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

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

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

この表Aが「入れ歯」というわけですね。
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 になるように使用しています。

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

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

STEP 2. CROSS JOINを利用する

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

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

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

cross_join.png

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

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

この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です。

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

下記のような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 関数を使うと便利です。

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

SELECT generate_series(1, 5);

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

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

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をすることで入れ歯が作成できます。

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 という便利な関数がないので、再帰を使うことで連番を取得していきます。

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

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

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 関数を使用しましょう。

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をすることで入れ歯が作成できます。

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

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

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 さんの記事となります。
どんな記事を書いてくれるのか自分も知らないので、ドキドキしながらお待ち下さいｗ

6
2
3

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
6
2