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>
Pingback: Funciones analíticas RANK, DENSE_RANK, FIRST y LAST - DBandTech.com