Vodafone, analizzare il traffico con SQL

Questa è la query MySQL da fare per caricare i dati esportati dal sito Vodafone (scegliendo di esportare dei CSV):

load data local infile '#FILE#' into table traffico fields terminated by "," ignore 3 lines (tipo, destinazione, numero, @data, @durata, costo, note, roaming, sim, tecnologia, operatore, volume_altri_op, volume_vlive) set data = str_to_date(@data, '%d/%m/%Y %H:%i:%s'), durata = str_to_date(@durata, '%H:%i:%s');

Si presuppone di chiamare "traffico" la tabella risultate, e che al posto di #FILE# si metta il percorso al file esportato.

Dopo aver caricato tutti i dati, queste sono delle prime query per avere delle analisi indicative:

-- Riepilogo mensile per tipologia (voce, sms...)
select date_format(data, '%Y%m'), tipo, sum(costo), count(*) from traffico where data >= '2010-09-01' group by date_format(data, '%Y%m'), tipo;
-- Riepilogo per tipologia
select tipo, sum(costo), count(*) from traffico where data >= '2010-09-01' group by tipo;

Questa è una query molto più completa che mostra un riepilogo mensile delle varie parti di costo, e fornisce 4 colonne finali con alcune analisi di quanto avrei speso se avessi avuto attivo un particolare piano (nel caso specifico stavo esaminando una offerta che prevedeva un costo fisso di 19€ che includeva 100min di chiamate a 0.1 cent al minuto - il resto alla normale tariffa - e 100sms a 0.1 cent - il resto alla normale tariffa).

select date_format(data, '%Y%m') as p, 
sum(if(tipo = 'Chiamate voce e video',costo,0)) as voce_price,
sum(if(tipo = 'Chiamate voce e video',1,0)) as voce_no,
sum(if(tipo = 'Chiamate voce e video',time_to_sec(durata),0))/60 as voce_min,
sum(if(tipo = 'Chiamate voce e video',costo,0)) / sum(if(tipo = 'Chiamate voce e video',time_to_sec(durata),0))*60 as voce_price_min,
sum(if(tipo = 'Servizi di messaggistica',costo,0)) as sms_price,
sum(if(tipo = 'Servizi di messaggistica',1,0)) as sms_no,
sum(if(tipo = 'Servizi di messaggistica' or tipo = 'Chiamate voce e video',costo,0)) as vocesms_price,
sum(if(tipo = 'Servizi di messaggistica' or tipo = 'Chiamate voce e video',0,costo)) as other_price,
19 +
if(
    sum(if(tipo = 'Chiamate voce e video',time_to_sec(durata),0))/60 < 100, 
    sum(if(tipo = 'Chiamate voce e video',time_to_sec(durata),0))/60, 
    100) * 0.01 +
if(
    sum(if(tipo = 'Chiamate voce e video',time_to_sec(durata),0))/60 > 100, 
    sum(if(tipo = 'Chiamate voce e video',time_to_sec(durata),0))/60 - 100, 
    0) * 
    sum(if(tipo = 'Chiamate voce e video',costo,0)) / sum(if(tipo = 'Chiamate voce e video',time_to_sec(durata),0))*60 +
if(
    sum(if(tipo = 'Servizi di messaggistica',1,0)) < 100,
    sum(if(tipo = 'Servizi di messaggistica',1,0)),
    100) * 0.01 +
if(
    sum(if(tipo = 'Servizi di messaggistica',1,0)) > 100,
    sum(if(tipo = 'Servizi di messaggistica',1,0)) - 100,
    0) * 0.1 
as new_price
from traffico 
group by date_format(data, '%Y%m');

Da notare che la simulazione "new_price" è solo un andamento che tende a quello reale: va considerato che il prezzo delle chiamate voce dipende dall'orario (con il mio piano spendo meno la sera e i festivi), consideralo realmente sarebbe stato complicato, quindi considero il costo chiamata sulla media mensile.