Wprowadzenie do funkcji okien T-SQL

spędziłem ostatnie sześć lat podróżując po Stanach Zjednoczonych, opowiadając specjalistom z baz danych o funkcjach okien T-SQL podczas sobót SQL i innych wydarzeń. Jestem zdumiony, jak niewiele osób słyszało o tych funkcjach, a jeszcze mniej, którzy z nich korzystają. Na końcu każdej prezentacji jedna lub więcej osób podchodzi, aby powiedzieć, że chciałaby dowiedzieć się o tych funkcjach wiele lat wcześniej, ponieważ mogły być korzystne dla tak wielu zapytań.,

te funkcje zostały promowane w celu poprawy wydajności w porównaniu z innymi, bardziej tradycyjnymi metodami. Częściowo się Zgadzam. Ułatwiają one pisanie wielu zapytań, a czasami poprawiają wydajność.

nie ma to nic wspólnego z systemem operacyjnym Windows

funkcje te są częścią standardów ANSI SQL 2003 i, w przypadku SQL Server, są funkcjami T-SQL używanymi do pisania zapytań. Nie mają one nic wspólnego z systemem operacyjnym Windows ani żadnymi wywołaniami API. Inne systemy bazodanowe, takie jak Oracle, również włączyły je jako część własnego języka SQL.,

funkcje Window (również windowing lub windowed) wykonują obliczenia na zbiorze wierszy. Lubię myśleć o „patrzeniu przez okno” na zwracane wiersze i mieć ostatnią szansę na wykonanie obliczeń. Okno jest zdefiniowane przez klauzulę OVER, która określa, czy wiersze są podzielone na mniejsze zbiory i czy są uporządkowane. W rzeczywistości, jeśli używasz funkcji okna, zawsze używasz klauzuli OVER. Klauzula OVER jest również częścią następnej wartości dla składni wymaganej dla obiektu sequence, ale w przeciwnym razie jest używana z funkcjami okien.,

klauzula OVER może zawierać partycję według opcji. Powoduje to podział wierszy na mniejsze zbiory. Możesz pomyśleć, że jest to to samo, co grupa przez, ale tak nie jest. Podczas grupowania zwracany jest jeden wiersz na unikalną grupę. Podczas używania partycji by zwracane są wszystkie wiersze szczegółów wraz z obliczeniami. Jeśli masz w domu okno, które jest podzielone na Szyby, każda szyba jest oknem. Podczas myślenia o funkcjach okna, cały zestaw wyników jest partycją, ale podczas korzystania z partycji by, każda partycja może być również uważana za okno., Partycja by JEST OBSŁUGIWANA-i opcjonalna-dla wszystkich funkcji okienkowych.

klauzula OVER może również zawierać opcję ORDER BY. Jest to niezależne od klauzuli ORDER BY w zapytaniu. Niektóre funkcje wymagają ORDER BY, a inne nie są obsługiwane. Gdy kolejność wierszy jest ważna przy obliczaniu, wymagana jest kolejność według.

funkcje okna mogą być używane tylko w klauzulach SELECT i ORDER BY zapytania. Są one stosowane po połączeniu, filtrowaniu lub grupowaniu.,

funkcje rankingowe

najczęściej używane funkcje okien, funkcje rankingowe, są dostępne od 2005 roku. To wtedy Microsoft wprowadził ROW_NUMBER, RANK, DENSE_RANK i NTILE. ROW_NUMBER jest bardzo często używany do dodawania unikalnych numerów wierszy do partycji lub do całego zestawu wyników. Dodanie numeru wiersza lub jednej z innych funkcji rankingowych zwykle nie jest celem, ale jest krokiem w drodze do rozwiązania.

ORDER BY jest wymagane w klauzuli OVER, gdy używa się ROW_NUMBER i innych funkcji z tej grupy., To mówi silnikowi bazy danych, w jakiej kolejności powinny być stosowane liczby. Jeśli wartości kolumn lub wyrażeń użytych w ORDER BY nie są unikalne, ranga i DENSE_RANK zajmą się powiązaniami, podczas gdy numer ROW_NUMBER nie dba o powiązania. NTILE służy do dzielenia wierszy na koszyki na podstawie kolejności przez.

jedną z zalet ROW_NUMBER jest możliwość zamiany nie-unikalnych wierszy w unikalne wiersze. Można to na przykład wykorzystać do wyeliminowania zduplikowanych wierszy.

aby pokazać, jak to działa, zacznij od tabeli tymczasowej zawierającej zduplikowane wiersze., Pierwszym krokiem jest utworzenie tabeli i wypełnienie jej.

dodanie numeru wiersza i podział według każdej kolumny spowoduje ponowne uruchomienie numerów wierszy dla każdego unikalnego zestawu wierszy. Możesz zidentyfikować unikalne wiersze, znajdując te z numerem wiersza równym jeden.,

select Col1, col2,

row_number() over(Partition by Col1, col2 order by Col1) as rownum

from #duplicates;

teraz wszystko, co musisz zrobić, to usunąć wszystkie wiersze, które mają numer rzędu większy niż jeden., Problem polega na tym, że nie można dodać funkcji okna do klauzuli WHERE.,

1 2	DELETE #Duplicates WHERE ROW_NUMBER() OVER(PARTITION BY Col1, Col2 ORDER BY Col1) <> 1;

You’ll see this error message:

The way around this problem is to separate the logic using a common table expression (CTE)., Następnie możesz usunąć wiersze bezpośrednio z CTE.

sukces! Dodatkowe wiersze zostały usunięte, a unikalny zestaw wierszy pozostaje.

aby zobaczyć różnicę między NUMEREM_ wiersza, rangą i DENSE_RANK, uruchom to zapytanie:

ORDER BY dla każdej klauzuli OVER to OrderDate, która nie jest unikalna. Ten klient złożył dwa zamówienia w dniu 2013-10-24. ROW_NUMBER po prostu kontynuował przypisywanie liczb i nie zrobił nic innego, nawet jeśli istnieje duplikat daty., Ranga przypisana 6 do obu wierszy, a następnie przechwycona do ROW_NUMBER z 8 w następnym wierszu. DENSE_RANK przypisał również 6 do dwóch wierszy, ale przypisał 7 do następnego wiersza.

Dwa wyjaśniają różnicę, pomyśl o ROW_NUMBER jako o pozycji. Ranga jest zarówno pozycyjna, jak i logiczna. Te dwa rzędy są uporządkowane logicznie tak samo, ale następny rząd jest uszeregowany według pozycji w zbiorze. DENSE_RANK klasyfikuje je logicznie. Zamówienie 2013-11-04 to 7 unikalna Data.

funkcja końcowa w tej grupie nazywa się NTILE. Przypisuje numery wiadra do wierszy zamiast numerów wierszy lub szeregów., Oto przykład:

NTILE ma parametr, w tym przypadku 4, który jest liczbą łyżek, które chcesz zobaczyć w wynikach. Zamówienie przez jest stosowane do sumy sprzedaży. Wiersze z NAJNIŻSZYM 25% są przypisane 1, wiersze z najwyższym 25% są przypisane 4. Na koniec wyniki NTILE są mnożone przez 1000, aby uzyskać kwotę bonusu. Ponieważ 14 nie może być równomiernie podzielone przez 4, dodatkowy rząd przechodzi do każdego z pierwszych dwóch wiadrów.

Agregaty okienne

Agregaty okienne zostały również wprowadzone wraz z SQL Server 2005., To sprawia, że pisanie trudnych zapytań jest łatwe, ale często będzie działać gorzej niż starsze techniki. Pozwalają one dodać ulubioną funkcję agregacji do zapytania Nie agregującego. Powiedzmy, że na przykład chcesz wyświetlić wszystkie zamówienia Klientów wraz z podsumowaniem dla każdego klienta., Dodając sumę używając klauzuli OVER, możesz to bardzo łatwo osiągnąć:

select CustomerID, OrderDate, salesorderid, totaldue,

Sum(totaldue) over(Partition by CustomerID) as subtotal

from sales.,SalesOrderHeader;

dodając partycję przez, obliczana jest suma cząstkowa dla każdego klienta. Można użyć dowolnej funkcji agregującej, a klauzula ORDER BY in OVER NIE JEST OBSŁUGIWANA.

ulepszenia agregatów okien w 2012 r.

począwszy od 2012 r., możesz dodać ORDER BY do klauzuli OVER do agregatów okien, aby na przykład wygenerować bieżące sumy i średnie kroczące. W Tym Samym Czasie Microsoft wprowadził koncepcję kadrowania. Dodawanie partycji przez jest jak dzielenie okna na panele., Dodanie kadru jest jak stworzenie witraża. Każdy wiersz ma osobne okno, w którym zostanie zastosowane wyrażenie.

dzięki temu ulepszeniu możesz tworzyć uruchomione sumy nawet bez dodawania składni kadrowania., Here is an example that returns a running total by customer:

SELECT CustomerID, OrderDate, SalesOrderID, TotalDue,

SUM(TotalDue) OVER(PARTITION BY CustomerID ORDER BY SalesOrderID)

AS RunningTotal

FROM Sales.,SalesOrderHeader;

domyślna ramka, która jest używana, jeśli ramka nie jest określona, to zakres między nieograniczonym poprzedzającym i bieżącym wierszem. Niestety, nie będzie to działać tak dobrze, jak w przypadku podania tej ramki: wiersze między niepohamowanym poprzedzającym i bieżącym wierszem. Różnica polega na wierszu słowa. Zakres jest tylko częściowo zaimplementowany w tym czasie i jest przeznaczony do pracy z okresami czasu, podczas gdy ROWS jest pozycyjny., Ramka, ROWS BETWEEN UNBOUNDED previous and CURRENT ROW, oznacza, że okno składa się z pierwszego wiersza partycji i wszystkich wierszy aż do bieżącego wiersza. Każde obliczenia są wykonywane na innym zestawie wierszy. Na przykład podczas wykonywania obliczeń dla wiersza 4 używane są wiersze od 1 do 4.

podczas wykonywania obliczeń dla wiersza 5 wiersze są od 1 do 5. Okno powiększa się w miarę przechodzenia z jednego rzędu do następnego.,

Możesz również użyć składni ROWS pomiędzy N poprzedzającym i bieżącym wierszem lub ROWS między bieżącym wierszem i N następującym. Może to być przydatne np. przy obliczaniu trzymiesięcznej średniej kroczącej. Poniższy rysunek przedstawia wiersze między 2 poprzednim i bieżącym wierszem.

gdy 5 jest bieżącym wierszem, okno przesuwa się; nie zmienia rozmiaru.,

oto lista terminów, które musisz znać podczas pisania opcji kadrowania:

przyznaję, że ta składnia jest nieco myląca, ale użycie polecenia SQL ułatwia pisanie opcji kadrowania!

funkcje offsetowe

dołączone również do wydania SQL Server 2012 są czterema funkcjami, które pozwalają na dołączanie wartości z innych wierszy – bez konieczności samodzielnego łączenia. Microsoft nazywa te „funkcje analityczne”, ale zawsze odnoszą się do nich jako „funkcje offsetowe” podczas prezentacji na ten temat., Dwie funkcje pozwalają na wyciągnięcie kolumn lub wyrażeń z wiersza przed (LAG) lub po (LEAD) bieżącym wierszem. Pozostałe dwie funkcje umożliwiają zwracanie wartości z pierwszego wiersza partycji (FIRST_VALUE) lub ostatniego wiersza partycji (LAST_VALUE). FIRST_VALUE i LAST_VALUE również wymagają kadrowania, więc pamiętaj, aby dołączyć ramkę podczas korzystania z tych funkcji. Wszystkie cztery funkcje wymagają opcji ORDER BY klauzuli OVER. Ma to sens, ponieważ silnik bazy danych musi znać kolejność wierszy, aby dowiedzieć się, który wiersz zawiera wartość do zwrócenia.,

niektórzy mają ulubiony zespół, niektórzy mają ulubiony film. Mam ulubioną funkcję-LAG. Jest łatwy w użyciu (bez ramki!) i świetnie się spisuje., Here is an example:

SELECT CustomerID, OrderDate, SalesOrderID,

LAG(SalesOrderID) OVER(PARTITION BY CustomerID ORDER BY SalesOrderID

) AS PrevOrder

FROM Sales.,SalesOrderHeader

Zamów przez CustomerID;

LAG i LEAD wymagają argumentu – kolumny lub wyrażenia, które chcesz zwrócić. Domyślnie LAG Zwraca wartość z poprzedniego wiersza, a LEAD Zwraca wartość z następnego wiersza. Można to zmodyfikować, podając wartość parametru OFFSET, który domyślnie wynosi 1. Zauważ, że pierwszy wiersz partycji zwraca NULL. Jeśli chcesz nadpisać wartości null, możesz podać wartość domyślną., Here is a similar query that goes back two rows and has a default value:

SELECT CustomerID, OrderDate, SalesOrderID,

LAG(SalesOrderID,2,0) OVER(PARTITION BY CustomerID

ORDER BY SalesOrderID) AS Back2Orders

FROM Sales.,SalesOrderHeader;

FIRST_VALUE i LAST_VALUE mogą być użyte do znalezienia wartości z pierwszego lub ostatniego wiersza partycji. Pamiętaj, aby określić ramkę, nie tylko ze względu na wydajność, ale ponieważ domyślna Ramka nie działa tak, jak można by się spodziewać po LAST_VALUE. Domyślna ramka, zakres między nieograniczonym poprzedzającym i bieżącym wierszem, przechodzi tylko do bieżącego wiersza. Ostatni wiersz partycji nie jest uwzględniony., Aby uzyskać oczekiwane wyniki, należy podać wiersze między bieżącym wierszem a niepohamowanym następnym, gdy używa się LAST_VALUE., Oto przykład użycia FIRST_VALUE:

select CustomerID, OrderDate, salesorderid,

first_value(salesorderid) over(partition by CustomerID

Zamów przez salesorderid

wiersze między niepohamowanym poprzedzającym i bieżącym wierszem) jako pierwsze zamówienie

ze sprzedaży.,SalesOrderHeader;

Funkcje statystyczne

Microsoft grupuje te cztery funkcje – PROCENT_RANK, CUME_DIST, PERCENTYLE_DISC, percentyle_cont – wraz z funkcjami offsetowymi wywołującymi wszystkie osiem funkcji analitycznych. Ponieważ lubię je odróżniać od funkcji offsetowych, nazywam je statystycznymi.

PROCENT_RANK i CUME_DIST zapewniają ranking dla każdego wiersza nad partycją. Różnią się nieznacznie. PERCENT_RANK zwraca procent wierszy o niższej randze niż bieżący wiersz., „Mój wynik jest wyższy niż 90% wyników.”CUME_DIST, czyli rozkład kumulacyjny, zwraca dokładną rangę. „Mój wynik to 90% wyników.”Oto przykład z wykorzystaniem średniej wysokiej temperatury w St. Louis dla każdego miesiąca. Zauważ, że stopnie zostały określone przez temperaturę Fahrenheita.

szeregi nie są określane przez wartości względne, ale przez pozycje wierszy. Zauważ, że Marzec i Listopad mają tę samą średnią wysoką temperaturę, więc zostały sklasyfikowane tak samo.

być może zastanawiasz się, jak obliczyć PROCENT_RANK i CUME_DIST., Oto formuły:

1 2	procent_rank = (ranga -1)/(liczba wierszy -1) cume_dist = (ranga)/(liczba wierszy)

percentyle_disc i percentyle_cont działają w odwrotny sposób. Biorąc pod uwagę rangę procentową, znajdź wartość w tej randze., Różnią się tym, że PERCENTYLE_DISC zwróci wartość, która istnieje w zbiorze, podczas gdy PERCENTYLE_CONT obliczy dokładną wartość, jeśli żadna z wartości w zbiorze nie spadnie dokładnie do tej rangi. Można użyć PERCENTYLE_CONT, aby obliczyć medianę, podając 0,5 jako rangę procentową. Na przykład, która temperatura wynosi 50% W St. Louis?

funkcja PERCENTYLE_CONT przyjmuje średnią z dwóch wartości położonych najbliżej środka, 67 i 69, i uśrednia je. PERCENTYLE_DISC zwraca dokładną wartość, 67., Zauważ również, że te dwie funkcje mają dodatkową klauzulę nie widzianą w innych funkcjach, wewnątrz grupy, która zawiera kolejność BY zamiast w klauzuli OVER.

podsumowanie

Ten artykuł jest bardzo szybkim przeglądem funkcji okna T-SQL. Dwa typy funkcji zostały wydane w SQL Server 2005, funkcje rankingowe i agregaty okien. W 2012 roku Poprawiono agregację okien z kadrowaniem i funkcjami analitycznymi. Lubię rozdzielać funkcje analityczne na dwie grupy, funkcje offsetowe i statystyczne., Funkcje okien ułatwiają pisanie wielu zapytań i uważam, że jest to główna korzyść. W niektórych przypadkach, zapytania będą działać lepiej, zbyt, ale to jest dyskusja na inny dzień.

mam nadzieję, że ten artykuł zainspirował cię do zapoznania się z tymi fantastycznymi funkcjami!

Redgate Hub (Polski)