Dashboard-Bericht - Beiträge

Daten & Berichterstattung
, ,
1

Dies ist eine SQL-Version des Dashboard-Berichts für Beiträge.

Dieser Bericht liefert eine tägliche Zählung der Beiträge, die innerhalb eines bestimmten Datumsbereichs erstellt wurden. Er wurde entwickelt, um die Aktivität in regulären Themen zu verfolgen, ausgenommen Beiträge aus privaten Nachrichten und anderen speziellen Archetypen.

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

SELECT
    p.created_at::date AS "Tag",
    COUNT(p.id) AS "Anzahl"
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

Erklärung der SQL-Abfrage

  • Parameter:
    • Die Abfrage akzeptiert zwei Parameter, :start_date und :end_date, die den Datumsbereich für den Bericht definieren. Beide Datumsparameter akzeptieren das Datumsformat JJJJ-MM-TT.

Die SQL-Abfrage führt die folgenden Operationen aus:

  • Datenauswahl:
    • Sie wählt das Datum (created_at::date) aus, an dem jeder Beitrag erstellt wurde, und wandelt es in ein Datumsformat um, um die Zeitkomponente zu ignorieren.
    • Sie zählt auch die Anzahl der Beiträge (COUNT(p.id)), die an jedem Datum erstellt wurden.
  • Joins:
    • Die Abfrage verbindet die posts-Tabelle mit der topics-Tabelle über einen INNER JOIN. Dieser Join stellt sicher, dass nur Beiträge berücksichtigt werden, die mit vorhandenen Themen verknüpft sind.
    • Sie filtert alle gelöschten Themen heraus (t.deleted_at ISNULL).
  • Filter:
    • Sie filtert Beiträge, um nur diejenigen innerhalb des angegebenen Datumsbereichs einzuschließen (p.created_at::date BETWEEN :start_date AND :end_date).
    • Sie schließt gelöschte Beiträge aus (p.deleted_at ISNULL).
    • Sie beschränkt die Ergebnisse auf Beiträge aus regulären Themen (t.archetype = 'regular').
    • Sie berücksichtigt nur Beiträge vom Typ p.post_type = 1 und schließt Moderatoraktionen, Whispers und Small-Action-Beiträge aus.
  • Gruppierung und Sortierung:
    • Die Ergebnisse werden nach dem Datum der Beitragerstellung gruppiert (GROUP BY p.created_at::date).
    • Die endgültige Ausgabe wird nach dem Datum in aufsteigender Reihenfolge sortiert (ORDER BY 1), wobei sich 1 auf die erste Spalte in der SELECT-Anweisung bezieht, nämlich das Datum.

Beispielergebnisse

Tag Anzahl
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 „Gefällt mir“
2

Das ist großartig.
Gäbe es eine Möglichkeit, die Kategorie/Unterkategorie zur Laufzeit als Datum zu definieren?
Und als Bonus, können wir entweder die Ergebnisse nach Benutzer auflisten oder den Benutzer ebenfalls definieren?

Was ich versuche, ist zu sehen, wie viele Beiträge während (Bereich) in meinen Support-Ticket-Bereichen von meinem Support-Personal erstellt werden.

3

Ja, Sie können die folgende Abfrage dafür verwenden:

--[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 "Benutzer",
    p.created_at::date AS "Datum",
    COUNT(p.id) AS "Anzahl"
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

Parameter:

  • :start_date & :end_date: Definieren Sie den Berichtszeitraum (erforderlich)
  • :category_id: Optionaler Filter für eine bestimmte Kategorie
  • :user_id: Optionaler Filter für einen bestimmten Benutzer
  • :include_subcategories: Option zum Einbeziehen von Unterkategorien der ausgewählten Kategorie

Diese Abfrage zeigt:

  • Benutzer: Benutzername des Beitragsautors
  • Datum: Das Kalenderdatum, an dem Beiträge erstellt wurden
  • Anzahl: Anzahl der von diesem Benutzer an diesem Datum erstellten Beiträge

Beispieldaten:

Benutzer Datum Anzahl
benutzer 1 2023-01-01 3
benutzer 2 2023-01-01 2
benutzer 3 2023-01-01 1
benutzer 1 2023-01-02 2
benutzer 2 2023-01-02 3
benutzer 1 2023-01-03 1
… … …
2 „Gefällt mir“
4

Danke, das ist eine große Hilfe!

2 „Gefällt mir“