SQLShack (Polski)

admin

wykonujemy obliczenia na danych za pomocą różnych zagregowanych funkcji, takich jak Max, Min i AVG. Za pomocą tych funkcji otrzymujemy pojedynczy wiersz wyjściowy. SQL Sever dostarcza funkcje RANK SQL do określania rank dla poszczególnych pól zgodnie z kategoriami. Zwraca zagregowaną wartość dla każdego uczestniczącego wiersza. Funkcje SQL RANK znane są również jako funkcje okien.

  • Uwaga: termin Windows nie odnosi się do systemu operacyjnego Microsoft Windows., Są to funkcje rangi SQL.

mamy następujące funkcje rangi.

  • ROW_NUMBER ()
  • RANK ()
  • DENSE_RANK ()
  • NTILE ()

w funkcjach SQL RANK używamy klauzuli OVER() do definiowania zbioru wierszy w zestawie wynikowym. Możemy również użyć klauzuli SQL PARTITION BY do zdefiniowania podzbioru danych w partycji. Możesz również użyć klauzuli Order by do sortowania wyników w porządku malejącym lub rosnącym.

zanim przejrzymy te funkcje SQL RANK, przygotujmy przykładowe dane., W tych przykładowych danych mamy Wyniki egzaminu dla trzech uczniów z matematyki, Przedmiotów Ścisłych i języka angielskiego.

mamy następujące przykładowe dane w tabeli ExamResult.

użyjmy każdej funkcji rangi SQL w nadchodzących przykładach.

ROW_Number() funkcja SQL RANK

używamy funkcji ROW_Number() funkcja SQL RANK, aby uzyskać unikalny numer sekwencyjny dla każdego wiersza w podanych danych. Daje rangę jeden dla pierwszego wiersza, a następnie zwiększa wartość o jeden dla każdego wiersza. Otrzymujemy różne stopnie dla rzędu o podobnych wartościach.,

wykonaj poniższe zapytanie, aby uzyskać rangę dla uczniów według ich ocen.,

1
2
3
4
5
SELECT Studentname,
Subject,
Marks,
ROW_NUMBER() OVER(ORDER BY Marks) RowNumber
FROM ExamResult;

By default, it sorts the data in ascending order and starts assigning ranks for each row., Na powyższym zrzucie ekranu otrzymujemy wiersz numer 1 dla znaków 50.

możemy określić kolejność malejącą za pomocą klauzuli Order By i odpowiednio zmienia ona rangę.,ae”>

1
2
3
4
5
SELECT Studentname,
Subject,
Marks,
ROW_NUMBER() OVER(ORDER BY Marks desc) RowNumber
FROM ExamResult;

RANK() SQL RANK Function

We use RANK() SQL Rank function to specify rank for each row in the result set., Mamy wyniki dla trzech przedmiotów. Chcemy uszeregować wyniki uczniów według ich ocen w przedmiotach. Na przykład na poniższym zrzucie ekranu uczennica Isabella otrzymała najwyższe noty z przedmiotu angielskiego i najniższe noty z przedmiotu matematyki. Zgodnie z ocenami, Isabella otrzymuje pierwszą pozycję w języku angielskim i 3 miejsce w przedmiotach matematycznych.

wykonaj następujące zapytanie, aby uzyskać ten wynik.,v id=”c5a1f1b829″>

1
2
3
4
5
6
7
SELECT Studentname,
Subject,
Marks,
RANK() OVER(PARTITION BY Studentname ORDER BY Marks DESC) Rank
FROM ExamResult
ORDER BY Studentname,
Rank;

Let’s execute the following query of SQL Rank function and look at the result set., W tym zapytaniu nie określiliśmy klauzuli SQL PARTITION By, aby podzielić dane na mniejszy podzbiór. Używamy funkcji SQL Rank z klauzulą over NA klauzuli Marks (w porządku malejącym), aby uzyskać rangi dla odpowiednich wierszy.,div id=”7d9d5b54ae”>

1
2
3
4
5
6
SELECT Studentname,
Subject,
Marks,
RANK() OVER(ORDER BY Marks DESC) Rank
FROM ExamResult
ORDER BY Rank;

In the output, we can see each student get rank as per their marks irrespective of the specific subject., Na przykład najwyższa i najniższa liczba punktów w kompletnym zestawie wyników wynosi odpowiednio 90 i 50. W zestawie wyników najwyższa ocena otrzymuje rangę 1, a najniższa – rangę 9.

Jeśli dwóch uczniów otrzyma te same oceny (w naszym przykładzie, wiersze 4 i 5), ich stopnie również są takie same.

DENSE_RANK() funkcja rangi SQL

używamy funkcji DENSE_RANK (), aby określić unikalny numer rangi w partycji zgodnie z podaną wartością kolumny. Jest ona podobna do funkcji rangi z niewielką różnicą.,

w funkcji rangi SQL DENSE_RANK(), jeśli mamy zduplikowane wartości, SQL przydziela różne rangi również tym wierszom. Idealnie, powinniśmy uzyskać tę samą rangę dla zduplikowanych lub podobnych wartości.

wykonajmy następujące zapytanie za pomocą funkcji DENSE_RANK ().,

1
2
3
4
5
6
wybierz Studentname,
przedmiot
Marx
DENSE_RANK() nad(zlecenie wykonywane w kolejności malejącej) ranking
od ExamResult
kolejność w rankingu;

w weekend, można zobaczyć, tytuł u nas takie same, jak dla Lily i Isabella kto zdobył 70 znaków.,

użyjmy funkcji DENSE_RANK w połączeniu z klauzulą SQL PARTITION BY.,v id=”2d0aafc924″>

1
2
3
4
5
6
7

select studentname,
subject,
marks,
dense_rank() over(Partition by subject order by marks desc) rank
from examresult
order by studentname,
rank;

nie mamy dwóch uczniów o podobnych znakach, dlatego w tym przypadku ustawiamy wynik podobny do funkcji rank.,

zaktualizujmy znak ucznia poniższym zapytaniem i powtórzmy zapytanie.

1
update examresult set Marks=70 where studentname=’Isabella' and subject=’maths'

widzimy, że w grupie studentów Isabella otrzymała podobne oceny w matematyka i przedmioty ścisłe., Ranga jest również taka sama dla obu przedmiotów w tym przypadku.

zobaczmy różnicę między funkcją RANK() i Dense_rank() SQL Rank za pomocą następującego zapytania.,b829″>

1
2
3
4
5
6
7
SELECT Studentname,
Subject,
Marks
DENSE_RANK() OVER(PARTITION BY studentName ORDER BY Marks ) Rank
FROM ExamResult
ORDER BY Studentname,
Rank;

Na wyjściu można zobaczyć podział na wyjściu funkcji rangi wewnątrz partycji., Nie mamy żadnej luki w funkcji DENSE_RANK.

na poniższym zrzucie ekranu możesz zobaczyć, że Isabella ma podobne liczby w obu tematach. Funkcja rangi przypisuje rangę 1 dla podobnych wartości, jednak wewnętrznie ignoruje rangę drugą, a następny wiersz otrzymuje rangę trzecią.

w funkcji Dense_Rank zachowuje rangę i nie daje żadnych odstępów dla wartości.

funkcja NTILE(N) SQL RANK

używamy funkcji NTILE(N) do dystrybucji liczby wierszy w określonej (N) liczbie grup., Każda grupa wierszy otrzymuje rangę zgodnie z podanym warunkiem. Musimy określić wartość dla żądanej liczby grup.

w moim przykładzie mamy dziewięć rekordów w tabeli wyników egzaminu. NTILE (2) pokazuje, że w wyniku wymagamy grupy dwóch rekordów.,

1
2
3
4
5
SELECT *,
NTILE(2) OVER(
ORDER BY Marks DESC) Rank
FROM ExamResult
ORDER BY rank;

In the output, we can see two groups. Group 1 contains five rows, and Group 2 contains four rows.,

podobnie NTILE(3) dzieli liczbę wierszy trzech grup mających trzy rekordy w każdej grupie.,

1
2
3
4
5
SELECT *,
NTILE(3) OVER(
ORDER BY Marks DESC) Rank
FROM ExamResult
ORDER BY rank;

We can use SQL PARTITION BY clause to have more than one partition., W poniższym zapytaniu każda partycja na temat jest podzielona na dwie grupy.,

1
2
3
4
SELECT *,
NTILE(2) OVER(PARTITION BY subject ORDER BY Marks DESC) Rank
FROM ExamResult
ORDER BY subject, rank;

Practical usage of SQL RANK functions

We can use SQL RANK function to fetch specific rows from the data., Załóżmy, że chcemy uzyskać dane uczniów z rang od 1 do 3. W poniższym zapytaniu używamy common table expressions (CTE), aby uzyskać dane za pomocą funkcji ROW_NUMBER (), a następnie filtrujemy wynik z CTE, aby spełnić nasz warunek.,d>

ze StudentRanks jako
(
SELECT *, ROW_NUMBER() OVER( ORDER BY Marks) as Ranks
FROM ExamResult
SELECT StudentName , Marks
od studentranks
gdzie szeregi >= 1 i szeregi <=3
uporządkuj według Rang

div

możemy użyć polecenia offset fetch począwszy od SQL Server 2012, aby pobrać określoną liczbę rekordów.,v id=”0fa57edc44″>

ze StudentRanks jako
(
SELECT *, ROW_NUMBER() OVER( ORDER BY Marks) as Ranks
FROM ExamResult
)
SELECT StudentName , Marks
FROM StudentRanks

kolejność według Rang offset 1 wiersze pobrać następne 3 wiersze tylko;

szybkie podsumowanie funkcji SQL Rank

numer wiersza

przypisuje sekwencyjny numer rang do każdego unikalnego rekordu.,

RANK

przypisuje numer rank do każdego wiersza na partycji. Pomija liczbę dla podobnych wartości.

Dense_RANK

przypisuje numer rangi do każdego wiersza na partycji. Nie pomija liczby dla podobnych wartości.

NTILE(n)

dzieli liczbę wierszy zgodnie z podaną partycją i przypisuje unikalną wartość w partycji.,

podsumowanie

w tym artykule zbadaliśmy funkcje rangi SQL i różnice między tymi funkcjami. Programiści sql dobrze znają te funkcje, aby dobrze badać i zarządzać swoimi danymi. Jeśli masz jakieś uwagi lub pytania, zostaw je w komentarzach poniżej.,

  • Autor
  • Ostatnie posty
jako MCSA certified I Microsoft Certified Trainer w Gurgaon, Indie, z 13-letnim doświadczeniem, Rajendra pracuje dla wielu dużych firm koncentrując się na optymalizacji wydajności, monitorowania, wysokiej dostępności i strategii odzyskiwania po awarii i wdrożenia., Jest autorem setek autorytatywnych artykułów na temat SQL Server, Azure, MySQL, Linux, Power BI, Performance tuning, AWS/Amazon RDS, Git i pokrewnych technologii, które do tej pory obejrzało ponad 10 milionów czytelników.
jest twórcą jednego z największych darmowych internetowych zbiorów artykułów na jeden temat, z jego 50-częściową serią na SQL Server Always On Availability Groups. W oparciu o swój wkład w społeczność SQL Server został wyróżniony różnymi nagrodami, w tym prestiżowym „najlepszym autorem roku” nieprzerwanie w 2020 i 2021 w sqlshack.,
Raj jest zawsze zainteresowany nowymi wyzwaniami, więc jeśli potrzebujesz pomocy Konsultacyjnej na dowolny temat poruszany w jego pismach, można do niego dotrzeć w rajendra.gupta16@gmail.,com
Zobacz wszystkie posty Rajendra Gupta

najnowsze posty Rajendra Gupta (Zobacz wszystkie)
  • Wykonywanie mniejszych i większych aktualizacji wersji dla AWS RDS SQL Server – 29 stycznia 2021
  • wdrażanie instancji AWS RDS PostgreSQL – 27 stycznia 2021
  • migracja lokalnych baz danych SQL do AWS RDS SQL Server za pomocą AWS DMS-25 stycznia 2021

Dodaj komentarz

Twój adres email nie zostanie opublikowany. Pola, których wypełnienie jest wymagane, są oznaczone symbolem *