# COALESCE を使用した NULL 値の処理

**URL:** <https://meta.discourse.org/t/handling-null-values-with-coalesce/278813>\
**Category:** Data & reporting\
**Tags:** sql-tutorial\
**Created:** [2023 年 9 月 13 日午前 1:15 UTC](https://meta.discourse.org/t/handling-null-values-with-coalesce/278813 "2023-09-13T01:15:35Z")\
**Posts on this page:** 1\
**Showing post:** 1

<div class="post-metadata">

**Author:** ![SaraDev](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/saradev/32/335139_2.png) [@SaraDev](https://meta.discourse.org/u/SaraDev)\
**Post date:** [2023 年 9 月 13 日午前 1:15 UTC](https://meta.discourse.org/t/handling-null-values-with-coalesce/278813/1 "2023-09-13T01:15:35Z")

</div>

このチュートリアルでは、[Data Explorer](https://meta.discourse.org/t/32566?silent=true) の SQL クエリにおける `COALESCE` 関数の使い方について解説します。

`COALESCE` を使うと、クエリ結果の NULL 値を処理できます。データに NULL 値が含まれている場合、`COALESCE` を使用して、これらの NULL 値に対してデフォルト値（例：0）を指定できます。

後続の計算やデータ分析を行う際に NULL 値が問題や誤解を招く可能性がある場合、`COALESCE` は特に有用です。

# 構文

`COALESCE` 関数は 2 つ以上の引数を受け取り、左から右へ順に最初に遭遇する NULL 以外の値を返します。すべての引数が NULL の場合、`COALESCE` は NULL を返します。

`COALESCE` の基本的な構文は以下の通りです。

```plaintext
COALESCE(value1, value2, ..., valueN)

```

例えば、`COALESCE(NULL, 1, 2)` は `1` を返します。なぜなら `1` が最初の NULL 以外の引数だからです。

# 例クエリ

[Data Explorer](https://meta.discourse.org/t/32566?silent=true) クエリにおける `COALESCE` の使い方を理解するために、いくつかの例クエリを見てみましょう。

## 投稿数、いいね数、ブックマーク数

_難易度：初心者_

このクエリは、サイト上の各ユーザーが作成した投稿数、受け取ったいいね数、受け取ったブックマーク数の合計を取得します。ユーザーに投稿、いいね、またはブックマークがない場合、`COALESCE` 関数は NULL の代わりに 0 を返します。

```sql
SELECT 
    users.id AS user_id,
    users.username,
    COALESCE(COUNT(posts.id), 0) AS post_count,
    COALESCE(SUM(posts.like_count), 0) AS likes_received,
    COALESCE(SUM(posts.bookmark_count), 0) AS bookmarks_received
FROM 
    users
LEFT JOIN 
    posts ON users.id = posts.user_id
GROUP BY 
    users.id, users.username
ORDER BY 
    post_count DESC, likes_received DESC, bookmarks_received DESC

```

_結果例:_

| user | username | post\_count | likes\_received | bookmarks\_received |
| --- | --- | --- | --- | --- |
| 1 | alice | 345 | 6 | 9 |
| 2 | bella | 278 | 5 | 6 |
| 3 | charlie | 37 | 3 | 3 |
| 4 | dave | 0 | 0 | 0 |

このクエリでは、`users` テーブルと `posts` テーブルを `user_id` フィールドで結合しています。その後、`COALESCE` 関数を使用して、ユーザーに投稿、いいね、またはブックマークがない場合に NULL の代わりに 0 を返すようにしています。結果はユーザー ID とユーザー名でグループ化され、投稿数、いいね数、ブックマーク数の降順でソートされます。

## ユーザーごとのトピック数と返信数

_難易度：中級_

このクエリは、2 つの日付の間に行われた各ユーザーのトピック数と返信数を取得します。ユーザーにトピックや返信がない場合、`COALESCE` は NULL の代わりに 0 を返します。

```sql
-- [params]
-- date :start_date 
-- date :end_date
-- string NULL:username

WITH qtt_topics AS (
    SELECT 
        t.user_id,
        COUNT(*) AS topics
    FROM topics t
    WHERE    
        t.user_id > 0 
        AND t.deleted_at ISNULL
        AND t.archetype = 'regular'
        AND t.created_at::date BETWEEN :start_date AND :end_date
    GROUP BY t.user_id
    ),
    
qtt_replies AS (
    SELECT 
        p.user_id,
        COUNT(*) AS replies
    FROM posts p
    WHERE    
        p.user_id > 0 
        AND p.deleted_at ISNULL
        AND p.post_number != 1
        AND p.created_at::date BETWEEN :start_date AND :end_date
    GROUP BY p.user_id
    ),

total AS (
    SELECT
        COALESCE(qr.user_id, qt.user_id) user_id,
        COALESCE(topics,0) qtt_topics,
        COALESCE(replies,0) qtt_replies
    FROM qtt_topics qt
    FULL JOIN qtt_replies qr ON qt.user_id = qr.user_id
    ORDER BY user_id)

SELECT 
    username,
    qtt_topics,
    qtt_replies
FROM total
INNER JOIN users u ON u.id = user_id
WHERE :username ISNULL OR LOWER(username) LIKE '%'||LOWER(:username)||'%'

```

_結果例:_

| username | qtt\_topics | qtt\_replies |
| --- | --- | --- |
| Alice | 10 | 50 |
| Bella | 15 | 45 |
| Charlie | 12 | 30 |

このクエリでは、`total` 共通テーブル式（CTE）内で `COALESCE` が使用されています。`qtt_topics` または `qtt_replies` のいずれかで `user_id` が NULL の場合、もう一方の値を使用するようにしています。これは、`qtt_topics` と `qtt_replies` を結合するために FULL JOIN が使用されており、ユーザーがトピックしかない場合（またはその逆の場合）、その `user_id` がどちらかのテーブルで NULL になる可能性があるためです。`COALESCE` はこれを防ぎます。

> **詳細な説明（インラインコメント付き）**
>
> ```sql
> -- [params]
> -- date :start_date 
> -- date :end_date
> -- string NULL:username
> 
> -- ユーザーごとのトピック数をカウントするための CTE（共通テーブル式）を定義
> WITH qtt_topics AS (
> SELECT 
> t.user_id, -- ユーザー ID
> COUNT(*) AS topics -- トピック数
> FROM topics t -- topics テーブルから
> WHERE    
> t.user_id > 0 -- 0 以外のユーザー ID のみ考慮
> AND t.deleted_at ISNULL -- 削除されていないトピックのみ考慮
> AND t.archetype = 'regular' -- 通常のトピックのみ考慮
> AND t.created_at::date BETWEEN :start_date AND :end_date -- start_date と end_date の間に作成されたトピックのみ考慮
> GROUP BY t.user_id -- ユーザー ID でグループ化し、ユーザーごとのトピック数を取得
> ),
>     
> -- ユーザーごとの返信数をカウントするための CTE を定義
> qtt_replies AS (
> SELECT 
> p.user_id, -- ユーザー ID
> COUNT(*) AS replies -- 返信数
> FROM posts p -- posts テーブルから
> WHERE    
> p.user_id > 0 -- 0 以外のユーザー ID のみ考慮
> AND p.deleted_at ISNULL -- 削除されていない投稿のみ考慮
> AND p.post_number != 1 -- トピックの最初の投稿（つまり返信）以外の投稿のみ考慮
> AND p.created_at::date BETWEEN :start_date AND :end_date -- start_date と end_date の間に作成された投稿のみ考慮
> GROUP BY p.user_id -- ユーザー ID でグループ化し、ユーザーごとの返信数を取得
> ),
> 
> -- ユーザーごとのトピック数と返信数を結合するための CTE を定義
> total AS (
> SELECT
> COALESCE(qr.user_id, qt.user_id) user_id, -- ユーザー ID（qtt_replies または qtt_topics のいずれかから）
> COALESCE(topics,0) qtt_topics, -- トピック数（NULL の場合は 0 を返す）
> COALESCE(replies,0) qtt_replies -- 返信数（NULL の場合は 0 を返す）
> FROM qtt_topics qt -- qtt_topics CTE から
> FULL JOIN qtt_replies qr ON qt.user_id = qr.user_id -- ユーザー ID で qtt_replies CTE と結合
> ORDER BY user_id -- ユーザー ID でソート
> )
> 
> -- 最終結果セットを取得するためのメインクエリ
> SELECT 
> username, -- ユーザー名
> qtt_topics, -- トピック数
> qtt_replies -- 返信数
> FROM total -- total CTE から
> INNER JOIN users u ON u.id = user_id -- ユーザー ID で users テーブルと結合
> WHERE :username ISNULL OR LOWER(username) LIKE '%'||LOWER(:username)||'%' -- ユーザー名でフィルタリング（指定された場合）
> 
> ```

## 解決済み質問データ

_難易度：上級_ / _[Discourse Solved](https://meta.discourse.org/t/discourse-solved/30155) プラグインが必要_

このクエリは、トピックの詳細（解決済みかどうか、最初の返信までの時間、解決までの時間、その他の関連統計など）を取得するために使用されます。

このクエリは、サイト上のすべてのトピックが解決可能であると仮定しています。

```sql
--[params]
-- date :start_date = 2022-01-01
-- date :end_date = 2024-01-01
-- text :tag_name = all

WITH valid_topics AS (
    SELECT 
        t.id,
        t.user_id,
        t.title,
        t.views,
        posts_count-1 AS "posts_count", 
        t.created_at,
        (CURRENT_DATE::date-t.created_at::date) AS "total_days",
        string_agg(tags.name, ', ') AS tag_names
    FROM topics t
    LEFT JOIN topic_tags tt ON tt.topic_id = t.id
    LEFT JOIN tags ON tags.id = tt.tag_id
    LEFT JOIN tag_group_memberships tgm ON tgm.tag_id = tt.tag_id
    LEFT JOIN (SELECT topic_id, MIN(created_at) AS "created_at"
                FROM posts
                WHERE deleted_at ISNULL
                    AND post_type = 1
                    AND post_number > 1
                GROUP BY topic_id) date_first_reply ON date_first_reply.topic_id = t.id
    WHERE t.deleted_at ISNULL
        AND t.created_at::date BETWEEN :start_date AND :end_date
        AND t.archetype = 'regular'
    GROUP BY t.id, t.user_id, t.title, t.views, posts_count, t.created_at
),

solved_topics AS (
    SELECT 
        vt.id,
        tcf.created_at
    FROM topic_custom_fields tcf
    INNER JOIN valid_topics vt ON vt.id = tcf.topic_id
    WHERE tcf.name = 'accepted_answer_post_id'
),

last_reply AS (
    SELECT p.topic_id, p.user_id FROM posts p
    INNER JOIN (SELECT topic_id, MAX(id) AS post FROM posts p
                WHERE deleted_at ISNULL
                AND post_type = 1
                GROUP BY topic_id) x ON x.post = p.id
),

first_reply AS (
    SELECT p.topic_id, p.user_id, p.created_at FROM posts p
    INNER JOIN (SELECT topic_id, MIN(id) AS post FROM posts p
                WHERE deleted_at ISNULL
                AND post_type = 1
                AND post_number > 1
                GROUP BY topic_id) x ON x.post = p.id
)

SELECT
    CASE 
        WHEN st.id IS NOT NULL THEN 'solved'
        ELSE 'unsolved'
    END AS status,
    vt.tag_names,
    vt.id AS topic_id,
    vt.user_id topic_user_id,
    ue.email,
    vt.title,
    vt.views,
    lr.user_id AS last_reply_user_id,
    ue2.email AS last_reply_user_email,
    vt.created_at::date topic_create,
    COALESCE(TO_CHAR(fr.created_at,'YYYY-MM-DD'),'') AS first_reply_create,
    COALESCE(TO_CHAR(st.created_at,'YYYY-MM-DD'),'') AS solution_create,
    COALESCE(fr.created_at::date - vt.created_at::date,0) AS "time_first_reply(days)",
    COALESCE(CEIL(extract(epoch from (fr.created_at - vt.created_at))/3600.00),0) as "time_first_reply(hours)",
    COALESCE(st.created_at::date - vt.created_at::date,0) AS "time_solution(days)",
    COALESCE(CEIL(extract(epoch from (st.created_at - vt.created_at))/3600.00),0) as "time_solution(hours)",
    posts_count AS number_of_replies,
    total_days AS total_days_without_solution
FROM valid_topics vt
LEFT JOIN last_reply lr ON lr.topic_id = vt.id
LEFT JOIN first_reply fr ON fr.topic_id = vt.id
LEFT JOIN solved_topics st ON st.id = vt.id
INNER JOIN user_emails ue ON vt.user_id = ue.user_id AND ue."primary" = true
LEFT JOIN user_emails ue2 ON lr.user_id = ue2.user_id AND ue2."primary" = true
WHERE (:tag_name = 'all' OR LOWER(vt.tag_names) LIKE '%' || LOWER(:tag_name) || '%')
ORDER BY tag_names, total_days DESC

```

_結果例:_

| status | tag\_names | topic | topic\_user | email | title | views | last\_reply\_user | last\_reply\_user\_email | topic\_create | first\_reply\_create | solution\_create | time\_first\_reply(days) | time\_first\_reply(hours) | time\_solution(days) | time\_solution(hours) | number\_of\_replies | total\_days\_without\_solution |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| solved | a, c, b | A Topic Title (7) | alice | [alice@example.com](mailto:alice@example.com) | A Topic Title | 58 | bella | [bella@example.com](mailto:bella@example.com) | 2023-08-25 | 2023-08-25 | 2023-08-29 | 0 | 1 | 1 | 24 | 9 | 4 |
| unsolved | tag1 | Welcome to the Lounge (3) | system | no\_email | Welcome to the Lounge | 3 | system | no\_email | 2023-05-01 | | | 0 | 0 | 0 | 0 | 2 | 134 |

このクエリでは、`COALESCE` が以下の行で使用されています：

- `COALESCE(TO_CHAR(fr.created_at,'YYYY-MM-DD'),'') AS first_reply_create`：この行は、最初の返信の `created_at` 日付を文字列に変換しています。最初の返信が存在しない場合（つまり、`fr.created_at` が NULL の場合）、空文字列（‘’）を返します。
- `COALESCE(TO_CHAR(st.created_at,'YYYY-MM-DD'),'') AS solution_create`：上記と同様に、この行は解決の `created_at` 日付を文字列に変換しています。解決が存在しない場合（つまり、`st.created_at` が NULL の場合）、空文字列（‘’）を返します。
- `COALESCE(fr.created_at::date - vt.created_at::date,0) AS "time_first_reply(days)"`：この行は、トピックの作成と最初の返信との間の時間差を日単位で計算しています。最初の返信が存在しない場合（つまり、`fr.created_at` が NULL の場合）、0 を返します。
- `COALESCE(CEIL(extract(epoch from (fr.created_at - vt.created_at))/3600.00),0) as "time_first_reply(hours)"`：この行は、トピックの作成と最初の返信との間の時間差を時間単位で計算しています。最初の返信が存在しない場合（つまり、`fr.created_at` が NULL の場合）、0 を返します。
- `COALESCE(st.created_at::date - vt.created_at::date,0) AS "time_solution(days)"`：この行は、トピックの作成と解決との間の時間差を日単位で計算しています。解決が存在しない場合（つまり、`st.created_at` が NULL の場合）、0 を返します。
- `COALESCE(CEIL(extract(epoch from (st.created_at - vt.created_at))/3600.00),0) as "time_solution(hours)"`：この行は、トピックの作成と解決との間の時間差を時間単位で計算しています。解決が存在しない場合（つまり、`st.created_at` が NULL の場合）、0 を返します。

これらのすべての場合において、`COALESCE` は最終結果に NULL 値が表示されないようにするために使用されており、これによりクエリ結果の可読性が向上し、後続のデータ処理や分析に役立ちます。

> **詳細な説明（インラインコメント付き）**
>
> ```sql
> --[params]
> -- date :start_date = 2022-01-01
> -- date :end_date = 2024-01-01
> -- text :tag_name = all
> 
> -- 有効なトピック用の CTE を定義
> WITH valid_topics AS (
> -- 必要なフィールドを選択
> SELECT 
> t.id, -- トピック ID
> t.user_id, -- ユーザー ID
> t.title, -- トピックタイトル
> t.views, -- ビュー数
> posts_count-1 AS "posts_count", -- トピック内の投稿数
> t.created_at, -- トピックの作成日
> (CURRENT_DATE::date-t.created_at::date) AS "total_days", -- トピック作成からの総日数
> string_agg(tags.name, ', ') AS tag_names -- トピックに関連するすべてのタグを結合
> FROM topics t -- topics テーブルから
> -- タグ名を取得するために必要なテーブルを結合
> LEFT JOIN topic_tags tt ON tt.topic_id = t.id
> LEFT JOIN tags ON tags.id = tt.tag_id
> LEFT JOIN tag_group_memberships tgm ON tgm.tag_id = tt.tag_id
> -- 各トピックの最初の返信の日付を取得するサブクエリ
> LEFT JOIN (SELECT topic_id, MIN(created_at) AS "created_at"
> FROM posts
> WHERE deleted_at ISNULL
> AND post_type = 1
> AND post_number > 1
> GROUP BY topic_id) date_first_reply ON date_first_reply.topic_id = t.id
> WHERE t.deleted_at ISNULL -- 削除されていないトピックのみ考慮
> AND t.created_at::date BETWEEN :start_date AND :end_date -- start_date と end_date の間に作成されたトピックのみ考慮
> AND t.archetype = 'regular' -- 通常のトピックのみ考慮
> GROUP BY t.id, t.user_id, t.title, t.views, posts_count, t.created_at -- 正しいカウントを取得するために必要なフィールドでグループ化
> ),
> 
> -- 解決済みトピック用の CTE を定義
> solved_topics AS (
> -- トピック ID と解決の作成日を選択
> SELECT 
> vt.id,
> tcf.created_at
> FROM topic_custom_fields tcf -- topic_custom_fields テーブルから
> INNER JOIN valid_topics vt ON vt.id = tcf.topic_id -- valid_topics CTE と結合
> WHERE tcf.name = 'accepted_answer_post_id' -- 承認された回答を持つトピックのみ考慮
> ),
> 
> -- 各トピックの最後の返信用の CTE を定義
> last_reply AS (
> -- トピック ID と最後の返信のユーザー ID を選択
> SELECT p.topic_id, p.user_id FROM posts p
> INNER JOIN (SELECT topic_id, MAX(id) AS post FROM posts p -- 各トピックの最後の投稿の ID を取得するサブクエリ
> WHERE deleted_at ISNULL
> AND post_type = 1
> GROUP BY topic_id) x ON x.post = p.id -- posts テーブルと結合して最後の返信のユーザー ID を取得
> ),
> 
> -- 各トピックの最初の返信用の CTE を定義
> first_reply AS (
> -- トピック ID、ユーザー ID、最初の返信の作成日を選択
> SELECT p.topic_id, p.user_id, p.created_at FROM posts p
> INNER JOIN (SELECT topic_id, MIN(id) AS post FROM posts p -- 各トピックの最初の返信の ID を取得するサブクエリ
> WHERE deleted_at ISNULL
> AND post_type = 1
> AND post_number > 1
> GROUP BY topic_id) x ON x.post = p.id -- posts テーブルと結合して最初の返信のユーザー ID と作成日を取得
> )
> 
> -- 最終結果セットを取得するためのメインクエリ
> SELECT
> CASE 
> WHEN st.id IS NOT NULL THEN 'solved' -- トピック ID が solved_topics CTE に含まれている場合、ステータスは 'solved'
> ELSE 'unsolved' -- それ以外の場合、ステータスは 'unsolved'
> END AS status,
> vt.tag_names, -- タグ名
> vt.id AS topic_id, -- トピック ID
> vt.user_id topic_user_id, -- ユーザー ID
> ue.email, -- ユーザーのメールアドレス
> vt.title, -- トピックタイトル
> vt.views, -- ビュー数
> lr.user_id AS last_reply_user_id, -- 最後の返信のユーザー ID
> ue2.email AS last_reply_user_email, -- 最後の返信を行ったユーザーのメールアドレス
> vt.created_at::date topic_create, -- トピックの作成日
> COALESCE(TO_CHAR(fr.created_at,'YYYY-MM-DD'),'') AS first_reply_create, -- 最初の返信の作成日（存在する場合は日付、存在しない場合は空文字列）
> COALESCE(TO_CHAR(st.created_at,'YYYY-MM-DD'),'') AS solution_create, -- 解決の作成日（存在する場合は日付、存在しない場合は空文字列）
> COALESCE(fr.created_at::date - vt.created_at::date,0) AS "time_first_reply(days)", -- 最初の返信までの日数
> COALESCE(CEIL(extract(epoch from (fr.created_at - vt.created_at))/3600.00),0) as "time_first_reply(hours)", -- 最初の返信までの時間
> COALESCE(st.created_at::date - vt.created_at::date,0) AS "time_solution(days)", -- 解決までの日数
> COALESCE(CEIL(extract(epoch from (st.created_at - vt.created_at))/3600.00),0) as "time_solution(hours)", -- 解決までの時間
> posts_count AS number_of_replies, -- 返信数
> total_days AS total_days_without_solution -- 解決なしの総日数
> FROM valid_topics vt -- valid_topics CTE から
> LEFT JOIN last_reply lr ON lr.topic_id = vt.id -- last_reply CTE と結合
> LEFT JOIN first_reply fr ON fr.topic_id = vt.id -- first_reply CTE と結合
> LEFT JOIN solved_topics st ON st.id = vt.id -- solved_topics CTE と結合
> INNER JOIN user_emails ue ON vt.user_id = ue.user_id AND ue."primary" = true -- user_emails テーブルと結合してユーザーのメールアドレスを取得
> LEFT JOIN user_emails ue2 ON lr.user_id = ue2.user_id AND ue2."primary" = true -- user_emails テーブルと結合して最後の返信を行ったユーザーのメールアドレスを取得
> WHERE (:tag_name = 'all' OR LOWER(vt.tag_names) LIKE '%' || LOWER(:tag_name) || '%') -- タグ名でフィルタリング
> ORDER BY tag_names, total_days DESC -- タグ名と総日数の降順でソート
> 
> ```

* * *

[Data Explorer](https://meta.discourse.org/t/32566?silent=true) クエリで `COALESCE` をどのように使用したかに関する質問や例があれば、ぜひ以下でご共有ください。🙂

---

_[View the full topic](https://meta.discourse.org/t/handling-null-values-with-coalesce/278813)._