Calculer les moments de participation totaux par mois à l'aide de Data Explorer

[Konrad_Sopala]

Salut les magiciens de l’Explorateur de données !

Est-ce que quelqu’un a déjà essayé de créer une requête qui donne le nombre total de moments de participation par mois via les requêtes de l’Explorateur de données ?

Je parle du nombre total de tous les messages, des solutions et des likes par mois.

Merci d’avance !

[Rotblut (Bernhard)]

Regarde si ça te plaît.

querys.md

[Konrad_Sopala]

Je ne vois pas exactement celui que je cherche

[simon]

@Konrad_Sopala

Salut les wizards de Data Explorer !

Je ne suis pas expert en Data Explorer, mais j’aime bien écrire des requêtes quand j’ai un peu de temps. Avant de trop m’emballer, je suppose que vous cherchez une ventilation mensuelle des statistiques. Si c’est le cas, quelque chose comme ceci pourrait fonctionner :

--[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

Avant d’exécuter la requête, vous devez fournir une valeur pour le paramètre start_date. Elle doit être au format yyyy-mm-dd. Cependant, la requête n’extrait que la partie mois de cette date. La raison pour laquelle un paramètre de date de début est nécessaire est que la requête entraînerait un dépassement de délai sur un grand site si elle était exécutée sur toute la période depuis la mise en ligne du site. Grâce au paramètre de date de début, je peux l’exécuter sur Meta sur une période de quelques années sans qu’elle ne dépasse le délai.

Une chose à noter avec cette requête est que la colonne active_users_count renvoie le nombre d’utilisateurs uniques qui se sont connectés au site au cours d’un mois ; elle ne vérifie pas si ces utilisateurs ont effectué des actions sur le site (par exemple, aimer un post ou créer un post). Il serait peut-être possible de le faire dans la requête, mais je m’inquiète des problèmes de dépassement de délai.

Faites-moi savoir si ce n’est pas le type de données que vous recherchez. Si l’idée générale est correcte, dites-moi si vous avez besoin d’ajouter des données supplémentaires à la requête ou si vous remarquez quelque chose qui semble incorrect dans ses résultats.

[Konrad_Sopala]

Merci @simon de partager cela, c’est vraiment utile et c’est essentiellement ce que je recherche ! Puis-je vous demander s’il est possible de modifier une chose dans la requête ? Je n’aime pas spécifier la date de début. Je voudrais sommer toutes ces valeurs (publications, utilisateurs, solutions, likes) et obtenir le résultat de la requête de cette manière, par mois :

[simon]

@Konrad_Sopala

Je n’aime pas spécifier la date de début. J’aimerais sommer toutes ces valeurs (publications, utilisateurs, solutions, likes) et obtenir le résultat de la requête de cette manière, par mois :

Cela pourrait être possible. Je vais essayer. Le paramètre de date de début a été ajouté à la requête à la dernière minute, car j’ai constaté que la requête expirait si je l’exécutais sur Meta pour toute la durée de mise en ligne du site. Il existe peut-être des moyens d’améliorer l’efficacité de la requête pour éliminer le problème de délai d’attente. Sinon, la requête devrait permettre de définir une plage de temps plutôt que de demander uniquement une date de début. Ainsi, vous pourrez récupérer toutes les données d’un site en exécutant la requête plusieurs fois avec différentes plages de temps.

[Konrad_Sopala]

Bien sûr ! Je vous attends dès que vous aurez le temps de faire cet ajustement.

[michebs (Michelle)]

Bonjour Konrad,

Voici la requête ajustée.

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 

Si vous devez ajouter toutes les colonnes pour correspondre exactement à l’image, voici la requête :

Détails SQL

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]

C’est exactement ce que je cherchais ! Merci encore à @michebs ! Avez-vous pensé à créer une sorte de collection open source de requêtes Data Explorer et à la partager avec les utilisateurs de Discourse ?

[codinghorror (Jeff Atwood)]

Oui, c’est la liste intégrée de requêtes fournie avec Data Explorer

[Konrad_Sopala]

Oui, je sais, mais est-ce quelque chose que tu développes ? Par exemple, en te basant sur les questions posées ici sur le forum.

[rishabh]

Nous acceptons les PR pour ajouter des requêtes, voici un exemple :

FEATURE: Add a poll results query (#43)

Par ailleurs, (Superseded) What cool data explorer queries have you come up with? et la liste de @SidV discourse-data-explorer/querys.md at queries · SidVal/discourse-data-explorer · GitHub regorgent également de requêtes utiles !

[Konrad_Sopala]

Super ! Merci de l’avoir partagé !