sql - paginacion - Palabra clave Oracle ''Partition By'' y ''Row_Number''

row_number() over(partition by oracle 11g

Question

Tengo una consulta SQL escrita por otra persona y estoy tratando de averiguar qué hace. ¿Puede alguien explicar las palabras clave Partition By y Row_Number aquí y dar un ejemplo simple de ello en acción, así como por qué uno querría usarlo?

Un ejemplo de partición por:

(SELECT cdt.*,
        ROW_NUMBER ()
        OVER (PARTITION BY cdt.country_code, cdt.account, cdt.currency
              ORDER BY cdt.country_code, cdt.account, cdt.currency)
           seq_no
   FROM CUSTOMER_DETAILS cdt);

He visto algunos ejemplos en línea, son demasiado profundos.

¡Gracias por adelantado!

Answer 1

A menudo uso row_number () como una forma rápida de descartar registros duplicados de mis declaraciones seleccionadas. Solo agrega una cláusula where. Algo como...

select a,b,rn 
  from (select a, b, row_number() over (partition by a,b order by a,b) as rn           
          from table) 
 where rn=1;

Answer 2

Eso selecciona el número de fila por código de país, cuenta y moneda. Por lo tanto, las filas con el código de país "EE. UU.", La cuenta "XYZ" y la moneda "$ USD" obtendrán un número de fila asignado desde 1-n; lo mismo aplica para cualquier otra combinación de esas columnas en el conjunto de resultados.

Esta consulta es algo graciosa, porque la cláusula order by no hace absolutamente nada. Todas las filas en cada partición tienen el mismo código de país, cuenta y moneda, por lo que no hay orden de punto por esas columnas. Por lo tanto, los últimos números de fila asignados en esta consulta particular serán impredecibles.

Espero que ayude...

Answer 3

Sé que este es un hilo viejo, pero PARTICIÓN es el equivalente de GROUP BY not ORDER BY. ORDER BY en esta función es. . . ORDEN POR. Es solo una manera de crear unicidad a partir de la redundancia agregando un número de secuencia. O puede eliminar los otros registros redundantes mediante la cláusula WHERE al hacer referencia a la columna con alias para la función. Sin embargo, DISTINCT en la declaración SELECT probablemente lograría lo mismo en ese sentido.

Answer 4

PARTITION BY conjuntos segregados, esto le permite trabajar (ROW_NUMBER (), COUNT (), SUM (), etc.) en un conjunto relacionado de forma independiente.

En su consulta, el conjunto relacionado consta de filas con cdt.country_code similar, cdt.account, cdt.currency. Cuando realiza particiones en esas columnas y aplica ROW_NUMBER en ellas. Esas otras columnas en esa combinación / conjunto recibirán un número secuencial de ROW_NUMBER

Pero esa consulta es divertida, si su partición está compuesta por datos únicos y coloca un row_number en ella, solo producirá el mismo número. Es como hacer un ORDER BY en una partición que se garantiza que es única. Ejemplo, piense en GUID como una combinación única de cdt.country_code, cdt.account, cdt.currency

newid() produce GUID, entonces ¿qué esperarías con esta expresión?

select
   hi,ho,
   row_number() over(partition by newid() order by hi,ho)
from tbl;

... Bien, todas las particiones (ninguna fue particionada, cada fila está particionada en su propia fila) rows ''row_numbers están todos configurados en 1

Básicamente, debe particionar en columnas no únicas. ORDER BY en OVER necesitó la PARTITION BY para tener una combinación no única, de lo contrario, todos los row_numbers se convertirán en 1

Un ejemplo, estos son tus datos:

create table tbl(hi varchar, ho varchar);

insert into tbl values
(''A'',''X''),
(''A'',''Y''),
(''A'',''Z''),
(''B'',''W''),
(''B'',''W''),
(''C'',''L''),
(''C'',''L'');

Entonces esto es análogo a tu consulta:

select
   hi,ho,
   row_number() over(partition by hi,ho order by hi,ho)
from tbl;

¿Cuál será el resultado de eso?

HI  HO  COLUMN_2
A   X   1
A   Y   1
A   Z   1
B   W   1
B   W   2
C   L   1
C   L   2

¿Ves la combinación de HI HO? Las primeras tres filas tienen una combinación única, por lo tanto, se establecen en 1, las filas B tienen la misma W, por lo tanto, diferentes ROW_NUMBERS, del mismo modo con filas HI C.

Ahora, ¿por qué es necesario el ORDER BY ? Si el desarrollador anterior simplemente quiere poner un row_number en datos similares (por ejemplo, HI B, todos los datos son BW, BW), simplemente puede hacer esto:

select
   hi,ho,
   row_number() over(partition by hi,ho)
from tbl;

Pero, por desgracia, Oracle (y el servidor Sql también) no permite la partición sin ORDER BY ; mientras que en Postgresql, ORDER BY en PARTITION es opcional: http://www.sqlfiddle.com/#!1/27821/1

select
   hi,ho,
   row_number() over(partition by hi,ho)
from tbl;

Su ORDER BY en su partición parece un poco redundante, no debido a la falla del desarrollador anterior, alguna base de datos simplemente no permite PARTITION sin ORDER BY , es posible que no pueda encontrar una buena columna candidata para ordenar. Si las columnas PARTITION BY y ORDER BY son iguales, simplemente elimine ORDER BY, pero como algunas bases de datos no lo permiten, puede hacer esto:

SELECT cdt.*,
        ROW_NUMBER ()
        OVER (PARTITION BY cdt.country_code, cdt.account, cdt.currency
              ORDER BY newid())
           seq_no
   FROM CUSTOMER_DETAILS cdt

¿No puede encontrar una buena columna para ordenar datos similares? También puede ordenar aleatoriamente, los datos particionados tienen los mismos valores de todos modos. Puede usar GUID por ejemplo (usa newid() para SQL Server). Para que tenga el mismo resultado que el desarrollador anterior, es desafortunado que alguna base de datos no permita PARTITION sin ORDER BY

Aunque en realidad, me elude y no puedo encontrar una buena razón para poner un número en las mismas combinaciones (BW, BW en el ejemplo anterior). Da la impresión de que la base de datos tiene datos redundantes. De alguna manera me recordó a esto: ¿Cómo obtener un único registro de la misma lista de registros de la tabla? Sin restricción única en la tabla

Realmente parece arcano ver una PARTICIÓN POR con la misma combinación de columnas con ORDEN POR, no puede inferir fácilmente la intención del código.

Prueba en vivo: http://www.sqlfiddle.com/#!3/27821/6

Pero como dbaseman también notó, es inútil dividir y ordenar en las mismas columnas.

Usted tiene un conjunto de datos como este:

create table tbl(hi varchar, ho varchar);

insert into tbl values
(''A'',''X''),
(''A'',''X''),
(''A'',''X''),
(''B'',''Y''),
(''B'',''Y''),
(''C'',''Z''),
(''C'',''Z'');

Entonces usted PARTICIÓN POR hola, ho; y luego ORDEN POR hola, ho. No tiene sentido numerar datos similares :-) http://www.sqlfiddle.com/#!3/29ab8/3

select
   hi,ho,
   row_number() over(partition by hi,ho order by hi,ho) as nr
from tbl;

Salida:

HI  HO  ROW_QUERY_A
A   X   1
A   X   2
A   X   3
B   Y   1
B   Y   2
C   Z   1
C   Z   2

¿Ver? ¿Por qué es necesario poner números de fila en la misma combinación? ¿Qué analizará en triple A, X, en doble B, Y, en doble C, Z? :-)

Solo necesita usar PARTITION en una columna que no es única, luego ordena la columna única -ing de columna (s) no exclusiva (s). El ejemplo lo hará más claro:

create table tbl(hi varchar, ho varchar);

insert into tbl values
(''A'',''D''),
(''A'',''E''),
(''A'',''F''),
(''B'',''F''),
(''B'',''E''),
(''C'',''E''),
(''C'',''D'');

select
   hi,ho,
   row_number() over(partition by hi order by ho) as nr
from tbl;

PARTITION BY hi funciona en una columna no única, luego en cada columna con particiones, ordena en su columna única (ho), ORDER BY ho

Salida:

HI  HO  NR
A   D   1
A   E   2
A   F   3
B   E   1
B   F   2
C   D   1
C   E   2

Ese conjunto de datos tiene más sentido

Prueba en vivo: http://www.sqlfiddle.com/#!3/d0b44/1

Y esto es similar a su consulta con las mismas columnas en PARTITION BY y ORDER BY:

select
   hi,ho,
   row_number() over(partition by hi,ho order by hi,ho) as nr
from tbl;

Y esta es la salida:

HI  HO  NR
A   D   1
A   E   1
A   F   1
B   E   1
B   F   1
C   D   1
C   E   1

¿Ver? ¿sin sentido?

Prueba en vivo: http://www.sqlfiddle.com/#!3/d0b44/3

Finalmente, esta podría ser la consulta correcta:

SELECT cdt.*,
     ROW_NUMBER ()
     OVER (PARTITION BY cdt.country_code, cdt.account -- removed: cdt.currency
           ORDER BY 
               -- removed: cdt.country_code, cdt.account, 
               cdt.currency) -- keep
        seq_no
FROM CUSTOMER_DETAILS cdt