Analytics - n.1: NTILE

Con questo post inauguro la serie "Analytics", ovvero come utilizzare le potenti funzioni analitiche di Oracle 10g.

NTILE è una funzione che permette di ottenere il numero corrente di partizione dei dati, basandosi su un criterio di partizione determinato nella query.

Facciamo subito un esempio: ho una tabella che contiene i tempi di arrivo di una maratona con tanti partecipanti, diciamo 5000. Di questi, 1000 sono arrivati al traguardo, mentre gli altri si sono persi per strada.
La tabella contiene un ID univoco del corridore e il suo tempo di arrivo.

Supponiamo che io voglia dividere l'insieme degli "arrivati" in n gruppi omogenei, cioè aventi lo stesso numero di componenti.
Gli ID, anche se generati con una sequenza, sono distribuiti casualmente, dato che solo 1/5 dei corridori è arrivato alla fine; lo stesso vale per i tempi di arrivo.

La funzione NTILE ci permette di dividere il nostro set di dati in partizioni uguali, assegnando ad ogni riga il numero di partizione in cui si trova. Il criterio per trovarsi in una partizione o in un'altra è il tempo di arrivo in ordine crescente.

Dividiamoli ad esempio in 10 gruppi:

SQL> select * from marathon;

 RUNNER_ID ARRIVAL_TIME                                                                                                                                       
---------- -------------------                                                                                                                                
        93 2008-06-19 13:11:39                                                                                                                                
      3144 2008-06-19 12:44:10                                                                                                                                
      4450 2008-06-19 12:54:54                                                                                                                                
       453 2008-06-19 12:52:58                                                                                                                                
       808 2008-06-19 12:48:00                                                                                                                                
      4036 2008-06-19 13:11:39                                                                                                                                
      4048 2008-06-19 13:06:48                                                                                                                                
       660 2008-06-19 12:51:40                                                                                                                                
      4091 2008-06-19 13:12:38                                                                                                                                
       591 2008-06-19 13:02:55                                                                                                                                
[...]

Con la query seguente otteniamo la divisione in gruppi:

SQL> select runner_id, arrival_time, ntile(10) over (order by arrival_time) nt from marathon;

Come si può vedere, ci sono esattamente 100 corridori in ogni gruppo:

SQL> select nt, count(*) from
(select runner_id, arrival_time,
ntile(10) over (order by arrival_time) nt from marathon)
group by nt order by nt;

        NT   COUNT(*)                                                                                                                                         
---------- ----------                                                                                                                                         
         1        100                                                                                                                                         
         2        100                                                                                                                                         
         3        100                                                                                                                                         
         4        100                                                                                                                                         
         5        100                                                                                                                                         
         6        100                                                                                                                                         
         7        100                                                                                                                                         
         8        100                                                                                                                                         
         9        100                                                                                                                                         
        10        100                                                                                                                                         

10 rows selected.

Possiamo anche calcolare l'ntile su una partizione dei dati originari: immaginiamo per esempio che ai corridori siano state assegnate delle pettorine colorate di rosso se il loro id era tra 1 e 1000, verde se tra 1001 e 2000, blu se maggiore di 2000, divisi quindi per categoria.
Una query come la seguente divide in dieci sottogruppi ogni categoria, basandosi sempre sul tempo di arrivo:

SQL> select runner_id, arrival_time, color,
ntile(10) over (partition by color order by arrival_time) from marathon;