Je souhaite fournir à notre équipe des statistiques sur le nombre d’utilisateurs actifs actuels, selon la définition suivante d’« Utilisateur actif » :
Utilisateurs ayant soit lu (connectés) soit publié sur notre forum au cours de l’année dernière.
Nous avons beaucoup d’utilisateurs en mode liste de diffusion qui répondent par e-mail, nous manquerions donc beaucoup d’entre eux si nous nous contentions de recueillir le nombre de lecteurs comme le fait cette requête :
Je peux également obtenir le nombre de ceux qui ont publié ici :
Je suis simplement trop bête pour les combiner avec un OU afin de récupérer les deux groupes. Pouvez-vous m’aider ?
Cela devrait vous fournir tous les utilisateurs qui ont soit lu en étant connectés, soit publié au cours de la dernière année. L’explorateur de données affichera également leur nombre.
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
Il existe probablement une méthode plus efficace pour le faire, mais cela fonctionne . Si vous souhaitez une période spécifique, vous pouvez remplacer > CURRENT_TIMESTAMP - INTERVAL '365 days' (les deux occurrences) par quelque chose comme ceci : BETWEEN '20200101'::date AND '20210101'::date .
Vous avez fourni d’excellentes informations et références dans votre question ! Je n’ai eu qu’à sélectionner les éléments pertinents et les combiner.
Merci ! Je crois fermement en la nécessité de faciliter la tâche aux autres pour qu’ils puissent aider autant que possible.
UNION – c’était le point faible de mes connaissances en SQL. Tout est résolu maintenant, merci ! Je publierai ma requête finale ici une fois qu’elle sera plus affinée.
En fait, j’ai fini par faire quelque chose de légèrement différent car je voulais plus d’informations et UNION s’est révélé un peu restrictif. Je voulais également décomposer les résultats par groupes. J’ai réussi (je pense) en modifiant la requête incluse « Active Lurkers » avec une autre requête que j’ai pour extraire des informations spécifiques aux groupes :
-- [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
Merci d’avoir partagé ceci. J’ai essayé de l’utiliser ainsi qu’un code très similaire sur des requêtes de badges, mais j’obtiens cette erreur :
Violation de contrat :
La requête ne renvoie pas de colonne 'granted_at'
Est-ce qu’il me manque quelque chose dans le code pour pouvoir utiliser cette requête sur le système de badges ? Mon cas d’utilisation est d’automatiser le groupe des ‘lurkers’
Les requêtes de badges sont assez complexes - et elles nécessitent simplement un ‘user_id’ et un ‘granted_at’ pour être retournées. Vous devrez donc un peu bidouiller.
Je vous recommande de creuser les sujets sur les requêtes de badges (lisez-les attentivement) et d’essayer un peu. Je vous conseillerais également de ne l’exécuter qu’une fois par jour, car cela pourrait être un peu lourd autrement.
. Si vous souhaitez une période spécifique, vous pouvez remplacer