Difficulté pour une requete de comptage

Pascal G. a écrit :

Bonjour



Dans une table, j'enregistre les connexions d'utilisateur avec leur
login (ou celui qu'ils ont essayé), l'ip envoyée et la date. La
connexion réussie est enregistrée par un 1, sinon 0 par défaut.

Je voudrais faire une requete qui me sorte pour le même login le nombre
de dernières connexions ratées (donc le nombre d'enregistrement) depuis
leur dernière connexion réussie.

Exemple : table des connexion ratées, affichée dans l'ordre de date
décroissant (le plus résent en haut).
toto 0
titi 1
toto 0
nobel 1
nobel 0
toto 1
toto 0
toto 0

Je cherche à faire une requete qui me dirait, dans cet exemple, que toto
a 2 connexions ratées, titi aucune, nobel 1.

C'est sûrement faux (je suis fatigué et le bordeaux était bon) et
probablement pas standard (Q&D test sur MySQL qui était lancé, pas
cherché plus loin), donc je ne devrais pas poster ça, mais si ça peut te
mettre sur la piste (ne serait-ce qu'en faisant réagir quelqu'un...)


mysql> explain connections;
+--------+------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| login | varchar(5) | YES | | NULL | |
| status | int(11) | YES | | NULL | |
| date | date | YES | | NULL | |
+--------+------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> select * from connections;
+----+-------+--------+------------+
| id | login | status | date |
+----+-------+--------+------------+
| 1 | foo | 1 | 2009-01-01 |
| 2 | foo | 0 | 2009-01-02 |
| 3 | foo | 0 | 2009-01-03 |
| 4 | foo | 1 | 2009-02-01 |
| 5 | foo | 1 | 2009-02-02 |
| 6 | foo | 1 | 2009-02-03 |
| 7 | bar | 1 | 2009-01-01 |
| 8 | bar | 1 | 2009-01-02 |
| 9 | bar | 0 | 2009-01-03 |
| 10 | bar | 0 | 2009-01-04 |
| 11 | foo | 0 | 2009-03-01 |
| 12 | foo | 0 | 2009-03-02 |
| 13 | baaz | 1 | 2009-03-01 |
| 14 | baaz | 1 | 2009-03-02 |
| 15 | baaz | 1 | 2009-03-03 |
+----+-------+--------+------------+
15 rows in set (0.00 sec)

mysql> select c1.login, count(c1.status) as nb_failed
from connections c1
where c1.status=0
and c1.date > (
select c2.date
from connections c2
where c2.status=1
and c2.loginÁ.login
order by c2.date desc
limit 1
)
group by c1.login
union
select c1.login, 0 as nb_failed
from connections c1
where c1.status=1 and c1.date >= (
select c2.date
from connections c2
where c2.loginÁ.login
order by c2.date desc
limit 1
)
group by c1.login;

+-------+-----------+
| login | nb_failed |
+-------+-----------+
| bar | 2 |
| foo | 2 |
| baaz | 0 |
+-------+-----------+
3 rows in set (0.00 sec)


Critiques et corrections nécessaires et bienvenues...

Le Thu, 26 Mar 2009 21:46:00 +0100, Pascal G. a écrit:

Dans une table, j'enregistre les connexions d'utilisateur avec leur
login (ou celui qu'ils ont essayé), l'ip envoyée et la date. La
connexion réussie est enregistrée par un 1, sinon 0 par défaut.

Je voudrais faire une requete qui me sorte pour le même login le nombre
de dernières connexions ratées (donc le nombre d'enregistrement) depuis
leur dernière connexion réussie.

SELECT login,COUNT(*) FROM connections WHERE is_success=0 GROUP BY login

--
Patrick Mevzek . . . . . . . . . . . . . . Dot and Co
<http://www.dotandco.net/> <http://www.dotandco.com/>
<http://www.dotandco.net/ressources/icann_registrars/prices>
<http://icann-registrars-life.dotandco.net/>

Le Thu, 26 Mar 2009 21:50:30 +0000, Patrick Mevzek a écrit:

Le Thu, 26 Mar 2009 21:46:00 +0100, Pascal G. a écrit:

Dans une table, j'enregistre les connexions d'utilisateur avec leur
login (ou celui qu'ils ont essayé), l'ip envoyée et la date. La
connexion réussie est enregistrée par un 1, sinon 0 par défaut.

Je voudrais faire une requete qui me sorte pour le même login le nombre
de dernières connexions ratées (donc le nombre d'enregistrement) depuis
leur dernière connexion réussie.

SELECT login,COUNT(*) FROM connections WHERE is_success=0 GROUP BY login

Pardon c'est une bêtise, je n'avais pas lu "depuis leur denière connexion réussie".
Non testé, mais quelque chose comme ca devrait le faire :

SELECT login,COUNT(*) FROM connections AS c1
WHERE
is_success=0
AND
date_connection >= (SELECT date_connection FROM connections WHERE
loginÁ.login AND is_success=1 ORDER BY date_connection DESC LIMIT 1)
GROUP BY login

Ca doit pouvoir se tourner en jointure aussi.

--
Patrick Mevzek . . . . . . . . . . . . . . Dot and Co
<http://www.dotandco.net/> <http://www.dotandco.com/>
<http://www.dotandco.net/ressources/icann_registrars/prices>
<http://icann-registrars-life.dotandco.net/>

"Bruno Desthuilliers" <bdesth.quelquechose@free.quelquepart.fr> a écrit dans
le message de news:49cbf281$0$23749

Dans une table, j'enregistre les connexions d'utilisateur avec leur login
(ou celui qu'ils ont essayé), l'ip envoyée et la date. La connexion
réussie est enregistrée par un 1, sinon 0 par défaut.

Je voudrais faire une requete qui me sorte pour le même login le nombre
de dernières connexions ratées (donc le nombre d'enregistrement) depuis
leur dernière connexion réussie.

Exemple : table des connexion ratées, affichée dans l'ordre de date
décroissant (le plus résent en haut).
toto 0
titi 1
toto 0
nobel 1
nobel 0
toto 1
toto 0
toto 0

Je cherche à faire une requete qui me dirait, dans cet exemple, que toto
a 2 connexions ratées, titi aucune, nobel 1.

C'est sûrement faux (je suis fatigué et le bordeaux était bon) et
probablement pas standard (Q&D test sur MySQL qui était lancé, pas cherché
plus loin), donc je ne devrais pas poster ça, mais si ça peut te mettre
sur la piste (ne serait-ce qu'en faisant réagir quelqu'un...)


mysql> explain connections;
+--------+------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| login | varchar(5) | YES | | NULL | |
| status | int(11) | YES | | NULL | |
| date | date | YES | | NULL | |
+--------+------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> select * from connections;
+----+-------+--------+------------+
| id | login | status | date |
+----+-------+--------+------------+
| 1 | foo | 1 | 2009-01-01 |
| 2 | foo | 0 | 2009-01-02 |
| 3 | foo | 0 | 2009-01-03 |
| 4 | foo | 1 | 2009-02-01 |
| 5 | foo | 1 | 2009-02-02 |
| 6 | foo | 1 | 2009-02-03 |
| 7 | bar | 1 | 2009-01-01 |
| 8 | bar | 1 | 2009-01-02 |
| 9 | bar | 0 | 2009-01-03 |
| 10 | bar | 0 | 2009-01-04 |
| 11 | foo | 0 | 2009-03-01 |
| 12 | foo | 0 | 2009-03-02 |
| 13 | baaz | 1 | 2009-03-01 |
| 14 | baaz | 1 | 2009-03-02 |
| 15 | baaz | 1 | 2009-03-03 |
+----+-------+--------+------------+
15 rows in set (0.00 sec)

mysql> select c1.login, count(c1.status) as nb_failed
from connections c1
where c1.status=0
and c1.date > (
select c2.date
from connections c2
where c2.status=1
and c2.loginÁ.login
order by c2.date desc
limit 1
)
group by c1.login
union
select c1.login, 0 as nb_failed
from connections c1
where c1.status=1 and c1.date >= (
select c2.date
from connections c2
where c2.loginÁ.login
order by c2.date desc
limit 1
)
group by c1.login;

+-------+-----------+
| login | nb_failed |
+-------+-----------+
| bar | 2 |
| foo | 2 |
| baaz | 0 |
+-------+-----------+
3 rows in set (0.00 sec)


Critiques et corrections nécessaires et bienvenues...

Bonjour


Je viens d'essayer sur ta table :


CREATE TABLE `connections` (
`id` smallint(6) NOT NULL auto_increment,
`login` varchar(50) collate utf8_bin NOT NULL,
`status` tinyint(1) NOT NULL,
`date` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT ;

--
-- Dumping data for table `connections`
--

INSERT INTO `connections` (`id`, `login`, `status`, `date`) VALUES
(1, 'foo', 1, '2009-01-01 00:00:00'),
(2, 'foo', 0, '2009-01-02 00:00:00'),
(3, 'foo', 0, '2009-01-03 00:00:00'),
(4, 'foo', 1, '2009-02-01 00:00:00'),
(5, 'foo', 1, '2009-02-02 00:00:00'),
(6, 'foo', 1, '2009-02-03 00:00:00'),
(7, 'bar', 1, '2009-01-01 00:00:00'),
(8, 'bar', 1, '2009-01-02 00:00:00'),
(9, 'bar', 0, '2009-01-03 00:00:00'),
(10, 'bar', 0, '2009-01-04 00:00:00'),
(11, 'foo', 0, '2009-03-01 00:00:00'),
(12, 'foo', 0, '2009-03-02 00:00:00'),
(13, 'baaz', 1, '2009-03-01 00:00:00'),
(14, 'baaz', 1, '2009-03-02 00:00:00'),
(15, 'baaz', 1, '2009-03-03 00:00:00');

SELECT `login`,`status` FROM `connections` ORDER BY date DESC
baaz 1
baaz 1
foo 0
baaz 1
foo 0
foo 1
foo 1
foo 1
bar 0
foo 0
bar 0
bar 1
foo 0
bar 1
foo 1

Ben... oui, il me donne les bons résultats. Je n'ai plus qu'à l'adapter à ma
table et à effectuer une recherche selon le nom d'user choisi.

Le bordeaux te réussit mieux qu'à moi !
;-)

Bien joué, merci,


@+
Pascal

Dans une table, j'enregistre les connexions d'utilisateur avec leur
login (ou celui qu'ils ont essayé), l'ip envoyée et la date. La
connexion réussie est enregistrée par un 1, sinon 0 par défaut.

Je voudrais faire une requete qui me sorte pour le même login le nombre
de dernières connexions ratées (donc le nombre d'enregistrement) depuis
leur dernière connexion réussie.

Exemple : table des connexion ratées, affichée dans l'ordre de date
décroissant (le plus résent en haut).
toto 0
titi 1
toto 0
nobel 1
nobel 0
toto 1
toto 0
toto 0

Je cherche à faire une requete qui me dirait, dans cet exemple, que toto
a 2 connexions ratées, titi aucune, nobel 1.

C'est sûrement faux (je suis fatigué et le bordeaux était bon) et
probablement pas standard (Q&D test sur MySQL qui était lancé, pas
cherché plus loin), donc je ne devrais pas poster ça, mais si ça peut te
mettre sur la piste (ne serait-ce qu'en faisant réagir quelqu'un...)


mysql> explain connections;
+--------+------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| login | varchar(5) | YES | | NULL | |
| status | int(11) | YES | | NULL | |
| date | date | YES | | NULL | |
+--------+------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> select * from connections;
+----+-------+--------+------------+
| id | login | status | date |
+----+-------+--------+------------+
| 1 | foo | 1 | 2009-01-01 |
| 2 | foo | 0 | 2009-01-02 |
| 3 | foo | 0 | 2009-01-03 |
| 4 | foo | 1 | 2009-02-01 |
| 5 | foo | 1 | 2009-02-02 |
| 6 | foo | 1 | 2009-02-03 |
| 7 | bar | 1 | 2009-01-01 |
| 8 | bar | 1 | 2009-01-02 |
| 9 | bar | 0 | 2009-01-03 |
| 10 | bar | 0 | 2009-01-04 |
| 11 | foo | 0 | 2009-03-01 |
| 12 | foo | 0 | 2009-03-02 |
| 13 | baaz | 1 | 2009-03-01 |
| 14 | baaz | 1 | 2009-03-02 |
| 15 | baaz | 1 | 2009-03-03 |
+----+-------+--------+------------+
15 rows in set (0.00 sec)

mysql> select c1.login, count(c1.status) as nb_failed
from connections c1
where c1.status=0
and c1.date > (
select c2.date
from connections c2
where c2.status=1
and c2.loginÁ.login
order by c2.date desc
limit 1
)
group by c1.login
union
select c1.login, 0 as nb_failed
from connections c1
where c1.status=1 and c1.date >= (
select c2.date
from connections c2
where c2.loginÁ.login
order by c2.date desc
limit 1
)
group by c1.login;

+-------+-----------+
| login | nb_failed |
+-------+-----------+
| bar | 2 |
| foo | 2 |
| baaz | 0 |
+-------+-----------+
3 rows in set (0.00 sec)


Critiques et corrections nécessaires et bienvenues...

Bonjour


Je viens d'essayer sur ta table :


CREATE TABLE `connections` (
`id` smallint(6) NOT NULL auto_increment,
`login` varchar(50) collate utf8_bin NOT NULL,
`status` tinyint(1) NOT NULL,
`date` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT ;

--
-- Dumping data for table `connections`
--

INSERT INTO `connections` (`id`, `login`, `status`, `date`) VALUES
(1, 'foo', 1, '2009-01-01 00:00:00'),
(2, 'foo', 0, '2009-01-02 00:00:00'),
(3, 'foo', 0, '2009-01-03 00:00:00'),
(4, 'foo', 1, '2009-02-01 00:00:00'),
(5, 'foo', 1, '2009-02-02 00:00:00'),
(6, 'foo', 1, '2009-02-03 00:00:00'),
(7, 'bar', 1, '2009-01-01 00:00:00'),
(8, 'bar', 1, '2009-01-02 00:00:00'),
(9, 'bar', 0, '2009-01-03 00:00:00'),
(10, 'bar', 0, '2009-01-04 00:00:00'),
(11, 'foo', 0, '2009-03-01 00:00:00'),
(12, 'foo', 0, '2009-03-02 00:00:00'),
(13, 'baaz', 1, '2009-03-01 00:00:00'),
(14, 'baaz', 1, '2009-03-02 00:00:00'),
(15, 'baaz', 1, '2009-03-03 00:00:00');

SELECT `login`,`status` FROM `connections` ORDER BY date DESC
baaz 1
baaz 1
foo 0
baaz 1
foo 0
foo 1
foo 1
foo 1
bar 0
foo 0
bar 0
bar 1
foo 0
bar 1
foo 1

Ben... oui, il me donne les bons résultats. Je n'ai plus qu'à l'adapter à
ma table et à effectuer une recherche selon le nom d'user choisi.

Bonjour

Après plusieurs tests, je m'aperçois que la requete me donne une mauvaise
réponse lorsque l'utilisateur n'est jamais arrivé à se connecter.

INSERT INTO `connections` (`id`, `login`, `status`, `date`) VALUES (NULL,
'moo', '0', '2009-03-29 00:00:00');
INSERT INTO `connections` (`id`, `login`, `status`, `date`) VALUES (NULL,
'moo', '0', '2009-03-29 00:00:00');

Oui, cela est logique puisque la requete cherche la dernière bonne
connexion. Je me rend compte que cela est finalement exactement ce que
j'avais inscris dans mon post initial, mais, pas tout à fait ce que je
souhaite faire : le but de cette requete est de bloquer un compte (et
d'envoyer un mail aux admins) selon le nombre de connexions ratées. Donc,
pour cela il faut commencer à compter à partir de la dernière bonne
connexion, mais aussi même si il n'y a jamais eu de bonne connexion.

Je me dis que pour que ma fonction fonctionne (c'est le cas de le dire), je
n'ai qu'à, lors de l'enregistrement, lui marquer comme connexion réussie,
mais je sors aussi les connexions ratées et réussies dans une autre page
pour suivre l'activité quotidienne...

@+
Pascal

Le Sun, 29 Mar 2009 08:48:23 +0200, Pascal G. a écrit:

Dans une table, j'enregistre les connexions d'utilisateur avec leur
login (ou celui qu'ils ont essayé), l'ip envoyée et la date. La
connexion réussie est enregistrée par un 1, sinon 0 par défaut.

Je voudrais faire une requete qui me sorte pour le même login le
nombre de dernières connexions ratées (donc le nombre
d'enregistrement) depuis leur dernière connexion réussie.

SELECT login,COUNT(*) FROM connections AS c1 WHERE
is_success=0
AND
date_connection >= (SELECT date_connection FROM connections WHERE
loginÁ.login AND is_success=1 ORDER BY date_connection DESC LIMIT 1)
GROUP BY login

Bonjour.

En coréllation avec la réponse de bruno, la requete me donne une
mauvaise réponse lorsque l'utilisateur n'est jamais arrivé à se
connecter, voilà pourquoi elle m'indiquait quelquefois des résultats
faux avec ma table réelle.

En pas très subtil, alors en ajoutant :
UNION SELECT login,COUNT(*) FROM connections AS c1
WHERE is_success=0
AND
NOT EXISTS (SELECT date_connection FROM connections
WHERE loginÁ.login AND is_success=1)
GROUP BY login

Ou en reformulant la deuxième partie du AND du début
en le transformant en AND (X OR Y)
X étant le AND initial et Y celui qui précède juste

Ou plus subtil, mais à tester :

SELECT c1.login,COUNT(*)
FROM connections AS c1
LEFT OUTER JOIN
(
SELECT login,MAX(date) FROM connections
WHERE status=1
GROUP BY login
) AS c2
ON (c1.loginÂ.login AND c1.date >= c2.date)
WHERE
c1.status=0
GROUP BY c1.login


--
Patrick Mevzek . . . . . . . . . . . . . . Dot and Co
<http://www.dotandco.net/> <http://www.dotandco.com/>
<http://www.dotandco.net/ressources/icann_registrars/prices>
<http://icann-registrars-life.dotandco.net/>

Le Sun, 29 Mar 2009 22:22:35 +0200, Pascal G. a écrit:

Sinon, je pensais m'orienter avec php sur deux requetes : sur la
première je regarde si il a déjà eu une bonne connexion, selon le cas
j'oriente la requete suivante soit avec un simple select count(*), soit
avec la requete de ta première réponse.

(Réponse générique)

Mon point de vue et que je donne aussi en cours est qu'il faut
"toujours" (sauf exceptions motivées donc) donner le maximum de travail
au SGBDR plutôt que d'émuler la même chose au niveau applicatif parce
qu'il est optimisé pour cela et qu'on transfère moins de données ainsi.

Sans compter qu'une boucle au niveau applicatif correspond en gros à une
jointure au niveau relationnel, et que donc il y a souvent moyen de tou
faire en un SELECT plutôt que plusieurs. Cela force ainsi à utiliser
chacun des outils pour leurs avantages propres plutôt que de se réfugier
dans ce qu'on connaît. J'ai très fréquemment vu des codes (PHP souvent)
complétement alambiqués pour faire en des dizaines de lignes ce qui
tenait en une ligne de SQL correctement phrasé venant de personnes qui ne
connaissaient pas suffisamment le modèle relationnel pour l'exploiter à
100%.

Bien sûr, cela n'aide pas quand on doit faire une application multi-SGBDR
et que tous ces derniers n'ont pas le même niveau de fonctionnalités.
C'est de moins en moins vrai mais il y a quelques années à part un SELECT
de base on ne pouvait pas faire grand chose de subtil dans MySQL
(jointures, requêtes imbriquées, etc.)

On peut aussi tirer profit des procédures stockées dans les SGBDR qui
sont un peu à mi-chemin et qui permettent d'assurer des traitements
homogènes quelque soit le canal d'accès (quand elles sont couplées avec
des déclencheurs ou des règles de re-écriture)

Il y a bien sûr des contre-exemples : une fois j'avais le SGBDR installé
sur une machine bien moins puissante que celle où était l'applicatif et
c'était une vieille version de MySQL et j'allais au final plus vite (au
sens temps de calcul) à simuler une jointure du côté applicatif (en
faisant 2 select globaux sur les 2 tables qui m'intéressaient) qu'à la
faire faire par le SGBDR (le volume des données n'était pas négligeable
mais la jointure était en pratique proche d'une exportation globale donc
en fait en terme de volumétrie les deux solutions étaient équivalentes
pour le réseau).

Mais c'était donc un compromis fait explicitement en toute connaissance.


--
Patrick Mevzek . . . . . . . . . . . . . . Dot and Co
<http://www.dotandco.net/> <http://www.dotandco.com/>
<http://www.dotandco.net/ressources/icann_registrars/prices>
<http://icann-registrars-life.dotandco.net/>