Questa è una versione SQL del Report Dashboard per le Reazioni.
Questo report fornisce un conteggio giornaliero di tutti i “mi piace” e delle reazioni ai post su un sito per un intervallo di date specificato.
Questo report aiuta a fornire un’istantanea dell’interazione degli utenti all’interno di una community di Discourse misurando la frequenza delle varie emoji di reazione e dei “mi piace” sui post. Analizzando l’utilizzo delle diverse emoji di reazione, gli amministratori possono ottenere informazioni sulla frequenza con cui gli utenti interagiscono con i contenuti, sulle risposte emotive degli utenti ai post e identificare sia le emoji di reazione popolari che quelle sottoutilizzate.
Questo report richiede che il plugin Discourse Reactions sia abilitato sul tuo sito. Le reazioni disponibili nel report dipenderanno da quali reazioni specifiche sono state abilitate con l’impostazione del sito
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
Spiegazione della Query SQL
Parametri
- La query accetta due parametri,
:start_datee:end_date, che definiscono l’intervallo di date per il report. Entrambi i parametri di data accettano il formato dataAAAA-MM-GG.
Struttura della Query
- Query Interna (Reazioni): La query inizia con una sottoquery che seleziona la data (
day), il tipo di reazione (reaction_value) e il conteggio delle reazioni (reactions_count) dalla tabelladiscourse_reactions_reactionsunita adiscourse_reactions_reaction_userstramitereaction_id. Questa unione garantisce che stiamo tenendo conto delle reazioni specifiche dell’utente. - Aggregazione per Tipi di Reazione: I dati selezionati vengono quindi raggruppati per
dayereaction_value, limitando l’intervallo alle date di inizio e fine selezionate. Questo viene utilizzato per calcolare il conteggio totale di ciascun tipo di reazione per ogni giorno nell’intervallo di date specificato. - Ordinamento dei Totali delle Reazioni: Per ogni tipo di reazione, la query utilizza un’istruzione
CASEper aggregare il numero di volte in cui viene utilizzata la reazione specifica e converte il risultato in un intero per un conteggio pulito.- Potrebbe essere necessario modificare
reaction_value = '...'in questa sezione a seconda delle reazioni abilitate sul tuo sito.
- Potrebbe essere necessario modificare
- Sottoquery per il Conteggio dei “Mi Piace”: Viene utilizzata una sottoquery separata per calcolare il numero totale di “mi piace” (
likes_count) per ogni giorno utilizzando la tabellapost_actionsin cuipost_action_type_idcorrisponde ai “mi piace”. - Combinazione dei Dati: La query esterna combina quindi il conteggio dei “mi piace” con i conteggi delle reazioni unendo sul campo
day. - Selezione Finale: La
SELECTpiù esterna genera l’output finale con ilday, il numero di “mi piace” (likes_count) e i conteggi di ciascun tipo di reazione. Se non ci sono dati per i “mi piace” in un giorno specifico, la funzioneCOALESCEgarantisce che venga visualizzato uno zero invece diNULL. - Ordinamento dei Risultati: I risultati sono ordinati per data (
r.day) per creare una serie temporale dell’interazione sul sito.
Colonne
day: La data in cui sono stati conteggiati le reazioni e i “mi piace”.likes_count: Numero totale di “mi piace” per ogni giorno.- Ciascun tipo di reazione (laughing, cry, exploding_head, ecc.): Colonne individuali che mostrano il conteggio totale di ciascun tipo di reazione al giorno.
Risultati di Esempio
| 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 |
a 
.