Gesamte Partizipationsmomente pro Monat mit Data Explorer berechnen

[Konrad_Sopala]

Hallo, ihr Data-Explorer-Zauberer!

Hat schon jemand versucht, eine Abfrage zu erstellen, die die Gesamtzahl der Teilnahmeaktivitäten pro Monat über Data-Explorer-Abfragen liefert?

Ich meine die Gesamtzahl aller Beiträge, Lösungen und Likes pro Monat.

Vielen Dank im Voraus!

[Rotblut (Bernhard)]

Schau mal, ob dir das gefällt.

querys.md

[Konrad_Sopala]

Ich sehe nicht genau das, wonach ich suche

[simon]

@Konrad_Sopala

Hallo ihr Data Explorer-Zauberer!

Ich bin kein Experte für Data Explorer, aber ich schreibe gerne Abfragen, wenn ich Zeit habe. Bevor ich mich zu sehr darin verliere, gehe ich davon aus, dass ihr eine Aufschlüsselung der Statistiken nach Monaten sucht. Falls ja, könnte so etwas funktionieren:

--[params]
-- date :start_date

WITH month_starts AS (
SELECT generate_series(date_trunc('month', :start_date::date), CURRENT_DATE, interval '1 month')::date AS month_start
),
monthly_posts AS (
SELECT
month_start,
COUNT(1) AS posts_count
FROM posts p
JOIN month_starts
ON p.created_at::date >= month_start AND p.created_at::date <= month_start + interval '1 month - 1 day'
WHERE p.deleted_at IS NULL
AND p.post_type = 1
AND p.created_at >= :start_date
GROUP BY month_start
),
monthly_total_users AS (
SELECT
month_start,
COUNT(1) AS total_users_count
FROM users u
JOIN month_starts
ON u.created_at::date <= month_start + interval '1 month - 1 day'
WHERE u.id > 0
GROUP BY month_start
),
monthly_active_users AS (
SELECT
month_start,
COUNT(DISTINCT user_id) AS active_users_count
fROM user_visits uv
JOIN month_starts
ON uv.visited_at >= month_start AND uv.visited_at <= month_start + interval '1 month - 1 day'
WHERE uv.visited_at >= :start_date
GROUP BY month_start
),
monthly_solutions AS (
SELECT
month_start,
COUNT(1) AS solutions_count
FROM user_actions ua
JOIN month_starts ms
ON ua.created_at::date >= month_start AND ua.created_at::date <= month_start + interval '1 month - 1 day'
WHERE ua.action_type = 15
AND ua.created_at >= :start_date
GROUP BY month_start
),
monthly_likes AS (
SELECT
month_start,
COUNT(1) AS likes_count
FROM user_actions ua
JOIN month_starts ms
ON ua.created_at::date >= month_start AND ua.created_at::date <= month_start + interval '1 month - 1 day'
WHERE ua.action_type = 2
AND ua.created_at >= :start_date
GROUP BY month_start
)

SELECT
ms.month_start,
COALESCE(posts_count, 0) AS posts_count,
COALESCE(total_users_count, 0) AS total_users_count,
COALESCE(active_users_count, 0) AS active_users_count,
COALESCE(solutions_count, 0) AS solutions_count,
COALESCE(likes_count, 0) AS likes_count
FROM month_starts ms
LEFT JOIN monthly_posts mp ON mp.month_start = ms.month_start
LEFT JOIN monthly_total_users mtu ON mtu.month_start = ms.month_start
LEFT JOIN monthly_active_users mau ON mau.month_start = ms.month_start
LEFT JOIN monthly_solutions mts ON mts.month_start = ms.month_start
LEFT JOIN monthly_likes ml ON ml.month_start = ms.month_start
ORDER BY month_start DESC

Bevor ihr die Abfrage ausführt, müsst ihr einen Wert für den Parameter start_date angeben. Dieser sollte im Format jjjj-mm-tt vorliegen. Die Abfrage extrahiert jedoch nur den Monatsanteil aus diesem Datum. Der Grund für den benötigten Startdatum-Parameter liegt darin, dass die Abfrage auf einer großen Website bei Ausführung über den gesamten Zeitraum, seit die Website online ist, ein Timeout auslösen würde. Mit dem Startdatum-Parameter kann ich sie für Meta über einen Zeitraum von einigen Jahren ausführen, ohne dass ein Timeout auftritt.

Ein wichtiger Hinweis zur Abfrage: Die Spalte active_users_count gibt die Anzahl der eindeutigen Benutzer zurück, die den Monat über auf die Website eingeloggt sind – sie prüft nicht, ob diese Benutzer Aktionen auf der Website durchgeführt haben (z. B. einen Beitrag geliked oder erstellt). Möglicherweise könnte die Abfrage das tun, aber ich mache mir Sorgen wegen Timeout-Problemen.

Lasst mich wissen, ob dies nicht die Art von Daten ist, die ihr sucht. Wenn die grundsätzliche Idee stimmt, gebt mir bitte Bescheid, ob weitere Daten zur Abfrage hinzugefügt werden müssen oder ob euch etwas an den Ergebnissen falsch erscheint.

[Konrad_Sopala]

Vielen Dank @simon für die Weitergabe, das ist wirklich hilfreich und entspricht weitgehend dem, wonach ich suche! Darf ich Sie fragen, ob es möglich ist, eine Änderung an der Abfrage vorzunehmen? Ich möchte das Startdatum nicht angeben. Ich würde alle diese Werte (Beiträge, Benutzer, Lösungen, Likes) zusammenfassen und das Abfrageergebnis monatlich so dargestellt haben:

[simon]

@Konrad_Sopala

Ich gebe das Startdatum ungern an. Ich würde alle diese Werte (Beiträge, Benutzer, Lösungen, Likes) zusammenfassen und das Abfrageergebnis monatlich so dargestellt haben:

Das könnte möglich sein. Ich werde es versuchen. Der Parameter für das Startdatum wurde erst im letzten Moment zur Abfrage hinzugefügt, als ich feststellte, dass die Abfrage bei einer Ausführung auf Meta für die gesamte Zeit, seit die Website online ist, ein Timeout verursachen würde. Es gibt möglicherweise Wege, die Effizienz der Abfrage zu verbessern, um das Timeout-Problem zu beheben. Falls nicht, sollte die Abfrage die Möglichkeit bieten, einen Zeitrahmen festzulegen, anstatt nur ein Startdatum abzufragen. Auf diese Weise könnten Sie alle Daten für eine Website abrufen, indem Sie die Abfrage ein paar Mal mit unterschiedlichen Zeitrahmen ausführen.

[Konrad_Sopala]

Klar! Ich warte dann auf dich, sobald du Zeit hast, diese Anpassung vorzunehmen.

[michebs (Michelle)]

Hallo Konrad,

nachfolgend findest du die angepasste Abfrage.

WITH monthly_users AS (
    SELECT 
        date_part('year', created_at) AS year, 
        date_part('month', created_at) AS month,
        COUNT(*) AS "new_users_month"
    FROM users
    WHERE id > 0
    GROUP BY date_part('year', created_at), date_part('month', created_at)
    ORDER BY date_part('year', created_at) ASC, date_part('month', created_at)
),

monthly_posts AS (
	SELECT
        date_part('year', created_at) AS year, 
        date_part('month', created_at) AS month,
		COUNT(*) AS "posts_count"
	FROM posts p
	WHERE p.deleted_at IS NULL
		AND p.post_type = 1
	GROUP BY date_part('year', created_at), date_part('month', created_at)
	ORDER BY date_part('year', created_at) ASC, date_part('month', created_at)
),

monthly_active_users AS (
	SELECT
        date_part('year', visited_at) AS year, 
        date_part('month', visited_at) AS month,
		COUNT(DISTINCT user_id) AS "active_users_count"
	FROM user_visits uv
	GROUP BY date_part('year', visited_at), date_part('month', visited_at)
	ORDER BY date_part('year', visited_at) ASC, date_part('month', visited_at)
),

monthly_solutions AS (
	SELECT
        date_part('year', created_at) AS year, 
        date_part('month', created_at) AS month,
		COUNT(*) AS "solutions_count"
	FROM user_actions ua
	WHERE ua.action_type = 15
	GROUP BY date_part('year', created_at), date_part('month', created_at)
	ORDER BY date_part('year', created_at) ASC, date_part('month', created_at)
),

monthly_likes AS (
	SELECT
        date_part('year', created_at) AS year, 
        date_part('month', created_at) AS month,
		COUNT(*) AS "likes_count"
	FROM user_actions ua
	WHERE ua.action_type = 2
	GROUP BY date_part('year', created_at), date_part('month', created_at)
	ORDER BY date_part('year', created_at) ASC, date_part('month', created_at)
)

SELECT
    mu.year,
    mu.month,
    SUM(new_users_month) over (ORDER BY mu.year, mu.month rows between unbounded preceding AND current row) AS total_users,
    posts_count,
    COALESCE(active_users_count, 0) AS active_users_count,
    COALESCE(solutions_count, 0) AS solutions_count,
    COALESCE(likes_count, 0) AS solutions_count
FROM monthly_users mu
LEFT JOIN monthly_posts mp ON mp.year = mu.year AND mp.month = mu.month
LEFT JOIN monthly_active_users mau ON mau.year = mu.year AND mau.month = mu.month
LEFT JOIN monthly_solutions ms ON ms.year = mu.year AND ms.month = mu.month
LEFT JOIN monthly_likes ml ON ml.year = mu.year AND ml.month = mu.month
ORDER BY mu.year, mu.month 

Falls du alle Spalten hinzufügen musst, um exakt dem Bild zu entsprechen, lautet die Abfrage:

SQL-Details

WITH monthly_users AS (
    SELECT 
        date_part('year', created_at) AS year, 
        date_part('month', created_at) AS month,
        COUNT(*) AS new_users_month
    FROM users
    WHERE id > 0
    GROUP BY date_part('year', created_at), date_part('month', created_at)
    ORDER BY date_part('year', created_at) ASC, date_part('month', created_at)
),

monthly_posts AS (
	SELECT
        date_part('year', created_at) AS year, 
        date_part('month', created_at) AS month,
		COUNT(*) AS posts_count
	FROM posts p
	WHERE p.deleted_at IS NULL
		AND p.post_type = 1
	GROUP BY date_part('year', created_at), date_part('month', created_at)
	ORDER BY date_part('year', created_at) ASC, date_part('month', created_at)
),

monthly_active_users AS (
	SELECT
        date_part('year', visited_at) AS year, 
        date_part('month', visited_at) AS month,
		COUNT(DISTINCT user_id) AS active_users_count
	FROM user_visits uv
	GROUP BY date_part('year', visited_at), date_part('month', visited_at)
	ORDER BY date_part('year', visited_at) ASC, date_part('month', visited_at)
),

monthly_solutions AS (
	SELECT
        date_part('year', created_at) AS year, 
        date_part('month', created_at) AS month,
		COUNT(*) AS solutions_count
	FROM user_actions ua
	WHERE ua.action_type = 15
	GROUP BY date_part('year', created_at), date_part('month', created_at)
	ORDER BY date_part('year', created_at) ASC, date_part('month', created_at)
),

monthly_likes AS (
	SELECT
        date_part('year', created_at) AS year, 
        date_part('month', created_at) AS month,
		COUNT(*) AS likes_count
	FROM user_actions ua
	WHERE ua.action_type = 2
	GROUP BY date_part('year', created_at), date_part('month', created_at)
	ORDER BY date_part('year', created_at) ASC, date_part('month', created_at)
)

SELECT
    mu.year,
    mu.month,   
    SUM(new_users_month + COALESCE(posts_count,0) + 
        COALESCE(active_users_count, 0) + 
        COALESCE(solutions_count, 0) + 
        COALESCE(likes_count, 0)) 
        over (ORDER BY mu.year, mu.month rows between unbounded preceding AND current row) AS sum_total
FROM monthly_users mu
LEFT JOIN monthly_posts mp ON mp.year = mu.year AND mp.month = mu.month
LEFT JOIN monthly_active_users mau ON mau.year = mu.year AND mau.month = mu.month
LEFT JOIN monthly_solutions ms ON ms.year = mu.year AND ms.month = mu.month
LEFT JOIN monthly_likes ml ON ml.year = mu.year AND ml.month = mu.month
ORDER BY mu.year, mu.month 

[Konrad_Sopala]

Das ist genau das, wonach ich gesucht habe! Nochmals vielen Dank, @michebs! Habt ihr schon darüber nachgedacht, eine Art Open-Source-Sammlung von Data-Explorer-Abfragen zu erstellen und sie mit Discourse-Nutzern zu teilen?

[codinghorror (Jeff Atwood)]

Ja, das ist die integrierte Liste der Abfragen, die im Data Explorer enthalten sind

[Konrad_Sopala]

Ja, das weiß ich, aber ist das etwas, das du ausbaust? Zum Beispiel basierend auf den Fragen hier aus dem Forum.

[rishabh]

Wir können PRs zur Hinzufügung von Abfragen annehmen. Hier ist ein Beispiel:

FEATURE: Add a poll results query (#43)

Darüber hinaus findest du auf (Superseded) What cool data explorer queries have you come up with? und in @SidVs Liste unter discourse-data-explorer/querys.md at queries · SidVal/discourse-data-explorer · GitHub viele weitere nützliche Abfragen!

[Konrad_Sopala]

Super! Danke, dass du das geteilt hast!