Report con alias di campo dinamici

Tip scritto da john_revelator circa un anno fa

Utilizzo di funzioni come alias di campo tramite l'impiego di prepared statement.

Oggi mi sono imbattuto in un'esigenza un pò particolare e volevo condividere con voi la mia soluzione.
In pratica dovevo creare un semplice report relativo alle vendite di una serie di prodotti negli ultimi tre mesi.
L'unica "difficoltà" che si presentava era relativa all'impiego di alias dinamici. Non volevo infatti usare degli alias statici del tipo "mese attuale", "mese scorso", "due mesi fa" ma volevo che fossero sempre aggiornati al periodo di esecuzione della query stessa.

Qui di seguito un dump esemplificativo.

 1create table alias_dinamici(
 2id int not null auto_increment primary key,
 3id_prodotto int,
 4data_prodotto date,
 5prezzo decimal(4,2)
 6) engine = myisam;
 7
 8insert into alias_dinamici (id_prodotto,data_prodotto,prezzo)
 9values 
10(1,'2011-03-01',12.50),
11(2,'2011-03-14',18.50),
12(3,'2011-03-10',15.00),
13(3,'2011-03-25',19.50),
14(1,'2011-02-20',10),
15(1,'2011-02-28',15),
16(2,'2011-01-10',8.30),
17(3,'2011-01-27',8.30),
18(2,'2010-12-30',15),
19(2,'2010-12-30',10),
20(1,'2010-12-04',5),
21(3,'2010-12-12',15);

Dopo un attimo di empasse ho pensato che una prepared statement potesse venire incontro alle mie esigenze.

 1set @mese_corrente = date_format(curdate(),'`%b %Y`'),
 2    @mese_scorso = date_format(curdate() -interval 1 month,'`%b %Y`'),
 3    @due_mesi_fa = date_format(curdate() -interval 2 month,'`%b %Y`');
 4
 5set @str = concat("select id_prodotto,
 6           sum(if(date_format(data_prodotto,'%Y%m') = date_format(curdate(),'%Y%m'),prezzo,0)) as ",@mese_corrente,
 7         ",sum(if(date_format(data_prodotto,'%Y%m') = date_format(curdate() -interval 1 month,'%Y%m'),prezzo,0)) as ",@mese_scorso,
 8         ",sum(if(date_format(data_prodotto,'%Y%m') = date_format(curdate() -interval 2 month,'%Y%m'),prezzo,0)) as ",@due_mesi_fa,
 9             ' from alias_dinamici group by id_prodotto');
10prepare stmt from @str;
11execute stmt;

Questo è il risultato:

1+-------------+----------+----------+----------+
2| id_prodotto | Mar 2011 | Feb 2011 | Jan 2011 |
3+-------------+----------+----------+----------+
4|           1 |    12.50 |    25.00 |     0.00 |
5|           2 |    18.50 |     0.00 |     8.30 |
6|           3 |    34.50 |     0.00 |     8.30 |
7+-------------+----------+----------+----------+

Spostiamo a un giorno qualsiasi di febbraio la data del nostro computer e riproviamo la nostra query. In questo caso, come ci aspettavamo, il risultato comprenderà l'intervallo febbrario 2011 - dicembre 2010

1+-------------+----------+----------+----------+
2| id_prodotto | Feb 2011 | Jan 2011 | Dec 2010 |
3+-------------+----------+----------+----------+
4|           1 |    25.00 |     0.00 |     5.00 |
5|           2 |     0.00 |     8.30 |    25.00 |
6|           3 |     0.00 |     8.30 |    15.00 |
7+-------------+----------+----------+----------+

Nel caso volessimo i nomi dei mesi in italiano non dovremo far altro che settare a monte la variabile lc_time_names:

1set lc_time_names = 'it_IT';

Current rating: 0.0/5
1
2
3
4
5

Tip precedente: Esecuzione dump in formato CSV (tabelle in engine MyIsam esportate in formato CSV)

Tip successivo: UNION / UNION ALL su campi int unsigned zerofill.