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.