Zuerst einmal, frohes neues Jahr!! 
Ich würde gerne die SQL-Abfrage wissen, um die durchschnittliche Anzahl der Antworten pro Thema, die von Benutzern (ohne Administratoren) pro Monat gegeben wurden, zu erhalten.
Alternativ einen Weg, um das Verhältnis der Gesamtzahl der von Mitgliedern veröffentlichten Beiträge im Vergleich zur Gesamtzahl der vom Personal pro Monat veröffentlichten Beiträge zu erhalten.
Danke!
Frohes neues Jahr (ein bisschen verspätet 
)
Meinen Sie mit dem Durchschnitt Themen, die sowohl von Mitarbeitern als auch von Nicht-Mitarbeitern erstellt wurden, aber die Anzahl der Antworten nur von Nicht-Mitarbeitern? Und möchten Sie nur Administratoren oder Administratoren und Moderatoren ausschließen?
Hallo Jammy!
Genau, die Abfrage kann alle Themen (sowohl von Mitarbeitern als auch von Nicht-Mitarbeitern erstellt) anzeigen, aber die Antwortanzahl nur von Beiträgen von Nicht-Mitarbeitern.
Für jetzt können wir nur Administratoren ausschließen (da ich meine Community starte, sind Administratoren und Moderatoren gleich :))
Aber es wäre auch schön, das Verhältnis zwischen Themen von Mitarbeitern und Themen von Nicht-Mitarbeitern (ohne Administratoren) einfach zu ermitteln.
Ich denke, so etwas würde Ihnen die gewünschten Zahlen liefern:
-- [params]
-- date :start_date
-- date :end_date
WITH staff_data AS (
SELECT user_id, true as is_staff
FROM group_users
WHERE group_id = 3
),
month_stats AS (
SELECT
date_trunc('month', p.created_at)::date AS month,
COUNT(*) FILTER (WHERE p.post_number = 1) AS total_topics,
COUNT(*) FILTER (WHERE p.post_number <> 1) AS total_posts,
COUNT(DISTINCT p.user_id) FILTER (WHERE p.post_number <> 1 AND is_staff IS NOT TRUE) AS non_staff_users,
COUNT(*) FILTER (WHERE p.post_number <> 1 AND is_staff IS NOT TRUE) AS non_staff_posts,
COUNT(DISTINCT p.user_id) FILTER (WHERE p.post_number <> 1 AND is_staff) AS staff_users,
COUNT(*) FILTER (WHERE p.post_number <> 1 AND is_staff) AS staff_posts
FROM posts p
LEFT JOIN topics t ON t.id = p.topic_id
LEFT JOIN staff_data s ON p.user_id = s.user_id
WHERE p.created_at::date BETWEEN :start_date AND :end_date
AND t.archetype = 'regular'
AND t.deleted_at IS NULL
AND p.deleted_at IS NULL
AND p.post_type = 1
AND p.user_id > 0
GROUP BY month
)
SELECT
ms.month AS "Monat",
ms.total_topics AS "Alle Themen",
ms.total_posts AS "Alle Beiträge",
ms.non_staff_posts AS "Beiträge von Nicht-Mitarbeitern",
ROUND(ms.non_staff_posts * 100.0 / NULLIF(ms.total_posts, 0),1) || '%' AS "Beiträge von Nicht-Mitarbeitern (% gesamt)",
ms.non_staff_posts / NULLIF(ms.total_topics, 0) AS "Durchschnittliche Beiträge von Nicht-Mitarbeitern pro Thema",
ms.non_staff_users AS "Nicht-Mitarbeiter, die gepostet haben",
ms.staff_posts AS "Beiträge von Mitarbeitern",
ROUND(ms.staff_posts * 100.0 / NULLIF(ms.total_posts, 0),1) || '%' AS "Beiträge von Mitarbeitern (% gesamt)",
ms.staff_posts / NULLIF(ms.total_topics, 0) AS "Durchschnittliche Beiträge von Mitarbeitern pro Thema",
ms.staff_users AS "Mitarbeiter, die gepostet haben"
FROM month_stats ms
ORDER BY "Monat"
Was Ihnen dann so etwas wie das hier liefern würde:
Und ein bisschen Text für den guten Zweck:
Diese Abfrage soll eine monatliche statistische Zusammenfassung der Forenaktivität liefern, die sich speziell auf die Unterscheidung zwischen Beiträgen von „Mitarbeitern“ und „Nicht-Mitarbeitern“ innerhalb eines bestimmten Zeitrahmens konzentriert. Die berechneten Kennzahlen umfassen die Gesamtzahl der erstellten Themen, alle erstellten Beiträge, die Anzahl der einzelnen Nicht-Mitarbeiter, die gepostet haben, die Anzahl und den Prozentsatz der von Nicht-Mitarbeitern erstellten Beiträge, die durchschnittliche Anzahl von Beiträgen von Nicht-Mitarbeitern pro Thema sowie die entsprechenden Zahlen für Mitarbeiter. Die Informationen sollen Einblicke in das Nutzerengagement, die Inhaltserstellung und die Beteiligungsrate von Mitarbeitern im Vergleich zu Nicht-Mitarbeitern an den Diskussionen im Forum geben. Die Abfrage stellt die Genauigkeit sicher, indem sie nur „reguläre“ (Nicht-PM-)Themen berücksichtigt und gelöschte Beiträge oder Themen, Flüsterbeiträge/kleine Beiträge/Moderatoraktionen sowie Beiträge von Systembenutzern innerhalb des angegebenen Zeitraums ausschließt.
Für diese Abfrage ist das Kriterium „Mitarbeiter“, dass sie zur automatischen Gruppe @staff gehören, zu der sowohl Administratoren als auch Moderatoren gehören – dies kann jedoch angepasst werden, um nur Administratoren oder sogar eine benutzerdefinierte Gruppe von Mitarbeitern anzusprechen, die technisch gesehen nicht „Site-Mitarbeiter“ sind. Wenn Sie Moderatoren definitiv ausschließen möchten, können Sie group_id am Anfang stattdessen auf ‘1’ umstellen. 
Ist das die Art von Sache, nach der Sie suchen?
Aus irgendeinem seltsamen Grund weiß ich, dass die ID des Personals drei ist. Aber wie könnte man diese ID finden? Zuerst war ich mir absolut sicher, dass sie in URLs eingebettet ist, wie alle anderen IDs auch, aber nein. Es wird nur der Name verwendet.
Ich kenne so wenig SQL, dass man leicht sagen kann, ich kann es nicht, aber dies zeigt jede Gruppen-ID
select
id,
name
from
groups
Aber sicherlich gibt es einen gängigeren Weg, sie zu finden, oder?
Persönlich wünsche ich mir sehr eine group_id-Parameter-Suche, genau wie die für user_id 
- Param dropdown for group_id in data explorer query
Aber bis dieser Traum wahr wird, verwende ich die JSON-Daten der Gruppen-Seite, um sie herauszufinden, z.B. https://meta.discourse.org/g.json
Sie können eine Gruppensuche innerhalb der Abfrage selbst durchführen, sodass sie mit Gruppennamen funktioniert, was möglicherweise eine benutzerfreundlichere Methode ist. Also so etwas wie:
-- [params]
-- string :group_name
SELECT user_id, true as is_staff
FROM group_users
WHERE group_id = (SELECT id FROM groups WHERE name = LOWER(:group_name))
(oder die hartcodierte Version, wenn Sie keinen Parameter wünschen:)
SELECT user_id, true as is_staff
FROM group_users
WHERE group_id = (SELECT id FROM groups WHERE name = 'admins')
Vielen Dank! Vielleicht habe ich etwas übersehen, aber wenn ich auf „Ausführen“ klicke, erhalte ich diese Fehlermeldung:
Ja, das ist eine Eigenart, die ich hätte erwähnen sollen. Wenn Sie Ihre Seite aktualisieren, sollten die Eingabefelder für die Parameter angezeigt werden.
Ich wette, Sie haben diesen Schnipsel spontan erstellt. Denn er funktioniert nicht ganz gut 
Er zeigt jeden Benutzer aus der gewünschten Gruppe an und behauptet, jeder sei is_staff 
Aber danke! Ich habe wertvolle Informationen für grundlegende Administratoren über JSON und die Verwendung von SQL erhalten (wirklich, aber ich sehe trotzdem gerne, wie die KI-Berichterstattung das sieht…)
In diesem Beispiel ist das is_staff-Bit Teil der Funktion dieser speziellen Abfrage. Es wird hier speziell SELECT user_id, true as is_staff hinzugefügt und nicht aus der Datenbank selbst bezogen. Es setzt jeden aus der von Ihnen als „Mitarbeiter“ bezeichneten Gruppe, damit sie in die beiden Ergebnismengen (Mitarbeiterbeiträge vs. Nicht-Mitarbeiterbeiträge) aufgeteilt werden können.
Wenn Sie also eine Gruppe für „Mitarbeiter“ hätten, die technisch gesehen keine Website-Mitarbeiter wären, wie sie die Datenbank festlegt, könnten Sie sie trotzdem hinzufügen und sie würden in den „Mitarbeiter“-Eimer und nicht in den „Nicht-Mitarbeiter“-Eimer fallen.
OMG, das ist genau das, was ich gebraucht habe, vielen Dank!
Um sicherzugehen: umfassen „Posts“ Themen + Antworten, oder zählen sie nur Antworten?
Vielen Dank nochmals!
Bei diesem Beitrag sind die ‘posts’ nicht der erste Beitrag des Themas, sondern nur die Antworten.
Hallo @JammyDodger
Glaubst du, es ist möglich, dasselbe nur für Themen (=neuer Thread erstellt) zu haben, bitte?
Vielen Dank!
Meinen Sie ein Verhältnis zwischen Themen, die von Mitarbeitern erstellt wurden, im Vergleich zu denen, die nicht von Mitarbeitern erstellt wurden und zu dieser Abfrage hinzugefügt wurden?
Ja, in derselben Abfrage wäre das großartig!
Ich denke, das Hinzufügen dieser Spalten sollte ausreichen:
-- [params]
-- date :start_date
-- date :end_date
WITH staff_data AS (
SELECT user_id, true as is_staff
FROM group_users
WHERE group_id = 3
),
month_stats AS (
SELECT
date_trunc('month', p.created_at)::date AS month,
COUNT(*) FILTER (WHERE p.post_number = 1) AS total_topics,
COUNT(*) FILTER (WHERE p.post_number = 1 AND is_staff IS NOT TRUE) AS non_staff_topics,
COUNT(*) FILTER (WHERE p.post_number = 1 AND is_staff IS TRUE) AS staff_topics,
COUNT(*) FILTER (WHERE p.post_number <> 1) AS total_posts,
COUNT(DISTINCT p.user_id) FILTER (WHERE p.post_number <> 1 AND is_staff IS NOT TRUE) AS non_staff_users,
COUNT(*) FILTER (WHERE p.post_number <> 1 AND is_staff IS NOT TRUE) AS non_staff_posts,
COUNT(DISTINCT p.user_id) FILTER (WHERE p.post_number <> 1 AND is_staff) AS staff_users,
COUNT(*) FILTER (WHERE p.post_number <> 1 AND is_staff) AS staff_posts
FROM posts p
LEFT JOIN topics t ON t.id = p.topic_id
LEFT JOIN staff_data s ON p.user_id = s.user_id
WHERE p.created_at::date BETWEEN :start_date AND :end_date
AND t.archetype = 'regular'
AND t.deleted_at IS NULL
AND p.deleted_at IS NULL
AND p.post_type = 1
AND p.user_id > 0
GROUP BY month
)
SELECT
ms.month AS "Monat",
ms.total_topics AS "Alle Themen",
ms.non_staff_topics AS "Themen von Nicht-Mitarbeitern",
ROUND(ms.non_staff_topics * 100.0 / NULLIF(ms.total_topics, 0),1) || '%' AS "Themen von Nicht-Mitarbeitern (% gesamt)",
ms.staff_topics AS "Themen von Mitarbeitern",
ROUND(ms.staff_topics * 100.0 / NULLIF(ms.total_topics, 0),1) || '%' AS "Themen von Mitarbeitern (% gesamt)",
ms.total_posts AS "Alle Beiträge",
ms.non_staff_posts AS "Beiträge von Nicht-Mitarbeitern",
ROUND(ms.non_staff_posts * 100.0 / NULLIF(ms.total_posts, 0),1) || '%' AS "Beiträge von Nicht-Mitarbeitern (% gesamt)",
ms.non_staff_posts / NULLIF(ms.total_topics, 0) AS "Durchschnittliche Beiträge von Nicht-Mitarbeitern pro Thema",
ms.non_staff_users AS "Nicht-Mitarbeiter, die gepostet haben",
ms.staff_posts AS "Beiträge von Mitarbeitern",
ROUND(ms.staff_posts * 100.0 / NULLIF(ms.total_posts, 0),1) || '%' AS "Beiträge von Mitarbeitern (% gesamt)",
ms.staff_posts / NULLIF(ms.total_topics, 0) AS "Durchschnittliche Beiträge von Mitarbeitern pro Thema",
ms.staff_users AS "Mitarbeiter, die gepostet haben"
FROM month_stats ms
ORDER BY "Monat"
Vielen Dank, das ist perfekt!
This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.
