NISLab 小板研究室Publicationへの投稿
🗃️

リレーショナルデータベースにおけるNULLの区別

suu
に公開
MySQL
DB
tech

リレーショナルデータベースにおけるNULLの区別

こんにちは，NISLab B4の吉本です．

NISLab Advent Calendar ７日目は，研究テーマとは異なりますが，リレーショナルデータベースのNULLについてお話ししたいと思います🎄🎁

私たちの研究室

https://nisk.doshisha.ac.jp/

アドベントカレンダー７日目

https://nislab-advent-calendar-2024-12.vercel.app

早速ですが，リレーショナルデータベースの論理体系は，真・偽に加えてNULLを持つ３値論理が採用されている一方で，NULLには2通りの意味があることはご存知でしょうか？

３値論理でも一緒くたに扱われる２種類のNULLについて，それぞれの違いが意識できる問題をLeetCodeで見つけたので，それを紹介してNULLの理解を深めたいと思います．

【参考】LeetCodeとは？

LeetCodeは、コーディング面接準備のためのオンラインプラットフォームである。求職者やコーディング愛好家の間で人気を博している。
https://ja.wikipedia.org/wiki/LeetCode

NULLには２種類の意味がある

リレーショナルデータモデルにおいて，NULLには次の２種類があります．

  • unknown（未知）
  • property inapplicable（適用不可属性）

具体例で考えると，次のような顧客テーブルがあったとき，「年齢のNULL」と「ペットの名前のNULL」は違う意味を持つことがイメージできると思います．

リレーショナルデータベースではこの２種類のNULLを区別しない，真・偽・NULLの３値の論理体系が採用されています．

３値論理についてはWikipediaなど様々な記事で詳解されているので，本記事では触れずに進みます．

https://ja.wikipedia.org/wiki/3値論理

LeetCodeの例で見るNULLの区別

ここからが本題です．

ここまでの記事では，リレーショナルデータモデルでは２種類のNULLがあるが，２種類をひとまとめにして扱う３値論理が採用されていることを述べてきました．

私は普段この２種類のNULLの違いを意識できていなかったのですが，違いを意識させられる問題をLeetCodeで見つけました．

良い機会なので，そのLeetCodeの問題，262. Trips and Usersをじっくり見ていきます（問題はLeetCodeに権利があるので，詳細はLeetCodeのサイトでご確認ください🙏）．

https://leetcode.com/problems/trips-and-users/

問題の概要

タクシー手配のステータスのテーブル（Trips）とユーザテーブル（Users）を使って，「10月1日~10月3日の3日間の，バンされていないユーザによるタクシー手配の，1日あたりのキャンセル率」を求めよ．

この問題のテストケース11を見てみます．
テストケース11で使用する２つのテーブルと想定解は，以下の通りに設定されています．

Tripsテーブル

| id   | client_id | driver_id | city_id | status    | request_at |
| ---- | --------- | --------- | ------- | --------- | ---------- |
| 1111 | 1         | 10        | 1       | completed | 2013-10-01 |

Usersテーブル

| users_id | banned | role   |
| -------- | ------ | ------ |
| 1        | Yes    | client |
| 10       | No     | driver |

想定解

| Day | Cancellation Rate |
| --- | ----------------- |

この想定解ではDay，Cancellation RateともにNULLとなっています．

そこでDay，Cancellation Rate がNULLの内，unknownとproperty inapplicableのどちらに該当するのかを考えてみます．

想定解のNULLはunknown? property inapplicable?

順番が前後しますが，先にCancellation Rate から検討します．

Cancellation Rate は文字通りキャンセル率であり，このカラムにはTripsテーブル，Usersテーブルを用いて算出した値が入ります．

今回算出したいのは「バンされていないユーザによるタクシー手配の1日あたりのキャンセル率」ですが，テストケース11では，Tripsテーブルにはバンされたクライアントによる手配の１件のみが記録されています．

従ってCancellation Rate のNULLはproperty inapplicableであると考えられます．

次にDay のNULLについて検討します．
unknown, property inapplicable の可能性について順番に考えていきます🤔

  • Dayは unknown のNULLか？

    今回算出したいのが「1日あたりのキャンセル率」であるため，Day属性が unknown だと，タプルが意味をなさなくなる．

  • Dayは property inapplicable のNULLか？

    仮に property inapplicable であるならば，それは指定された10月1日~10月3日の間に手配が一件も無かった場合ではないか．
    しかし，テストケース11では期間中にタクシーの手配が一件行われている．

この２点から，Dayはどんなケースであっても unknown のNULLは取るべきでなく，テストケース11においては property inapplicable のNULLも不適当だと考えます．

つまりキャンセル率を分析するには，テストケース11に対して以下の結果を返すクエリが適当なのではないでしょうか？

| Day        | Cancellation Rate |
| ---------- | ----------------- |
| 2013-10-01 | -                 |


想定解のNULL考察の結論

まとめると，想定解のDay，Cancellation Rate属性のNULLはどちらもproperty inapplicable（適用不可属性）に該当するが，テストケース11ではDay はNULLを取らない方が良いと考えました．

この結論に合わせて作成した，キャンセル率を集計するクエリを載せておきます．

LeetCodeの想定解に合わせた書き方
WITH ValidClients AS(
    SELECT
        users_id AS valid_client_id
    FROM
        Users
    WHERE
        banned = 'No'
        AND
        role = 'client'
), ValidDrivers AS(
    SELECT
        users_id AS valid_driver_id
    FROM
        Users
    WHERE
        banned = 'No'
        AND
        role = 'driver'
), OctoberTrips AS(
    SELECT
        *
    FROM
        Trips
    WHERE
        Trips.request_at BETWEEN '2013-10-01' AND '2013-10-03'
)
SELECT DISTINCT
    OctoberTrips.request_at AS 'Day',
    ROUND(SUM(IF(OctoberTrips.status LIKE 'cancelled%', 1, 0)) OVER (PARTITION BY OctoberTrips.request_at)
        / SUM(1) OVER (PARTITION BY OctoberTrips.request_at), 2) AS 'Cancellation Rate'
FROM
    OctoberTrips
    INNER JOIN ValidClients
        ON  OctoberTrips.client_id = ValidClients.valid_client_id
    INNER JOIN ValidDrivers
        ON  OctoberTrips.driver_id = ValidDrivers.valid_driver_id
吉本的想定解に合わせた書き方
WITH ValidClients AS(
    SELECT
        users_id AS valid_client_id
    FROM
        Users
    WHERE
        banned = 'No'
        AND
        role = 'client'
), ValidDrivers AS(
    SELECT
        users_id AS valid_driver_id
    FROM
        Users
    WHERE
        banned = 'No'
        AND
        role = 'driver'
), OctoberTrips AS(
    SELECT
        *
    FROM
        Trips
    WHERE
        Trips.request_at BETWEEN '2013-10-01' AND '2013-10-03'
), ValidUsersOctoberTrips AS(
    SELECT
        OctoberTrips.request_at AS 'Day',
        ROUND(SUM(IF(OctoberTrips.status LIKE 'cancelled%', 1, 0)) OVER (PARTITION BY OctoberTrips.request_at)
            / SUM(1) OVER (PARTITION BY OctoberTrips.request_at), 2) AS 'Rate'
    FROM
        OctoberTrips
        INNER JOIN ValidClients
            ON  OctoberTrips.client_id = ValidClients.valid_client_id
        INNER JOIN ValidDrivers
            ON  OctoberTrips.driver_id = ValidDrivers.valid_driver_id
)
SELECT DISTINCT
    OctoberTrips.request_at AS 'Day',
    ValidUsersOctoberTrips.Rate as 'Cancellation Rate'
FROM
    OctoberTrips
    LEFT OUTER JOIN ValidUsersOctoberTrips
        ON OctoberTrips.request_at = ValidUsersOctoberTrips.Day

２つのクエリの違う点は，吉本的想定解の方ではOctoberTripsサブクエリを使用し，期間中少なくとも一件のタクシー手配が行われた日を必ずDayカラムに存在させるようにしています．

NULLの種類を考えて得られたこと

LeetCodeの想定解に文句をつける結論を出してしまいましたが，NULLには複数の意味があることを知っていると，クエリ実行結果の解釈の幅も狭まってきます．

10月1日~10月3日にタクシーの手配は一件も無かったのか，それともバンされたユーザを含めると期間中のタクシー手配はあったのかを区別できると考えて，本記事では新しい想定解を設定しました．

NULLについて考えることで，納得できるデータ集計に近づくことができたと感じます．

一方で自分と他者の「納得できる集計方法」が一致するかは，調整の余地があります．

私が新たに作った想定解には2013-10-01のタプルしか存在していませんが，NISLabメンバーからは2013-10-02，2013-10-03のタプルも作った方が，３日間のキャンセル率として理解しやすいという意見をもらいました．

次のようなイメージです．

| Day        | Cancellation Rate |
| ---------- | ----------------- |
| 2013-10-01 | -                 |
| 2013-10-02 | -                 |
| 2013-10-03 | -                 |

集計結果を複数人が見る場合は，より多くの人が理解しやすい形式にしたいところです．

そのためにはNULLの理解以外にも，分かりやすいクエリを書きカラム名を丁寧に設定することや，メタデータの整備といった方法で，理解しやすいデータ集計に近づけたいと思います．

まとめ

本記事ではLeetCodeの想定解に対してぼやく形で，２種類のNULLについての理解を試みました．

今回NULLには２種類の意味があると紹介しましたが，もっと細分化する考えもあるので，NULLの深みにはまるクリスマスも良いかもしれません🎁

LeetCodeの問題を解くと複数のテスト結果を見てクエリを修正できるので，SQLの勉強にとても良いと感じています．

皆さんもLeetCodeでアルゴリズム以外の問題にも取り組んでみてください！

今回出したNULLの区別の結論↓や新しい想定解，作成したクエリについてのご意見などは，是非コメントで教えていただけると嬉しいです👀

想定解のDay，Cancellation Rate属性のNULLはどちらもproperty inapplicable（適用不可属性）に該当するが，テストケース11ではDay はNULLを取らない方が良い

NISLab 小板研究室
NISLab 小板研究室Publication

同志社大学ネットワーク情報システム研究室（小板研究室 nisk.doshisha.ac.jp/ NISLab 小板研

Discussion