La partición de número de fila SQL lleva datos de rango al grupo
row_number()OVER(PARTITION BY COL1 ORDER BY COL2) significa agrupar por COL1, dentro del grupo mediante clasificación COL2, y cada conjunto de valores de clasificación interna calculados por esta función representa el orden de los números (el conjunto es consecutivamente único). La diferencia con Rownum es que el conjunto de resultados ordenado usando Rownum primero agrega Rownum a la pseudocolumna y luego ordena, mientras que la cláusula de clasificación incluida en esta función ordena primero y luego calcula el número de fila.
row_number() es similar a rownum, pero más potente (puede ordenar empezando por 1 en cada grupo).
rank() es una clasificación por salto Cuando hay dos segundos lugares, el siguiente es el cuarto lugar (también en cada grupo).
dense_rank()l es una disposición secuencial y todavía hay un tercer lugar después de los dos segundos lugares. Por el contrario, número_fila no tiene valores duplicados 🙂.
lag(arg1, arg2, arg3):
arg1 es la expresión devuelta por otras filas
arg2 es el desplazamiento de la partición de fila actual. Si desea devolver el número de fila anterior, el desplazamiento es un número positivo.
arg3 es el valor devuelto cuando el número representado por arg2 excede el rango de agrupación.
Mire algunas declaraciones SQL:
Declaración 1:
seleccione número_fila() sobre(ordenar por venta/cnt desc) como ordenar, venta/ cnt
de (
seleccione -60 como venta, 3 como cnt de unión dual
seleccione 24 como venta, 6 como cnt de unión dual
seleccione 50 como venta, 5 como cnt de unión dual
seleccione -20 como venta, 2 como cnt de unión dual
seleccione 40 como venta, 8 como cnt de dual );
Resultado:
ORDENAR VENTA/CNT
---------- ----------
1 10
2 5
3 4
4 -10
5 -20
Declaración 2: Consultar los salarios de los empleados, ordenados por departamento
SELECT ename, sal, row_number() over (partition by deptno order by sal desc) as sal _order from scott.emp;
Resultado de la ejecución:
ENAME SAL SAL_ORDER
------------------------ - ------ ---- ----------
REY 5000 1
CLARK 2450 2
MILLER 1300 3
SCOTT 3000 1
FORD 3000 2
JONES 2975 3
ADAMS 1100 4
SMITH 8005
BLAKE 2850 1
ALLEN 1600 2
TURNER 1500 3
WARD 1250 4
MARTIN 1250 5
JAMES 950 6
14 líneas seleccionadas.
Declaración 3: Consultar el salario máximo de cada departamento
select deptno, ename, sal from
(select deptno, ename, sal, row_number() over (partición por deptno orden por sal desc) as sal_order
de scott.emp) donde sal_order lt
Resultado de la ejecución:
DEPTNO ENAME SAL<; /p>
---------- -------------------- --- -------
10 KING 5000
20 SCOTT 3000
30 BLAKE 2850
3 filas seleccionadas.
Declaración 4:
Seleccione deptno, sal, rango() sobre (partición por deptno orden por sal) como ranking_order de scott.order por deptno;
Resultado de la ejecución:
DEPTNO SAL RANK_ORDER
---------- ---------- ----------
10 1300 1
10 2450 2
10 5000 3
20 800 1
20 1100 2
20 2975 3
20 3000 4
20 3000 4
30 950 1
30 1250 2
30 1250 2
30 1500 4
30 1600 5
30 2850 6
14 filas seleccionadas.
Declaración 5:
Seleccione deptno, sal, densa_rank() sobre (partición por deptno orden por sal) como densa_rank_order de scott.order por deptn Resultado de la ejecución:
DEPTNO SAL DENSE_RANK_ORDER
---------- ---------- ------- ------ ---
10 1300 1
10 2450 2
10 5000 3
20 800 1<
20 1100 2
20 2975 3
20 3000 4
20 3000 4
30 950 1
30 1250 2
30 1250 2
30 1500 3
30 1600 4
30 2850 5
La línea 14 está seleccionada.
Declaración 6:
Seleccione deptno, ename, sal, lag (ename, 1, null) sobre (partición por deptno orden por ename) como lag_ de scott.emp orden por deptno ;
Resultado de la ejecución:
DEPTNO ENAME SAL LAG_
---------- ----------- --------- ---------- --------------------
10 CLARK 2450
10 KING 5000 CLARK
10 MILLER 1300 KING
20 ADAMS 1100
20 FORD 3000 ADAMS
20 JONES 2975 FORD
20 SCOTT 3000 JONES
20 SMITH 800SCOTT
30 ALLEN 1600
30 BLAKE 2850 ALLEN
30 JAMES 950 BLAKE
30 MARTIN 1250 JAMES
30 TURNER 1500 MARTIN
30 WARD 1250 TURNER
La línea 14 ha sido seleccionado.