Dies ist eine SQL-Version des Dashboard-Berichts für Themen ohne Antwort.
Der Dashboard-Bericht ist so konzipiert, dass er die Anzahl der Themen zählt, die innerhalb eines bestimmten Datumsbereichs erstellt wurden und auf die keine Antworten von anderen Benutzern eingegangen sind. Dieser Bericht kann nach einer bestimmten Kategorie gefiltert werden und optional Unterkategorien einschließen.
-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2024-02-01
-- null int :category_id
-- boolean :include_subcategories = false
WITH no_response_total AS (
SELECT *
FROM (
SELECT t.id, t.created_at, MIN(p.post_number) first_reply
FROM topics t
LEFT JOIN posts p ON p.topic_id = t.id AND p.user_id != t.user_id AND p.deleted_at IS NULL AND p.post_type = 1
WHERE t.archetype <> 'private_message'
AND t.deleted_at ISNULL
AND (
:category_id IS NULL
OR t.category_id = :category_id
OR (
:include_subcategories
AND t.category_id IN (
SELECT id FROM categories WHERE parent_category_id = :category_id
)
)
)
GROUP BY t.id
) tt
WHERE tt.first_reply IS NULL OR tt.first_reply < 2
)
SELECT
DATE(nrt.created_at) AS date,
COUNT(nrt.id) AS topics_without_response
FROM no_response_total nrt
WHERE nrt.created_at::date BETWEEN :start_date AND :end_date
GROUP BY date
ORDER BY date ASC
Parameter
Datumsangaben:
Die Abfrage akzeptiert zwei Parameter, :start_date und :end_date, die den Datumsbereich für den Bericht definieren. Beide Datumsangaben akzeptieren das Datumsformat JJJJ-MM-TT.
Kategorieparameter:
:category_id: Ein Integer-Parameter, der auf die ID einer bestimmten Kategorie gesetzt werden kann, um die Analyse auf Beiträge innerhalb dieser Kategorie zu beschränken. Wenn er auf null gesetzt oder nicht angegeben wird, werden Themen aus allen Kategorien berücksichtigt.
:include_subcategories: Ein boolescher Parameter, der steuert, ob Beiträge aus Unterkategorien der angegebenen :category_id eingeschlossen werden sollen. Wenn er auf true gesetzt ist, enthält der Bericht Links zu Beiträgen sowohl in der angegebenen Kategorie als auch in ihren Unterkategorien; wenn false, wird nur die angegebene Kategorie berücksichtigt.
Erklärung der SQL-Abfrage
Die Abfrage beginnt mit einem Common Table Expression (CTE) namens no_response_total. Dieser CTE führt die folgenden Schritte aus:
Auswahl von Themen: Er wählt alle Themen (t.id) und ihre Erstellungsdaten (t.created_at) aus der Tabelle topics aus.
Left Join mit Beiträgen: Er führt einen Left Join mit der Tabelle posts durch, um die erste Antwort auf jedes Thema zu finden. Die Join-Bedingungen stellen sicher, dass der Beitrag nicht vom Ersteller des Themas stammt (p.user_id != t.user_id), der Beitrag nicht gelöscht wurde (p.deleted_at IS NULL) und der Beitrag vom Typ 1 ist, was typischerweise eine Standardantwort darstellt.
Filtern von Themen: Die Abfrage filtert Themen heraus, die private Nachrichten sind (t.archetype <> 'private_message') und Themen, die gelöscht wurden (t.deleted_at ISNULL).
Kategoriefilterung: Wenn eine :category_id angegeben ist, filtert die Abfrage Themen, um nur die in der angegebenen Kategorie einzuschließen. Wenn :include_subcategories true ist, werden auch Themen aus Unterkategorien der angegebenen Kategorie eingeschlossen.
Gruppierung und minimale Beitragsnummer: Die Themen werden nach ihrer ID gruppiert und die minimale Beitragsnummer (MIN(p.post_number)) berechnet, um die erste Antwort zu finden.
Filtern auf keine Antwort: Die Unterabfrage tt filtert Themen heraus, deren erste Antwort eine Beitragsnummer von 2 oder höher hat, und lässt nur Themen ohne Antwort (tt.first_reply IS NULL) oder nur den ursprünglichen Beitrag (tt.first_reply < 2) übrig.
Nachdem der CTE no_response_total definiert wurde, führt die Hauptabfrage Folgendes aus:
Filtern nach Datumsbereich: Sie filtert die Themen aus dem CTE nach den angegebenen Start- und Enddaten (:start_date und :end_date).
Zählen von Themen ohne Antwort: Sie zählt die Anzahl der Themen ohne Antwort für jedes Datum innerhalb des angegebenen Bereichs.
Gruppierung nach Datum: Die Ergebnisse werden nach dem Datum der Themen-Erstellung gruppiert (DATE(nrt.created_at)).
Sortierung: Die Ergebnisse werden nach Datum in aufsteigender Reihenfolge sortiert.
Können Sie eine Version erstellen, die keine Parameter enthält? Ich möchte eine Version erstellen, die 7 Tage zurückblickt, um sie dann per E-Mail an Personen zu senden, und habe Schwierigkeiten, diesen Code zu verwenden, da Parameter darin festgelegt sind.
Ja, hier ist eine aktualisierte Version der Abfrage, die 7 Tage vor dem aktuellen Datum zurückblickt, ohne Parameter zu verwenden.
Diese Version enthält keine Filterung nach Kategorien oder Unterkategorien.
WITH no_response_total AS (
SELECT *
FROM (
SELECT
t.id,
t.created_at,
MIN(p.post_number) AS first_reply
FROM
topics t
LEFT JOIN
posts p
ON
p.topic_id = t.id
AND p.user_id != t.user_id
AND p.deleted_at IS NULL
AND p.post_type = 1
WHERE
t.archetype <> 'private_message'
AND t.deleted_at IS NULL
AND (
t.category_id = :category_id
OR t.category_id IN (
SELECT id FROM categories WHERE parent_category_id = :category_id
)
)
GROUP BY
t.id
) tt
WHERE
tt.first_reply IS NULL
OR tt.first_reply < 2
)
SELECT
DATE(nrt.created_at) AS date,
COUNT(nrt.id) AS topics_without_response
FROM
no_response_total nrt
WHERE
nrt.created_at::date BETWEEN CURRENT_DATE - INTERVAL '7 days' AND CURRENT_DATE
GROUP BY
date
ORDER BY
date ASC
Wenn Sie anpassen möchten, wie weit die Abfrage zurückblickt, müssen Sie nur diese Zeile in der Abfrage ändern:
nrt.created_at::date BETWEEN CURRENT_DATE - INTERVAL '7 days' AND
Vielen Dank für die Antwort. Ich werde darauf zurückkommen, wenn ich es das nächste Mal benötige, da sich der Fokus nun auf etwas anderes verlagert hat und ich keine Zeit hatte, darauf zurückzukommen.
Ich habe wirklich Schwierigkeiten, dies nicht datumsgesteuert, sondern monats-/jahresgesteuert zu machen.
Ich habe mehrere Dinge versucht, um dies zum Laufen zu bringen, aber es sagt mir immer wieder, dass die Spalte nicht existiert (obwohl sie existiert, da ich sie gerade innerhalb einer WITH-Anweisung erstellt und dann anschließend darauf verwiesen habe).
Wie könnte jemand diesen Code ändern, damit wir anstatt Themen ohne Antwort Tag für Tag, sie Jahr für Jahr, Monat für Monat usw. sehen können?
Um die Abfrage so zu modifizieren, dass sie Themen ohne Antworten nach Jahr, Monat oder anderen Zeitintervallen aggregieren kann, könnten Sie der Funktion date_trunc einen Parameter hinzufügen, um das gewünschte Intervall anzugeben.
Zum Beispiel:
-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2025-01-01
-- null int :category_id
-- boolean :include_subcategories = false
-- text :interval = day -- Optionen: day, week, month, year
WITH no_response_total AS (
SELECT *
FROM (
SELECT t.id, t.created_at, MIN(p.post_number) first_reply
FROM topics t
LEFT JOIN posts p ON p.topic_id = t.id AND p.user_id != t.user_id AND p.deleted_at IS NULL AND p.post_type = 1
WHERE t.archetype <> 'private_message'
AND t.deleted_at ISNULL
AND (
:category_id IS NULL
OR t.category_id = :category_id
OR (
:include_subcategories
AND t.category_id IN (
SELECT id FROM categories WHERE parent_category_id = :category_id
)
)
)
GROUP BY t.id
) tt
WHERE tt.first_reply IS NULL OR tt.first_reply < 2
)
SELECT
date_trunc(:interval, nrt.created_at)::date AS period,
COUNT(nrt.id) AS topics_without_response
FROM no_response_total nrt
WHERE nrt.created_at::date BETWEEN :start_date AND :end_date
GROUP BY period
ORDER BY period ASC
Wenn Sie die Parameter entfernen möchten, könnten Sie alternativ eine Abfrage wie diese verwenden:
WITH no_response_total AS (
SELECT *
FROM (
SELECT t.id, t.created_at, MIN(p.post_number) first_reply
FROM topics t
LEFT JOIN posts p ON p.topic_id = t.id AND p.user_id != t.user_id AND p.deleted_at IS NULL AND p.post_type = 1
WHERE t.archetype <> 'private_message'
AND t.deleted_at ISNULL
GROUP BY t.id
) tt
WHERE tt.first_reply IS NULL OR tt.first_reply < 2
)
SELECT
date_trunc('year', nrt.created_at)::date AS period,
COUNT(nrt.id) AS topics_without_response
FROM no_response_total nrt
WHERE nrt.created_at::date BETWEEN '2024-01-01' AND '2025-01-01'
GROUP BY period
ORDER BY period ASC
Danke, das hat das Problem mit dem Erhalt des Jahres gelöst.
Ich stecke jetzt wieder fest, da Datumsangaben in Postgre anscheinend anders funktionieren
to_char(t.created_at, ‘MM-YY’) as Yearmonth,
Dies gibt mir 10-22, was „Okt-22“ darstellt.
Wie kann ich 10-22 in Okt-22 ändern? Ich habe versucht, in Discourse nach Anleitungen zu suchen, konnte aber nichts finden, es sei denn, ich weiß nicht, wo ich suchen soll?
Um das Datumsformat von 10-22 in Oct-22 in PostgreSQL zu ändern, können Sie die Funktion TO_CHAR verwenden. Diese Funktion ermöglicht es Ihnen, Daten auf verschiedene Arten zu formatieren, zum Beispiel:
SELECT
TO_CHAR(TO_DATE('10-22', 'MM-YY'), 'Mon-YY') AS formatted_date
In dieser SQL-Anweisung:
TO_DATE('10-22', 'MM-YY') konvertiert den String 10-22 mithilfe des Formats MM-YY in einen Datumstyp.
TO_CHAR(..., 'Mon-YY') formatiert dieses Datum dann so, dass der abgekürzte Monatsname gefolgt vom Jahr angezeigt wird, was zu Oct-22 führt.
Hier ist ein weiteres Beispiel, das auf der oben geteilten Version der Abfrage Topics with No Response mit dem Parameter interval basiert:
-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2025-01-01
-- null int :category_id
-- boolean :include_subcategories = false
-- text :interval = month
WITH no_response_total AS (
SELECT *
FROM (
SELECT t.id, t.created_at, MIN(p.post_number) first_reply
FROM topics t
LEFT JOIN posts p ON p.topic_id = t.id AND p.user_id != t.user_id AND p.deleted_at IS NULL AND p.post_type = 1
WHERE t.archetype <> 'private_message'
AND t.deleted_at ISNULL
AND (
:category_id IS NULL
OR t.category_id = :category_id
OR (
:include_subcategories
AND t.category_id IN (
SELECT id FROM categories WHERE parent_category_id = :category_id
)
)
)
GROUP BY t.id
) tt
WHERE tt.first_reply IS NULL OR tt.first_reply < 2
)
SELECT
TO_CHAR(date_trunc(:interval, nrt.created_at)::date, 'Mon-YY') AS period,
COUNT(nrt.id) AS topics_without_response
FROM no_response_total nrt
WHERE nrt.created_at::date BETWEEN :start_date AND :end_date
GROUP BY period, date_trunc(:interval, nrt.created_at)::date
ORDER BY date_trunc(:interval, nrt.created_at)::date ASC
Als Referenz würden die Ergebnisse dieser Abfrage wie folgt aussehen:
