SELECT – OVER Clause (Transact-SQL) (Polski)

• 08/11/2017
• 17 minut na przeczytanie
• • V
  • L
  • c
  • j
  • M
  • +11

Dotyczy: SQL Server (wszystkie obsługiwane wersje) Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Równoległa hurtownia danych

Określa partycjonowanie i kolejność zestawu wierszy przed zastosowaniem skojarzonej funkcji okna. Oznacza to, że klauzula OVER definiuje okno lub zestaw wierszy określony przez użytkownika w zestawie wyników zapytania. Następnie funkcja okna oblicza wartość dla każdego wiersza w oknie. Możesz użyć klauzuli OVER z funkcjami do obliczania zagregowanych wartości, takich jak średnie kroczące, skumulowane agregacje, sumy bieżące lub najwyższe N wyników na grupę.

• Funkcje rankingowe

• Funkcje agregujące

• Funkcje analityczne

• NEXT VALUE FOR

Konwencje składni języka Transact-SQL

Składnia

Argumenty

Funkcje okna mogą mieć następujące argumenty w swojej klauzuli OVER:

• PARTITION BY, która dzieli wynik zapytania na partycje.
• ORDER BY, który definiuje logiczną kolejność wierszy w każdej partycji zestawu wyników.
• ROWS / RANGE, który ogranicza wiersze w partycji poprzez określenie punktów początkowych i końcowych w partycji. Wymaga argumentu ORDER BY, a wartość domyślna jest od początku partycji do bieżącego elementu, jeśli określono argument ORDER BY.

Jeśli nie określisz żadnego argumentu, funkcje okna zostaną zastosowane do całego zestawu wyników.

object_id	min	max
3	3	2139154666
5	3	2139154666
…	…	…
2123154609	3	2139154666
2139154666	3	2139154666

PARTITION BY

Dzieli zestaw wyników zapytania na partycje. Funkcja okna jest stosowana do każdej partycji oddzielnie, a obliczenia są ponownie uruchamiane dla każdej partycji ion.

Jeśli nie określono PARTITION BY, funkcja traktuje wszystkie wiersze zestawu wyników zapytania jako jedną partycję. Funkcja zostanie zastosowana do wszystkich wierszy w partycji, jeśli nie określisz klauzuli ORDER BY.

PARTITION BY value_expression

Określa kolumnę, według której podzielony jest zestaw wierszy. value_expression może odnosić się tylko do kolumn udostępnionych przez klauzulę FROM. value_expression nie może odwoływać się do wyrażeń ani aliasów na liście wyboru. wyrażenie_wartości może być wyrażeniem kolumnowym, podzapytaniem skalarnym, funkcją skalarną lub zmienną zdefiniowaną przez użytkownika.

ORDER BY

Definiuje logiczną kolejność wierszy w każdej partycji zestawu wyników. Oznacza to, że określa logiczną kolejność, w jakiej wykonywane są obliczenia funkcji okna.

• Jeśli nie jest określony, domyślną kolejnością jest ASC i funkcja okna użyje wszystkich wierszy w partycji.
• Jeśli jest określona, a ROWS / RANGE nie jest określona, to domyślnie RANGE UNBOUNDED PRECEDING AND CURRENT ROW jest używane jako domyślne dla okna ramka według funkcji, które mogą akceptować opcjonalną specyfikację ROWS / RANGE (na przykład min lub max).

order_by_expression
Określa kolumnę lub wyrażenie do sortowania. order_by_expression może odnosić się tylko do kolumn udostępnionych przez klauzulę FROM. Nie można podać liczby całkowitej reprezentującej nazwę kolumny lub alias.

COLLATE collation_name
Określa, że operacja ORDER BY powinna być wykonywana zgodnie z sortowaniem określonym w collation_name. collation_name może być nazwą sortowania Windows lub nazwą sortowania SQL. Aby uzyskać więcej informacji, zobacz Sortowanie i obsługa Unicode. COLLATE ma zastosowanie tylko do kolumn typu char, varchar, nchar i nvarchar.

ASC | DESC
Określa, że wartości w określonej kolumnie powinny być sortowane w kolejności rosnącej lub malejącej. ASC jest domyślną kolejnością sortowania.Wartości null są traktowane jako najniższe możliwe wartości.

ROWS lub RANGE

Dotyczy: SQL Server 2012 (11.x) i nowszych.

Dalsze ograniczenia wiersze w partycji, określając punkty początkowe i końcowe w partycji. Odbywa się to poprzez określenie zakresu wierszy w odniesieniu do bieżącego wiersza za pomocą skojarzenia logicznego lub fizycznego. Powiązanie fizyczne uzyskuje się za pomocą klauzuli ROWS.

Klauzula ROWS ogranicza wiersze w partycji, określając stałą liczbę wierszy poprzedzających lub następujących po bieżącym wierszu. Alternatywnie klauzula RANGE logicznie ogranicza wiersze w partycji, określając zakres wartości w odniesieniu do wartości w bieżącym wierszu. Wiersze poprzedzające i kolejne są definiowane na podstawie kolejności w klauzuli ORDER BY. Ramka okna „RANGE … CURRENT ROW …” zawiera wszystkie wiersze, które mają takie same wartości w wyrażeniu ORDER BY, co bieżący wiersz. Na przykład ROWS BETWEEN 2 PRECED AND CURRENT ROW oznacza, że okno wierszy, na których działa funkcja, ma rozmiar trzech wierszy, zaczynając od 2 wierszy poprzedzających bieżący wiersz.

UNBOUNDED PRECEDING

Dotyczy: SQL Server 2012 (11.x) i nowsze.

Określa, że okno zaczyna się w pierwszym wierszu partycji. UNBOUNDED PRECEDING można określić tylko jako punkt początkowy okna.

< specyfikacja wartości bez znaku > PRECEDING
Określona z < specyfikacją wartości bez znaku > w celu wskazania liczby wierszy lub wartości poprzedzających bieżący wiersz. Ta specyfikacja nie jest dozwolona dla RANGE.

BIEŻĄCY WIERSZ

Dotyczy: SQL Server 2012 (11.x) i nowszych.

Określa, że okno jest uruchamiane lub kończy się w bieżącym wierszu, gdy jest używany z ROWS lub bieżącą wartością, gdy jest używany z RANGE. BIEŻĄCY WIERSZ można określić jako punkt początkowy i końcowy.

MIĘDZY I

Dotyczy: SQL Server 2012 (11.x) i nowszych.

Używane z ROWS lub RANGE do określenia dolnego (początkowego) i górnego (końcowego) punktu granicznego okna. < granica ramki okna > definiuje punkt początkowy granicy i < obramowanie okna > definiuje punkt końcowy granicy. Górna granica nie może być mniejsza niż dolna granica.

UNBOUNDED FOLLOWING

Dotyczy: SQL Server 2012 (11.x) i nowszych.

Określa, że okno kończy się w ostatnim rzędzie partycji. UNBOUNDED FOLLOWING można określić tylko jako punkt końcowy okna. Na przykład RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING definiuje okno, które zaczyna się od bieżącego wiersza i kończy na ostatnim wierszu partycji.

< specyfikacja wartości bez znaku > NASTĘPUJĄCE
Określone w < specyfikacji wartości bez znaku > w celu wskazania liczby wiersze lub wartości, które mają być zgodne z bieżącym wierszem. Gdy < specyfikacja wartości bez znaku > FOLLOWING jest określona jako punkt początkowy okna, punkt końcowy musi być < specyfikacja wartości bez znaku > NASTĘPUJĄCY. Na przykład ROWS BETWEEN 2 FOLLOWING AND 10 FOLLOWING definiuje okno, które zaczyna się od drugiego wiersza następującego po bieżącym wierszu i kończy się dziesiątym wierszem następującym po bieżącym wierszu. Ta specyfikacja nie jest dozwolona dla RANGE.

Literał liczby całkowitej bez znaku
Dotyczy: SQL Server 2012 (11.x) i nowszych.

Jest dodatnim literałem liczby całkowitej (w tym 0 ), która określa liczbę wierszy lub wartości poprzedzających lub następujących po bieżącym wierszu lub wartości. Ta specyfikacja jest ważna tylko dla ROWS.

Uwagi ogólne

W pojedynczym zapytaniu z jedną klauzulą FROM można użyć więcej niż jednej funkcji okna. Klauzula OVER dla każdej funkcji może różnić się partycjonowaniem i kolejnością.

Jeśli nie określono PARTITION BY, funkcja traktuje wszystkie wiersze zestawu wyników zapytania jako jedną grupę.

Ważne !

Jeśli określono ROWS / RANGE i < ramka okna poprzedzająca > jest używana dla < zasięg ramki okna > (krótka składnia) to ta specyfikacja jest używana jako punkt początkowy granicy ramy okna, a BIEŻĄCY WIERSZ jest używany jako punkt końcowy granicy. Na przykład „ROWS 5 PRECEDING” jest równe „ROWS BETWEEN 5 PRECEDING AND CURRENT ROW”.

Ograniczenia i ograniczenia

Klauzula OVER nie może być używana z funkcją agregującą CHECKSUM.

RANGE nie może być używane z < specyfikacją wartości bez znaku > PRECEDING lub < specyfikacja wartości bez znaku > NASTĘPUJE.

W zależności od rankingu, zagregowanego lub analitycznego funkcja używana z klauzulą OVER, < klauzula ORDER BY > i / lub < Klauzula ROWS i RANGE > może nie być obsługiwana.

Przykłady

A. Używanie klauzuli OVER z funkcją ROW_NUMBER

Poniższy przykład przedstawia użycie klauzuli OVER z funkcją ROW_NUMBER do wyświetlenia numeru wiersza dla każdego wiersza w partycji. Klauzula ORDER BY określona w klauzuli OVER porządkuje wiersze w każdej partycji według kolumny SalesYTD. Klauzula ORDER BY w instrukcji SELECT określa kolejność, w jakiej zwracany jest cały zestaw wyników zapytania.

Oto zestaw wyników.

B. Używanie klauzuli OVER z funkcjami agregującymi

W poniższym przykładzie zastosowano klauzulę OVER z funkcjami agregującymi we wszystkich wierszach zwróconych przez zapytanie. W tym przykładzie użycie klauzuli OVER jest bardziej wydajne niż używanie podzapytań do uzyskania zagregowanych wartości.

Oto zestaw wyników.

Poniższy przykład pokazuje użycie klauzuli OVER z funkcją agregującą w obliczonej wartości.

Oto zestaw wyników. Zwróć uwagę, że agregaty są obliczane przez SalesOrderID, a Percent by ProductID jest obliczane dla każdego wiersza każdego SalesOrderID.

C. Tworzenie średniej ruchomej i skumulowanej sumy

W poniższym przykładzie zastosowano funkcje AVG i SUM z klauzulą OVER w celu podania średniej ruchomej i skumulowanej sumy rocznej sprzedaży dla każdego terytorium w Sales.SalesPerson table. Dane są podzielone na partycje według TerritoryID i logicznie uporządkowane według SalesYTD. Oznacza to, że funkcja AVG jest obliczana dla każdego terytorium na podstawie roku sprzedaży. Zwróć uwagę, że w przypadku TerritoryID 1 istnieją dwa wiersze dla roku sprzedaży 2005, przedstawiające dwóch sprzedawców prowadzących sprzedaż w tym roku. Obliczana jest średnia sprzedaż dla tych dwóch wierszy, a następnie trzeci wiersz reprezentujący sprzedaż za rok 2006 jest uwzględniany w obliczeniach.

Oto zestaw wyników.

W tym przykładzie, klauzula OVER nie obejmuje PARTITION BY. Oznacza to, że funkcja zostanie zastosowana do wszystkich wierszy zwróconych przez zapytanie. Klauzula ORDER BY określona w klauzuli OVER określa logiczną kolejność stosowania funkcji AVG. Zapytanie zwraca średnią ruchomą sprzedaży według roku dla wszystkich obszarów sprzedaży określonych w klauzuli WHERE. Klauzula ORDER BY określona w instrukcji SELECT określa kolejność, w jakiej wiersze zapytania są wyświetlane.

Oto zestaw wyników.

D. Określanie klauzuli ROWS

Dotyczy: SQL Server 2012 (11.x) i nowszych.

W poniższym przykładzie użyto klauzuli ROWS do zdefiniowania okna, w którym wiersze są obliczane jako bieżący wiersz i N kolejnych wierszy (w tym przykładzie 1 wiersz).

Oto zestaw wyników.

W poniższym przykładzie klauzula ROWS jest określona jako UNBOUNDED PRECEDING. W rezultacie okno zaczyna się w pierwszym wierszu partycji.

Oto zestaw wyników.

Przykłady: równoległa hurtownia danych

E. Używanie klauzuli OVER z funkcją ROW_NUMBER

Poniższy przykład zwraca ROW_NUMBER dla przedstawicieli handlowych na podstawie przypisanych im kwot sprzedaży.

Oto częściowy zestaw wyników.

F. Używanie klauzuli OVER z funkcjami agregującymi

Poniższe przykłady pokazują użycie klauzuli OVER z funkcjami agregującymi. W tym przykładzie użycie klauzuli OVER jest bardziej wydajne niż używanie podzapytań.

Oto zestaw wyników.

Poniższy przykład pokazuje, używając klauzuli OVER z funkcją agregującą w obliczonej wartości. Zwróć uwagę, że agregaty są obliczane przez SalesOrderNumber, a procent całkowitego zamówienia sprzedaży jest obliczany dla każdego wiersza każdego SalesOrderNumber.

Pierwszy początek tego zestawu wyników to:

Zobacz też

Funkcje agregujące (Transact-SQL)
Funkcje analityczne (Transact-SQL)
Doskonały post na blogu o funkcjach okien i OVER, na sqlmag.com, autorstwa Itzika Ben-Gana