logo

TechCodeview

Funció SQL Server Row_Number amb PARTITION BY

La funció de número de fila és una de les funcions de finestra utilitzades per assignar el número de fila a les files de cadascuna de les particions en l'ordre en què s'han ordenat a la partició mitjançant el PARTICIÓ clàusula, PARTICIÓ només el COMANDA la clàusula es pot utilitzar dins de la clàusula OVER en aquest cas, tota la taula es considerarà com una partició. Però la clàusula ORDER BY és obligatòria per utilitzar el ROW_NUMBER() ja que organitza les files a les particions en aquest ordre lògic i, posteriorment, la funció ROW_NUMBER() pot assignar el número de fila. A cada partició, el número de fila comença des de l'1.

Sintaxi:



ROW_NUMBER ( )

OVER ( [ PARTITION BY col_1,col_2… ] ORDER BY col_3,col_4.. ASC o DESC) AS nom_columna

Components de la funció ROW_NUMBER().



  • PARTICIÓ PER: Aquesta és la subclàusula principal que divideix les files en finestres i per a cada fila, els valors de funcions de la finestra es calcularà aplicat.
  • DEMANAT PER: S'utilitza per ordenar les files de la partició, per defecte és l'ordre ascendent. Sense la clàusula ORDER BY, la funció ROW_NUMBER() no funciona.
  • Tipus de retorn: El tipus de retorn de la funció ROW_NUMBER() és BIGINT.

Ús de la funció ROW_NUMBER().

Pas 1: Creeu la base de dades Geeksforgeeks mitjançant la següent consulta SQL:

Consulta 

CREATE DATABASE Geeksforgeeks>

Utilitzeu la base de dades GFG.



Consulta 

USE techcodeview.com>

Pas 3: Elaborar una taula amb els alumnes de les diferents seccions amb les seves notes totals sobre 500.

Consulta 

CREATE TABLE studentsSectionWise( studentId INT, studentName VARCHAR(100), sectionName VARCHAR(50), studentMarks INT  );>

Pas 4: Inseriu les files a la taula:

Consulta 

INSERT INTO studentsSectionWise VALUES (1, 'Geek1','A',461), (1, 'Geek2','B',401), (1, 'Geek3','C',340), (2, 'Geek4','A',446), (2, 'Geek5','B',361), (2, 'Geek6','C',495), (3, 'Geek7','A',436), (3, 'Geek8','B',367), (3, 'Geek9','C',498), (4, 'Geek10','A',206), (4, 'Geek11','B',365), (4, 'Geek12','C',485), (5, 'Geek13','A',446), (5, 'Geek14','B',368),  (5, 'Geek15','C',295),  (6, 'Geek16','C',495);>

Pas 5: Consulta la taula

SELECT * FROM studentsSectionWise>

Sortida:

estudiantsSeccióWise-Taula

StudentSectionWise taula

java llegir fitxer csv

S'utilitza la clàusula ORDER_BY amb la funció ROW_NUMBER().

Utilitzar simplement la clàusula ORDER BY amb ascendent o descendent considera tota la taula com una sola partició.

Comprovem el rankNumber dels estudiants que utilitzen la funció ROW_NUMBER() amb la clàusula ORDER BY que inclou totes les seccions: 

SELECT * , ROW_NUMBER() OVER ( ORDER BY studentMarks DESC) AS rankNumber FROM studentsSectionWise>

Explicació: A la consulta hem utilitzat la clàusula ORDER BY amb les StudentMarks que s'ordena en ordre DESC.

Sortida:

sortida

Sortida

Explicació: En el resultat podem veure que la taula sencera es considera com una sola partició i el nombre de rangNumber està en ordre creixent amb els StudentMarks en ordre descendent.

S'utilitza PARTITION_BY amb la funció ROW_NUMBER().

Utilitzar simplement la clàusula PARTITION BY divdies i la taula en diferents particions basades en el nom de la columna i després la clàusula ORDER BY amb ascendent o descendent considera tota la taula com una sola partició. I llavors el ROW_NUMBER() funciona cada número únic començant per 1 a cada partició.

Comprovem la secció de classificació utilitzant la funció ROW_NUMBER() amb les clàusules PARTITION BY i ORDER BY. 

SELECT * , ROW_NUMBER() OVER (PARTITION BY sectionName ORDER BY studentMarks DESC) AS rankNumber FROM studentsSectionWise;>

Explicació: En la consulta que hem utilitzat clàusula PARTITION BY per particionar la taula a la seccióName wise i la clàusula ORDER BY amb les StudentMarks que s'ordena a DESC.

Sortida:

sortida

Sortida

Explicació: En el resultat podem veure que tenim particions dividides per la secció Nom i després, a cada partició, els estudiants s'ordenen en funció de les notes d'estudiants en l'ordre DESC, la funció ROW_NUMBER() se li assigna el número de fila a partir de cada partició i s'incrementa. .

Comprensió de ROW_NUMBER() sense PARTITION BY

La funció ROW_NUMBER() d'SQL Server és una eina flexible que us permet proporcionar a cada fila d'un conjunt de resultats un número de fila únic. És igual d'eficaç quan s'utilitza sense la clàusula PARTITION BY, tot i que s'utilitza sovint juntament amb ella per agrupar i classificar dins de particions. Les possibilitats i els usos de ROW_NUMBER() sense la clàusula PARTITION BY es tractaran en aquest article.

Sintaxi:

SELECCIONA

ROW_NUMBER() MÉS ( DEMANAT PER YourOrderByColumn) AS RowNum,

Altres columnes

DE

YourTableName;

Comprovem la secció de classificació utilitzant la funció ROW_NUMBER() sense les clàusules PARTITION BY i ORDER BY. 

SELECT * , ROW_NUMBER() OVER (ORDER BY studentMarks DESC) AS rankNumber FROM studentsSectionWise>

Sortida:

sortida

Sortida

S'utilitza ROW_NUMBER() per obtenir el subconjunt de files

La funció ROW_NUMBER() es pot utilitzar per obtenir el subconjunt de files de la taula mitjançant el CTE que pot ser útil en el cas de la paginació de les dades.

Comprovem els 2 primers classificats de cada classe utilitzant el CTE (Expressió de taula comuna) amb la funció ROW_NUMBER(): 

WITH topTwoRankers AS  (   SELECT * , ROW_NUMBER() OVER (PARTITION BY sectionName ORDER BY studentMarks DESC) AS rankNumber  FROM studentsSectionWise  )  SELECT *  FROM topTwoRankers  WHERE rankNumber <=2;>

Explicació: Aquí, a la consulta, hem utilitzat l'expressió CTE per obtenir la taula temporal de la consulta utilitzada anteriorment i hem utilitzat la consulta SELCT externa per obtenir els estudiants el nombre de rang dels quals és menor o igual a 2.

Sortida:

sortida

Sortida

Explicació: En el resultat podem veure que tenim els 2 primers classificadors de cada secció que són el subconjunt de la taula obtingut a partir de la consulta que es mostra a l'exemple anterior.

Avantatges deROW_NUMBER()>Amb PARTITION BY

  1. Permet una classificació avançada de particions.
  2. Habilita la numeració única de files dins de grups específics.
  3. Permet una major flexibilitat en circumstàncies que impliquen una anàlisi extensa.

Avantatges deROW_NUMBER()>Sense PARTICIÓ PER

  1. Senzill i senzill.
  2. facilita la classificació dels elements segons una columna concreta.
  3. Pot oferir un millor rendiment, especialment per a conjunts de resultats més petits.

Conclusió

És molt útil quan volem tenir la seqüenciació sobre la base de la partició i fer les operacions necessàries. Utilitzant clàusula ORDER BY és obligatori mentre s'utilitza la funció ROW_NUMBER(). També utilitzant el CTE podem obtenir els subconjunts de la taula de les particions ordenades. El principal cas d'ús del ROW_NUMBER() és la classificació de les files sobre la base de diferents particions, que és molt útil per a l'anàlisi de dades.