Calcola i momenti totali di partecipazione per mese utilizzando Data Explorer

[Konrad_Sopala]

Ciao, maghi degli Esploratori di Dati!

Qualcuno ha mai provato a creare una query che restituisca il conteggio totale dei momenti di partecipazione per mese utilizzando le query degli Esploratori di Dati?

Intendo il numero totale di tutti i post, le soluzioni e i like per mese.

Grazie in anticipo!

[Rotblut (Bernhard)]

Guarda se ti piace.

querys.md

[Konrad_Sopala]

Non vedo esattamente quello che sto cercando

[simon]

@Konrad_Sopala

Ciao, maghi di Data Explorer!

Non sono un esperto di Data Explorer, ma mi piace scrivere query quando ho tempo. Prima di lasciarmi trascinare troppo, suppongo che tu stia cercando una ripartizione mensile delle statistiche. Se è così, qualcosa del genere potrebbe funzionare:

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

Prima di eseguire la query, devi fornire un valore per il parametro start_date. Deve essere nel formato yyyy-mm-dd. La query estrae solo la parte del mese da quella data. La ragione per cui è necessario un parametro di data di inizio è che la query andrebbe in timeout su un sito di grandi dimensioni se eseguita su tutto il periodo in cui il sito è online. Utilizzando il parametro di data di inizio, riesco a eseguirla su Meta per un periodo di alcuni anni senza che vada in timeout.

Una cosa da notare nella query è che la colonna active_users_count restituisce il numero di utenti unici che si sono connessi al sito durante un mese: non verifica se questi utenti abbiano eseguito azioni sul sito (ad esempio, hanno messo like a un post o ne hanno creato uno). Forse la query potrebbe farlo, ma sono preoccupato per i problemi di timeout.

Fammi sapere se questi non sono i dati che stai cercando. Se l’idea generale è corretta, fammi sapere se hai bisogno di aggiungere altri dati alla query o se noti qualcosa che sembra errato nei risultati.

[Konrad_Sopala]

Grazie @simon per aver condiviso, è davvero utile ed è proprio quello che stavo cercando! Posso chiederti se è possibile apportare una modifica alla query? Non mi piace specificare la data di inizio. Vorrei sommare tutti quei valori (post, utenti, soluzioni, like) e ottenere il risultato della query in questo modo, per mese:

[simon]

@Konrad_Sopala

Non mi piace specificare la data di inizio. Vorrei sommare tutti quei valori (post, utenti, soluzioni, mi piace) e ottenere il risultato della query così, per mese:

Questo potrebbe essere possibile. Proverò. Il parametro della data di inizio è stato aggiunto all’ultima ora alla query, quando ho scoperto che la query avrebbe generato un timeout se eseguita su Meta per tutto il periodo in cui il sito è online. Potrebbero esserci modi per migliorare l’efficienza della query ed eliminare il problema del timeout. In caso contrario, la query dovrebbe permettere di impostare un intervallo di tempo invece di richiedere solo una data di inizio. In questo modo, potrai recuperare tutti i dati di un sito eseguendo la query più volte con diversi intervalli di tempo.

[Konrad_Sopala]

Certo! Ti aspetto quando avrai tempo di fare quella modifica.

[michebs (Michelle)]

Ciao Konrad,

Di seguito trovi la query aggiornata.

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 

Se hai bisogno di aggiungere tutte le colonne, per essere esattamente uguale all’immagine, questa è la query:

Dettagli 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]

È esattamente quello che stavo cercando! Grazie ancora @michebs! Avete mai pensato di creare una sorta di raccolta open source di query per Data Explorer e condividerla con gli utenti di Discourse?

[codinghorror (Jeff Atwood)]

Sì, è l’elenco integrato di query incluso con Data Explorer

[Konrad_Sopala]

Sì, lo so, ma è qualcosa che espandi? Ad esempio, in base alle domande qui sul forum.

[rishabh]

Possiamo accettare PR per aggiungere query, ecco un esempio:

FEATURE: Add a poll results query (#43)

Inoltre, (Superseded) What cool data explorer queries have you come up with? e la lista di @SidV discourse-data-explorer/querys.md at queries · SidVal/discourse-data-explorer · GitHub contengono un sacco di query utili!

[Konrad_Sopala]

Ottimo! Grazie per averlo condiviso!