J'ai besoin de faire un rapport avec les utilisateurs connectés par plage de dates mais sans doublons le même jour (si quelqu'un a été connecté deux fois le même jour, nous ne le listerons pas deux fois). malheureusement, nous conservons les informations de connexion en tant que json (ouais, je ne peux pas les changer en table séparée, je ne sais pas qui a conçu cette base de données). Requête pour voir tous les utilisateurs connectés:
id | username | email | login_date -----+-------------------------+---------------------------------+---------------------------- 102 | example | example@example.com | 2018-12-06 09:30:10.573+00 42 | rafal | rafal@example.com | 2018-12-06 09:45:24.884+00 576 | john | john@example.com | 2018-12-06 09:35:24.922+00 576 | john | john@example.com | 2018-12-07 09:58:04.253+00
Modifié est l'horodatage avec le fuseau horaire et il est utilisé comme date de connexion.
Je n'ai trouvé que des exemples avec le nombre d'identifiants distincts par jour mais Je ne sais pas comment le modifier pour obtenir des résultats distincts par jour
exemple de données:
id | username | email | login_date -----+-------------------------+---------------------------------+---------------------------- 102 | example | example@example.com | 2018-12-06 09:30:10.573+00 102 | example | example@example.com | 2018-12-06 09:32:34.235+00 42 | rafal | rafal@example.com | 2018-12-06 09:45:24.884+00 576 | john | john@example.com | 2018-12-06 09:35:24.922+00 576 | john | john@example.com | 2018-12-07 09:58:04.253+00
données recherchées:
select a.id, username, email, ah.modified as login_date
from accounts a join
account_history ah
on modified_acc_id = a.id
where ah.data::jsonb->>'message' = 'Logon';
Comme vous pouvez le voir, sans deuxième ligne
4 Réponses :
Vous semblez vouloir le nombre de jours-utilisateur pour une période donnée. Si je comprends bien:
select count(*) as num_user_days_in_range
from (select a.username, date_trunc('day', ah.modified) as login_date
from accounts a join
account_history ah
on modified_acc_id = a.id
where ah.data::jsonb->>'message' = 'Logon'
group by a.username, login_date
) u
where login_date >= $date1 and login_date < $date2
J'ai édité ma question, je ne veux pas du nombre d'utilisateurs, juste des utilisateurs distincts par jour
utiliser la fonction de fenêtre row_number()
select id,username,email,login_date from ( select a.id, username, email, ah.modified as login_date, row_number() over(partition by a.id, username,email order by ah.modified) rn from accounts a join account_history ah on modified_acc_id = a.id where ah.data::jsonb->>'message' = 'Logon' ) t where t.rn=1
DISTINCT ON vous donne exactement la première ligne d'un groupe ordonné. Dans votre exemple, le groupe est la partie id et la date de l’horodatage login_date
SELECT DISTINCT ON (a.id, ah.modified::date)
a.id,
username,
email,
ah.modified as login_date
FROM accounts a
JOIN account_history ah
ON modified_acc_id = a.id
WHERE ah.data::jsonb->>'message' = 'Logon'
ORDER BY a.id, ah.modified::date, ah.modified
Explication de ORDER Clause BY :
Vous devez d'abord classer par les colonnes DISTINCT . Mais dans votre cas, vous ne voulez pas vraiment commander par date uniquement, mais aussi par heure. Ainsi, après avoir trié par date (ce qui est nécessaire en raison de vos colonnes DISTINCT ), vous devez également classer par horodatage.
Ainsi, toute la requête pourrait être simplifiée en ( sans sous-requête):
SELECT DISTINCT ON (id, login_date::date)
*
FROM (
-- <your query>
) s
ORDER BY id, login_date::date, login_date
Il semble que quand il y a une dupe, vous prenez la date la plus tôt. Si tel est le cas, cela fonctionne-t-il?
select
a.id, username, email, min (ah.modified) as login_date
from accounts a join
account_history ah
on modified_acc_id = a.id
where ah.data::jsonb->>'message' = 'Logon'
group by a.id, username, email, ah.modified::date
@GordonLinoff modifié