logo

TechCodeview

SQL Server Row_Number-funktion med PARTITION BY

Rækkenummerfunktionen er en af ​​vinduesfunktionerne, der bruges til at tildele rækkenummeret til rækkerne i hver af partitionerne i den rækkefølge, de blev sorteret i partitionen ved hjælp af SKILLEVÆG klausul, PARTITION kun BESTILLE klausul kan bruges inde i OVER-klausulen i sådanne tilfælde vil hele tabellen blive betragtet som én partition. Men ORDER BY-klausulen er obligatorisk for at bruge ROW_NUMBER() funktion, da den arrangerer rækkerne i partitionerne i den logiske rækkefølge, og senere ROW_NUMBER() funktion kan tildele rækkenummeret. I hver partition starter rækkenummeret fra 1.

Syntaks:



ROW_NUMBER ( )

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

Komponenter af ROW_NUMBER() funktion



  • OPDELING AF: Dette er den vigtigste underklausul, der opdeler rækkerne i vinduer, og for hver række er værdierne af vinduesfunktioner anvendt vil blive beregnet.
  • BESTIL AF: Dette bruges til at sortere rækkerne i partitionen, som standard er det den stigende rækkefølge. Uden ORDER BY-sætningen virker ROW_NUMBER()-funktionen ikke.
  • Returtype: Returtypen for ROW_NUMBER()-funktionen er BIGINT.

Brug af ROW_NUMBER()-funktionen

Trin 1: Opret databasen Geeksforgeeks ved at bruge følgende SQL-forespørgsel:

Forespørgsel 

CREATE DATABASE Geeksforgeeks>

Brug GFG-databasen.



Forespørgsel 

USE techcodeview.com>

Trin 3: Lav en tabel med eleverne fra forskellige sektioner med deres samlede karakterer ud af 500.

Forespørgsel 

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

Trin 4: Indsæt rækkerne i tabellen:

Forespørgsel 

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);>

Trin 5: Tjek tabellen

SELECT * FROM studentsSectionWise>

Produktion:

studerendeSectionWise-Tabel

studerendeSectionWise tabel

tiger løve forskel

Bruger ORDER_BY klausul med ROW_NUMBER() funktion

Ved blot at bruge ORDER BY-sætningen med stigende eller faldende betragtes hele tabellen som kun én partition.

Lad os kontrollere rankNumber for eleverne ved hjælp af ROW_NUMBER()-funktionen med ORDER BY-sætningen inklusive alle sektioner: 

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

Forklaring: I forespørgslen har vi brugt ORDER BY-klausulen med studentsMarks som er ordnet i DESC rækkefølge.

Produktion:

produktion

Produktion

Forklaring: I resultatet kan vi se, at hele tabellen betragtes som en enkelt partition, og rankNumber-tallet er i stigende rækkefølge med studentsMarks i faldende rækkefølge.

Bruger PARTITION_BY med ROW_NUMBER() funktion

Ved blot at bruge PARTITION BY-klausulen opdeles og tabellen i forskellige partitioner baseret på kolonnenavnet og derefter ORDER BY-klausulen med stigende eller faldende, betragtes hele tabellen kun som én partition. Og så fungerer ROW_NUMBER() hvert unikt tal fra 1 i hver partition.

Lad os tjekke rangeringssektionen med funktionen ROW_NUMBER() med PARTITION BY og ORDER BY-klausulerne. 

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

Forklaring: I den forespørgsel, vi har brugt PARTITION BY klausul at opdele tabellen på afsnittet Navn wise og ORDER BY klausulen med studentsMarks, som er ordnet i DESC.

Produktion:

produktion

Produktion

Forklaring: I resultatet kan vi se, at vi har partitioner divideret med afsnittet Navn, og derefter i hver partition er eleverne ordnet på basis af elevmærker i DESC-rækkefølgen, ROW_NUMBER()-funktionen tildelt rækkenummeret startende fra i hver partition, hvor det bliver inkrementeret .

Forstå ROW_NUMBER() Uden PARTITION BY

SQL Servers ROW_NUMBER() funktion er et fleksibelt værktøj, der giver dig mulighed for at give hver række i et resultatsæt et unikt rækkenummer. Det er lige så effektivt, når det bruges uden PARTITION BY-klausulen, selvom det ofte bruges sammen med det til gruppering og rangering inden for partitioner. Mulighederne og anvendelserne af ROW_NUMBER() uden PARTITION BY-sætningen vil blive diskuteret i denne artikel.

Syntaks:

VÆLG

ROW_NUMBER() OVER ( BESTIL EFTER YourOrderByColumn) SOM RowNum,

Andre kolonner

FRA

DitTabelnavn;

Lad os tjekke rangeringssektionen med funktionen ROW_NUMBER() uden PARTITION BY og ORDER BY-sætningerne. 

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

Produktion:

produktion

Produktion

Brug af ROW_NUMBER() til at få delmængden af ​​rækker

Funktionen ROW_NUMBER() kan bruges til at hente undersættet af rækker fra tabellen ved hjælp af CTE, hvilket kan være nyttigt i tilfælde af paginering af dataene.

Lad os tjekke de 2 bedste ranglister i hver klasse ved hjælp af CTE (Common Table Expression) med ROW_NUMBER() funktion: 

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

Forklaring: Her i forespørgslen har vi brugt CTE-udtrykket til at hente den midlertidige tabel fra forespørgslen ovenfor, og vi har brugt den ydre SELCT-forespørgsel til at få de elever, hvis rangnummer er mindre end eller lig med 2.

Produktion:

produktion

Produktion

Forklaring: I resultatet kan vi se, at vi har de øverste 2 ranglister fra hver sektion, som er undergruppen af ​​tabellen, der er opnået fra forespørgslen vist i det foregående eksempel.

Fordele vedROW_NUMBER()>Med OPDELING AF

  1. Giver mulighed for avanceret partitionsrangering.
  2. Aktiverer unik rækkenummerering inden for specifikke grupper.
  3. Giver mulighed for større fleksibilitet under omstændigheder, der involverer omfattende analyser.

Fordele vedROW_NUMBER()>Uden OPDELING AF

  1. Ligetil og enkelt.
  2. gør det nemt at rangordne elementer efter en bestemt kolonne.
  3. Kan give bedre ydeevne, især for mindre resultatsæt.

Konklusion

Det er meget nyttigt, når vi ønsker at have sekvenseringen på basis af partition og udføre de nødvendige operationer. Ved brug af ORDER BY klausul er obligatorisk, mens du bruger ROW_NUMBER()-funktionen. Også ved at bruge CTE kan vi få undersæt af tabellen fra de bestilte partitioner. Den primære anvendelse af ROW_NUMBER() er rangering af rækkerne på basis af forskellige partitioner, hvilket er meget nyttigt til dataanalyseformål.