데이터 집계 시 DATE_TRUNC 사용

커뮤니티 구축 데이터 및 보고
1

date_trunc 함수는 SQL에서 매우 강력한 도구입니다. 이 함수는 지정된 날짜 부분에 따라 TIMESTAMP 또는 INTERVAL 값을 잘라낼(truncate) 수 있어, 특정 시간 단위로 데이터를 집계하거나 그룹화할 때 없어서는 안 될 함수입니다.

구문(Syntax)

date_trunc 함수의 구문은 다음과 같습니다:

date_trunc('date_part', field)
  • date_part: 잘라낼 날짜 또는 타임스탬프의 부분을 지정하는 문자열입니다. 다음 값 중 하나를 사용할 수 있습니다:
    • millennium
    • century
    • decade
    • year
    • quarter
    • month
    • week
    • day
    • hour
    • minute
    • second
    • milliseconds
    • microseconds
  • field: 잘라낼 대상이 되는 타임스탬프 또는 인터벌입니다.

DE(Data Explorer) 쿼리에서 사용 예시

date_trunc를 활용하는 몇 가지 예제 쿼리를 살펴보겠습니다.

월별 신규 토픽 수

난이도: 초급

이 SQL 쿼리는 Discourse 데이터베이스에서 각 월에 생성된 토픽의 수를 계산하는 데 사용됩니다.

SELECT 
    date_trunc('month', created_at)::DATE AS month,
    count(id)
FROM topics
GROUP BY month
ORDER BY month DESC

이 쿼리에서 date_trunc('month', created_at)::DATE는 created_at 타임스탬프를 월 단위로 잘라낸 후 날짜 형식으로 캐스팅하여, 토픽이 생성된 월별로 그룹화합니다.

count(id) 함수는 각 월에 생성된 토픽의 수를 계산합니다. 결과는 월별로 내림차순으로 정렬되므로 가장 최근 월이 맨 위에 표시됩니다.

결과 예시:

month count
2023-09-01 1
2023-08-01 6
2023-07-01 10
인라인 주석 포함 상세 설명 
-- 토픽이 생성된 월과 토픽 수를 선택합니다
SELECT 
    -- 'created_at' 타임스탬프를 월 단위로 잘라내고 날짜로 캐스팅합니다
    -- 이는 토픽이 생성된 월별로 그룹화하는 것입니다
    date_trunc('month', created_at)::DATE AS month,
    -- 각 월에 생성된 토픽 수를 계산합니다
    count(id)
-- 'topics' 테이블에서 가져옵니다
FROM topics
-- 월별로 결과를 그룹화합니다
GROUP BY month
-- 월별로 내림차순으로 결과를 정렬합니다
-- 즉, 가장 최근 월이 맨 위에 오도록 합니다
ORDER BY month DESC

누적 사용자 수

난이도: 중급

이 쿼리는 Discourse 포럼의 사용자 가입 현황을 주간 리포트로 제공하며, 누적 사용자 수도 함께 표시합니다. WITH 절을 사용하여 임시 결과 집합(daily_signups)을 생성한 후, 해당 결과 집합에서 데이터를 선택합니다.

-- [params]
-- date :start_date = 2000-01-01
-- date :end_date

WITH daily_signups AS(
SELECT
    to_char(date_trunc('week', u.created_at)::date,'YYYY-MM-DD') AS Date,
    Count (id) as Signups
FROM users u
WHERE
    u.created_at::date BETWEEN :start_date::date AND :end_date::date
GROUP BY Date
)

SELECT
    Date, Signups, SUM(Signups) OVER (ORDER BY Date) as total_users
FROM daily_signups
ORDER BY Date Asc

이 쿼리의 작동 방식은 다음과 같습니다:

  • WITH 절은 daily_signups라는 이름의 임시 결과 집합을 생성합니다. 이 결과 집합에는 :start_date와 :end_date 매개변수로 지정된 시작일과 종료일 사이의 각 주별 사용자 가입 수가 포함됩니다.
  • daily_signups 결과 집합 내에서 date_trunc('week', u.created_at)::date는 created_at 타임스탬프를 주 단위로 잘라낸 후 날짜로 캐스팅합니다. 이를 통해 사용자가 가입한 주별로 사용자를 효과적으로 그룹화합니다.
  • Count(id)는 각 주에 가입한 사용자의 수를 계산합니다.
  • 메인 SELECT 문에서 SUM(Signups) OVER (ORDER BY Date)는 사용자의 누적 합계를 계산합니다. OVER (ORDER BY Date) 절은 날짜 순서대로 정렬된 행에 대해 합계를 계산하도록 지정하므로, 각 날짜까지의 누적 가입 수가 됩니다.
  • 결과는 날짜별로 오름차순으로 정렬됩니다.

결과 예시:

date signups total_users
2013-01-28 20 20.0
2013-02-04 2136 2156.0
2013-02-11 442 2598.0
인라인 주석 포함 상세 설명 
-- 시작일과 종료일 매개변수를 정의합니다
-- [params]
-- date :start_date = 2000-01-01
-- date :end_date

-- 각 주별 사용자 가입 수를 계산하기 위한 공용 테이블 표현식(CTE)을 생성합니다
WITH daily_signups AS(
SELECT
    -- 'created_at' 타임스탬프를 주 단위로 잘라내고 날짜 문자열로 포맷팅합니다
    to_char(date_trunc('week', u.created_at)::date,'YYYY-MM-DD') AS Date,
    -- 가입한 사용자의 수를 계산합니다
    Count (id) as Signups
FROM users u
WHERE
    -- 시작일과 종료일 사이에 가입한 사용자만 포함합니다
    u.created_at::date BETWEEN :start_date::date AND :end_date::date
GROUP BY Date
)

-- 날짜, 가입 수, 가입 누적 합계를 선택합니다
SELECT
    Date, 
    Signups, 
    -- 가입 누적 합계를 계산합니다
    SUM(Signups) OVER (ORDER BY Date) as total_users
FROM daily_signups
-- 날짜별로 오름차순으로 결과를 정렬합니다
ORDER BY Date Asc

월별 해결 및 미해결 질문 수

난이도: 중급 / Discourse Solved 플러그인 필요

이 쿼리는 Discourse 포럼에서 해결된 질문과 미해결 질문의 수를 월별 리포트로 제공합니다. 이 쿼리는 사이트의 모든 토픽이 해결될 수 있다고 가정합니다.

-- [params]
-- date :start_date
-- date :end_date

WITH monthly_questions AS (
    SELECT
        date_trunc('month', created_at)::DATE AS month,
        COUNT(id) as total_questions
    FROM topics
    WHERE
        created_at::date BETWEEN :start_date::date AND :end_date::date
    GROUP BY month
),
solved_questions AS (
    SELECT
        date_trunc('month', created_at)::DATE AS month,
        COUNT(id) as solved
    FROM user_actions
    WHERE
        created_at::date BETWEEN :start_date::date AND :end_date::date
    AND action_type = 15
    GROUP BY month
)

SELECT
    mq.month, 
    mq.total_questions, 
    COALESCE(sq.solved, 0) as solved,
    mq.total_questions - COALESCE(sq.solved, 0) as unsolved
FROM monthly_questions mq
LEFT JOIN solved_questions sq ON mq.month = sq.month
ORDER BY mq.month ASC

이 쿼리에서 monthly_questions CTE는 각 월에 생성된 질문(토픽)의 총 수를 계산합니다. solved_questions CTE는 user_actions 테이블에서 action type = 15인 id의 수를 계산함으로써 각 월에 해결로 표시된 질문의 수를 계산합니다.

메인 SELECT 문은 총 질문 수에서 해결된 질문 수를 빼서 미해결 질문의 수를 계산합니다. 결과는 월별로 오름차순으로 정렬되므로 가장 오래된 월이 맨 위에 표시됩니다.

결과 예시:

month total_questions solved unsolved
2023-07-01 10 3 7
2023-08-01 6 0 6
2023-09-01 1 1 0
인라인 주석 포함 상세 설명 
-- [params]
-- date :start_date = 2000-01-01
-- date :end_date

-- 각 월에 생성된 질문(토픽)의 총 수를 계산하기 위한 CTE를 생성합니다
WITH monthly_questions AS (
    SELECT
        -- 'created_at' 타임스탬프를 월 단위로 잘라냅니다
        date_trunc('month', created_at)::DATE AS month,
        -- 각 월에 생성된 토픽 수를 계산합니다
        COUNT(id) as total_questions
    FROM topics
    WHERE
        -- 시작일과 종료일 사이에 생성된 토픽만 포함합니다
        created_at::date BETWEEN :start_date::date AND :end_date::date
    GROUP BY month
),
-- 각 월에 해결로 표시된 질문의 수를 계산하기 위한 CTE를 생성합니다
solved_questions AS (
    SELECT
        -- 'created_at' 타임스탬프를 월 단위로 잘라냅니다
        date_trunc('month', created_at)::DATE AS month,
        -- 각 월에 해결된 질문 수를 계산합니다
        COUNT(id) as solved
    FROM user_actions
    WHERE
        -- 시작일과 종료일 사이에 수행된 작업만 포함합니다
        created_at::date BETWEEN :start_date::date AND :end_date::date
        -- 작업 유형이 15인 경우(해결된 질문을 의미)만 고려합니다
        AND action_type = 15
    GROUP BY month
)

-- 월, 질문 총 수, 해결된 질문 수, 미해결 질문 수를 선택합니다
SELECT
    mq.month, 
    mq.total_questions, 
    -- 해당 월에 해결된 질문이 없으면 0을 표시합니다
    COALESCE(sq.solved, 0) as solved,
    -- 질문 총 수에서 해결된 질문 수를 빼어 미해결 질문 수를 구합니다
    mq.total_questions - COALESCE(sq.solved, 0) as unsolved
FROM monthly_questions mq
-- 월 기준으로 'monthly_questions'와 'solved_questions' CTE를 조인합니다
LEFT JOIN solved_questions sq ON mq.month = sq.month
-- 월별로 오름차순으로 결과를 정렬합니다
ORDER BY mq.month ASC

토픽 답변 통계

난이도: 고급

이 복잡한 SQL 쿼리는 Discourse 포럼의 토픽 활동에 대한 주간 리포트를 제공합니다. 토픽 데이터를 몇 가지 주요 지표로 나누어 분석합니다: 최소 하나 이상의 답변이 있는 토픽 수, 답변이 없는 토픽 수, 답변이 없는 상태로 방치된 최대 일수, 첫 답변까지의 평균 소요 시간입니다.

WITH posts_list AS (
    SELECT 
        t.id topic_id,
        p.post_number,
        p.created_at,
        ROW_NUMBER () OVER (
                    PARTITION BY p.topic_id 
                    ORDER BY p.post_number)  AS post_order
    FROM posts p
    INNER JOIN topics t ON t.id = p.topic_id AND archetype = 'regular' AND t.deleted_at ISNULL
    WHERE p.post_type = 1
        AND p.deleted_at ISNULL
        AND t.deleted_at ISNULL
        AND t.archetype = 'regular'
    ORDER BY p.topic_id, p.post_number
),
atleast_1_response AS (
    SELECT 
        date_trunc('week', t.created_at::date)::date AS "week",
        COUNT(*) AS "topics with atleast one response"
    FROM topics t
    WHERE t.archetype = 'regular'
        AND t.deleted_at ISNULL
        AND t.posts_count >= 2
    GROUP BY "week"
),
no_response AS(
    SELECT 
        date_trunc('week', t.created_at::date)::date AS "week",
        COUNT(*) AS "topics without response"
    FROM topics t
    WHERE t.archetype = 'regular'
        AND t.deleted_at ISNULL
        AND t.posts_count = 1
    GROUP BY "week"
),
max_days_without_response AS(
    SELECT 
        date_trunc('week', t.created_at::date)::date AS "week",
        CURRENT_DATE::date-MIN(created_at)::date AS "max days without response"
    FROM topics t
    WHERE t.archetype = 'regular'
        AND t.deleted_at ISNULL
        AND t.posts_count = 1
    GROUP BY "week"
),
avg_time_first_response AS (
    SELECT 
        date_trunc('week', pl.created_at::date)::date AS "week",
        EXTRACT(epoch FROM AVG(pl2.created_at-pl.created_at))/3600 AS "avg time first response (h)"
    FROM posts_list pl
    INNER JOIN posts_list pl2 ON pl.topic_id = pl2.topic_id AND pl2.post_order = 2
    WHERE  pl.post_order = 1
    GROUP BY "week" 
)

SELECT ar.week, 
    "topics without response", 
    "max days without response", 
    "topics with atleast one response",
    "avg time first response (h)"
FROM atleast_1_response ar
INNER JOIN no_response nr ON nr.week = ar.week
INNER JOIN max_days_without_response mdwr ON mdwr.week = ar.week
INNER JOIN avg_time_first_response atfr ON atfr.week = ar.week
ORDER BY "week" DESC

이 쿼리의 작동 방식 요약은 다음과 같습니다:

  • posts_list 공용 테이블 표현식(CTE)은 posts와 topics 테이블에서 모든 일반 게시글을 topic_id와 post_number 순서대로 선택합니다. 또한 각 토픽 내의 게시글마다 행 번호(post_order)를 부여합니다.
  • atleast_1_response CTE는 각 주별로 최소 하나 이상의 답변이 있는 일반 토픽의 수(즉, posts_count가 2 이상인 경우)를 계산합니다.
  • no_response CTE는 각 주별로 답변이 없는 일반 토픽의 수(즉, posts_count가 1인 경우)를 계산합니다.
  • max_days_without_response CTE는 각 주별로 답변이 없는 토픽이 답변을 받지 못한 채 방치된 최대 일수를 계산합니다.
  • avg_time_first_response CTE는 각 주별로 각 토픽의 첫 답변까지의 평균 소요 시간을 시간 단위로 계산합니다.
  • 메인 SELECT 문은 이러한 CTE들을 주(week) 기준으로 조인하여 관련 컬럼을 선택합니다. 결과는 주별로 내림차순으로 정렬됩니다.
week topics without response max days without response topics with atleast one response avg time first response (h)
2023-09-04 15 2 47 2.6778684519444444
2023-08-28 30 9 138 8.7899938238888889
2023-08-21 22 16 130 9.3280889688888889
인라인 주석 포함 상세 설명 
-- topic_id와 post_number 순서대로 정렬된 모든 일반 게시글의 임시 테이블(CTE)을 생성합니다
WITH posts_list AS (
    SELECT 
        t.id topic_id,  -- 토픽 ID
        p.post_number,  -- 게시글 번호
        p.created_at,   -- 게시글 생성일
        -- 해당 토픽 내의 각 게시글에 행 번호를 부여합니다
        ROW_NUMBER () OVER (
                    PARTITION BY p.topic_id 
                    ORDER BY p.post_number)  AS post_order
    FROM posts p
    -- topics 테이블과 조인하며, 삭제되지 않은 일반 토픽만 고려합니다
    INNER JOIN topics t ON t.id = p.topic_id AND archetype = 'regular' AND t.deleted_at ISNULL
    WHERE p.post_type = 1
        AND p.deleted_at ISNULL  -- 삭제된 게시글 제외
        AND t.deleted_at ISNULL  -- 삭제된 토픽 제외
        AND t.archetype = 'regular'  -- 일반 토픽만 고려
    ORDER BY p.topic_id, p.post_number
),
-- 각 주별로 최소 하나 이상의 답변이 있는 일반 토픽 수를 계산하기 위한 CTE를 생성합니다
atleast_1_response AS (
    SELECT 
        -- created_at 타임스탬프를 주 단위로 잘라냅니다
        date_trunc('week', t.created_at::date)::date AS "week",
        COUNT(*) AS "topics with atleast one response"
    FROM topics t
    WHERE t.archetype = 'regular'  -- 일반 토픽만 고려
        AND t.deleted_at ISNULL  -- 삭제된 토픽 제외
        AND t.posts_count >= 2  -- 최소 하나 이상의 답변이 있는 토픽만 고려
    GROUP BY "week"
),
-- 각 주별로 답변이 없는 일반 토픽 수를 계산하기 위한 CTE를 생성합니다
no_response AS(
    SELECT 
        -- created_at 타임스탬프를 주 단위로 잘라냅니다
        date_trunc('week', t.created_at::date)::date AS "week",
        COUNT(*) AS "topics without response"
    FROM topics t
    WHERE t.archetype = 'regular'  -- 일반 토픽만 고려
        AND t.deleted_at ISNULL  -- 삭제된 토픽 제외
        AND t.posts_count = 1  -- 답변이 없는 토픽만 고려
    GROUP BY "week"
),
-- 각 주별로 답변이 없는 토픽이 답변을 받지 못한 채 방치된 최대 일수를 계산하기 위한 CTE를 생성합니다
max_days_without_response AS(
    SELECT 
        -- created_at 타임스탬프를 주 단위로 잘라냅니다
        date_trunc('week', t.created_at::date)::date AS "week",
        -- 토픽 생성일에서 현재 날짜까지의 일수를 계산합니다
        CURRENT_DATE::date-MIN(created_at)::date AS "max days without response"
    FROM topics t
    WHERE t.archetype = 'regular'  -- 일반 토픽만 고려
        AND t.deleted_at ISNULL  -- 삭제된 토픽 제외
        AND t.posts_count = 1  -- 답변이 없는 토픽만 고려
    GROUP BY "week"
),
-- 각 주별로 각 토픽의 첫 답변까지의 평균 소요 시간(시간 단위)을 계산하기 위한 CTE를 생성합니다
avg_time_first_response AS (
    SELECT 
        -- created_at 타임스탬프를 주 단위로 잘라냅니다
        date_trunc('week', pl.created_at::date)::date AS "week",
        -- 첫 답변까지의 평균 소요 시간을 시간 단위로 계산합니다
        EXTRACT(epoch FROM AVG(pl2.created_at-pl.created_at))/3600 AS "avg time first response (h)"
    FROM posts_list pl
    -- posts_list CTE와 조인하며, 각 토픽의 두 번째 게시글만 고려합니다
    INNER JOIN posts_list pl2 ON pl.topic_id = pl2.topic_id AND pl2.post_order = 2
    WHERE  pl.post_order = 1  -- 각 토픽의 첫 번째 게시글만 고려
    GROUP BY "week" 
)

-- 주, 답변이 없는 토픽 수, 답변이 없는 최대 일수, 최소 하나 이상의 답변이 있는 토픽 수, 첫 답변까지의 평균 소요 시간을 선택합니다
SELECT ar.week, 
    "topics without response", 
    "max days without response", 
    "topics with atleast one response",
    "avg time first response (h)"
FROM atleast_1_response ar
-- 주(week) 기준으로 CTE들을 조인합니다
INNER JOIN no_response nr ON nr.week = ar.week
INNER JOIN max_days_without_response mdwr ON mdwr.week = ar.week
INNER JOIN avg_time_first_response atfr ON atfr.week = ar.week
-- 주별로 내림차순으로 정렬합니다
ORDER BY "week" DESC

이것들은 Data Explorer 쿼리에서 date_trunc를 사용할 수 있는 몇 가지 예시에 불과합니다. 이 쿼리 중 어떤 것을 사이트에서 사용해도 되며, 질문이 있으시면 아래에 남겨주세요. :slight_smile:

5개의 좋아요
Retrospectively pulling number of users each calendar month