Somma progressiva

Visita il nuovo sito https://www.markonetools.it/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.

Prendiamo come esempio 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 mostrato a lato.

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 è 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 a lato.

select EMPNO, EMSTDATE, EMPTIME,

         row_number() over (partition by EMPNO order by EMSTDATE desc) as RANK

    from EMPPROJACT

    where EMPNO in('000170', '000230');

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).

Bibliografia

Running Totals in an SQL Query, Skip Marchesani, IT Jungle 2-mar-2011: https://www.itjungle.com/2011/03/02/fhg030211-story01/
Running Totals in an SQL Query - take 2, Skip Marchesani, IT Jungle 13-lug-2011: https://www.itjungle.com/2011/07/13/fhg071311-story01/

Google Sites

Report abuse