Function - (Moving|Rolling|Running) Calculation

1 - About

(Moving|Rolling|Running) Calculation are computed with window function.

3 - Example

3.1 - Running total

For example, running total of sum (sal + comm) department wise on emp table :

Oracle in Sqlplus:

break ON deptno skip 1
 
SELECT 
    deptno, 
    empno, 
    SUM(sal+nvl(comm,0)) OVER (partition BY deptno ORDER BY empno) running_total
FROM
    scott.emp
ORDER BY
    deptno, 
    empno;
    DEPTNO      EMPNO  RUNNING_TOTAL
---------- ---------- ----------
        10       7782       2450
                 7839       7450
                 7934       8750

        20       7369        800
                 7566       3775
                 7788       6775
                 7876       7875
                 7902      10875

        30       7499       1900
                 7521       3650
                 7654       6300
                 7698       9150
                 7844      10650
                 7900      11600
code/function/moving.txt · Last modified: 2018/10/17 21:09 by gerardnico