18 lipca, 2020

SQLShack (Polski)

W tym artykule omówimy funkcja SQL ROW_NUMBER. To jest kontynuacja podstawowej serii SQL. W tym przewodniku wyjaśnię, o co chodzi w funkcji okna, i zobaczysz przykładowe przykłady, aby zrozumieć koncepcje stojące za funkcją SQL ROW_NUMBER.

Wprowadzenie

Najbardziej Powszechnie używaną funkcją w SQL Server jest funkcja SQL ROW_NUMBER. Funkcja SQL ROW_NUMBER jest dostępna w SQL Server 2005 i nowszych wersjach.

ROW_NUMBER dodaje unikalną, rosnącą liczbę do siatki wyników. Kolejność, w jakiej stosowane są numery wierszy, jest określana przez wyrażenie ORDER BY. W większości przypadków w wyrażeniu ORDER BY określa się co najmniej jedną kolumnę, ale można użyć bardziej złożonych wyrażeń, a nawet zapytania podrzędnego. Tworzy więc stale rosnącą wartość całkowitą i zawsze zaczyna się od 1, a kolejne wiersze otrzymują następną wyższą wartość.

Możesz go również używać z klauzulą PARTITION BY. Ale kiedy przekracza granicę lub granicę partycji, resetuje licznik i zaczyna od 1. Tak więc partycja może mieć wartości 1, 2, 3 itd., A drugie partycje ponownie rozpoczynają licznik od 1, 2, 3… i tak dalej i tak dalej.

Podstawy:

  1. Funkcja SQL ROW_NUMBER jest nietrwałym generowaniem sekwencji wartości tymczasowych i jest obliczana dynamicznie podczas wykonywania zapytania.
  2. Nie ma gwarancji, że wiersze zwrócone przez zapytanie SQL za pomocą funkcji SQL ROW_NUMBER będą uporządkowane dokładnie tak samo przy każdym wykonaniu.
  3. Funkcje ROW_NUMBER i RANK są podobne. Wynik ROW_NUMBER to sekwencja wartości zaczynająca się od 1 z przyrostem o 1, ale podczas gdy funkcja POZYCJA, wartości są również zwiększane o 1, ale wartości będą się powtarzać dla remisów.
  4. Jeśli doświadczenie z Oracle, to ROWNUM jest Ci bardziej znane. To jest pseudokolumna. Zaczyna się od 1 i schodzi w dół, zwiększając o jeden, aż do końca tabeli.
  5. Funkcja SQL ROW_NUMBER ma charakter dynamiczny i możemy resetować wartości za pomocą klauzuli PARTITION BY.
  6. Klauzula ORDER BY zapytania i klauzula ORDER BY funkcji OVER klauzula nie ma ze sobą nic wspólnego.

Składnia

1
2

ROW_NUMBER ()
OVER (] order_by_clause col1, col2 ..)

ROW_NUMBER

ROW_NUMBER, po którym następuje funkcja OVER a następnie w nawiasach użyj klauzuli ORDER BY. Wymagane jest użycie klauzuli ORDER BY, aby narzucić porządek dla zestawu wyników.

OVER

Klauzula OVER definiuje okno lub zestaw wierszy, które okno funkcja działa, więc zrozumienie tego jest naprawdę ważne. Możliwe elementy klauzuli OVER to ORDER BY i PARTITION BY.

Wyrażenie ORDER BY klauzuli OVER jest obsługiwane, gdy wiersze muszą być ustawione w określony sposób, aby funkcja działała.

1
PARTITION BY value_expression1

PARTITION BY

Klauzula Partition By jest opcjonalna. Określając wartość, dzieli zestaw wyników utworzony przez klauzulę FROM na partycje, do których jest stosowana funkcja SQL ROW_NUMBER. Wartości określone w klauzuli PARTITION definiują granice zestawu wyników. Jeśli klauzula PARTITION BY nie jest określona, to klauzula OVER działa na wszystkich wierszach tabeli wynikowej jako pojedynczym zestawie danych. Ta klauzula może składać się z jednej lub więcej kolumn, bardziej złożonego wyrażenia lub nawet zapytania podrzędnego.

order_by_clause

Klauzula Order by jest klauzulą obowiązkową. Określa kolejność i powiązanie wartości tymczasowej z wierszami określonej partycji. Klauzula ORDER BY jest wyrażeniem klauzuli OVER i określa, w jaki sposób wiersze muszą być ułożone w określony sposób dla funkcji.

Demo

W tej sekcji Przyjrzyjmy się funkcji SQL ROW_NUMBER. W całej demonstracji użyłem bazy danych AdventureWorks2016.

Jak używać ROW_NUMBER w zapytaniach SQL

W poniższych przykładach zobaczymy użycie klauzuli OVER.

Pozwól nam uzyskać listę wszystkich klientów, przewidując takie kolumny, jak SalesOrderID, OrderDate, SalesOrderNumber, SubTotal, TotalDue i RowNum. Funkcja Row_Number jest stosowana z kolejnością kolumny CustomerID.Wartość tymczasowa zaczyna się od 1 przypisanej na podstawie kolejności IDklienta, a wartości są kontynuowane do ostatnich wierszy tabeli. Kolejność CustomerID nie jest gwarantowana, ponieważ w zapytaniu nie określamy klauzuli ORDER BY.

1
2
3
4
5
6
7
8
9
10
11

USE AdventureWorks2016;
GO
SELECT ROW_NUMBER () OVER (
ORDER BY CustomerID) AS RowNum,
CustomerID,
SalesOrderID,
OrderDate,
SalesOrderNumber,
SubTotal,
TotalDue
FROM Sales.SalesOrderHeader;

Jak używać klauzuli Order by

W poniższym przykładzie zastosowano klauzulę ORDER BY w zapytaniu. Klauzula ORDER BY w kwerendzie zastosowanej w kolumnie SalesOrderID. Widzimy, że wiersze w wynikach są nadal uporządkowane i zwrócone. Row_Number jest nadal stosowany do CustomerID. Dane wyjściowe wskazują, że ORDER BY zapytania i ORDER BY klauzuli OVER są niezależne od danych wyjściowych.

1
2
3
4
5
6
7
8
9
10
11
12

USE AdventureWorks2016;
GO
SELECT ROW_NUMBER () OVER (
ORDER BY CustomerID) AS RowNum,
CustomerID,
SalesOrderID,
OrderDate,
SalesOrderNumber,
SubTotal,
TotalDue
FROM Sales.SalesOrderHeader
ORDER BY SalesOrderID;

Jak używać wielu kolumn z klauzulą OVER

W poniższym przykładzie widać, że wymieniliśmy customerID i OrderDate w klauzuli ORDER BY. Daje to szczegóły klienta z najnowszymi szczegółami zamówienia wraz z sekwencją numerów przypisanych do całego zestawu wyników.

1
2
3
4
5
6
7
8
9
10

USE AdventureWorks2016;
GO
SELECT ROW_NUMBER () OVER (ORDER BY CustomerID, OrderDate DESC) AS RowNum,
CustomerID,
SalesOrderID,
OrderDate,
SalesOrderNumber,
SubTotal,
TotalDue
FROM Sales.SalesOrderHeader

Jak używać funkcji SQL ROW_NUMBER z PARTITION

W poniższym przykładzie zastosowano klauzulę PARTITION BY w polach CustomerID i OrderDate. Na wyjściu widać, że klient 11019 ma trzy zamówienia na miesiąc 2014-czerwiec. W takim przypadku partycja jest wykonywana na więcej niż jednej kolumnie.

Partycja jest połączeniem OrderDate i CustomerID. Row_Number zacznie się od nowa dla każdej unikalnej kombinacji OrderDate i CustomerID. W ten sposób łatwo jest znaleźć klienta, który złożył więcej niż jedno zamówienie tego samego dnia.

1
2
3
4
5
6
7
8
9
10
11
12

USE AdventureWorks2016;
GO
SELECT ROW_NUMBER () OVER (PARTITION BY CustomerID,
DATEADD (MONTH, DATEDIFF (MONTH, 0, OrderDate), 0)
ORDER BY SUBTotal DESC) AS MonthlyOrders,
CustomerID,
SalesOrderID,
OrderDate,
SalesOrderNumber,
SubTotal,
TotalDue
FROM Sales.SalesOrderHeader;

Jak zwrócić podzbiór wierszy za pomocą CTE i ROW_NUMBER

W poniższym przykładzie przeanalizujemy SalesOrderHeader, aby wyświetlić pięć największych zamówień złożonych przez każdego klienta co miesiąc. Za pomocą funkcji Month manipuluje się kolumnami orderDate w celu pobrania części miesiąca.W ten sposób sprzedaż odpowiadająca konkretnemu miesiącowi (OrderDate) wraz z klientem (CustomerID) jest podzielona na partycje.

Aby wyświetlić pięć największych zamówień w każdym miesiącu dla każdego klienta, używany jest CTE. Na danych partycji jest tworzone okno i przypisywane do niego wartości, a następnie wywoływane jest CTE w celu pobrania największych zamówień.

1
2
3
4
5
6
7
8
9
10
11
12

Z cte
AS (SELECT ROW_NUMBER OVER (PARTITION BY customerID, MONTH (OrderDate) ORDER BY SUBTotal DESC, TotalDue DESC) AS ROW_NUM,
CustomerID,
MONTH (OrderDate) Month,
SubTotal,
TotalDue,
OrderDate
FROM Sales.SalesOrderHeader
)
SELECT *
FROM cte
WHERE ROW_NUM < = 5

Podsumowanie

Jak dotąd szczegółowo omówiliśmy funkcję SQL ROW_NUMBER. Omówiliśmy kilka przykładów, od prostych do złożonych. Omówiliśmy również, jak używać funkcji SQL ROW_NUMBER z CTE (Common Table Expressions). W większości przypadków zawsze zobaczysz klauzulę over z każdą funkcją okna.

Klauzula over definiuje okno, które widzi każdy wiersz. W klauzuli over znajduje się partycja by, znowu jest obsługiwana przez każdą funkcję okna, po której następuje klauzula order by. Na razie to wszystko… Mam nadzieję, że podobał Ci się ten artykuł.

  • Autor
  • Najnowsze posty
Jestem technologiem baz danych i mam ponad 11 lat bogatego, praktycznego doświadczenia w technologiach baz danych. Posiadam certyfikat Microsoft Certified Professional oraz tytuł magistra aplikacji komputerowych.
Moją specjalnością jest projektowanie & wdrażania rozwiązań wysokiej dostępności i międzyplatformowej migracji bazy danych. Obecnie pracujemy nad technologiami SQL Server, PowerShell, Oracle i MongoDB.
Wyświetl wszystkie posty Prashantha Jayarama

Najnowsze posty Prashantha Jayarama (zobacz wszystkie)
  • Krótkie omówienie audytu bazy danych w SQL – 28 stycznia 2021
  • Jak skonfigurować usługę Azure Data Sync między bazami danych Azure SQL a lokalnym programem SQL Server – 20 stycznia 2021 r.
  • Jak wykonywać operacje importu / eksportu bazy danych Azure SQL za pomocą programu PowerShell – 14 stycznia , 2021

admin
0

Dodaj komentarz

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

Archiwa

Najnowsze wpisy