Palabra clave "Partición por" de Oracle
¿Alguien puede explicar qué partition byhace la palabra clave y dar un ejemplo simple de ella en acción, así como también por qué uno querría usarla? Tengo una consulta SQL escrita por otra persona y estoy tratando de descubrir qué hace.
Un ejemplo de partición por:
SELECT empno, deptno, COUNT(*)
OVER (PARTITION BY deptno) DEPT_COUNT
FROM emp
Los ejemplos que he visto en línea parecen demasiado profundos.
La PARTITION BYcláusula establece el rango de registros que se utilizarán para cada "GRUPO" dentro de la OVERcláusula.
En su SQL de ejemplo, DEPT_COUNTdevolverá la cantidad de empleados dentro de ese departamento para cada registro de empleado. (Es como si estuviera desnomalizando la emptabla; aún así devuelve todos los registros de la emptabla).
emp_no dept_no DEPT_COUNT
1 10 3
2 10 3
3 10 3 <- three because there are three "dept_no = 10" records
4 20 2
5 20 2 <- two because there are two "dept_no = 20" records
Si hubiera otra columna (por ejemplo, state), entonces podría contar cuántos departamentos hay en ese estado.
Es como obtener los resultados de GROUP BY( SUM,, AVGetc.) sin agregar el conjunto de resultados (es decir, eliminar registros coincidentes).
Es útil cuando usa las funciones LAST OVERo MIN OVERpara obtener, por ejemplo, el salario más bajo y más alto en el departamento y luego lo usa en un cálculo contra el salario de estos registros sin una subselección, lo cual es mucho más rápido.
Lea el artículo vinculado de AskTom para obtener más detalles.
El concepto está muy bien explicado por la respuesta aceptada, pero encuentro que cuanto más ejemplo uno ve, mejor se asimila. Aquí hay un ejemplo incremental:
- El jefe dice "dame la cantidad de artículos que tenemos en stock agrupados por marca"
Tu dices : "no hay problema"
SELECT
BRAND
,COUNT(ITEM_ID)
FROM
ITEMS
GROUP BY
BRAND;
Resultado:
+--------------+---------------+
| Brand | Count |
+--------------+---------------+
| H&M | 50 |
+--------------+---------------+
| Hugo Boss | 100 |
+--------------+---------------+
| No brand | 22 |
+--------------+---------------+
- El jefe dice "Ahora dame una lista de todos los artículos, con su marca Y la cantidad de artículos que tiene la marca respectiva".
Puedes intentar:
SELECT
ITEM_NR
,BRAND
,COUNT(ITEM_ID)
FROM
ITEMS
GROUP BY
BRAND;
Pero obtienes:
ORA-00979: not a GROUP BY expression
Aquí es donde OVER (PARTITION BY BRAND)entra en juego:
SELECT
ITEM_NR
,BRAND
,COUNT(ITEM_ID) OVER (PARTITION BY BRAND)
FROM
ITEMS;
Lo que significa:
COUNT(ITEM_ID)- obtener el número de artículosOVER- Sobre el conjunto de filas.(PARTITION BY BRAND)- que tengan la misma marca
Y el resultado es:
+--------------+---------------+----------+
| Items | Brand | Count() |
+--------------+---------------+----------+
| Item 1 | Hugo Boss | 100 |
+--------------+---------------+----------+
| Item 2 | Hugo Boss | 100 |
+--------------+---------------+----------+
| Item 3 | No brand | 22 |
+--------------+---------------+----------+
| Item 4 | No brand | 22 |
+--------------+---------------+----------+
| Item 5 | H&M | 50 |
+--------------+---------------+----------+
etc...
Es la extensión de SQL llamada analítica. El "over" en la declaración de selección le dice a Oracle que la función es una función analítica, no un grupo por función. La ventaja de utilizar análisis es que puede recopilar sumas, recuentos y mucho más con solo pasar los datos una vez en lugar de recorrerlos en bucle con subselecciones o, peor aún, PL/SQL.
Parece confuso al principio, pero rápidamente se convertirá en algo natural. Nadie lo explica mejor que Tom Kyte. Entonces el enlace de arriba es genial.
Por supuesto, leer la documentación es imprescindible.
EMPNO DEPTNO DEPT_COUNT
7839 10 4
5555 10 4
7934 10 4
7782 10 4 --- 4 records in table for dept 10
7902 20 4
7566 20 4
7876 20 4
7369 20 4 --- 4 records in table for dept 20
7900 30 6
7844 30 6
7654 30 6
7521 30 6
7499 30 6
7698 30 6 --- 6 records in table for dept 30
Aquí estamos contando el departamento respectivo. En cuanto al departamento 10, tenemos 4 registros en la tabla emp. Resultados similares para los departamentos 20 y 30 también.
Aug 25 '2009 06:08