funkcja analityczna oblicza wartości nad grupą wierszy i zwraca jako pojedynczy wynik dla każdego wiersza. Różni się to od funkcji agregującej, która Zwraca pojedynczy wynik dla grupy wierszy.

funkcja analityczna zawiera klauzulęOVER, która definiuje okno wierszy wokół analizowanego wiersza. Dla każdego wiersza wynik funkcji analitycznej jest obliczany przy użyciu wybranego okna wierszy jako wejścia, co może prowadzić do agregacji.

dzięki funkcjom analitycznym możesz obliczać średnie kroczące, pozycje rankingowe, sumy kalkulacyjne i wykonywać inne analizy.

następujące funkcje mogą być używane jako funkcje analityczne:funkcje nawigacyjne,funkcje numeracyjne i funkcje analityczne

składnia funkcji analitycznych

analytic_function_name ( ) OVER over_clauseover_clause: { named_window | ( ) }window_specification: ] ] window_frame_clause: { rows_range } { frame_start | frame_between }rows_range: { ROWS | RANGE }

Zasady notacji

• nawiasy kwadratowe „” wskazują opcjonalne klauzule.
• nawiasy „() ” oznaczają nawiasy dosłowne.
• pionowy pasek ” / ” wskazuje logiczne OR.
• klamry „{ }” załączają zestaw opcji.
• przecinek, po którym następuje elipsa w nawiasach kwadratowych „” oznacza, że poprzedni element może się powtarzać w liście rozdzielonej przecinkami.

opis

funkcja analityczna oblicza wyniki nad grupą wierszy. Możesz użyć następującej składni do zbudowania funkcji analitycznej:

• analytic_function_name: funkcja wykonująca operację analityczną.Na przykład można tu użyć funkcji numeracji RANK().
• argument_list: argumenty specyficzne dla funkcji analitycznej.Niektóre funkcje mają je, niektóre nie.
• OVER: słowo kluczowe wymagane w składni funkcji analitycznej poprzedzającej klauzulęOVER.
• over_clause: odwołuje się do okna, które definiuje groupof wierszy w tabeli, w którym ma być używana funkcja analityczna.
• window_specification: definiuje specyfikację okna.
• window_frame_clause: definiuje ramkę okna.
• rows_range: definiuje fizyczne wiersze lub alogiczny zakres ramki okna.

uwagi

funkcja analityczna może pojawić się jako skalarny argument wyrażenia w dwóch miejscach w zapytaniu:

• listaSELECT. Jeśli funkcja analityczna pojawia się na liścieSELECT, jej lista argumentów iOVER nie mogą odnosić się do aliasów wprowadzonych na tej samej liście SELECT.
• klauzulaORDER BY. Jeśli funkcja analityczna pojawia się w klauzuliORDER BY zapytania, jej lista argumentów może odnosić się do aliasów listySELECT.

funkcja analityczna nie może odwoływać się do innej funkcji analitycznej na liście dokumentów lub jej klauzuliOVER, nawet pośrednio poprzez alias.

funkcja analityczna jest oceniana po agregacji. Na przykład,GROUP BY klauzula i nieanalityczne funkcje agregujące są oceniane jako pierwsze.Ponieważ funkcje agregujące są oceniane przed funkcjami analitycznymi, funkcje agregujące mogą być używane jako argumenty wejściowe do funkcji analitycznych.

zwraca

pojedynczy wynik dla każdego wiersza na wejściu.

Definiowanie klauzuli OVER

analytic_function_name ( ) OVER over_clauseover_clause: { named_window | ( ) }

opis

OVER klauzula odwołuje się do okna, które definiuje grupę wierszy w tabeli, do której ma być używana funkcja analityczna. Możesz podać named_window, który jest zdefiniowany w zapytaniu, lub określić specyfikacje dla nowego okna.

uwagi

Jeśli nie podano nazwy okna ani specyfikacji okna, wiersze allinput są dołączane do okna dla każdego wiersza.

przykłady przy użyciuOVER klauzula

te zapytania używają specyfikacji okna:

• Oblicz sumę całkowitą
• Oblicz sumę łączną
• Oblicz średnią ruchomą
• oblicz liczbę pozycji w zakresie
• uzyskaj najpopularniejszą pozycję w każdej kategorii
• uzyskaj ostatnią wartość w zakresie
• Oblicz rangę

te zapytania używają nazwanego okna:

• Pobierz ostatnią wartość w zakresie
• użyj nazwanego okna w klauzuli ramki okna

Definiowanie specyfikacji okna

window_specification: ] ] 

opis

definiuje specyfikację okna.

• named_window: nazwa istniejącego okna, które zostało zdefiniowane klauzulą WINDOW.

• PARTITION BY: rozbija wiersze wejściowe na oddzielne partycje, nad którymi funkcja analityczna jest niezależnie oceniana.
  • wiele wyrażeń partycji jest dozwolonych w klauzuli PARTITION BY.
  • wyrażenie nie może zawierać typów zmiennoprzecinkowych,typów niezgrupowalnych, stałych ani funkcji analitycznych.
  • Jeśli ta opcjonalna klauzula nie jest używana, wszystkie wiersze w tabeli wejściowej tworzą jedną partycję.
• ORDER BY: określa kolejność wierszy na partycji.Klauzula ta jest opcjonalna w większości sytuacji, ale jest wymagana w niektórych przypadkach dla funkcji nawigacyjnych.
• window_frame_clause : Dla aggregate analyticfunctions definiuje ramkę okna w bieżącej partycji.Ramka okna określa, co należy uwzględnić w oknie.Jeśli ta klauzula jest używana,ORDER BY jest wymagane, z wyjątkiem okien całkowicie zamkniętych.

uwagi

Jeśli nie ma klauzuliORDER BY ani klauzuli ramki okna,ramka okna zawiera wszystkie wiersze na tej partycji.

dla zagregowanych funkcji analitycznych, jeśli istnieje klauzula ORDER BY, ale nie ma klauzuli ramki okna, domyślnie używana jest następująca klauzula ramki okna:

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

na przykład następujące zapytania są równoważne:

SELECT book, LAST_VALUE(item) OVER (ORDER BY year)FROM Library

SELECT book, LAST_VALUE(item) OVER ( ORDER BY year RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)FROM Library

Zasady korzystania z nazwanego okna w specyfikacji okna

Jeśli używasz nazwanego okna w specyfikacji okna, obowiązują następujące zasady:

• specyfikacje w nazwanym oknie można rozszerzyć o nowe specyfikacje zdefiniowane w klauzuli specyfikacji okna.
• nie możesz mieć zbędnych definicji. Jeśli maszORDER BY clausew nazwanym oknie i klauzuli specyfikacji okna, zostanie wyrzucony błąd.

• kolejność klauzul ma znaczenie. PARTITION BY musi być pierwszy,a następnieORDER BY Iwindow_frame_clause. Jeśli dodasz nazwane okno, jego specyfikacje są przetwarzane jako pierwsze.

  --this works:SELECT item, purchases, LAST_VALUE(item) OVER (item_window ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS most_popularFROM ProduceWINDOW item_window AS (ORDER BY purchases)--this does not work:SELECT item, purchases, LAST_VALUE(item) OVER (item_window ORDER BY purchases) AS most_popularFROM ProduceWINDOW item_window AS (ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)

• nazwane okno iPARTITION BY nie mogą pojawić się razem w specyfikacji okna. Jeśli potrzebujesz PARTITION BY, dodaj go do nazwanego okna.

• nie można odwoływać się do nazwanego okna w klauzuli ORDER BY, zapytania zewnętrznego ani żadnego zapytania podrzędnego.

przykłady przy użyciu specyfikacji okna

te zapytania definiują partycje w funkcji analitycznej:

• Oblicz sumę cząstkową
• Oblicz sumę łączną
• uzyskaj najpopularniejszy element w każdej kategorii
• uzyskaj ostatnią wartość w zakresie
• Oblicz rangę
• użyj nazwanego okna w klauzuli ramki okna

te zapytania zawierają nazwane okno w specyfikacji okna:

• Get the last value in a range
• Use a named window in a window frame clause

te zapytania definiują sposób porządkowania wierszy na partycji:

• Oblicz sumę łączną
• Oblicz średnią ruchomą
• oblicz liczbę elementów w zakresie
• uzyskaj najpopularniejszy element w każdej kategorii
• uzyskaj ostatnią wartość w zakresie
• Oblicz rangę
• użyj nazwanego okna w klauzuli ramki okna

Definiowanie klauzuli ramki okna

window_frame_clause: { rows_range } { frame_start | frame_between }rows_range: { ROWS | RANGE }frame_between: { BETWEEN unbounded_preceding AND frame_end_a | BETWEEN numeric_preceding AND frame_end_a | BETWEEN current_row AND frame_end_b | BETWEEN numeric_following AND frame_end_c }frame_start: { unbounded_preceding | numeric_preceding | }frame_end_a: { numeric_preceding | current_row | numeric_following | unbounded_following }frame_end_b: { current_row | numeric_following | unbounded_following }frame_end_c: { numeric_following | unbounded_following }unbounded_preceding: UNBOUNDED PRECEDINGnumeric_preceding: numeric_expression PRECEDINGunbounded_following: UNBOUNDED FOLLOWINGnumeric_following: numeric_expression FOLLOWINGcurrent_row: CURRENT ROW

Klauzula ramki okna określa ramkę okna wokół bieżącego wiersza z partycją, nad którą jest obliczana funkcja analityczna.Tylko zagregowane funkcje analityczne mogą używać klauzuli ramki okna.

• rows_range: klauzula, która definiuje ramkę okna z fizycznym wierszem lub logicznym zakresem.

  • ROWS: oblicza ramkę okna na podstawie fizycznych przesunięć z wiersza bieżącego. Na przykład możesz dołączyć dwa wiersze przed i po bieżącym wierszu.
  • RANGE: oblicza ramkę okna na podstawie logicznego zakresu wierszy wokół bieżącego wiersza, na podstawie wartości klucza bieżącego wierszaORDER BY.Podana wartość zakresu jest dodawana lub odejmowana do bieżącej wartości wiersza, aby zdefiniować początkową lub końcową granicę zakresu dla ramki okna. W ramce okna opartej na zakresie musi być dokładnie jednowyrażenie w klauzuliORDER BY, a wyrażenie musi mieć typ anumeryczny.

  Wskazówka: Jeśli chcesz użyć zakresu z datą, użyjORDER BY z funkcjąUNIX_DATE(). Jeśli chcesz użyć zakresu ze znacznikiem czasu,użyj funkcji UNIX_SECONDS()UNIX_MILLIS() lub UNIX_MICROS().

• frame_between: tworzy ramkę okna z dolną i górną granicą.Pierwsza granica reprezentuje dolną granicę. Druga granica przedstawia górną granicę. Tylko niektóre kombinacje granic mogą być użyte, Jak pokazano w poprzedniej składni.

  • Zdefiniuj początek ramki okna za pomocąunbounded_precedingnumeric_precedingnumeric_followinglub current_row.
    • unbounded_preceding: ramka okna rozpoczyna się na początku części.
    • numeric_preceding lubnumeric_following: początek ramki okna jest względny do wiersza bieżącego.
    • current_row: ramka okna zaczyna się od bieżącego wiersza.
  • Zdefiniuj koniec ramki okna za pomocąnumeric_precedingnumeric_followingcurrent_row lubunbounded_following.
    • numeric_precedinglub numeric_following: koniec okna jest względny do bieżącego wiersza.
    • current_row : Ramka okna kończy się w bieżącym wierszu.
    • unbounded_following: ramka okna kończy się na końcu części.

• frame_start: tworzy ramkę okna z dolną granicą.Ramka okna kończy się w bieżącym wierszu.

  • unbounded_preceding: ramka okna rozpoczyna się na początku części.
  • numeric_preceding: początek ramki okna jest względem wiersza bieżącego.
  • current_row: ramka okna zaczyna się od bieżącego wiersza.

• numeric_expression: wyrażenie reprezentujące Typ numeryczny.Wyrażenie liczbowe musi być stałym, nieujemnym parametrem integeror.

uwagi

Jeśli granica wykracza poza początek lub koniec partycji,ramka okna będzie zawierać tylko wiersze z tej partycji.

nie można używać klauzuli ramki okna z funkcjami nawigacyjnymi i funkcjami numeracyjnymi,takimi jakRANK().

przykłady za pomocą klauzuli ramki okna

te zapytania obliczają wartości za pomocą ROWS:

• Oblicz sumę skumulowaną
• Oblicz średnią ruchomą
• uzyskaj najpopularniejszy element w każdej kategorii
• uzyskaj ostatnią wartość w zakresie
• użyj nazwanego okna w klauzuli ramki okna

te zapytania obliczają wartości za pomocąRANGE:

• Oblicz liczba pozycji w zakresie

te zapytania obliczają wartości z częściowo lub całkowicie niezwiązanym oknem:

• Oblicz sumę całkowitą
• Oblicz sumę łączną
• uzyskaj najpopularniejszy element w każdej kategorii
• Oblicz rangę

te zapytania obliczają wartości z granicami liczbowymi:

• Oblicz sumę łączną
• Oblicz średnią kroczącą
• oblicz liczbę elementów w ramach zakres
• uzyskaj ostatnią wartość w zakresie
• użyj nazwanego okna w klauzuli ramki okna

te zapytania obliczają wartości z bieżącym wierszem jako granicą:

• Oblicz sumę całkowitą
• Oblicz sumę łączną

odwołując się do nazwanego okna

SELECT query_expr, analytic_function_name ( ) OVER over_clauseFROM from_itemWINDOW named_window_expression over_clause: { named_window | ( ) }window_specification: ] ] named_window_expression: named_window AS { named_window | ( ) }

nazwane okno reprezentuje grupę wierszy w tabeli, na której należy użyć funkcji ananalitycznej. Nazwane okno jest zdefiniowane w klauzuliWINDOW I odwołuje się do funkcji analitycznej inan OVER clause.In klauzulaOVER, nazwane okno może pojawić się samo lub osadzone w specyfikacji okna.

przykłady

• Pobierz ostatnią wartość w zakresie
• użyj nazwanego okna w klauzuli ramki okna

pojęcia funkcji nawigacyjnych

funkcje nawigacyjne Zwykle obliczają niektóre value_expression nad innym wierszem w ramce okna niż wiersz bieżący. Składnia klauzuliOVER różni się w zależności od funkcji nawigacyjnych.

wymagania dotycząceOVER klauzula:

• PARTITION BY: opcjonalne.
• ORDER BY :
  1. Disallowed for PERCENTILE_CONT and PERCENTILE_DISC.
  2. Required for FIRST_VALUELAST_VALUENTH_VALUELEADand LAG.
• window_frame_clause:
  1. Disallowed for PERCENTILE_CONTPERCENTILE_DISCLEAD and LAG.
  2. Optional for FIRST_VALUELAST_VALUE, and NTH_VALUE.

dla wszystkich funkcji nawigacyjnych wynikowy typ danych jest tego samego typu covalue_expression.

koncepcje funkcji numeracyjnych

funkcje numeracyjne przypisują wartości całkowite do każdego wiersza na podstawie ich pozycji w określonym oknie.

przykładRANK()DENSE_RANK() IROW_NUMBER():

WITH Numbers AS (SELECT 1 as x UNION ALL SELECT 2 UNION ALL SELECT 2 UNION ALL SELECT 5 UNION ALL SELECT 8 UNION ALL SELECT 10 UNION ALL SELECT 10)SELECT x, RANK() OVER (ORDER BY x ASC) AS rank, DENSE_RANK() OVER (ORDER BY x ASC) AS dense_rank, ROW_NUMBER() OVER (ORDER BY x) AS row_numFROM Numbers+---------------------------------------------------+| x | rank | dense_rank | row_num |+---------------------------------------------------+| 1 | 1 | 1 | 1 || 2 | 2 | 2 | 2 || 2 | 2 | 2 | 3 || 5 | 4 | 3 | 4 || 8 | 5 | 4 | 5 || 10 | 6 | 5 | 6 || 10 | 6 | 5 | 7 |+---------------------------------------------------+

• RANK(): Dla x=5, rank wynosi 4, ponieważ RANK() zwiększa się o liczbę rówieśników w poprzedniej grupie porządkowania okien.
• DENSE_RANK(): dla x=5,dense_rank wynosi 3, ponieważDENSE_RANK() zawsze zwiększa się o 1, nigdy nie pomijając wartości.
• ROW_NUMBER(): dla x=5,row_num wynosi 4.

zbiorcze pojęcia funkcji analitycznych

funkcja zbiorcza jest funkcją, która wykonuje obliczenia na asecie wartości. Większość funkcji zbiorczych może być użyta w funkcji ananalitycznej. Te funkcje zbiorcze są nazywane funkcjami analitycznymi.

z zagregowanymi funkcjami analitycznymi, klauzulaOVER jest dołączana do wywołania funkcji aggregate; składnia wywołania funkcji pozostaje niezmieniona.Podobnie jak ich odpowiedniki funkcji agregujących, te funkcje analityczne wykonująregiony, ale w szczególności nad odpowiednią ramką okna dla każdego wiersza.Typy danych wynikowych tych funkcji analitycznych są takie same jak ich odpowiedniki.

przykłady funkcji analitycznych

w tych przykładach wyróżniony element jest bieżącym wierszem. Pogrubione elementy to wiersze, które są uwzględnione w analizie.

wspólne tabele używane w przykładach

następujące tabele są używane w kolejnych przykładach agregat analyticquery:ProduceEmployeesIFarm.

Tworzenie tabeli

niektóre przykłady odwołują się do tabeli o nazwieProduce:

WITH Produce AS (SELECT 'kale' as item, 23 as purchases, 'vegetable' as category UNION ALL SELECT 'orange', 2, 'fruit' UNION ALL SELECT 'cabbage', 9, 'vegetable' UNION ALL SELECT 'apple', 8, 'fruit' UNION ALL SELECT 'leek', 2, 'vegetable' UNION ALL SELECT 'lettuce', 10, 'vegetable')SELECT * FROM Produce+-------------------------------------+| item | category | purchases |+-------------------------------------+| kale | vegetable | 23 || orange | fruit | 2 || cabbage | vegetable | 9 || apple | fruit | 8 || leek | vegetable | 2 || lettuce | vegetable | 10 |+-------------------------------------+

tabela pracowników

niektóre przykłady odwołują się do tabeli o nazwieEmployees:

WITH Employees AS (SELECT 'Isabella' as name, 2 as department, DATE(1997, 09, 28) as start_date UNION ALL SELECT 'Anthony', 1, DATE(1995, 11, 29) UNION ALL SELECT 'Daniel', 2, DATE(2004, 06, 24) UNION ALL SELECT 'Andrew', 1, DATE(1999, 01, 23) UNION ALL SELECT 'Jacob', 1, DATE(1990, 07, 11) UNION ALL SELECT 'Jose', 2, DATE(2013, 03, 17))SELECT * FROM Employees+-------------------------------------+| name | department | start_date |+-------------------------------------+| Isabella | 2 | 1997-09-28 || Anthony | 1 | 1995-11-29 || Daniel | 2 | 2004-06-24 || Andrew | 1 | 1999-01-23 || Jacob | 1 | 1990-07-11 || Jose | 2 | 2013-03-17 |+-------------------------------------+

tabela gospodarstwa

niektóre przykłady odwołują się do tabeli o nazwieFarm:

WITH Farm AS (SELECT 'cat' as animal, 23 as population, 'mammal' as category UNION ALL SELECT 'duck', 3, 'bird' UNION ALL SELECT 'dog', 2, 'mammal' UNION ALL SELECT 'goose', 1, 'bird' UNION ALL SELECT 'ox', 2, 'mammal' UNION ALL SELECT 'goat', 2, 'mammal')SELECT * FROM Farm+-------------------------------------+| animal | category | population |+-------------------------------------+| cat | mammal | 23 || duck | bird | 3 || dog | mammal | 2 || goose | bird | 1 || ox | mammal | 2 || goat | mammal | 2 |+-------------------------------------+

Oblicz całkowitą sumę

oblicza całkowitą sumę wszystkich pozycji w tabeliProduce.

• (pomarańcza, jabłko, por, kapusta, sałata, jarmuż) = 54 zakupy ogółem
• (pomarańcza, jabłko, pora, kapusta, sałata, jarmuż) = 54 zakupy ogółem
• (pomarańcza, jabłko, pora, kapusta, sałata, jarmuż) = 54 zakupy ogółem
• (pomarańcza, jabłko, pora, kapusta, sałata, jarmuż) = 54 zakupy ogółem
• (pomarańcza, jabłko, pora, kapusta, sałata, jarmuż) = 54 całkowite zakupy
• (pomarańcza, jabłko, por, kapusta, sałata, jarmuż) = 54 całkowite zakupy

SELECT item, purchases, category, SUM(purchases) OVER () AS total_purchasesFROM Produce+-------------------------------------------------------+| item | purchases | category | total_purchases |+-------------------------------------------------------+| orange | 2 | fruit | 54 || leek | 2 | vegetable | 54 || apple | 8 | fruit | 54 || cabbage | 9 | vegetable | 54 || lettuce | 10 | vegetable | 54 || kale | 23 | vegetable | 54 |+-------------------------------------------------------+

Oblicz sumę częściową

to oblicza sumę częściową dla każdej kategorii wProduce table.

• owoce
  • (pomarańcza, jabłko) = 10 zakupów ogółem
  • (pomarańcza, jabłko) = 10 zakupów ogółem
• warzywa
  • (por, kapusta, sałata, jarmuż) = 44 zakupów ogółem
  • (por, kapusta, sałata, jarmuż) = 44 zakupów ogółem
  • (por, kapusta, sałata, jarmuż) = 44 suma zakupów
  • (por, kapusta, sałata, jarmuż) = 44 suma zakupów

SELECT item, purchases, category, SUM(purchases) OVER ( PARTITION BY category ORDER BY purchases ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS total_purchasesFROM Produce+-------------------------------------------------------+| item | purchases | category | total_purchases |+-------------------------------------------------------+| orange | 2 | fruit | 10 || apple | 8 | fruit | 10 || leek | 2 | vegetable | 44 || cabbage | 9 | vegetable | 44 || lettuce | 10 | vegetable | 44 || kale | 23 | vegetable | 44 |+-------------------------------------------------------+

Oblicz sumę skumulowaną

oblicza sumę skumulowaną dla każdej kategorii w tabeliProduce. Suma jest obliczana w odniesieniu do twierdzenia zdefiniowanego za pomocą klauzuliORDER BY.

• owoce
  • (pomarańcza, jabłko) = 2 zakupy ogółem
  • (pomarańcza, jabłko) = 10 zakupy ogółem
• warzywa
  • (por, kapusta, sałata, jarmuż) = 2 zakupy ogółem
  • (por, kapusta, sałata, jarmuż) = 11 zakupy ogółem
  • (por, kapusta, sałata, jarmuż) = 21 zakupy ogółem
  • (por, kapusta, sałata, jarmuż) = 44 Total purchases

SELECT item, purchases, category, SUM(purchases) OVER ( PARTITION BY category ORDER BY purchases ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS total_purchasesFROM Produce+-------------------------------------------------------+| item | purchases | category | total_purchases |+-------------------------------------------------------+| orange | 2 | fruit | 2 || apple | 8 | fruit | 10 || leek | 2 | vegetable | 2 || cabbage | 9 | vegetable | 11 || lettuce | 10 | vegetable | 21 || kale | 23 | vegetable | 44 |+-------------------------------------------------------+

robi to samo, co w poprzednim przykładzie. Nie musisz dodawaćCURRENT ROW jako granicy, chyba że chcesz, aby była czytelna.

SELECT item, purchases, category, SUM(purchases) OVER ( PARTITION BY category ORDER BY purchases ROWS UNBOUNDED PRECEDING ) AS total_purchasesFROM Produce

w tym przykładzie wszystkie elementy w tabeliProduce są dołączone do partycji. Analizowane są tylko poprzedzające wiersze. Analiza rozpoczyna się dwa razy przed bieżącym wierszem na partycji.

• (pomarańcza, por, jabłko, kapusta, sałata, jarmuż) = NULL
• (pomarańcza, por, jabłko, kapusta, sałata, jarmuż) = NULL
• (pomarańcza, por, jabłko, kapusta, sałata, jarmuż) = 2
• (pomarańcza, por, jabłko, kapusta, sałata, jarmuż) = 4
• (pomarańcza, por, jabłko, kapusta, sałata, jarmuż) = 12
• (pomarańcza, por, jabłko, kapusta, sałata, jarmuż) = 21

SELECT item, purchases, category, SUM(purchases) OVER ( ORDER BY purchases ROWS BETWEEN UNBOUNDED PRECEDING AND 2 PRECEDING ) AS total_purchasesFROM Produce;+-------------------------------------------------------+| item | purchases | category | total_purchases |+-------------------------------------------------------+| orange | 2 | fruit | NULL || leek | 2 | vegetable | NULL || apple | 8 | fruit | 2 || cabbage | 9 | vegetable | 4 || lettuce | 10 | vegetable | 12 || kale | 23 | vegetable | 21 |+-------------------------------------------------------+

Oblicz średnią ruchomą

to oblicza średnią ruchomą w tabeli Produce.Dolna granica to 1 wiersz przed bieżącym wierszem. Górna granica jest 1 wiersz po bieżącym wierszu.

• (pomarańcza, por, jabłko, kapusta, sałata, jarmuż) = 2 średnie zakupy
• (pomarańcza, por, jabłko, kapusta, sałata, jarmuż) = 4 średnie zakupy
• (pomarańcza, por, jabłko, kapusta, sałata, jarmuż) = 6.3333 średnie zakupy
• (pomarańcza, por, jabłko, kapusta, sałata, jarmuż) = 9 średnie zakupy
• (pomarańcza, por, jabłko,, kapusta, sałata, jarmuż) = 14 średnie zakupy
• (pomarańcza, por, jabłko, kapusta, sałata, jarmuż) = 16.5 średnie zakupy

SELECT item, purchases, category, AVG(purchases) OVER ( ORDER BY purchases ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING ) AS avg_purchasesFROM Produce+-------------------------------------------------------+| item | purchases | category | avg_purchases |+-------------------------------------------------------+| orange | 2 | fruit | 2 || leek | 2 | vegetable | 4 || apple | 8 | fruit | 6.33333 || cabbage | 9 | vegetable | 9 || lettuce | 10 | vegetable | 14 || kale | 23 | vegetable | 16.5 |+-------------------------------------------------------+

oblicz liczbę artykułów w zakresie

Ten przykład pobiera liczbę zwierząt, które mają podobną populację w tabeliFarm.

• (Gęś, pies, wół, Koza, kaczka, kot) = 4 zwierzęta w przedziale populacji 0-2.
• (Gęś, pies, wół, Koza, kaczka, Kot) = 5 zwierząt w przedziale populacji 1-3.
• (Gęś, pies, wół, Koza, kaczka, Kot) = 5 zwierząt w przedziale populacji 1-3.
• (Gęś, pies, wół, Koza, kaczka, Kot) = 5 zwierząt w przedziale populacji 1-3.
• (Gęś, pies, wół, Koza, kaczka, kot) = 4 zwierzęta w przedziale populacji 2-4.
• (Gęś, pies, wół, Koza, kaczka, kot) = 1 zwierzę w przedziale populacji 22-24.

SELECT animal, population, category, COUNT(*) OVER ( ORDER BY population RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING ) AS similar_populationFROM Farm;+----------------------------------------------------------+| animal | population | category | similar_population |+----------------------------------------------------------+| goose | 1 | bird | 4 || dog | 2 | mammal | 5 || ox | 2 | mammal | 5 || goat | 2 | mammal | 5 || duck | 3 | bird | 4 || cat | 23 | mammal | 1 |+----------------------------------------------------------+

Pobierz Najpopularniejszy element w każdej kategorii

Ten przykład pobiera najpopularniejszy element w każdej kategorii. Określa, w jaki sposób wiersze w oknie są dzielone i uporządkowane na każdej partycji. Odwołuje się do tabeliProduce.

• owoce
  • (pomarańcza, jabłko) = jabłko jest najpopularniejsze
  • (pomarańcza, jabłko) = jabłko jest najpopularniejsze
• warzywo
  • (por, kapusta, sałata, jarmuż) = jarmuż jest najpopularniejszy
  • (por, kapusta, sałata, jarmuż) = jarmuż jest najpopularniejszy
  • (por, kapusta, sałata, jarmuż) = jarmuż jest najpopularniejszy

SELECT item, purchases, category, LAST_VALUE(item) OVER ( PARTITION BY category ORDER BY purchases ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS most_popularFROM Produce+----------------------------------------------------+| item | purchases | category | most_popular |+----------------------------------------------------+| orange | 2 | fruit | apple || apple | 8 | fruit | apple || leek | 2 | vegetable | kale || cabbage | 9 | vegetable | kale || lettuce | 10 | vegetable | kale || kale | 23 | vegetable | kale |+----------------------------------------------------+

Pobierz ostatnią wartość w zakresie

Ten przykład pobiera najpopularniejszy element w określonej ramce okna, używającProduce stół. Rama okna analizuje do trzech razy na raz. Przyjrzyj się uważnie kolumniemost_popular dotyczącej warzyw.Zamiast uzyskać najpopularniejszy przedmiot w określonej kategorii, otrzymuje on najbardziej popularny przedmiot w określonym zakresie w tej kategorii.

• owoce
  • (pomarańcza, jabłko) = najpopularniejsze jabłko
  • (pomarańcza, jabłko) = najpopularniejsze jabłko
• warzywo
  • (por, kapusta, sałata, jarmuż) = najpopularniejsza jest kapusta
  • (por, kapusta, sałata, jarmuż) = najpopularniejsza jest sałata
  • (por, kapusta, sałata, jarmuż) = jest najpopularniejszy
  • (por, kapusta, sałata, jarmuż) = jarmuż jest najpopularniejszy

SELECT item, purchases, category, LAST_VALUE(item) OVER ( PARTITION BY category ORDER BY purchases ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING ) AS most_popularFROM Produce+----------------------------------------------------+| item | purchases | category | most_popular |+----------------------------------------------------+| orange | 2 | fruit | apple || apple | 8 | fruit | apple || leek | 2 | vegetable | cabbage || cabbage | 9 | vegetable | lettuce || lettuce | 10 | vegetable | kale || kale | 23 | vegetable | kale |+----------------------------------------------------+

Ten przykład zwraca te same wyniki co poprzedni przykład, ale zawiera okno o nazwie item_window. Niektóre specyfikacje okna są zdefiniowane bezpośrednio w klauzuliOVER, a niektóre są zdefiniowane w nazwanym oknie.

SELECT item, purchases, category, LAST_VALUE(item) OVER ( item_window ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING ) AS most_popularFROM ProduceWINDOW item_window AS ( PARTITION BY category ORDER BY purchases)

Oblicz rangę

Ten przykład oblicza rangę każdego pracownika w swoim dziale NA podstawie daty rozpoczęcia pracy. Specyfikacja okna jest definiowana bezpośrednio w klauzuliOVEREmployees

• Departament 1
  • (Jacob, Anthony, Andrew) = Przypisz rangę 1 do Jacoba
  • (Jacob, Anthony, Andrew) = Przypisz rangę 2 do Anthony
  • (Jacob, Anthony, Andrew) = Przypisz rangę 3 do Andrew
• Departament 2
  • (Isabella, Daniel, Jose) = Przypisz rangę 1 do Isabelli
  • (Isabella, Daniel, Jose) = Przypisz rangę 2 do Daniel
  • (Isabella, Daniel, Jose) = Przypisz rangę 3 do Jose

SELECT name, department, start_date, RANK() OVER (PARTITION BY department ORDER BY start_date) AS rankFROM Employees;+--------------------------------------------+| name | department | start_date | rank |+--------------------------------------------+| Jacob | 1 | 1990-07-11 | 1 || Anthony | 1 | 1995-11-29 | 2 || Andrew | 1 | 1999-01-23 | 3 || Isabella | 2 | 1997-09-28 | 1 || Daniel | 2 | 2004-06-24 | 2 || Jose | 2 | 2013-03-17 | 3 |+--------------------------------------------+

użyj nazwanego okna w klauzuli ramki okna

Możesz zdefiniować część swojej logiki w nazwanym oknie, a część w klauzula ramki okna. Ta logika jest połączona. Oto przykład, używając tabeli Produce.

SELECT item, purchases, category, LAST_VALUE(item) OVER (item_window) AS most_popularFROM ProduceWINDOW item_window AS ( PARTITION BY category ORDER BY purchases ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)+-------------------------------------------------------+| item | purchases | category | most_popular |+-------------------------------------------------------+| orange | 2 | fruit | apple || apple | 8 | fruit | apple || leek | 2 | vegetable | lettuce || cabbage | 9 | vegetable | kale || lettuce | 10 | vegetable | kale || kale | 23 | vegetable | kale |+-------------------------------------------------------+

Możesz również uzyskać poprzednie wyniki za pomocą następujących przykładów:

SELECT item, purchases, category, LAST_VALUE(item) OVER (item_window) AS most_popularFROM ProduceWINDOW a AS (PARTITION BY category), b AS (a ORDER BY purchases), c AS (b ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING), item_window AS (c)

SELECT item, purchases, category, LAST_VALUE(item) OVER (item_window ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS most_popularFROM ProduceWINDOW a AS (PARTITION BY category), b AS (a ORDER BY purchases), item_window AS (b)

poniższy przykład powoduje błąd, ponieważ klauzula ramki okna została zdefiniowana dwukrotnie:

SELECT item, purchases, category, LAST_VALUE(item) OVER ( item_window ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING ) AS most_popularFROM ProduceWINDOW item_window AS ( ORDER BY purchases ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)