Ultimo aggiornamento: 22-Giu-2019

6 Luglio 2018MarkOne

Somma progressiva

In diverse situazioni si può avere la necessità di eseguire una query creando una colonna calcolata che contenga la somma progressiva (ovvero running total) di un campo.

Un contesto tipico può essere una query sul file dei movimenti contabili per visualizzare le righe dell’estratto conto e un campo calcolato con il saldo progressivo ad ogni record. Oppure un altro esempio analogo può essere l’interrogazione dei movimenti di magazzino con il calcolo della giacenza di magazzino per ogni record. Un semplice esempio è illustrato sotto:

In questo articolo vediamo come è possibile ottenere questa colonna con UNA SOLA istruzione SQL sfruttando le CTE (common table expression). Sul tema CTE si veda anche l’articolo Query ricorsive. Oltre alle CTE sfruttiamo anche una funzione OLAP: row_number.

Entrambe sono disponibili da V5R4.

Prendiamo come esempio la tabella EMPPROJACT dal database di esempio di IBM che contiene per ogni impiegato le ore di attività svolte su uno o più progetti.

Selezioniamo i dati degli impiegati 000170 e 000230:

select *
  from EMPPROJACT
   where EMPNO in('000170', '000230')
   order by EMPNO, EMSTDATE;

Con la query seguente:

with RANKTBL as
 (select EMPNO, EMSTDATE, EMPTIME,
         row_number() over (partition by EMPNO order by EMSTDATE desc) as RANK
    from EMPPROJACT)
 select A.EMPNO, A.EMSTDATE, A.EMPTIME,
       (select sum(EMPTIME)
          from RANKTBL as B
          where B.EMPNO = A.EMPNO and B.RANK >= A.RANK) as RUNNING_TOTAL
   from RANKTBL as A
   where A.EMPNO in('000170', '000230')
   order by A.EMPNO, A.RANK desc;

si ottiene il risultato:

Ovvero un result set che mostra i record ordinati per codice impiegato (EMPNO) in ordine crescente di data attività con le ore lavorate (EMPTIME) e la colonna RUNNING_TOTAL che contiene la somma progressiva delle ore lavorate. La somma progressiva si azzera per ogni impiegato (in un programma a ciclo RPG si direbbe a “rottura di codice impiegato”).

Lo scopo della prima query temporanea

select EMPNO, EMSTDATE, EMPTIME,
         row_number() over (partition by EMPNO order by EMSTDATE desc) as RANK
    from EMPPROJACT
    where EMPNO in('000170', '000230');

è assegnare tramite la funzione row_number una numerazione temporanea (colonna RANK) ai record della tabella EMPPROJACT: a rottura di codice impiegato (partition by EMPNO) e ordinata per data decrescente (order by EMSTDATE desc). Ottenendo il risultato temporaneo mostrato di seguito:

La query “finale” legge questa tabella temporanea RANKTBL e per ogni record esegue una subquery sempre sulla tabella temporanea RANKTBL che effettua la funzione sum su tutti i record dell’impiegato corrente che hanno il campo RANK (la numerazione temporanea calcolata in precedenza) maggiore o uguale del record corrente. Questa subquery restituisce per ogni record la somma progressiva (colonna RUNNING_TOTAL).

Da 7.3 è possibile semplificare ulteriormente la query sfruttando la clausola di aggregation-specification: rows between unbounded preceding and current row. In altri termini la quarta colonna della query significa “esegui la somma di EMPTIME a rottura di EMPNO ordinando le righe per EMSTDATE e selezionando tutte le righe precedenti a quella corrente (unbounded preceding) e la riga corrente (current row)”

select EMPNO, EMSTDATE, EMPTIME,
   sum(EMPTIME) over(partition by EMPNO order by EMSTDATE rows between unbounded preceding and current row) as RUNNING_TOTAL
  from EMPPROJACT
  where EMPNO in('000170', '000230')
  order by EMPNO, EMSTDATE;

Bibliografia