Report Dashboard - Sistema

Dati e reportistica
, ,
1

Questa è una versione SQL del report della dashboard per il sistema.

Questo report della dashboard fornisce un conteggio giornaliero del numero di messaggi personali inviati automaticamente dal sistema.

-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2025-01-01

SELECT
  DATE(created_at) AS day,
  COUNT(*) AS notifications_count
FROM topics
WHERE archetype = 'private_message'
  AND subtype = 'system_message'
  AND created_at BETWEEN :start_date AND :end_date
  AND deleted_at IS NULL
  AND user_id > 0
GROUP BY DATE(created_at)
ORDER BY day

Spiegazione della query SQL

La query funziona estraendo dati dalla tabella topics, in particolare quelli che si qualificano come messaggi privati agli utenti con un sottotipo di system_message entro un intervallo di tempo specificato. Analizziamola:

  • Parametri Data:
    • 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.
  • SELECT: La query seleziona due campi:
    • DATE(created_at) AS day: Estrae la parte della data dal timestamp created_at, raggruppando efficacemente i record per il giorno in cui sono stati creati.
    • COUNT(*) AS notifications_count: Conta il numero totale di PM generati dal sistema per ogni giorno.
  • FROM: Specifica la tabella topics come origine dati, che contiene i record di tutti gli argomenti, inclusi i messaggi privati.
  • WHERE: Contiene più filtri per restringere il set di dati:
    • archetype = 'private_message': Include solo le voci che sono messaggi privati.
    • subtype = 'system_message': Restringe ulteriormente la selezione solo ai messaggi generati dal sistema.
    • created_at BETWEEN :start_date AND :end_date: Filtra i PM a quelli creati nell’intervallo specificato dai parametri.
    • deleted_at IS NULL: Esclude i messaggi che sono stati eliminati.
    • user_id > 0: Assicura che i messaggi siano associati ad account utente reali piuttosto che al sistema o ad account anonimi.
  • GROUP BY: Raggruppa i risultati in base al giorno in cui sono stati creati.
  • ORDER BY: Ordina il set di risultati finale per giorno in ordine crescente, garantendo una sequenza cronologica dei conteggi giornalieri.

Risultati di esempio

day notifications_count
2024-01-01 5
2024-01-02 7
2024-01-03 11
2024-01-04 14
2024-01-05 8
3 Mi Piace
2

È possibile collegarlo a uno specifico PM, come il PM di benvenuto?

Vorrei sapere quanti di questi vengono inviati quotidianamente.

Opinioni?

1 Mi Piace
3

Sì, il modo migliore per aggiungerlo sarebbe aggiungere una sezione all’istruzione WHERE nella query, filtrando per il title dell’argomento.

Ad esempio:

WHERE archetype = 'private_message'
  AND subtype = 'system_message'
  AND created_at BETWEEN :start_date AND :end_date
  AND title = 'Greetings!'

Troverebbe tutti i messaggi Greetings!.

Nota che alcuni messaggi di sistema non includono un utente reale, quindi potrebbe essere necessario rimuovere la riga AND user_id > 0 con questo tipo di query.

Potresti anche voler rimuovere AND deleted_at IS NULL per contare comunque i messaggi di benvenuto che gli utenti potrebbero eliminare.

Potresti usare le espressioni regolari (regex) per trovare argomenti con titoli simili.

Per trovare un argomento tramite titolo utilizzando un’espressione regolare (regex) in PostgreSQL, puoi utilizzare l’operatore ~, che confronta un’espressione regolare con una stringa. La struttura della query sarebbe la seguente:

SELECT *
FROM topics
WHERE title ~ 'YourRegexPatternHere'

Sostituisci 'YourRegexPatternHere' con il pattern regex effettivo che desideri confrontare con il campo title.

Ad esempio, se stai cercando argomenti con titoli che contengono la parola “Welcome” (ignorando maiuscole/minuscole), potresti usare:

SELECT *
FROM topics
WHERE title ~* 'Welcome'

L’operatore ~* viene utilizzato per il confronto che ignora maiuscole/minuscole.

3 Mi Piace
4

Oh, questo è fantastico. Grazie mille! Ci proverò!

1 Mi Piace