Report della Dashboard - Post

Dati e reportistica
, ,
1

Questa è una versione SQL del Report della Bacheca per i Post.

Questo report fornisce un conteggio giornaliero dei post creati entro un intervallo di date specificato. È progettato per monitorare l’attività negli argomenti regolari, escludendo i post dai messaggi privati e da altri archetipi speciali.

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

SELECT
    p.created_at::date AS "Giorno",
    COUNT(p.id) AS "Conteggio"
FROM posts p
INNER JOIN topics t ON t.id = p.topic_id AND t.deleted_at ISNULL
WHERE p.created_at::date BETWEEN :start_date AND :end_date
    AND p.deleted_at ISNULL
    AND t.archetype = 'regular'
    AND p.post_type = 1
GROUP BY p.created_at::date
ORDER BY 1

Spiegazione della query SQL

  • Parametri:
    • La query accetta due parametri, :start_date e :end_date, che definiscono l’intervallo di date per il report. Entrambi i parametri di data accettano il formato data AAAA-MM-GG.

La query SQL esegue le seguenti operazioni:

  • Selezione dei dati:
    • Seleziona la data (created_at::date) in cui è stato creato ciascun post e la converte in formato data per ignorare la componente oraria.
    • Conta anche il numero di post (COUNT(p.id)) creati in ciascuna data.
  • Join:
    • La query unisce la tabella posts con la tabella topics utilizzando un INNER JOIN. Questo join garantisce che vengano considerati solo i post associati ad argomenti esistenti.
    • Filtra gli argomenti eliminati (t.deleted_at ISNULL).
  • Filtri:
    • Filtra i post per includere solo quelli all’interno dell’intervallo di date specificato (p.created_at::date BETWEEN :start_date AND :end_date).
    • Esclude i post eliminati (p.deleted_at ISNULL).
    • Limita i risultati ai post di argomenti regolari (t.archetype = 'regular').
    • Considera solo i post con p.post_type = 1, escludendo azioni del moderatore, sussurri e post di piccole azioni.
  • Raggruppamento e Ordinamento:
    • I risultati vengono raggruppati per data di creazione del post (GROUP BY p.created_at::date).
    • L’output finale è ordinato per data in ordine crescente (ORDER BY 1), dove 1 si riferisce alla prima colonna nell’istruzione SELECT, che è la data.

Risultati di esempio

Giorno Conteggio
2023-11-12 25
2023-11-13 35
2023-11-14 38
2023-11-15 47
2023-11-16 36
2023-11-17 79
… …
2 Mi Piace
2

Questo è fantastico.
Ci sarebbe un modo per poter definire la categoria/sottocategoria al runtime come la data?
E bonus, possiamo elencare i risultati per utente o definire anche l’utente?

Quello che sto cercando di fare è vedere quanti post vengono effettuati durante (intervallo) nelle aree dei miei ticket di supporto dal mio personale di supporto.

3

Sì, puoi usare la seguente query per questo:

--[params]
-- date :start_date
-- date :end_date
-- null category_id :category_id 
-- null user_id :user_id
-- boolean :include_subcategories = false

SELECT 
    u.username AS "Utente",
    p.created_at::date AS "Data",
    COUNT(p.id) AS "Conteggio"
FROM posts p
INNER JOIN topics t ON t.id = p.topic_id AND t.deleted_at IS NULL
INNER JOIN users u ON p.user_id = u.id
LEFT JOIN categories c ON t.category_id = c.id
WHERE p.created_at::date BETWEEN :start_date AND :end_date
    AND p.deleted_at IS NULL
    AND t.archetype = 'regular'
    AND p.post_type = 1
    AND (
        :category_id IS NULL 
        OR t.category_id = :category_id
        OR (:include_subcategories AND c.parent_category_id = :category_id)
    )
    AND (:user_id IS NULL OR p.user_id = :user_id)
GROUP BY u.username, p.created_at::date
ORDER BY p.created_at::date ASC, u.username

Parametri:

  • :start_date & :end_date: Definiscono l’intervallo di tempo del report (richiesto)
  • :category_id: Filtro opzionale per una categoria specifica
  • :user_id: Filtro opzionale per un utente specifico
  • :include_subcategories: Opzione per includere le sottocategorie della categoria scelta

Questa query mostra:

  • Utente: Nome utente dell’autore del post
  • Data: La data di calendario in cui sono stati creati i post
  • Conteggio: Numero di post creati da quell’utente in quella data

Dati di esempio:

Utente Data Conteggio
utente 1 2023-01-01 3
utente 2 2023-01-01 2
utente 3 2023-01-01 1
utente 1 2023-01-02 2
utente 2 2023-01-02 3
utente 1 2023-01-03 1
… … …
2 Mi Piace
4

Grazie, è di grande aiuto!

2 Mi Piace