[(my)SQL] Groupage des données / GROUP BY & COUNT [résolu]

Oyo

J'ai la table suivante :

[cpp]
CREATE TABLE httpd_logs (
Ip int(11) NOT NULL default '0',
datDay date NOT NULL default '0000-00-00',
datTime time NOT NULL default '00:00:00',
intStatus smallint(3) unsigned NOT NULL default '0',
intByte int(10) unsigned default NULL,
IdRequest int(10) unsigned NOT NULL default '0',
IdReferer int(10) unsigned default NULL,
IdUserAgent int(10) unsigned default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;[/cpp]

Et hormis le fait que ce soit du mySQL 4.1 (d'où les COLLATE) et que la table tape dans les 1.5*10^6 enregistrements, j'ai un petit problème :

[cpp]SELECT datDay, HOUR(datTime), COUNT(*)
FROM `httpd_logs`
GROUP BY datDay, HOUR(datTime)[/cpp]

Cette requête - aux optimisations près - me retourne le nombre d'Ip non distinctes enregistrée par Date (jour, mois, année) et pas Heure (ben l'heure c'est l'heure).

Ce qui donne par exemple :
[cpp]+------------+---------------+----------+
| datDay | HOUR(datTime) | COUNT(*) |
+------------+---------------+----------+
| 2004-12-22 | 4 | 315 |
| 2004-12-22 | 5 | 885 |
| 2004-12-22 | 6 | 733 |
| 2004-12-22 | 7 | 1018 |
| 2004-12-22 | 8 | 1662 |
| 2004-12-22 | 9 | 3421 |
| 2004-12-22 | 10 | 5129 |
| 2004-12-22 | 11 | 6448 |
| 2004-12-22 | 12 | 6243 |
| 2004-12-22 | 13 | 5570 |
+------------+---------------+----------+
10 rows in set (1.27 sec)[/cpp]

Ce n'est pas que cela me convient pas, mais cela me permet juste d'obtenir le nombre de hit (c'est à dire le nombre de connexion non distinctes) par heure.

Ce que je voudrais, c'est obtenir le nombre d'IP de manière distincte : cela représenterait le nombre de visiteur unique. En sus, je voudrais aussi prendre en compte l'user agent.

Par exemple si j'ai 212.10.12.1 avec Mozilla comme UA, et 212.10.12.1 avec IE comme UA, ça me fait 2 connexions.

[cpp]SELECT datDay AS `date`, HOUR(datTime) AS `hour`, COUNT(*) AS `hit`, COUNT(DISTINCT CONCAT(Ip, ':', IdUserAgent)) AS `visit`
FROM `httpd_logs`
GROUP BY datDay, HOUR(datTime)
ORDER BY visit DESC[/cpp]

Me renvoie :
[cpp]+------------+------+------+-------+
| date | hour | hit | visit |
+------------+------+------+-------+
| 2004-12-26 | 20 | 6768 | 310 |
| 2004-12-26 | 23 | 4482 | 290 |
| 2004-12-26 | 18 | 8637 | 283 |
| 2004-12-26 | 19 | 6671 | 281 |
| 2004-12-22 | 17 | 5493 | 276 |
+------------+------+------+-------+
5 rows in set (3.86 sec)[/cpp]

Ce qui semble juste à priori, mais si je fais :
[cpp]SELECT datDay AS `date`, HOUR(datTime) AS `hour`, Ip, IdUserAgent, COUNT(*) AS result
FROM httpd_logs
WHERE datDay = '2004-12-26' AND HOUR(datTime) = 20
GROUP BY Ip, IdUserAgent
ORDER BY result DESC;[/cpp]

J'obtiens :
[cpp]334 rows in set (0.34 sec)[/cpp]

Bien entendu, je peux séparer mes requêtes (pour obtenir d'un côté visites uniques et de l'autre hit) mais j'aimerai tout autant éviter si cela est possible.

Y a quelqu'un qui saurait m'aiguiller ?

J'ai fait simple et bourrin :
[cpp]UPDATE httpd_logs
SET strHash = MD5(CONCAT(Ip, ':', COALESCE(IdUserAgent,0))[/cpp]

Et puis :
[cpp]SELECT HOUR(datTime) AS h, COUNT(*), COUNT(DISTINCT strHash)
FROM httpd_logs
WHERE datDay = ?
GROUP BY h[/cpp]

(? = une journée quelconque)