Thursday, November 7, 2019

×Oracle MIN and MAX Analytic Functions


MIN and MAX Analytic Functions and MIN and MAX as Aggregate Functions
The MIN and MAX aggregate functions are used to calculate the minimum and maximum values of a set of data respectively. As aggregate functions they reduce the number of rows, hence the term "aggregate". If the data isn't grouped we turn the 14 rows in the EMP table to a single row with the aggregated values.
SELECT MIN(sal) AS min_sal,
       MAX(sal) AS max_sal
FROM   emp;

   MIN_SAL    MAX_SAL
---------- ----------
       800       5000

SQL>
We can get more granularity of information by including a GROUP BY clause. In the following example we see the minimum and maximum values on a per-department basis.
SELECT deptno,
       MIN(sal) AS min_sal,
       MAX(sal) AS max_sal
FROM   emp
GROUP BY deptno
ORDER BY deptno;

    DEPTNO    MIN_SAL    MAX_SAL
---------- ---------- ----------
        10       1300       5000
        20        800       3000
        30        950       2850

SQL>
In both cases we have aggregated the data to get the values, returning less rows than we started with. Analytic functions allow us to return these aggregate values while retaining the original row data.
MIN Analytic Function
The basic description for the MIN analytic function is shown below. The analytic clause is described in more detail here.
MIN([ DISTINCT | ALL ] expr) [ OVER (analytic_clause) ]
Using an empty OVER clause turns the MIN function into an analytic function. The lack of a partitioning clause means the whole result set is treated as a single partition, so we get the minimum salary for all employees, as well as all the original data.
SELECT empno,
       ename,
       deptno,
       sal,
       MIN(sal) OVER () AS min_sal
FROM   emp
ORDER BY deptno;

     EMPNO ENAME          DEPTNO        SAL    MIN_SAL
---------- ---------- ---------- ---------- ----------
      7782 CLARK              10       2450        800
      7839 KING               10       5000        800
      7934 MILLER             10       1300        800
      7566 JONES              20       2975        800
      7902 FORD               20       3000        800
      7876 ADAMS              20       1100        800
      7369 SMITH              20        800        800
      7788 SCOTT              20       3000        800
      7521 WARD               30       1250        800
      7844 TURNER             30       1500        800
      7499 ALLEN              30       1600        800
      7900 JAMES              30        950        800
      7698 BLAKE              30       2850        800
      7654 MARTIN             30       1250        800

SQL>
Adding the partitioning clause allows us to display the minimum salary per department, along with the employee data for each department.
SELECT empno,
       ename,
       deptno,
       sal,
       MIN(sal) OVER (PARTITION BY deptno) AS min_sal_by_dept
FROM   emp;

     EMPNO ENAME          DEPTNO        SAL MIN_SAL_BY_DEPT
---------- ---------- ---------- ---------- ---------------
      7782 CLARK              10       2450            1300
      7839 KING               10       5000            1300
      7934 MILLER             10       1300            1300
      7566 JONES              20       2975             800
      7902 FORD               20       3000             800
      7876 ADAMS              20       1100             800
      7369 SMITH              20        800             800
      7788 SCOTT              20       3000             800
      7521 WARD               30       1250             950
      7844 TURNER             30       1500             950
      7499 ALLEN              30       1600             950
      7900 JAMES              30        950             950
      7698 BLAKE              30       2850             950
      7654 MARTIN             30       1250             950

SQL>
MAX Analytic Function
The basic description for the MAX analytic function is shown below. The analytic clause is described in more detail here.
MAX([ DISTINCT | ALL ] expr) [ OVER (analytic_clause) ]
Using an empty OVER clause turns the MAX function into an analytic function. The lack of a partitioning clause means the whole result set is treated as a single partition, so we get the maximum salary for all employees, as well as all the original data.
SELECT empno,
       ename,
       deptno,
       sal,
       MAX(sal) OVER () AS max_sal
FROM   emp;

     EMPNO ENAME          DEPTNO        SAL    MAX_SAL
---------- ---------- ---------- ---------- ----------
      7369 SMITH              20        800       5000
      7499 ALLEN              30       1600       5000
      7521 WARD               30       1250       5000
      7566 JONES              20       2975       5000
      7654 MARTIN             30       1250       5000
      7698 BLAKE              30       2850       5000
      7782 CLARK              10       2450       5000
      7788 SCOTT              20       3000       5000
      7839 KING               10       5000       5000
      7844 TURNER             30       1500       5000
      7876 ADAMS              20       1100       5000
      7900 JAMES              30        950       5000
      7902 FORD               20       3000       5000
      7934 MILLER             10       1300       5000

SQL>
Adding the partitioning clause allows us to display the maximum salary per department, along with the employee data for each department.
SELECT empno,
       ename,
       deptno,
       sal,
       MAX(sal) OVER (PARTITION BY deptno) AS max_sal_by_dept
FROM   emp;

     EMPNO ENAME          DEPTNO        SAL MAX_SAL_BY_DEPT
---------- ---------- ---------- ---------- ---------------
      7782 CLARK              10       2450            5000
      7839 KING               10       5000            5000
      7934 MILLER             10       1300            5000
      7566 JONES              20       2975            3000
      7902 FORD               20       3000            3000
      7876 ADAMS              20       1100            3000
      7369 SMITH              20        800            3000
      7788 SCOTT              20       3000            3000
      7521 WARD               30       1250            2850
      7844 TURNER             30       1500            2850
      7499 ALLEN              30       1600            2850
      7900 JAMES              30        950            2850
      7698 BLAKE              30       2850            2850
      7654 MARTIN             30       1250            2850

SQL>


No comments:

Post a Comment