altre destinazioni

vedi anche

ultimi post

ultimi commenti

tag principali

categorie

archivi

powered by

  • WPFrontman + WP

friends

copyright

  • © 2004-2011
    Ludovico Magnocavallo
    tutti i diritti riservati

Yes, I am a data geek / 1

11 settembre 2009

1 commento

tag

categorie

Riprendo a parlare di cose serie, dopo i deragliamenti di questi giorni, con il primo di una serie aperiodica di post sulla manipolazione dei dati. E’ un argomento che mi ha sempre affascinato, e in questa serie descriverò per ogni post un piccolo problema che mi è capitato di dover risolvere lavorando, e una delle sue possibili soluzioni. Edit: questo post è anche su Stacktrace, pensavo fosse troppo semplice per Stacktrace ma Antonio la pensa diversamente.

In questo primo post vi racconto un problemino che mi è capitato sottomano giusto ieri in ufficio: come calcolare degli aggregati periodici (ad esempio ogni 5 minuti) da un elenco di rilevazioni del traffico di un servizio. I dati che ho ricevuto erano in formato Excel, ma per semplificarci la vita senza inciampare in tabelle pivot e soprattutto dato che siamo geek, per trattarli utilizzeremo un database relazionale (nel mio caso MySQL).

Iniziamo a dare un’occhiata ai dati, che sono semplici e consistono in righe ognuna composta da due colonne: l’ora di rilevazione, e il numero di kbyte inviati. Ogni riga rappresenta una singola transazione. Disegniamo una semplice tabella MySQL, con una chiave primaria generica (potremmo anche farne a meno) e un indice sul campo con l’ora di rilevazione:

CREATE TABLE `test` (
  `id` int(11) NOT NULL auto_increment,
  `tstamp` time NOT NULL,
  `size` int(11) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `idx_tstamp` (`tstamp`)
) ENGINE=InnoDB;

Carichiamo i dati (io ho usato la console di Python, ma ci sono altri mille modi per farlo tra cui LOAD DATA LOCAL INFILE), e vediamo come si presentano un paio di righe:

mysql> select * from test limit 2;
+----+----------+------+
| id | tstamp   | size |
+----+----------+------+
|  1 | 07:02:06 |    2 | 
|  2 | 07:02:09 |    2 | 
+----+----------+------+

Il primo problema è: come facciamo a raggruppare tutte le righe in intervalli di 5 minuti? Se proviamo a disegnare un paio di serie di minuti, la risposta è abbastanza evidente (almeno per chi come me + abituato a ragionare in modo visuale):

minuti                indice per l'aggregazione
 0  1  2  3  4        0
 5  6  7  8  9        1
10 11 12 13 14        2

Capito come fare? Semplice: basta dividere il minuto per l’intervallo che vogliamo considerare (in questo caso 5), e scartare i decimali utilizzando la funzione floor. Vediamo come si fa in MySQL:

mysql> select tstamp,
       floor(minute(tstamp)/5) as indice
       from test limit 2;
+----------+--------+
| tstamp   | indice |
+----------+--------+
| 07:02:06 |      0 | 
| 07:02:09 |      0 | 
+----------+--------+

Ovviamente per aggregare le nostre righe, oltre all’indice dei minuti avremo bisogno anche l’ora:

mysql> select
      hour(tstamp) as ora,
      floor(minute(tstamp) / 5) as intervallo,
      sum(size) as traffico from test
      group by ora, intervallo
      order by tstamp limit 3;
+------+------------+----------+
| ora  | intervallo | traffico |
+------+------------+----------+
|    7 |          0 |        6 | 
|    7 |          1 |     1604 | 
|    7 |          2 |       42 | 
+------+------------+----------+

Funziona? Direi di si, peccato solo che i risultati aggregati siano poco eleganti: due campi diversi per ora e intervallo, nessuno dei quali formattati, non aiutano certo la leggibilità. Possiamo fare di meglio: un campo unico per ora e intervallo, formattato e con il giusto padding (lo zero per ore e minuti a singola cifra), un intervallo descrittivo che non sia l’indice opaco che funziona per MySQL ma dice molto poco a chi dovrà utilizzare i dati, e aggiungiamo anche un campo ulteriore con il numero di pacchetti del periodo oltre alla somma del traffico:

mysql>
      select concat(
          lpad(hour(tstamp), 2, '0'),
          ':',
          lpad(floor(minute(tstamp) / 5) * 5, 2, '0'),
          '-',
          lpad(floor(minute(tstamp) / 5) * 5 + 5, 2, '0')
       ) as periodo,
       count(*) as trasmissioni,
       sum(size) as traffico
       from test
       group by hour(tstamp), floor(minute(tstamp) / 5)
       order by tstamp limit 3;
+----------+--------------+----------+
| periodo  | trasmissioni | traffico |
+----------+--------------+----------+
| 07:00-05 |            3 |        6 | 
| 07:05-10 |            1 |     1604 | 
| 07:10-15 |            3 |       42 | 
+----------+--------------+----------+

Chiaro? No? Ok, guardiamo in dettaglio come è costruita la query.

Il primo campo che estraiamo è il periodo su cui vengono aggregati i dati, composto con valori diversi uniti dalla funzione concat, che altro non fa che unire insieme in un unico campo i valori che gli vengono passati: l’ora, il separatore ‘:’, il primo minuto dell’intervallo, il separatore ‘-’, l’ultimo minuto dell’intervallo. Su ora e minuti facciamo poi il padding premettendo uno zero se il numero è in singola cifra, utilizzando la funzione lpad. Il secondo e il terzo campo sono le semplici funzioni aggregate di conteggio del numero di righe dell’intervallo, che ci restituisce il numero di trasmissioni, e la somma del traffico.

Dato poi che in questa query non abbiamo estratto l’indice di aggregazione come campo visibile, dobbiamo dichiararlo esplicitamente come clausola del group by invece di richiamarlo per nome come abbiamo fatto nella query precedente.

A questo punto, se dobbiamo passare i dati a qualche collega che deve importarli in Excel per generare un grafico, ci basta passare la query al client mysql come valore dell’argomento ‘-e’, aggiungere ‘–batch’ in modo da ottenere i risultati come campi delimitati da tabulatore, e redirigere l’output su un file.

1 commento