Mein Forum ist nicht sehr groß, und die meisten unserer Reaktionen stammen von „Mitarbeitern“ (Admins, Moderatoren, TL=4). Ich wollte sehen, wie sich die Likes von normalen Benutzern im Vergleich zu „Mitarbeitern“ verhalten, und die Anzahl der Beiträge pro Tag auflisten, um besser zu verstehen, was vor sich geht und wo wir unsere Bemühungen konzentrieren müssen, um die Nutzung von Reaktionen zu verbessern.
Mein Kumpel ChatGPT und ich haben uns das ausgedacht:
-- [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
Änderungen an @SaraDevs ursprünglicher Abfrage (danke, Sara!):
Zusammenfassung der SQL-Änderungen
- Mitarbeitergruppe generieren:
Eine staff_users-CTE wurde hinzugefügt, um Mitarbeiterbenutzer aus der users-Tabelle zu identifizieren. Ein Mitarbeiterbenutzer wird als einer der folgenden definiert:
admin = truemoderator = truetrust_level = 4
- Mitarbeiter-Likes separat erfassen:
Eine Unterabfrage wurde hinzugefügt, um die Anzahl der Likes von Mitarbeitern (staff_likes_count) zu berechnen, indem post_actions nach user_id in der staff_users-Gruppe gefiltert wird.
- Spalte für Nicht-Mitarbeiter-Likes umbenennen:
Die Ausgabekennzeichnung für Nicht-Mitarbeiter-Likes wurde von likes_count in regular_likes_count geändert.
- Gesamt-Likes hinzufügen:
Eine Spalte total_likes wurde eingeführt, um regular_likes_count und staff_likes_count zu summieren.
- Beiträge pro Tag hinzufügen:
Eine Unterabfrage wurde hinzugefügt, um die Anzahl der Beiträge pro Tag (posts_per_day) zu berechnen und sie mit dem Datumsbereich zu verknüpfen.
(Ja, ChatGPT hat diese Liste der Änderungen auch für mich erstellt.)
Beispielergebnisse:
| Datum |
regular_likes_count |
staff_likes_count |
posts_per_day |
| 01.01.24 |
0 |
6 |
7 |
| 02.01.24 |
0 |
5 |
3 |
| 03.01.24 |
1 |
0 |
4 |
| 04.01.24 |
1 |
2 |
5 |
| 05.01.24 |
9 |
9 |
30 |
| 06.01.24 |
0 |
1 |
11 |
| 07.01.24 |
2 |
4 |
11 |
| 08.01.24 |
0 |
5 |
18 |
| 09.01.24 |
0 |
0 |
2 |
| 10.01.24 |
0 |
0 |
7 |
| 11.01.24 |
0 |
4 |
5 |
| 12.01.24 |
4 |
0 |
4 |
| 13.01.24 |
6 |
0 |
10 |
| 14.01.24 |
1 |
7 |
18 |
| 15.01.24 |
2 |
4 |
7 |
Gleiche Abfrage wochenweise gemeldet zur Glättung
-- [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
Falls es interessant ist, hier sind die endgültigen Prompts, die Saras Abfrage modifiziert haben:
Ich habe eine SQL-Abfrage, die die tägliche Anzahl von Likes (likes_count) zwischen zwei Daten meldet, aber ich muss die folgenden Verbesserungen vornehmen, um eine endgültige Ausgabe zu erstellen, die Daten nach Wochen aggregiert und zusätzliche Details enthält:
- Definieren einer Mitarbeitergruppe:
- Erstellen Sie eine
staff_users-Gruppe aus der users-Tabelle. Ein Benutzer sollte als Mitarbeiter betrachtet werden, wenn er eine der folgenden Kriterien erfüllt:
admin = truemoderator = truetrust_level = 4
- Likes nach Mitarbeitern und Nicht-Mitarbeitern trennen:
- Fügen Sie zwei separate Spalten hinzu:
regular_likes_count: Anzahl der Likes von Nicht-Mitarbeitern.staff_likes_count: Anzahl der Likes von Mitarbeitern.
- Stellen Sie sicher, dass die Spalte
regular_likes_count Likes von Mitarbeitern ausschließt.
- Gesamt-Likes hinzufügen:
- Fügen Sie eine Spalte
total_likes hinzu, die regular_likes_count und staff_likes_count summiert.
- Beiträge pro Zeitraum hinzufügen:
- Fügen Sie eine Spalte
posts_per_week hinzu, die die Anzahl der Beiträge zählt, die während jeder Woche erstellt wurden.
- Nach Wochen aggregieren:
- Ändern Sie die Abfrage so, dass alle Daten nach wöchentlichen Intervallen anstatt täglich gruppiert werden.
- Fügen Sie eine Spalte
week_start hinzu, die das Startdatum jeder Woche darstellt.
- Nach Wochen begrenzen:
- Führen Sie einen Parameter
:weeks_ago ein, um die Ergebnisse auf die letzten N Wochen zu beschränken. Der Standardwert sollte 52 Wochen (1 Jahr) sein.
- Sortieren und endgültige Spalten:
- Stellen Sie sicher, dass die Ausgabe nach
week_start sortiert ist und die folgenden Spalten in dieser Reihenfolge enthält:
week_start: Das Startdatum der Woche.regular_likes_count: Die Anzahl der Likes von Nicht-Mitarbeitern.staff_likes_count: Die Anzahl der Likes von Mitarbeitern.total_likes: Die Summe von regular_likes_count und staff_likes_count.posts_per_week: Die Anzahl der Beiträge, die während der Woche erstellt wurden.