これは、リアクションのダッシュボードレポートの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の2つのパラメータを受け入れます。両方の日付パラメータはYYYY-MM-DDの日付形式を受け入れます。
クエリ構造
- 内部クエリ（リアクション）： クエリは、
discourse_reactions_reactionsテーブルとdiscourse_reactions_reaction_usersテーブルをreaction_idで結合し、日付（day）、リアクションの種類（reaction_value）、およびリアクションのカウント（reactions_count）を選択するサブクエリから始まります。この結合により、ユーザー固有のリアクションが考慮されることが保証されます。 - リアクションタイプごとの集計： 選択されたデータは、
dayとreaction_valueでグループ化され、範囲は選択された開始日と終了日に制限されます。これは、指定された日付範囲内の各日のリアクションタイプの合計数を計算するために使用されます。 - リアクション合計の並べ替え： 各リアクションタイプについて、クエリは
CASEステートメントを使用して特定のリアクションが使用された回数を集計し、結果を整数としてキャストしてクリーンなカウントにします。- このセクションの
reaction_value = '...'は、サイトで有効になっているリアクションに応じて調整する必要がある場合があります。
- このセクションの
- 「いいね！」カウントサブクエリ： 別個のサブクエリを使用して、
post_action_type_idが「いいね！」に対応するpost_actionsテーブルを使用して、各日の「いいね！」の総数（likes_count）を計算します。 - データの結合： 外側のクエリは、
dayフィールドで結合することにより、「いいね！」のカウントとリアクションのカウントを結合します。 - 最終選択： 最も外側の
SELECTは、day、「いいね！」の数（likes_count）、および各リアクションタイプのカウントを含む最終的な出力を生成します。特定の日に「いいね！」のデータがない場合、COALESCE関数によりNULLの代わりにゼロが表示されます。 - 結果の順序付け： 結果は日付（
r.day）で並べ替えられ、サイトでのエンゲージメントの時間系列が作成されます。
列
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 |
」から「
」に変更された場合、これは間違ってカウントしているという感覚があります。