Funciones analíticas MIN y MAX

Este artículo ofrece una descripción general de las funciones analíticas MIN y MAX. Si es nuevo en las funciones analíticas, probablemente debería leer primero esta introducción a las funciones analíticas.

  • Preparar
  • MIN and MAX como Funcion Agregada
  • Función Analítica MIN
  • Función Analítica MAX

Preparar

Los ejemplos de este artículo requieren la siguiente tabla.

--DROP TABLE emp PURGE;

CREATE TABLE emp (
  empno    NUMBER(4) CONSTRAINT pk_emp PRIMARY KEY,
  ename    VARCHAR2(10),
  job      VARCHAR2(9),
  mgr      NUMBER(4),
  hiredate DATE,
  sal      NUMBER(7,2),
  comm     NUMBER(7,2),
  deptno   NUMBER(2)
);

INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO emp VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO emp VALUES (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO emp VALUES (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO emp VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO emp VALUES (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO emp VALUES (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO emp VALUES (7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000,NULL,20);
INSERT INTO emp VALUES (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO emp VALUES (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO emp VALUES (7876,'ADAMS','CLERK',7788,to_date('13-JUL-87', 'dd-mm-rr')-51,1100,NULL,20);
INSERT INTO emp VALUES (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO emp VALUES (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO emp VALUES (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
COMMIT;

MIN y MAX como funciones agregadas

Las funciones agregadas MIN y MAX se utilizan para calcular los valores mínimo y máximo de un conjunto de datos respectivamente. Como funciones agregadas, reducen el número de filas, de ahí el término «aggregate». Si los datos no están agrupados, convertimos las 14 filas de la tabla EMP en una sola fila con los valores agregados.

SELECT MIN(sal) AS min_sal,
       MAX(sal) AS max_sal
FROM   emp;

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

SQL>

Podemos obtener más granularidad de la información al incluir una cláusula GROUP BY. En el siguiente ejemplo, vemos los valores mínimo y máximo por departamento.

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>

En ambos casos, hemos agregado los datos para obtener los valores, devolviendo menos filas de las que comenzamos. Las funciones analíticas nos permiten devolver estos valores agregados conservando los datos de la fila original.

Función analítica MIN

La descripción básica de la función analítica MIN se muestra a continuación.

MIN([ DISTINCT | ALL ] expr) [ OVER (analytic_clause) ]

El uso de una cláusula OVER vacía convierte la función MIN en una función analítica. La falta de una cláusula de partición significa que todo el conjunto de resultados se trata como una sola partición, por lo que obtenemos el salario mínimo para todos los empleados, así como todos los datos originales.

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>

Agregar la cláusula de partición nos permite mostrar el salario mínimo por departamento, junto con los datos de los empleados de cada departamento.

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>

Función analítica MAX

La descripción básica de la función analítica MAX se muestra a continuación.

MAX([ DISTINCT | ALL ] expr) [ OVER (analytic_clause) ]

El uso de una cláusula OVER vacía convierte la función MAX en una función analítica. La falta de una cláusula de partición significa que todo el conjunto de resultados se trata como una sola partición, por lo que obtenemos el salario máximo para todos los empleados, así como todos los datos originales.

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>

Agregar la cláusula de partición nos permite mostrar el salario máximo por departamento, junto con los datos de los empleados para cada departamento.

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>

1 comentario en “Funciones analíticas MIN y MAX”

  1. Pingback: Funciones analíticas RANK, DENSE_RANK, FIRST y LAST - DBandTech.com

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *