Qix.it

Un piccolo intermezzo in cui vi racconto come far inserire a MySQL un grafico ASCII nei risultati di una query.

Spesso un grafico è la soluzione migliore per cogliere a colpo d’occhio la distribuzione e l’andamento di una serie di dati. Il problema è che, per la maggior parte delle persone, fare un grafico vuol dire estrarre i dati, importarli in Excel (o equivalente), aggiustare il formato delle celle, e smanettare qualche minuto con il mouse per trovare la rappresentazione giusta.

Io invece sono un pigro, e se posso fare tutto da linea di comando con qualche pressione di tasto in più, e qualche movimento del mouse in meno, sono contento. E poi i grafici ASCII fanno tanto vintage.

Come esempio prendiamo una tabella le cui righe hanno un campo data, e proponiamoci di esaminare i giorni in cui si raccolgono il maggior numero di dati, e la loro distribuzione. La tabella dei post di un blog è un esempio calzante, io userò la tabella che raccoglie i post dal database di test di BlogBabel. Il campo che ci interessa è uno solo, date_published, un campo di tipo datetime che raccoglie appunto la data di pubblicazione. Come prima cosa, vediamo quale è il suo valore massimo per capire con che tipo di numeri abbiamo a che fare

mysql> select
    ->    cast(date_published as date) as day,
    ->    count(*) as num
    -> from crawler_entry
    -> group by day
    -> order by num desc
    -> limit 1;
+------------+-------+
| day        | num   |
+------------+-------+
| 2010-05-21 | 22837 |
+------------+-------+

Ci sono probabilmente modi più sofisticati di fare la stessa cosa, ma per i nostri scopi va bene quanto vedete qui sopra: facciamo un cast del campo datetime a date in modo da ridurlo al valore del giorno scartando ora minuti e secondi, raggruppiamo e contiamo le righe ottenendo i singoli totali per giorno (se avessimo un campo valore nelle singole righe useremmo sum() invece di count()), ordiniamo i risultati per i totali in ordine discendente, e teniamo solo il primo scartando tutto il resto. Phew, più lungo da spiegare che da fare.

A questo punto, possiamo studiare un altro po’ i dati per decidere quanti sono i giorni singoli e quali sono quelli con un totale significativo, in modo da limitare l’insieme dei risultati generati e renderlo più facilmente analizzabile. Proviamo ad esempio a vedere quanti sono i giorni che hanno almeno 1000 post

mysql> select count(day) from (
    ->    select cast(date_published as date) as day
    ->    from crawler_entry
    ->    group by day
    ->    having count(*) > 1000
    -> ) as aggregate;
+------------+
| count(day) |
+------------+
|         79 |
+------------+

Il meccanismo è lo stesso che abbiamo usato sopra: facciamo un cast del campo datetime a date, raggruppiamo in modo da avere i singoli totali, e infine usiamo i risultati come una tabella virtuale di cui contiamo le righe. Semplice, no?

A questo punto abbiamo tutte le informazioni che ci servono per estrarre i risultati definitivi e creare il nostro grafico ASCII: quale è il valore massimo del dato (22.000 e rotti), quale è la soglia minima che ci interessa (1.000), quante righe otterremo (79). Pronti? Via!

mysql> select
    ->    cast(date_published as date) as giorno,
    ->    concat(lpad(count(*), 7, ' '), ' ', rpad('', count(*) / 1000, '#')) as `numero di post`
    -> from crawler_entry
    -> group by giorno having count(*) >1000
    -> order by giorno desc;

Gli elementi di base sono gli stessi che abbiamo già visto sopra: il campo data ottenuto con il cast, il raggruppamento per avere i totali singoli, un limite per ottenere solo le righe con totale maggiore di 1.000, l’ordinamento discendente per giorno. La novità introdotta in questa query è il campo che abbiamo chiamato numero di post, che sono poi le singole barre del nostro grafico ASCII. Per ottenerlo:

• facciamo un pad a sinistra del valore numerico del totale, in modo che sia allineato a destra
• facciamo un padding di una stringa vuota che usi il carattere # come riempimento, e lo ripeta per un numero di volte uguale al totale diviso per 1.000 (la nostra soglia minima)
• concateniamo i due valori ottenuti (il totale allineato e i #) con uno spazio

Il risultato è una cosa così

+------------+---------------------------------+
| giorno     | numero di post                  |
+------------+---------------------------------+
| 2010-05-25 |    4597 #####                   |
| 2010-05-21 |   22837 ####################### |
| 2010-05-20 |   21067 #####################   |
| 2010-05-19 |   16395 ################        |
| 2010-05-18 |   14056 ##############          |
| 2010-05-17 |   12090 ############            |
| 2010-05-16 |    5683 ######                  |
| 2010-05-15 |    4623 #####                   |
| 2010-05-14 |    8579 #########               |
| 2010-05-13 |    8506 #########               |
| 2010-05-12 |    8310 ########                |
| 2010-05-11 |    7390 #######                 |
| 2010-05-10 |    7113 #######                 |
| 2010-05-09 |    3639 ####                    |
| 2010-05-08 |    2863 ###                     |
| 2010-05-07 |    5291 #####                   |
| 2010-05-06 |    5339 #####                   |
| 2010-05-05 |    5381 #####                   |
| 2010-05-04 |    4959 #####                   |
| 2010-05-03 |    4832 #####                   |
| 2010-05-02 |    2653 ###                     |
[... snip...]

Semplice, no?