ciao a tutti,
ho un problema nel realizzare una select in mysql,
ho questo db che contiene dei dati relativi al traffico generato da dei ip, ordinati per subnet, per un lasso di tempo
tabella composta da
in_byte
out_byte
ip
id_rete
mac
data
qui sotto riporto degli esempi di dati per farvi capire ciò che voglio ottenere
107, 0, 83929280, 43200, '00:30:6e:c3:45:62', '2007-10-26 16:38:45'
304, 0, 83929280, 43200, '00:30:6e:c3:45:62', '2007-10-26 16:39:07'
608, 0, 83929280, 43200, '00:30:6e:c3:45:62', '2007-10-26 16:44:19'
298, 0, 83929280, 43200, '00:30:6e:c3:45:62', '2007-10-26 16:50:37'
298, 0, 83929280, 43200, '00:30:6e:c3:45:62', '2007-10-26 17:01:36'
304, 0, 83929280, 43200, '00:30:6e:c3:45:62', '2007-10-26 18:54:45'
298, 0, 83929280, 43200, '00:30:6e:c3:45:62', '2007-10-26 19:00:37'
596, 0, 83929280, 43200, '00:1c:b0:78:fc:0c', '2007-10-26 19:09:43'
107, 0, 83929280, 43200, '00:30:6e:c3:45:62', '2007-10-29 14:19:08'
912, 0, 83929280, 43200, '00:30:6e:c3:45:62', '2007-10-29 14:24:42'
298, 0, 83929280, 43200, '00:30:6e:c3:45:62', '2007-10-29 14:30:17'
298, 0, 83929280, 43200, '00:30:6e:c3:45:62', '2007-10-29 14:39:35'
63364, 0, 83929280, 43200, '00:1c:b0:78:fc:0c', '2007-10-29 14:39:36'
63364, 0, 83929280, 43200, '00:1c:b0:78:fc:0c', '2007-10-29 14:39:37'
63364, 0, 83929280, 43200, '00:1c:b0:78:fc:0c', '2007-10-29 14:39:38'
107, 0, 83929280, 43200, '00:30:6e:c3:45:62', '2007-11-21 08:14:26'
304, 0, 83929280, 43200, '00:30:6e:c3:45:62', '2007-11-21 08:14:58'
608, 0, 83929280, 43200, '00:30:6e:c3:45:62', '2007-11-21 08:20:01'
298, 0, 83929280, 43200, '00:30:6e:c3:45:62', '2007-11-21 08:25:57'
298, 0, 83929280, 43200, '00:30:6e:c3:45:62', '2007-11-21 08:31:32'
107, 0, 83929280, 43200, '00:30:6e:c3:45:62', '2007-12-11 20:03:27'
608, 0, 83929280, 43200, '00:30:6e:c3:45:62', '2007-12-11 20:07:44'
298, 0, 83929280, 43200, '00:30:6e:c3:45:62', '2007-12-11 20:12:00'
298, 0, 83929280, 43200, '00:30:6e:c3:45:62', '2007-12-11 20:20:34'
304, 0, 83929280, 43200, '00:30:6e:c3:45:62', '2008-01-28 10:03:21'
608, 0, 83929280, 43200, '00:30:6e:c3:45:62', '2008-01-28 10:08:21'
298, 0, 83929280, 43200, '00:30:6e:c3:45:62', '2008-01-28 10:13:21'
298, 0, 83929280, 43200, '00:30:6e:c3:45:62', '2008-01-28 10:23:21'
63364, 0, 83929280, 43200, '00:1c:b0:78:fa:68', '2008-02-05 17:37:28'
come potete vedere dai dati, l'ip 83929280(che naturalmente è in long) è stato associato al mac 00:30:6e:c3:45:62 in data 2007-10-26 16:38:45 aa 2007-10-26 19:00:37... successivamente, in data 2007-10-26 19:09:43, l'ip è passato ad un altro mac 00:1c:b0:78:fc:0c...in data 2007-10-29 14:19:08 è tornato al mac 00:30:6e:c3:45:62...
quello che voglio ottenere, è una select che mi va raggruppando i mac per data di associazione dell'ip, quindi i risultati sopra diventerebbero
107, 0, 83929280, 43200, '00:30:6e:c3:45:62', '2007-10-26 16:38:45'
596, 0, 83929280, 43200, '00:1c:b0:78:fc:0c', '2007-10-26 19:09:43'
107, 0, 83929280, 43200, '00:30:6e:c3:45:62', '2007-10-29 14:19:08'
63364, 0, 83929280, 43200, '00:1c:b0:78:fc:0c', '2007-10-29 14:39:36'
107, 0, 83929280, 43200, '00:30:6e:c3:45:62', '2007-11-21 08:14:26'
63364, 0, 83929280, 43200, '00:1c:b0:78:fa:68', '2008-02-05 17:37:28'
come posso fare con una sola select??
se faccio una select di questo tipo
SELECT in_byte, out_byte, ip, id_rete, mac, data FROM movimenti group by id_rete, ip, mac, data order by data;
mi seleziona tutto raggruppato per data e a me interessa il raggruppamento per mac
e se faccio
SELECT in_byte, out_byte, ip, id_rete, mac, data FROM movimenti group by id_rete, ip, mac order by data;
mi raggruppa i mac ma non mi da le varie associazioni per data, ho una cosa del genere
107, 0, 83929280, 43200, '00:30:6e:c3:45:62', '2007-10-26 16:38:45'
596, 0, 83929280, 43200, '00:1c:b0:78:fc:0c', '2007-10-26 19:09:43'
63364, 0, 83929280, 43200, '00:1c:b0:78:fa:68', '2008-02-05 17:37:28'
Rispondi quotando
