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 / 3

26 maggio 2010

3 commenti

tag

categorie

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. :P

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?

3 commenti

  • Taifu
    26 maggio 2010 #

    Bello!

    E per chi usa un vero database ecco la traduzione:

    select
    cast(date_published as date)
    as giorno,
    lpad(cast(count(*) as varchar), 7, ’ ’)
    || ’ ’
    || rpad(’’, cast(count(*) as int), ’#’)
    as "numero di post"
    from crawler_entry
    group by giorno having count(*) >1000
    order by giorno desc;

    :-)

  • ludo
    26 maggio 2010 #

    Marco, chi usa un "vero database" ha già grandi problemi a districarsi con un client a console antiquato e un db gnucco, secondo me fa meglio a usare Excel. :P

  • C8E
    26 maggio 2010 #

    Enters sqlpython.

    $ sqlpython scott/tiger@oracle.galyleo.it
    0:scott@oracle.galyleo.it> select to_char(date_published, ’yyyy-mm-dd’) as giorno, count(*) as "numero di post" from crawler_entry group by date_published\b

    :oP