[MYSQL] Welke users hebben niet ingelogd deze maand?

[Digihelp ®]

Ik ben bezig met een statistieken systeem, zodat alle gebruikers kunnen zien wie de website het meeste gebruikt en wie de website niet gebruikt.

De login gegevens staan in een tabel met daarin de kolommen id, username, datetime en succes. succes = 1 als er succesvol is ingelogd, en 0 bij een foutief wachtwoord.

Ik heb al een query gemaakt om de 10 meest actieve gebruikers te tonen (10 keer op 1 dag inloggen telt als 1):

code:

SELECT username, COUNT(distinct DAY(datetime)) AS "dagen" 
FROM accesslog 
WHERE succes=1 AND MONTH(datetime)=2 AND YEAR(datetime)=2006 
GROUP BY username 
ORDER BY dagen DESC 
LIMIT 10

Maar nu wil ik ook zien welke users er niet actief zijn geweest in een bepaalde periode, in dit geval in februari 2006. Ik heb al een aantal zaken geprobeerd en gezocht op GoT en op Google, maar ik kom er niet uit. De MYSQL versie die ik gebruik is : 4.1.15. Subqueries zijn dus volgens mij niet mogelijk. Wie heeft er een idee ?

[ Voor 6% gewijzigd door Digihelp ® op 27-02-2006 22:11 . Reden: kleine toevoeging ]

[P_de_B]

Om te zien welke gebruikers niet actief zijn geweest kun je neem ik aan kijken welke users niet in de tabel accesslog staan?

Je kunt een join maken met deze tabel:

SQL:

SELECT username
FROM users_tabel t
LEFT OUTER JOIN accesslog a ON t.username = a.usernam
WHERE a.username IS NULL

Je doet dus een LEFT OUTER join en kijkt met de IS NULL welke records null zijn in de accesslog tabel, die records zijn dus niet gevonden.

[El_kingo]

Euhm, kun je niet gewoon het volgende doen:
[CODE]
SELECT username, COUNT(distinct DAY(datetime)) AS "dagen", succes
FROM accesslog
WHERE MONTH(datetime)=2 AND YEAR(datetime)=2006
GROUP BY username
ORDER BY succes, dagen ASC
LIMIT 10
[/code]


Edit: lezen!!!!, succes zegt alleen iets over verkeerde inlog... (sorry)

[ Voor 17% gewijzigd door El_kingo op 27-02-2006 22:21 ]

[Digihelp ®]

Dank P_de_B, jouw query werkt prima om te kijken wie er nog nooit heeft ingelogd.

Echter, wat ik wil weten is wie er in een bepaalde periode niet is ingelogd.

Ik heb jouw query enigszins aangepast om er een periode in te verwerken, maar nu krijg ik geen records terug:

SQL:

SELECT user 
FROM users u 
LEFT OUTER JOIN accesslog a ON u.user = a.username 
WHERE MONTH(a.datetime) = 2 AND a.username IS NULL

[P_de_B]

Je krijgt nu inderdaad geen records terug omdat je in de WHERE clausule gegevens opneemt van de tabel die je LEFT OUTER joined en waarvan je zegt dat het NULL moet zijn. Dat gaat niet werken.

In een bepaalde periode is aanmerkelijk lastiger zonder subquery, maar volgens mij ondersteunt 4.1 al wel subqueries?

SQL:

SELECT user
FROM users u
WHERE username NOT IN (SELECT username FROM accesslog WHERE MONTH(a.datetime)=2)

Werkt dit?

[Digihelp ®]

Na een kleine aanpassing (vanwege mijn naamgeving) doet hij het ! Erg bedankt

SQL:

SELECT user 
FROM users u 
WHERE user NOT IN 
(SELECT username FROM accesslog WHERE MONTH(datetime)=2)

[jvdmeer]

Digihelp ® schreef op maandag 27 februari 2006 @ 22:58:
Na een kleine aanpassing (vanwege mijn naamgeving) doet hij het ! Erg bedankt

SQL:

SELECT user 
FROM users u 
WHERE user NOT IN 
(SELECT username FROM accesslog WHERE MONTH(datetime)=2)

Probeer de NOT IN alsjeblieft weg te werken i.v.m. indexen, bijv middels de eerder genoemde LEFT JOIN en NULL. Eventueel kan je de month()-clausule ook opnemen in de join i.pv. de WHERE.

[Digihelp ®]

jvdmeer schreef op maandag 27 februari 2006 @ 23:12:
[...]
Eventueel kan je de month()-clausule ook opnemen in de join i.pv. de WHERE.

Wat bedoel je ? Hoe kan ik die clausule opnemen in de join ? Ik heb geen ervaring met outer joins en heb nog niets kunnen vinden over clausules in die join.

[ACM]

Digihelp ® schreef op maandag 27 februari 2006 @ 23:33:
Wat bedoel je ? Hoe kan ik die clausule opnemen in de join ? Ik heb geen ervaring met outer joins en heb nog niets kunnen vinden over clausules in die join.

Dat is vrij simpel op te nemen

Je eigen query is er op aan te passen:

SQL:

SELECT user 
FROM users u 
LEFT OUTER JOIN accesslog a ON u.user = a.username AND MONTH(a.datetime) = 2 
WHERE a.username IS NULL

Of de query zo precies doet wat je wilt weet ik niet, ik heb het niet getest, maar het gaat me om het idee voor je. In principe plaats je in die JOIN nu de clause waar het "niet aan moet voldoen" en dan kijk je daarna in de WHERE of er inderdaad niet aan voldaan was (die IS NULL check).

[StevenK]

Ik zou, om de performance wat soepeler te houden, bij elke user een 'last login' bijhouden.

Dan kun je gewoon zoeken op de users waarvan de last login langer dan een maand geleden is.

[Digihelp ®]

ACM schreef op dinsdag 28 februari 2006 @ 07:38:
[...]
Dat is vrij simpel op te nemen

Dat was inderdaad erg simpel...Het werkt nu prima, was geen rocketscience, maar ben toch blij dat ik de oplossing heb !