22 Giugno 2019MarkOne

Quanto pesi?

Non preoccupatevi non si tratta di un articolo con consigli per dimagrire e farvi mettere a dieta.

Vogliamo calcolare il peso in percentuale di un valore rispetto ad un totale.

Prendiamo come esempio la tabella EMPPROJACT del database di esempio del DB2.

    -- dettaglio ore lavorate
select EMPNO, PROJNO, ACTNO, EMPTIME
  from EMPPROJACT
   where EMPNO in('000170', '000240', '000250')
   order by EMPNO, PROJNO, ACTNO;

La tabella contiene il dettaglio delle attività (ACTNO) svolte dagli impiegati (EMPNO) sui vari progetti (PROJNO).

Desideriamo sapere il totale delle ore lavorate (EMPTIME) per ogni impiegato (EMPNO) e quanto “pesa” in percentuale il lavoro svolto da ogni impiegato rispetto al totale delle ore. Ovvero il risultato seguente:

Per ottenere questo risultato sfruttiamo due tecniche presenti nel DB2: le CTE (Common Table Expression, disponibili da V5R4) e la funzione OLAP ratio_to_report (disponibile da 7.3).

with
  grpImp as
    (select EMPNO, sum(EMPTIME) EMPTIME
      from EMPPROJACT
      where EMPNO in('000170', '000240', '000250')
      group by EMPNO)
  select EMPNO "Impiegato", EMPTIME "Ore",
     sum(EMPTIME) over(partition by 1) as "Tot.gen.",
     dec(ratio_to_report(EMPTIME) over(partition by 1)*100, 5, 2) "% su tot."
    from grpImp
    order by 1;

Creiamo prima una tabella temporanea grpImp per totalizzare le ore lavorate per impiegato.
La query finale ha come input la tabella grpImp. Per ogni record ottiene il totale generale usando la funzione sum combinata con la window-partition-clause over(partition by 1), ovvero calcola la somma di EMPTIME su tutti i record.
La terza colonna della query calcola il “peso percentuale” delle ore lavorate di ogni impiegato rispetto al totale generale: entra in gioco la funzione OLAP ratio_to_report. L’argomento della funzione è il campo di cui si desidera conoscere la percentuale del record corrente rispetto all’insieme dei record selezionati con la window-partition-clause over(partition by 1), ovvero la somma di EMPTIME sull’intero recordset (come visto in precedenza).

Proseguiamo e creiamo un esempio più complesso. Sempre partendo dalla tabella EMPPROJACT desideriamo estrarre il riepilogo delle ore lavorate per impiegato/progetto calcolando il peso percentuale delle ore lavorate per progetto riferito sia al totale ore dell’impiegato (riquadro blu) sia al totale generale (riquadro rosso):

Il metodo per arrivare a questo risultato è simile alla precedente query. Semplicemente si usa la funzione ratio_to_report con due diverse window-partition-clause: una (riquadro rosso) identica alla query precedente per avere il peso percentuale rispetto al totale e l’altra (riquadro blu) sezionata per impiegato (partition by EMPNO).

with
  grpImpPrg as(
    select EMPNO, PROJNO, sum(EMPTIME) EMPTIME
      from EMPPROJACT
      where EMPNO in('000170', '000240', '000250')
      group by EMPNO, PROJNO)
  select EMPNO "Impiegato", PROJNO "Progetto", EMPTIME "Ore",
     sum(EMPTIME) over(partition by EMPNO) as "Tot.imp.",
     dec(ratio_to_report(EMPTIME) over(partition by EMPNO)*100, 5, 2) "% su tot.imp.",
     sum(EMPTIME) over(partition by 1) as "Tot.gen.",
     dec(ratio_to_report(EMPTIME) over(partition by 1)*100, 5, 2) "% su tot.gen"
    from grpImpPrg
    order by 1, 2;

Il terzo esempio mostra il calcolo del peso percentuale su 3 livelli: rispetto al totale progetto (riquadro verde), al totale impiegato (riquadro blu) e al totale generale (riquadro rosso).

select EMPNO "Impiegato", PROJNO "Progetto", ACTNO "Attività", EMPTIME "Ore",
     sum(EMPTIME) over(partition by PROJNO) as "Tot.prog.",
     dec(ratio_to_report(EMPTIME) over(partition by PROJNO)*100, 5, 2) "% su tot.prog.",
     sum(EMPTIME) over(partition by EMPNO) as "Tot.imp.",
     dec(ratio_to_report(EMPTIME) over(partition by EMPNO)*100, 5, 2) "% su tot.imp.",
     sum(EMPTIME) over(partition by 1) as "Tot.gen.",
     dec(ratio_to_report(EMPTIME) over(partition by 1)*100, 5, 2) "% su tot.gen"
    from EMPPROJACT
    where EMPNO in('000170', '000240', '000250')
    order by 1, 2, 3;

Bibliografia