COALESCE を使用した NULL 値の処理

SaraDev · 2023 年 9 月 13 日午前 1:15

このチュートリアルでは、Data Explorer の SQL クエリにおける COALESCE 関数の使い方について解説します。

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

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

構文

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

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

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

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

例クエリ

Data Explorer クエリにおける COALESCE の使い方を理解するために、いくつかの例クエリを見てみましょう。

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

難易度：初心者

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

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 を返します。

-- [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 はこれを防ぎます。

詳細な説明（インラインコメント付き）

-- [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 プラグインが必要

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

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

--[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	A Topic Title	58	bella	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 値が表示されないようにするために使用されており、これによりクエリ結果の可読性が向上し、後続のデータ処理や分析に役立ちます。

詳細な説明（インラインコメント付き）

--[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 クエリで COALESCE をどのように使用したかに関する質問や例があれば、ぜひ以下でご共有ください。

トピック		返信	表示
Data explorer doesn't show email address with a where clause Bug	5	803	2017 年 9 月 20 日
Topics stats of Solved/Unsolved topics, inc. 'time to solution' Data & reporting sql-query , solved	0	190	2023 年 4 月 22 日
SQL query in Data Explorer to pull latest topics? Data & reporting sql-query	11	2875	2022 年 11 月 21 日
Attempting to create Monthly Leaderboard query - head exploding Data & reporting sql-query	19	1793	2024 年 6 月 8 日
Find posts "solved" in specific month Data & reporting solved , sql-query	4	1574	2019 年 3 月 29 日

COALESCE を使用した NULL 値の処理

構文

例クエリ

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

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

解決済み質問データ

関連トピック