Statistiche raggruppate per IP

[unlocketor]

Salve, ho un problema che ancora non sono riuscito a risolvere.

Magari la soluzione è semplice ma al momento non ho idee in proposito.

Ho un database che ha una struttura di questo tipo:

id, ip, data, browser, paginavisitata

dove vengono memorizzati i log del mio sito.

Ad esempio ho dei dati del tipo:

1, 194.100.100.100 , dataora n° 1, browser x, pagina n° 1

2, 194.100.100.100 , dataora n° 2, browser x, pagina n° 2

3, 255.100.100.100 , dataora n° 3, browser y, pagina n° 15

4, 194.100.100.100 , dataora n° 4, browser x, pagina n° 46

5, 194.100.100.100 , dataora n° 5, browser x, pagina n° 29

6, 255.100.100.100 , dataora n° 6, browser y, pagina n° 67

quindi analizzandoli estraggo 2 visite, 6 pagine visitate.

Il mio problema è che voglio visualizzare i dati raggruppando le pagine visitate per ip.

Mi spiego meglio.

Vorrei visualizzare qualcosa del tipo:

1, 194.100.100.100 , dataora n° 1, browser x, pagina n° 1

2, 194.100.100.100 , dataora n° 2, browser x, pagina n° 2

4, 194.100.100.100 , dataora n° 4, browser x, pagina n° 46

5, 194.100.100.100 , dataora n° 5, browser x, pagina n° 29

3, 255.100.100.100 , dataora n° 3, browser y, pagina n° 15

6, 255.100.100.100 , dataora n° 6, browser y, pagina n° 67

in modo da vedere le azioni compiute da uno stesso utente in un certo lasso di tempo.

Avevo pensato a qualcosa del tipo:

SELECT * FROM tabella WHERE dataora <= CURDATE() AND dataora >= CURDATE() - INTERVAL 3 DAY GROUP BY ip ORDER BY id DESC

ma visualizza naturalmente 1 solo ip per volta.

Avete idee per ottenere quel risultato?

Grazie per le risposte.

Ma fare semplicemente un ORDER BY ip?

[unlocketor]

Ma fare semplicemente un ORDER BY ip?

Avevo provato ma non ottengo il risultato che vorrei anche ordinando correttamente gli ip tramite la funzione INET_ATON (ip) perchè giustamente ottengo in modo disordinato in ordine di tempo.

Credo di aver risolto il problema aggiungendo alle opzioni di ordinamento anche la dataora e id.

E che sarebbe la funzione INET_ATON???


ciao!

[unlocketor]

E che sarebbe la funzione INET_ATON???

Se faccio un ordinamento per ip purtroppo SQL ha un difetto perchè nei campi disponibili non esiste il tipo "IP" quindi in genere questo dato può essere inserito come "testuale".

Quando viene fatto l'ordinamento viene in pratica fatta un'operazione automatica di casting però se ad esempio abbiamo gli IP:

65.255.255.39
65.255.255.40
65.255.255.41
65.255.255.4

se ordini viene fuori:

65.255.255.39
65.255.255.40
65.255.255.4
65.255.255.41

quella funzione trasforma in decimale l'ip in modo che l'ordinamento diventa corretto, ovvero:

65.255.255.4
65.255.255.39
65.255.255.40
65.255.255.41

[unlocketor]

Come non detto, non sono riuscito a risolvere del tutto il problema.

Dovrei ottenere con una singola query il risultato che si ottiene con doppia query:

SELECT DISTINCT (ip) AS ip_unico FROM tabella WHERE dataora <= CURDATE() AND dataora >= CURDATE() - INTERVAL 3 DAY GROUP BY ip ORDER BY id DESC

al cui interno è contenuta quest'altra:

SELECT * FROM tabella WHERE dataora <= CURDATE() AND dataora >= CURDATE() - INTERVAL 3 DAY AND ip=ip_unico ORDER BY id ASC

Questo significa che fa una query per ogni visita per cui con le oltre 150 visite giornaliere che ho sul mio sito significherebbe un dispendio di risorse enorme che andrebbe sempre peggio all'aumentare delle visite.

Quello che voglio fare è ottimizzare questa query facendone una singola in modo da estrarre i dati usando lo stesso stile che usa histats.com

Quindi in modo da vedere in ordine:

Refer: xxxxxx
IP: xxx.xxx.xxx.xxx
Browser: xxxxxxxxxx
Pagina visitata n° 1
Pagina visitata n° 2
Pagina visitata n° 3

Refer: yyyyyyy
IP: yyy.yyy.yyy.yyy
Browser: yyyyyyyyy
Pagina visitata n° 1
Pagina visitata n° 2
Pagina visitata n° 3
Pagina visitata n° 4
Pagina visitata n° 5


Avete in mente un modo per ottimizzare il tutto?