Palabra clave Oracle "Partition By"
¿Puede alguien explicar qué hace la palabra clave partition by y dar un ejemplo simple de ello en acción, así como por qué uno querría usarla? Tengo una consulta SQL escrita por otra persona y estoy tratando de averiguar lo que 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 un poco demasiado profundos.
5 answers
La cláusula PARTITION BY establece el rango de registros que se utilizarán para cada "GRUPO" dentro de la cláusula OVER.
En su ejemplo SQL, DEPT_COUNT devolverá el número de empleados dentro de ese departamento por cada registro de empleado. (Es como si des-nomalizaras la tabla emp; todavía devuelves todos los registros en la tabla emp.)
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 en ese Estado.
Es como conseguir el resultados de una GROUP BY (SUM, AVG, etc.) sin la agregación del conjunto de resultados.
Es útil cuando se usan las funciones LAST OVER o MIN OVER para obtener, por ejemplo, el salario más bajo y más alto en el departamento y luego usarlo en una calulación contra este salario de registros sin un sub selecto, que es mucho más rápido.
Lea el artículo vinculado de AskTom para más detalles.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2013-05-10 18:05:34
El concepto está muy bien explicado por la respuesta aceptada, pero encuentro que cuanto más ejemplo uno ve, mejor se hunde. Este es un ejemplo incremental:
1) Boss dice "consígueme el número de artículos que tenemos en stock agrupados por marca"
Usted dice: "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 |
+--------------+---------------+
2) El jefe dice "Ahora consígueme una lista de todos los artículos, con su marca Y el número de artículos que tienen esa marca"
Usted puede try:
SELECT
ITEM_NR
,BRAND
,COUNT(ITEM_ID)
FROM
ITEMS
GROUP BY
BRAND;
Pero obtienes:
ORA-00979: not a GROUP BY expression
Aquí es donde entra el OVER (PARTITION BY BRAND):
SELECT
ITEM_NR
,BRAND
,COUNT(ITEM_ID) OVER (PARTITION BY BRAND)
FROM
ITEMS;
Que significa:
-
COUNT(ITEM_ID)- obtener el número de elementos -
OVER- Sobre el conjunto de filas -
(PARTITION BY BRAND)- que tienen 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...
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2016-11-09 21:28:32
Es la extensión SQL llamada analytics. El "over" en la instrucción select le dice a oracle que la función es una función analítica, no un grupo por función. La ventaja de usar analytics es que puede recopilar sumas, recuentos y mucho más con un solo paso de los datos en lugar de recorrer los datos con sub selects o, peor aún, PL/SQL.
Parece confuso al principio, pero esto será una segunda naturaleza rápidamente. Nadie lo explica mejor que Tom Kyte. Así que el el enlace de arriba es genial.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2009-02-27 04:47:56
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 recibiendo la cuenta para el respectivo deptno. En cuanto al deptno 10 tenemos 4 registros en la tabla emp resultados similares para el deptno 20 y 30 también.
La palabra clave over partition es como si estuviéramos particionando los datos por client_id crear un subconjunto de cada id de cliente
select client_id, operation_date,
row_number() count(*) over (partition by client_id order by client_id ) as operationctrbyclient
from client_operations e
order by e.client_id;
Esta consulta devolverá el número de operaciones realizadas por el client_id
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2016-10-28 05:56:12