sql - functions - oracle over partition by

Palabra clave de "partición por" de Oracle

Question

¿Alguien puede explicar qué hace la partition by palabra clave y dar un ejemplo simple de ella en acción, así como por qué uno querría usarla? Tengo una consulta SQL escrita por otra persona y estoy tratando de averiguar 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 un poco demasiado profundos.

Answer 1

El concepto está muy bien explicado por la respuesta aceptada, pero me parece que cuanto más se ve un ejemplo, mejor se hunde. Aquí hay un ejemplo incremental:

1) Boss dice "consígame la cantidad de artículos que tenemos en stock agrupados por marca"

Tú 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 |
+--------------+---------------+

2) El jefe dice: "Ahora consígame una lista de todos los artículos, con su marca Y el número 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 entra OVER (PARTITION BY BRAND) :

 SELECT
ITEM_NR
,BRAND
,COUNT(ITEM_ID) OVER (PARTITION BY BRAND)
FROM
ITEMS;

Qué 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 ...

Answer 2

Es la extensión SQL llamada analítica. El "sobre" 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 usar la analítica es que puede recopilar sumas, recuentos y mucho más con un solo paso a través de los datos en lugar de recorrer los datos con subselecciones o, peor aún, PL / SQL.

Parece confuso al principio, pero esta será la segunda naturaleza rápidamente. Nadie lo explica mejor que Tom Kyte. Así que el enlace de arriba es genial.

Por supuesto, leer la documentation es imprescindible.

Answer 3

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 de SQL, DEPT_COUNT devolverá el número de empleados dentro de ese departamento para cada registro de empleado. (Es como si estuviera des-nomalizando la tabla emp ; todavía devuelve 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 obtener los resultados de GROUP BY ( SUM , AVG , etc.) sin la agregación del conjunto de resultados.

Es útil cuando usa 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 lo usa en una estimación contra este salario de registros sin una selección secundaria, que es mucho más rápida.

Lea el artículo de AskTom vinculado para más detalles.

Answer 4

La palabra clave sobre partición es como si estuviéramos particionando los datos por creación de ID de cliente 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

Answer 5

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 para el respectivo departamento. En cuanto a deptno 10, tenemos 4 registros en la tabla y resultados similares para deptno 20 y 30 también.