Il mio forum non è molto grande e la maggior parte delle nostre reazioni “mi piace” proviene dallo “staff” (amministratori, moderatori, TL=4). Volevo vedere come si confrontano i “mi piace” degli utenti normali rispetto allo “staff” e elencare il numero di post al giorno per avere un’idea migliore di cosa sta succedendo e dove dobbiamo concentrare gli sforzi per migliorare l’uso delle reazioni.
Io e il mio amico ChatGPT abbiamo ideato questo:
-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2024-12-31
WITH date_range AS (
SELECT date_trunc('day', series) AS date
FROM generate_series(
:start_date::timestamp,
:end_date::timestamp,
'1 day'::interval
) series
),
staff_users AS (
SELECT id
FROM users
WHERE admin = true OR moderator = true OR trust_level = 4
)
SELECT
dr.date::date,
COALESCE(pa_non_staff.regular_likes_count, 0) AS regular_likes_count,
COALESCE(pa_staff.staff_likes_count, 0) AS staff_likes_count,
COALESCE(pa_non_staff.regular_likes_count, 0) + COALESCE(pa_staff.staff_likes_count, 0) AS total_likes,
COALESCE(posts_count.posts_per_day, 0) AS posts_per_day
FROM
date_range dr
LEFT JOIN (
SELECT
date_trunc('day', pa.created_at) AS action_date,
COUNT(*) AS regular_likes_count
FROM post_actions pa
WHERE pa.post_action_type_id = 2
AND pa.user_id NOT IN (SELECT id FROM staff_users)
AND pa.created_at >= :start_date
AND pa.created_at <= (:end_date::date + 1)
GROUP BY action_date
) pa_non_staff ON dr.date = pa_non_staff.action_date
LEFT JOIN (
SELECT
date_trunc('day', pa.created_at) AS action_date,
COUNT(*) AS staff_likes_count
FROM post_actions pa
WHERE pa.post_action_type_id = 2
AND pa.user_id IN (SELECT id FROM staff_users)
AND pa.created_at >= :start_date
AND pa.created_at <= (:end_date::date + 1)
GROUP BY action_date
) pa_staff ON dr.date = pa_staff.action_date
LEFT JOIN (
SELECT
date_trunc('day', p.created_at) AS post_date,
COUNT(*) AS posts_per_day
FROM posts p
WHERE p.created_at >= :start_date
AND p.created_at <= (:end_date::date + 1)
GROUP BY post_date
) posts_count ON dr.date = posts_count.post_date
ORDER BY dr.date
Modifiche alla query originale di @SaraDev (grazie, Sara!):
Riepilogo modifiche SQL
- Genera Gruppo Staff:
Aggiunta una CTE staff_users per identificare gli utenti staff dalla tabella users. Un utente staff è definito come uno qualsiasi dei seguenti:
admin = truemoderator = truetrust_level = 4
- Separa Mi Piace Staff:
Aggiunta una sottoquery per calcolare il conteggio dei mi piace dagli utenti staff (staff_likes_count) filtrando post_actions per user_id nel gruppo staff_users.
- Rinomina Colonna Mi Piace Non Staff:
Modificata l’etichetta di output per i mi piace non staff da likes_count a regular_likes_count.
- Aggiungi Mi Piace Totali:
Introdotta una colonna total_likes per sommare regular_likes_count e staff_likes_count.
- Aggiungi Post al Giorno:
Aggiunta una sottoquery per calcolare il numero di post al giorno (posts_per_day) e unita all’intervallo di date.
(Sì, ChatGPT ha creato anche questo elenco di modifiche per me.)
Esempio di risultati:
|data|regular_likes_count|staff_likes_count|posts_per_day|
|:—:|:—:|:—:|:—
|1/1/24|0|6|7|
|1/2/24|0|5|3|
|1/3/24|1|0|4|
|1/4/24|1|2|5|
|1/5/24|9|9|30|
|1/6/24|0|1|11|
|1/7/24|2|4|11|
|1/8/24|0|5|18|
|1/9/24|0|0|2|
|1/10/24|0|0|7|
|1/11/24|0|4|5|
|1/12/24|4|0|4|
|1/13/24|6|0|10|
|1/14/24|1|7|18|
|1/15/24|2|4|7|\
Stessa query riportata per settimane per smussare
-- [params]
-- integer :weeks_ago = 52
WITH date_range AS (
SELECT date_trunc('week', series) AS week_start
FROM generate_series(
date_trunc('week', now()) - (:weeks_ago || ' weeks')::interval,
date_trunc('week', now()),
'1 week'::interval
) series
),
staff_users AS (
SELECT id
FROM users
WHERE admin = true OR moderator = true OR trust_level = 4
)
SELECT
dr.week_start::date AS week_start,
COALESCE(pa_non_staff.regular_likes_count, 0) AS regular_likes_count,
COALESCE(pa_staff.staff_likes_count, 0) AS staff_likes_count,
COALESCE(pa_non_staff.regular_likes_count, 0) + COALESCE(pa_staff.staff_likes_count, 0) AS total_likes,
COALESCE(posts_count.posts_per_week, 0) AS posts_per_week
FROM
date_range dr
LEFT JOIN (
SELECT
date_trunc('week', pa.created_at) AS action_week,
COUNT(*) AS regular_likes_count
FROM post_actions pa
WHERE pa.post_action_type_id = 2
AND pa.user_id NOT IN (SELECT id FROM staff_users)
AND pa.created_at >= date_trunc('week', now()) - (:weeks_ago || ' weeks')::interval
AND pa.created_at <= date_trunc('week', now())
GROUP BY action_week
) pa_non_staff ON dr.week_start = pa_non_staff.action_week
LEFT JOIN (
SELECT
date_trunc('week', pa.created_at) AS action_week,
COUNT(*) AS staff_likes_count
FROM post_actions pa
WHERE pa.post_action_type_id = 2
AND pa.user_id IN (SELECT id FROM staff_users)
AND pa.created_at >= date_trunc('week', now()) - (:weeks_ago || ' weeks')::interval
AND pa.created_at <= date_trunc('week', now())
GROUP BY action_week
) pa_staff ON dr.week_start = pa_staff.action_week
LEFT JOIN (
SELECT
date_trunc('week', p.created_at) AS post_week,
COUNT(*) AS posts_per_week
FROM posts p
WHERE p.created_at >= date_trunc('week', now()) - (:weeks_ago || ' weeks')::interval
AND p.created_at <= date_trunc('week', now())
GROUP BY post_week
) posts_count ON dr.week_start = posts_count.post_week
ORDER BY dr.week_start
Nel caso fosse interessante, ecco i prompt finali che hanno modificato la query di Sara:
Ho una query SQL che riporta il conteggio giornaliero dei mi piace (likes_count) tra due date, ma devo apportare i seguenti miglioramenti per produrre un output finale che aggreghi i dati per settimane e includa dettagli aggiuntivi:
- Definire un Gruppo Staff:
- Creare un gruppo
staff_users dalla tabella users. Un utente dovrebbe essere considerato staff se soddisfa uno dei seguenti criteri:
admin = truemoderator = truetrust_level = 4
- Separare i Mi Piace per Staff e Non Staff:
- Aggiungere due colonne separate:
regular_likes_count: Conteggio dei mi piace dagli utenti non staff.staff_likes_count: Conteggio dei mi piace dagli utenti staff.
- Assicurarsi che la colonna
regular_likes_count escluda i mi piace generati dagli utenti staff.
- Aggiungere Mi Piace Totali:
- Includere una colonna
total_likes che somma regular_likes_count e staff_likes_count.
- Aggiungere Post per Periodo:
- Aggiungere una colonna
posts_per_week che conta il numero di post creati durante ogni settimana.
- Aggregare per Settimane:
- Modificare la query per raggruppare tutti i dati per intervalli settimanali invece che giornalieri.
- Includere una colonna
week_start che rappresenta la data di inizio di ogni settimana.
- Limitare per Settimane Fa:
- Introdurre un parametro
:weeks_ago per limitare i risultati alle ultime N settimane. Il valore predefinito dovrebbe essere 52 settimane (1 anno).
- Ordinamento e Colonne Finali:
- Assicurarsi che l’output sia ordinato per
week_start e includa le seguenti colonne in quest’ordine:
week_start: La data di inizio della settimana.regular_likes_count: Il conteggio dei mi piace dagli utenti non staff.staff_likes_count: Il conteggio dei mi piace dagli utenti staff.total_likes: La somma di regular_likes_count e staff_likes_count.posts_per_week: Il numero di post creati durante la settimana.