Ich möchte unserem Team Statistiken darüber geben, wie viele aktuelle aktive Nutzer wir haben, mit folgender Definition von „Aktiver Nutzer":
Nutzer, die im letzten Jahr entweder eingeloggt gelesen oder in unserem Forum gepostet haben
Wir haben viele Nutzer im E-Mail-Modus, die per E-Mail antworten. Wenn wir also einfach nur die Anzahl der Leser gemäß dieser Abfrage ermitteln, werden wir viele verpassen:
Ich kann auch die Anzahl derer ermitteln, die hier gepostet haben:
Ich bin nur zu dumm, um sie mit einem OR zu kombinieren, damit wir beide Gruppen erfassen. Können Sie mir helfen?
Dies sollte Ihnen alle Benutzer zurückgeben, die entweder eingeloggt gelesen oder im letzten Jahr gepostet haben. Der Data Explorer gibt zudem die Anzahl der Benutzer aus.
SELECT p.user_id
FROM posts p
LEFT JOIN topics t ON t.id = p.topic_id
WHERE p.created_at::date > CURRENT_TIMESTAMP - INTERVAL '365 days'
AND t.deleted_at IS NULL
AND t.visible = TRUE
AND t.closed = FALSE
AND t.archived = FALSE
AND t.archetype = 'regular'
AND p.deleted_at IS NULL
UNION
SELECT u.user_id
FROM user_visits u
WHERE u.posts_read > 0
AND u.visited_at > CURRENT_TIMESTAMP - INTERVAL '365 days'
ORDER BY user_id
Es gibt wahrscheinlich einen effizienteren Weg, dies zu tun, aber es funktioniert . Wenn Sie einen bestimmten Zeitraum möchten, können Sie beide Vorkommen von > CURRENT_TIMESTAMP - INTERVAL '365 days' durch etwas wie BETWEEN '20200101'::date AND '20210101'::date ersetzen.
Sie haben in Ihrer Frage großartige Informationen und Referenzen bereitgestellt! Ich musste nur die richtigen Teile auswählen und kombinieren.
Danke! Ich glaube daran, es anderen so weit wie möglich leicht zu machen, zu helfen.
UNION – das war die Lücke in meinem SQL-Wissen. Jetzt ist alles geklärt, vielen Dank! Ich werde meine endgültige Abfrage hier veröffentlichen, sobald sie weiter verfeinert ist.
Ich habe mich letztlich für eine etwas andere Lösung entschieden, da ich mehr Informationen benötigte und UNION sich als etwas zu einschränkend erwies. Außerdem wollte ich die Auswertung nach Gruppen aufschlüsseln. Ich habe Erfolg erzielt (glaube ich), indem ich die enthaltene Abfrage für aktive Mitleser mit einer anderen Abfrage kombiniert habe, die ich zur Extraktion gruppenspezifischer Informationen verwende:
-- [params]
-- int :number_of_days = 365
-- string :group_name = trust_level_0
With included_users AS (
SELECT
gu.user_id
FROM group_users gu
JOIN groups g
ON g.id = gu.group_id
WHERE g.name = :group_name
),
posts_by_user AS (
SELECT COUNT(*) AS posts, p.user_id
FROM posts p
LEFT JOIN topics t ON t.id = p.topic_id
WHERE p.created_at::date > CURRENT_TIMESTAMP - INTERVAL ':number_of_days' day
AND t.deleted_at IS NULL
AND t.visible = TRUE
AND t.closed = FALSE
AND t.archived = FALSE
AND t.archetype = 'regular'
AND p.deleted_at IS NULL
GROUP BY p.user_id
),
posts_read_by_user AS (
SELECT SUM(posts_read) AS posts_read, uv.user_id
FROM user_visits uv
WHERE uv.posts_read > 0
AND uv.visited_at > CURRENT_TIMESTAMP - INTERVAL ':number_of_days' day
GROUP BY uv.user_id
)
SELECT
u.id AS "user_id",
u.username_lower AS "username",
u.last_seen_at,
COALESCE(pbu.posts, 0) AS "posts_created",
COALESCE(prbu.posts_read, 0) AS "posts_read"
FROM users u
LEFT JOIN posts_by_user pbu ON pbu.user_id = u.id
LEFT JOIN posts_read_by_user prbu ON prbu.user_id = u.id
WHERE u.active = true
AND u.id > 0
AND u.id IN (SELECT user_id FROM included_users)
AND (COALESCE(pbu.posts, 0) > 0 OR COALESCE(prbu.posts_read, 0) > 0)
ORDER BY u.id
Badge-Abfragen sind ziemlich knifflig – und sie erfordern lediglich eine „user_id“ und ein „granted_at“, die zurückgegeben werden. Sie müssen dies also ein wenig hacken.
Ich empfehle Ihnen, sich mit den Themen zu Badge-Abfragen zu befassen (lesen Sie sie sorgfältig durch) und es einfach mal zu versuchen. Ich würde es auch nur einmal am Tag ausführen, da es sonst etwas ressourcenintensiv sein könnte.
. Wenn Sie einen bestimmten Zeitraum möchten, können Sie beide Vorkommen von