이것은 반응(Reactions) 대시보드 리포트의 SQL 버전입니다.
이 리포트는 지정된 날짜 범위 내 사이트 전체 게시물에 대한 모든 좋아요와 반응의 일별 개수를 제공합니다.
이 리포트는 게시물에 대한 다양한 반응 이모지 및 좋아요의 빈도를 측정하여 Discourse 커뮤니티 내 사용자 참여도를 한눈에 파악하는 데 도움을 줍니다. 다양한 반응 이모지의 사용 현황을 분석함으로써 관리자는 사용자가 콘텐츠와 상호작용하는 빈도, 게시물에 대한 사용자의 감정적 반응, 그리고 인기 있는 이모지와 덜 사용되는 이모지를 식별할 수 있습니다.
이 리포트를 사용하려면 사이트에 Discourse Reactions 플러그인이 활성화되어 있어야 합니다. 리포트에서 사용할 수 있는 반응은
discourse_reactions_enabled_reactions사이트 설정에서 활성화된 특정 반응에 따라 달라집니다.
-- [params]
-- date :start_date = 2023-12-16
-- date :end_date = 2024-01-17
SELECT
r.day,
COALESCE(l.likes_count, 0) as likes_count,
sum(case when reaction_value = 'laughing' then reactions_count else 0 end)::int as laughing,
sum(case when reaction_value = 'cry' then reactions_count else 0 end)::int as cry,
sum(case when reaction_value = 'exploding_head' then reactions_count else 0 end)::int as exploding_head,
sum(case when reaction_value = 'clap' then reactions_count else 0 end)::int as clap,
sum(case when reaction_value = 'confetti_ball' then reactions_count else 0 end)::int as confetti_ball,
sum(case when reaction_value = 'hugs' then reactions_count else 0 end)::int as hugs,
sum(case when reaction_value = 'chefs_kiss' then reactions_count else 0 end)::int as chefs_kiss,
sum(case when reaction_value = '100' then reactions_count else 0 end)::int as one_hundred,
sum(case when reaction_value = '+1' then reactions_count else 0 end)::int as plus_one,
sum(case when reaction_value = 'rocket' then reactions_count else 0 end)::int as rocket,
sum(case when reaction_value = 'star_struck' then reactions_count else 0 end)::int as star_struck,
sum(case when reaction_value = 'eyes' then reactions_count else 0 end)::int as eyes,
sum(case when reaction_value = 'discourse' then reactions_count else 0 end)::int as discourse
FROM (
SELECT
date_trunc('day', drru.created_at)::date as day,
drr.reaction_value,
count(drru.id) as reactions_count
FROM discourse_reactions_reactions as drr
LEFT OUTER JOIN discourse_reactions_reaction_users as drru on drr.id = drru.reaction_id
WHERE drr.reaction_users_count IS NOT NULL
AND drru.created_at::date >= :start_date::date AND drru.created_at::date <= :end_date::date
GROUP BY drr.reaction_value, day
) r
LEFT JOIN (
SELECT
count(pa.id) as likes_count,
date_trunc('day', pa.created_at)::date as day
FROM post_actions as pa
WHERE pa.post_action_type_id = 2
AND pa.created_at::date >= :start_date::date AND pa.created_at::date <= :end_date::date
GROUP BY day
) l ON r.day = l.day
GROUP BY r.day, l.likes_count
ORDER BY r.day
SQL 쿼리 설명
파라미터
- 쿼리는 리포트의 날짜 범위를 정의하는
:start_date와:end_date두 가지 파라미터를 받아들입니다. 두 날짜 파라미터 모두YYYY-MM-DD형식의 날짜를 허용합니다.
쿼리 구조
- 내부 쿼리 (반응): 쿼리는
discourse_reactions_reactions테이블에서reaction_id기준으로discourse_reactions_reaction_users테이블과 조인하여 날짜(day), 반응 유형(reaction_value), 반응 개수(reactions_count)를 선택하는 서브쿼리로 시작합니다. 이 조인은 사용자별 반응을 정확히 반영하기 위해 사용됩니다. - 반응 유형별 집계: 선택된 데이터는
day와reaction_value별로 그룹화되며, 범위는 선택된 시작 및 종료 날짜로 제한됩니다. 이는 지정된 날짜 범위 내 각 날짜별로 각 유형의 반응 총 개수를 계산하는 데 사용됩니다. - 반응 합계 정렬: 각 반응 유형에 대해 쿼리는
CASE문을 사용하여 해당 특정 반응이 사용된 횟수를 집계하고, 깔끔한 개수를 위해 결과를 정수형으로 변환합니다.- 사이트에서 활성화된 반응에 따라 이 섹션의
reaction_value = '...'부분을 조정해야 할 수 있습니다.
- 사이트에서 활성화된 반응에 따라 이 섹션의
- 좋아요 개수 서브쿼리: 별도의 서브쿼리가
post_actions테이블을 사용하여 각 날짜별 좋아요 총 개수(likes_count)를 계산합니다. 여기서post_action_type_id는 좋아요에 해당합니다. - 데이터 결합: 외부 쿼리는
day필드를 기준으로 조인하여 좋아요 카운트와 반응 카운트를 결합합니다. - 최종 선택: 가장 바깥쪽
SELECT는day, 좋아요 수(likes_count), 각 반응 유형의 개수를 포함한 최종 출력을 생성합니다. 특정 날짜에 좋아요 데이터가 없는 경우,COALESCE함수는NULL대신 0이 표시되도록 보장합니다. - 결과 정렬: 결과는 날짜(
r.day) 기준으로 정렬되어 사이트 내 참여도의 시간 순서를 형성합니다.
열(Columns)
day: 반응과 좋아요가 집계된 날짜.likes_count: 각 날짜별 좋아요 총 개수.- 각 반응 유형(laughing, cry, exploding_head 등): 각 날짜별 반응 유형별 총 개수를 보여주는 개별 열.
결과 예시
| day | likes_count | laughing | cry | exploding_head | clap | confetti_ball | hugs | chefs_kiss | one_hundred | plus_one | rocket | star_struck | eyes | discourse |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 2023-12-16 | 13 | 0 | 3 | 0 | 3 | 1 | 0 | 0 | 0 | 5 | 2 | 2 | 1 | 0 |
| 2023-12-17 | 17 | 1 | 0 | 2 | 2 | 0 | 0 | 0 | 0 | 4 | 4 | 1 | 2 | 0 |
| 2023-12-18 | 46 | 0 | 1 | 0 | 6 | 0 | 1 | 3 | 0 | 27 | 3 | 4 | 5 | 0 |
에서 
로 변경된 경우, 이 계산이 잘못되고 있다는 느낌이 듭니다.