25 settembre 2008

Analytics - n.2: LEAD e LAG

LEAD e LAG sono funzioni analitiche che permettono di accedere al valore di un campo di un record del recordset risultato della nostra query, specificando l'offset dal record in cui la funzione viene valutata.
Nel caso di un offset che va fuori dal recordset, è possibile specificare un valore di default ritornato dalla funzione.

Il punto essenziale, come per gran parte delle funzioni analitiche, è l'ordinamento del risultato: il valore dell'n-esima riga prima o dopo la riga corrente è dato proprio dell'"order by" specificato nel partizionamento del resultset.

Prendiamo sempre l'esempio dei maratoneti che ho usato in un post precedente.
Mettiamo che io voglia conoscere la differenza di tempo in arrivo tra un corridore e il successivo, un po' come viene fatto alla fine di gare a tempo.
Posso usare la funzione LAG con parametro 1 e computata ordinando la tabella per arrival_time:
 SELECT runner_id,
arrival_time ,
extract(minute FROM
(arrival_time - lag(arrival_time, 1, arrival_time) over (order by arrival_time)) DAY TO second)
||''''
|| extract(second FROM
(arrival_time - lag(arrival_time, 1, arrival_time) over (order by arrival_time)) DAY TO second)
||'"' time_diff
FROM marathon
WHERE rownum < 10
ORDER BY arrival_time;

Nell'esempio prendo solo 10 righe per avere un po' di intervallo tra i tempi di arrivo e il risultato è il seguente:
 RUNNER_ID ARRIVAL_TIME        TIME_DIFF
---------- ------------------- ----------------------------------------------------------------------------------
4038 2008-06-19 12:32:09 0'0"
3144 2008-06-19 12:44:10 12'1"
660 2008-06-19 12:51:40 7'30"
4450 2008-06-19 12:54:54 3'14"
2441 2008-06-19 12:58:28 3'34"
591 2008-06-19 13:02:55 4'27"
3825 2008-06-19 13:05:03 2'8"
3169 2008-06-19 13:11:21 6'18"
4036 2008-06-19 13:11:39 0'18"

La funzionalità di LEAD è analoga a LAG, solo che va nel verso opposto, ovvero considera i record precedenti a quello in cui viene valutata.

4 commenti:

tangalor ha detto...

Ciao Rudy, questo post è davvero molto molto interessante. Quasta funzione di oracle apre davvero delle belle possibilità! :)

Avrei bisogno della stessa funzionalità su MySql ma su internet sembra non si trovi niente e dunque ho paura che non esista. Ne sai niente? Su MySql sai se esiste qualcosa di simile?

tangalor ha detto...

Se mi fai sapere qualcosa mi fai un grossisimo piacere, anche in privato a tangalor@gmail.com

Bye :)

Lorenzo

Rudy ha detto...

Ciao Lorenzo, penso che su MySQL non ci sia niente di analogo alle funzioni analitiche, per quanto ricordo.

Comunque l'unica cosa da fare è andare a vedere nel manuale, che è la ciò che farei io per rispondere alla tua domanda.

tangalor ha detto...

Spulciando su internet non ho trovato niente su LAG in mysql, credo proprio che purtroppo nn esista.

Ma ho troato questo link dove, a quanto ho capito, spiega come aggirare il problema

http://explainextended.com/2009/03/12/analytic-functions-optimizing-lag-lead-first_value-last_value/

Presto contatterò l'autore per vedere se mi dà una mano con un mio problema. Spero che il link possa far comodo pure a te. :)