COALESCE로 NULL 값 처리하기

SaraDev · 9월 13, 2023, 1:15오전

이 튜토리얼에서는 Data Explorer SQL 쿼리에서 COALESCE 함수의 사용법을 살펴봅니다.

COALESCE는 쿼리 결과의 NULL 값을 처리할 수 있게 해줍니다. 데이터에 NULL 값이 있는 경우, COALESCE를 사용하여 이러한 NULL 값에 대한 기본값(예: 0)을 지정할 수 있습니다.

COALESCE는 쿼리 결과에 대해 후속 계산이나 데이터 분석을 수행할 때, NULL 값이 문제를 일으키거나 오해를 불러일으킬 수 있는 상황에서 특히 유용합니다.

구문(Syntax)

COALESCE 함수는 두 개 이상의 인수를 받아, 목록에서 왼쪽에서 오른쪽으로 첫 번째로 발견되는 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

이 쿼리에서는 user_id 필드를 기준으로 users 테이블과 posts 테이블을 조인합니다. 그런 다음 COALESCE 함수를 사용하여 사용자가 게시물, 받은 좋아요, 받은 북마크가 없는 경우 NULL 대신 0을 반환하도록 합니다. 결과는 사용자 ID와 사용자 이름별로 그룹화되며, 게시물 수, 좋아요 수, 북마크 수의 내림차순으로 정렬됩니다.

사용자별 토픽과 답변 수

난이도: 중급

이 쿼리는 두 날짜 사이에 각 사용자가 만든 토픽과 답변의 수를 가져옵니다. 사용자가 토픽이나 답변이 없는 경우, 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

이 쿼리에서 COALESCE는 total 공용 테이블 표현식(CTE)에서 사용됩니다. 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  -- 마지막 답변의 사용자 ID를 가져오기 위해 posts 테이블과 조인
),

-- 각 토픽의 첫 번째 답변을 위한 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  -- 첫 번째 답변의 사용자 ID와 생성 날짜를 가져오기 위해 posts 테이블과 조인
)

-- 최종 결과 집합을 가져오기 위한 메인 쿼리
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	4	860	9월 20, 2017
Topics stats of Solved/Unsolved topics, inc. 'time to solution' Data & reporting solved , sql-query	0	245	4월 22, 2023
SQL query in Data Explorer to pull latest topics? Data & reporting sql-query	10	3001	6월 6, 2017
Attempting to create Monthly Leaderboard query - head exploding Data & reporting sql-query	18	2017	10월 8, 2018
Find posts "solved" in specific month Data & reporting solved , sql-query	4	1651	3월 29, 2019

COALESCE로 NULL 값 처리하기

구문(Syntax)

예제 쿼리

작성된 게시물, 받은 좋아요, 받은 북마크

사용자별 토픽과 답변 수

해결된 질문 데이터

Related topics