Yes, I am a data geek /1

Riprendo a parlare di programmazione, dopo i deragliamenti di questi giorni, con il primo di una serie aperiodica di post sulla manipolazione dei dati. È 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.

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 sì, 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.

Comments

  1. > floor(minute(tstamp)/5)

    MySql non ha la funzione ‘modulo’? Sarebbe stata molto più comoda …

  2. MySQL supporta l’operatore per operazioni di modulo aritmetico, l’operatore é MOD.
    Ma comunque l’operazione di modulo restistuisce il resto della divisione e non il quoziente

  3. > l’operazione di modulo restistuisce il
    > resto della divisione e non il quoziente

    Opsss, hai ragione, lapsus, mi riferivo alla possibilità di usare l’operatore di divisione di interi che, vedo sul manuale, si chiama DIV. Esiste qualche controindicazione che mi sfugge?

  4. Hai usato la console di python o quella di mysql? (testo dopo il primo blockquote).

    Probabilmente avrei utilizzato R e RMySQL, così da poter creare qualche grafico al volo, comunque la tua soluzione è molto interessante e non conoscevo select concat.

Policy per i commenti: Apprezzo moltissimo i vostri commenti, critiche incluse. Per evitare spam e troll, e far rimanere il discorso civile, i commenti sono moderati e prontamente approvati poco dopo il loro invio.


Speak Your Mind

*