en analytisk funktion beräknar värden över en grupp rader och returnerar ett enda resultat för varje rad. Detta skiljer sig från en aggregerad funktion,som returnerar ett enda resultat för en grupp rader.

en analysfunktion innehåller enOVER – klausul, som definierar ett fönster med radarrunt raden som utvärderas. För varje rad resulterar den analytiska funktionenberäknas med det valda fönstret i rader som inmatning, möjligtgör aggregering.

med analytiska funktioner kan du beräkna glidande medelvärden, rangordna objekt, beräkna kumulativa summor och utföra andra analyser.

följande funktioner kan användas som analytiska funktioner: navigeringsfunktioner, numreringsfunktioner ochaggregera analytiska funktioner

analytisk funktion syntax

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

noteringsregler

• hakparenteser ”” anger valfria klausuler.
• parenteser ”() ” anger bokstavliga parenteser.
• den vertikala stapeln ” / ” indikerar en logisk eller.
• Curly braces ”{ } ” bifoga en uppsättning alternativ.
• ett komma följt av en ellips inom hakparenteser ”” indikerar attDet föregående objektet kan upprepas i en kommaseparerad lista.

beskrivning

en analysfunktion beräknar resultat över en grupp rader. Du kan använda följande syntax för att bygga en analytisk funktion:

• analytic_function_name: funktionen som utför en analytisk operation.Till exempel kan numreringsfunktionen RANK() användas här.
• argument_list: argument som är specifika för den analytiska funktionen.Vissa funktioner har dem, vissa gör det inte.
• OVER: nyckelord som krävs i analytisk funktion syntax prejudicthe OVER klausul.
• over_clause: refererar till ett fönster som definierar en grupp av rader i en tabell för att använda en analysfunktion.
• window_specification: definierar specifikationerna förfönstret.
• window_frame_clause: definierar fönsterramenför fönstret.
• rows_range: definierar de fysiska raderna eller alogiska intervallet för en fönsterram.

anteckningar

en analysfunktion kan visas som ett skalärt uttryck operand intvå platser i frågan:

• SELECT listan. Om den analytiska funktionen visas iSELECT – listan kan dess argumentlista ochOVER – klausulen inte hänvisa till alias som introducerades i samma SELECT-lista.
• ORDER BY klausul. Om den analytiska funktionen visas iORDER BY – klausulen i frågan kan dess argumentlista hänvisa tillSELECT listalias.

en analytisk funktion kan inte referera till en annan analytisk funktion i dessargumentlista eller dessOVER klausul, även indirekt genom ett alias.

en analytisk funktion utvärderas efter aggregering. Till exempel utvärderasGROUP BY-klausulen och icke-analytiska aggregatfunktioner först.Eftersom aggregerade funktioner utvärderas före analytiska funktioner kan aggregerade funktioner användas som inmatningsoperander till analytiska funktioner.

returnerar

ett enda resultat för varje rad i inmatningen.

definiera överklausulen

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

beskrivning

OVER klausul refererar till ett fönster som definierar en grupp rader i en tabellpå vilken en analysfunktion ska användas. Du kan ange ettnamed_window som ärdefinierat i din fråga, eller du kandefiniera specifikationerna för ett nytt fönster.

anteckningar

om varken ett namngivet fönster eller fönsterspecifikation tillhandahålls ingår allinput-rader i fönstret för varje rad.

exempel medOVER klausul

dessa frågor använder fönsterspecifikationer:

• Beräkna en totalsumma
• Beräkna en kumulativ summa
• beräkna ett glidande medelvärde
• beräkna antalet objekt inom ett intervall
• få det mest populära objektet i varje kategori
• hämta det sista värdet i ett intervall
• compute rank

dessa frågor använder ett namngivet fönster:

• hämta det sista värdet i ett intervall
• Använd ett namngivet fönster i en fönsterramsats

definiera fönsterspecifikationen

window_specification: ] ] 

beskrivning

definierar specifikationerna för fönstret.

• named_window: namnet på ett befintligt fönster som vardefinierat med en WINDOW klausul.

• PARTITION BY: bryter upp ingångsraderna i separata partitioner, översom den analytiska funktionen utvärderas oberoende.
  • flera partitionsuttryck är tillåtna iPARTITION BY – klausulen.
  • ett uttryck kan inte innehålla flyttalstyper, icke-grupperbara typer, konstanter eller analytiska funktioner.
  • om denna valfria klausul inte används, alla rader i inmatningstabellenkomprisera en enda partition.
• ORDER BY: definierar hur rader beställs inom en partition.Denna klausul är valfri i de flesta situationer, men krävs i vissafall för navigeringsfunktioner.
• window_frame_clause: För aggregate analyticfunktioner, definierar fönsterramen inom den aktuella partitionen.Fönsterramen bestämmer vad som ska inkluderas i fönstret.Om denna klausul används krävs ORDER BY förutom fullt avgränsade fönster.

anteckningar

om varkenORDER BY eller fönsterramsklausul finns,innehåller fönsterramen alla rader i den partitionen.

för aggregerade analytiska funktioner, omORDER BY klausul är närvarande menfönsterramsklausulen är inte följande fönsterramklausulanvänds som standard:

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

till exempel är följande frågor likvärdiga:

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

regler för att använda ett namngivet fönster i fönsterspecifikationen

Om du använder ett namngivet fönster i dina fönsterspecifikationer gäller dessa regler:

• specifikationerna i det angivna fönstret kan utökasmed nya specifikationer som du definierar i fönsterspecifikationsklausulen.
• Du kan inte ha överflödiga definitioner. Om du har ett ORDER BY clausei det namngivna fönstret och fönsterspecifikationsklausulen kastas anerror.

• klausulernas ordning är viktig. PARTITION BY måste komma först, följt av ORDER BY och window_frame_clause. Om du lägger till ett namngivet fönster behandlas dess fönsterspecifikationer först.

  --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)

• ett namngivet fönster och PARTITION BY kan inte visas tillsammans i fönsterspecifikationen. Om du behöver PARTITION BY, Lägg till det i det namngivna fönstret.

• Du kan inte referera till ett namngivet fönster i ORDER BY, en yttre fråga eller någon underfråga.

exempel med fönsterspecifikationen

dessa frågor definierar partitioner i en analysfunktion:

• Beräkna en delsumma
• Beräkna en kumulativ summa
• få det mest populära objektet i varje kategori
• hämta det sista värdet i ett intervall
• beräkna rang
• Använd ett namngivet fönster i en fönsterramsklausul

dessa frågor inkluderar ett namngivet fönster i en fönsterspecifikation:

• hämta det sista värdet i ett intervall
• Använd ett namngivet fönster i en fönsterramsats

dessa frågor definierar hur rader beställs i en partition:

• Beräkna en delsumma
• Beräkna en kumulativ summa
• beräkna ett glidande medelvärde
• beräkna antalet objekt inom ett intervall
• få det mest populära objektet i varje kategori
• hämta det sista värdet i ett intervall
• beräkna rang
• Använd ett namngivet fönster i en fönsterramsklausul

definiera fönsterramen klausul

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

Fönsterramsklausulen definierar fönsterramen runt den aktuella raden meden partition, över vilken analysfunktionen utvärderas.Endast aggregerade analytiska funktioner kan använda en fönsterramsklausul.

• rows_range: en klausul som definierar en fönsterram med fysiska radareller ett logiskt intervall.

  • ROWS: beräknar fönsterramen baserat på fysiska förskjutningar från den aktuella raden. Du kan till exempel inkludera två rader före och efterden aktuella raden.
  • RANGE: beräknar fönsterramen baserat på ett logiskt radintervall runt den aktuella raden, baserat på den aktuella radens ORDER BY nyckelvärde.Det angivna intervallvärdet läggs till eller subtraheras till det aktuella radskey-värdet för att definiera en start-eller slutgräns för fönsterramen. I en intervallbaserad fönsterram måste det finnas exakt enuttryck iORDER BY – klausulen, och uttrycket måste ha anumerisk typ.

  tips: om du vill använda ett intervall med ett datum, användORDER BY med funktionenUNIX_DATE(). Om du vill använda ett intervall med en tidsstämpel använder du funktionenUNIX_SECONDS()UNIX_MILLIS() ellerUNIX_MICROS().

• frame_between: skapar en fönsterram med en nedre och övre gräns.Den första gränsen representerar den nedre gränsen. Den andra gränsenrepresenterar den övre gränsen. Endast vissa gränskombinationer kan varaanvänds, som visas i föregående syntax.

  • definiera början av fönsterramen medunbounded_precedingnumeric_precedingnumeric_following, ellercurrent_row.
    • unbounded_preceding: fönsterramen börjar i början avpartition.
    • numeric_precedingeller numeric_following: fönstrets start är relativt den aktuella raden.
    • current_row: fönsterramen startar vid den aktuella raden.
  • definiera slutet på fönsterramen mednumeric_precedingnumeric_followingcurrent_row, ellerunbounded_following.
    • numeric_precedingeller numeric_following: fönstrets slut är relativt den aktuella raden.
    • current_row: Fönsterramen slutar vid den aktuella raden.
    • unbounded_following: fönsterramen slutar i slutet avpartition.

• frame_start: skapar en fönsterram med en nedre gräns.Fönsterramen slutar vid den aktuella raden.

  • unbounded_preceding: fönsterramen börjar i början avpartition.
  • numeric_preceding: fönsterramens start är relativt den aktuella raden.
  • current_row: fönsterramen startar vid den aktuella raden.

• numeric_expression: ett uttryck som representerar en numerisk typ.Det numeriska uttrycket måste vara en konstant, icke-negativ helteller parameter.

anteckningar

om en gräns sträcker sig bortom början eller slutet av en partition kommer fönsterramen endast att innehålla rader inifrån den partitionen.

Du kan inte använda en fönsterramsklausul mednavigeringsfunktioner och numreringsfunktioner, till exempel RANK().

exempel med hjälp av fönsterramsklausulen

dessa frågor beräknar värden med ROWS:

• Beräkna en kumulativ summa
• beräkna ett glidande medelvärde
• få det mest populära objektet i varje kategori
• få det sista värdet i ett intervall
• Använd ett namngivet fönster i en fönsterramsklausul

dessa frågor beräknar värden med RANGE:

• beräkna värdet i en antal objekt inom ett intervall

dessa frågor beräknar värden med ett helt eller delvis obundet fönster:

• Beräkna en totalsumma
• Beräkna en delsumma
• Beräkna en kumulativ summa
• få det mest populära objektet i varje kategori
• beräkna rank

dessa frågor beräknar värden med numeriska gränser:

• Beräkna en kumulativ summa
• beräkna ett glidande medelvärde
• beräkna antalet objekt inom en range
• hämta det sista värdet i ett intervall
• Använd ett namngivet fönster i en fönsterramsats

dessa frågor beräknar värden med den aktuella raden som en gräns:

• Beräkna en totalsumma
• Beräkna en delsumma
• Beräkna en kumulativ summa

referera till ett namngivet fönster

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 | ( ) }

ett namngivet fönster representerar en grupp rader i en tabell för att använda ananalytisk funktion. Ett namngivet fönster definieras iWINDOW – klausulen och refereras till Inan analytic-funktionens OVER clause.In enOVER – klausul, ett namngivet fönster kan visas antingen av sig själv eller inbäddasinom en fönsterspecifikation.

exempel

• hämta det sista värdet i ett intervall
• Använd ett namngivet fönster i en fönsterramsklausul

navigationsfunktionskoncept

navigationsfunktioner beräknar i allmänhet någravalue_expression över en annan rad i fönsterramen från den aktuella raden. OVER klausulsyntaxen varierar mellan navigeringsfunktioner.

krav förOVER klausul:

• PARTITION BY: valfritt.
• 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.

för alla navigeringsfunktioner är resultatdatatypen samma typ somvalue_expression.

Numreringsfunktionskoncept

Numreringsfunktioner tilldela heltalvärden tillvarje rad baserat på deras position i det angivna fönstret.

exempel på RANK()DENSE_RANK() och ROW_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(): För x = 5 ärrank 4, eftersomRANK() steg med numberof peers i föregående fönsterbeställningsgrupp.
• DENSE_RANK(): för x = 5,dense_rank är 3, eftersomDENSE_RANK() alwaysincrements med 1, hoppar aldrig över ett värde.
• ROW_NUMBER(): för x=5, row_num är 4.

Aggregate analytic function concepts

en aggregatfunktion är en funktion som utför en beräkning på aset av värden. De flesta aggregerade funktioner kan användas i ananalytisk funktion. Dessa aggregerade funktioner kallasaggregera analytiska funktioner.

med aggregerade analytiska funktioner läggsOVER – satsen till aggregatfunktionssamtal; syntaxen för funktionssamtal förblir annars oförändrad.Liksom deras aggregerade funktion motsvarigheter, utför dessa analytiska funktioneraggregationer, men specifikt över den relevanta fönsterramen för varje rad.Resultatdatatyperna för dessa analytiska funktioner är desamma som derasaggregatfunktion motsvarigheter.

analytiska funktionsexempel

i dessa exempel är det markerade objektet den aktuella raden. De boldeditems är de rader som ingår i analysen.

vanliga tabeller som används i exempel

Följande tabeller används i de efterföljande aggregerade analyticquery-exemplen: ProduceEmployeesoch Farm.

producera tabell

några exempel hänvisar till en tabell som heter Produce:

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 |+-------------------------------------+

anställda tabell

några exempel hänvisar till en tabell som heter Employees:

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 |+-------------------------------------+

Farm table

några exempel hänvisar till en tabell som heter Farm:

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 |+-------------------------------------+

beräkna en totalsumma

detta beräknar en totalsumma för alla objekt iProduce tabell.

• (apelsin, äpple, purjolök, kål, sallad, grönkål) = 54 totala inköp
• (apelsin, äpple, purjolök, kål, sallad, grönkål) = 54 totala inköp
• (apelsin, äpple, purjolök, kål, sallad, grönkål) = 54 totala inköp
• (apelsin, äpple, purjolök, kål, sallad, grönkål) = 54 totala inköp
• (apelsin, äpple, purjolök, kål, sallad, grönkål) = 54 totala inköp
• (apelsin, äpple, purjolök, kål, sallad, grönkål) = 54 totala inköp
• (apelsin, äpple, purjolök, kål, sallad, grönkål)=54 totala inköp
• (apelsin, äpple, purjolök, kål, sallad, kale) = 54 totala inköp
• (apelsin, äpple, purjolök, kål, sallad, grönkål) = 54 totala inköp

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 |+-------------------------------------------------------+

beräkna en delsumma

detta beräknar en delsumma för varje kategori i Produce tabell.

• frukt
  • (apelsin, äpple) = 10 totala inköp
  • (apelsin, äpple) = 10 totala inköp
• vegetabiliska
  • (purjolök, kål, sallad, grönkål) = 44 totala inköp
  • (purjolök, kål, sallad, grönkål) = 44 totala inköp
  • (purjolök, kål, sallad, grönkål) = 44 totala inköp
  • (purjolök, kål, sallad, grönkål) = 44 totala inköp
  • /li>
  • (purjolök, kål, sallad, grönkål)=44 totala inköp

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 |+-------------------------------------------------------+

beräkna en kumulativ summa

detta beräknar en kumulativ summa för varje kategori i Produce tabell. Summan beräknas med avseende på theorder definieras medORDER BY klausul.

• frukt
  • (apelsin, äpple) = 2 totala inköp
  • (apelsin, äpple) = 10 totala inköp
• vegetabiliska
  • (purjolök, kål, sallad, grönkål) = 2 totala inköp
  • (purjolök, kål, sallad, grönkål) = 11 totala inköp
  • (purjolök, kål, sallad, grönkål) = 21 totala inköp
  • (purjolök, kål, sallad, grönkål) = 44 totala inköp

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 |+-------------------------------------------------------+

detta gör samma sak som föregående exempel. Du behöver inte lägga tillCURRENT ROW som en gräns om du inte vill läsa.

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

i det här exemplet ingår alla objekt i Produce tabelleni partitionen. Endast föregående rader analyseras. Analysen startar två rader före den aktuella raden i partitionen.

• (apelsin, purjolök, äpple, kål, sallad, grönkål) = NULL
• (apelsin, purjolök, äpple, kål, sallad, grönkål) = NULL
• (apelsin, purjolök, äpple, kål, sallad, grönkål) = 4
• (apelsin, purjolök, äpple, kål, sallad, grönkål) = 12
• (apelsin, purjolök, äpple, kål, sallad, grönkål) = 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 |+-------------------------------------------------------+

beräkna ett glidande medelvärde

detta beräknar ett rörligt medelvärde iProducetabell.Den nedre gränsen är 1 rad förenuvarande rad. Den övre gränsen är 1 rad efter den aktuella raden.

• (apelsin, purjolök, äpple, kål, sallad, grönkål) = 2 genomsnittliga inköp
• (apelsin, purjolök, äpple, kål, sallad, grönkål) = 4 genomsnittliga inköp
• (apelsin, purjolök, äpple, kål, sallad, grönkål) = 6.3333 genomsnittliga inköp
• (apelsin, purjolök, äpple, kål, sallad, grönkål) = 9 genomsnittliga inköp
• (apelsin, purjolök, äpple, kål, sallad, grönkål) = 9 genomsnittliga inköp
• (apelsin, purjolök, äpple, kål, sallad, grönkål) = 9 genomsnittliga inköp
• (apelsin, purjolök, äpple, kål, sallad, grönkål) = 14 genomsnittliga inköp
• (apelsin, purjolök, äpple, kål, sallad, grönkål) = 16.5 genomsnittliga inköp

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 |+-------------------------------------------------------+

beräkna antalet objekt inom ett intervall

detta exempel får antalet djur som har en liknande befolkningräkna i Farm tabell.

• (gås, hund, Oxe, get, anka, katt) = 4 djur mellan populationsintervallet 0-2.
• (gås, hund, Oxe, get, anka, katt) = 5 djur mellan populationsintervall 1-3.
• (gås, hund, Oxe, get, anka, katt) = 5 djur mellan populationsintervall 1-3.
• (gås, hund, Oxe, get, anka, katt) = 5 djur mellan populationsintervall 1-3.
• (gås, hund, Oxe, get, anka, katt) = 4 djur mellan populationsintervallet 2-4.
• (gås, hund, Oxe, get, anka, katt) = 1 Djur mellan populationsintervallet 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 |+----------------------------------------------------------+

få det mest populära objektet i varje kategori

det här exemplet får det mest populära objektet i varje kategori. Det definierar hur radari ett fönster partitioneras och beställs i varje partition. TabellenProduce refereras till.

• fruit
  • (apelsin, äpple) = apple är mest populärt
  • (apelsin, äpple) = apple är mest populärt
• vegetabiliska
  • (blomkål, vitkål, sallad, grönkål) = grönkål är mest populärt
  • (blomkål, vitkål, sallad, grönkål) = grönkål är mest populärt
  • (blomkål, vitkål, sallad, grönkål) = grönkål är mest populärt
  • (blomkål, vitkål, sallad, grönkål) = grönkål är mest populärt

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 |+----------------------------------------------------+

Få det sista värdet i ett intervall

Detta exempel blir den mest populära i ett visst fönster, usingthe Produce tabell. Fönsterramen analyserar upp till trerader åt gången. Ta en närmare titt på kolumnen most_popular för grönsaker.Istället för att få det mest populära objektet i en viss kategori blir det det mest populära objektet i ett visst intervall i den kategorin.

• frukt
  • (apelsin, äpple) = äpple är mest populära
  • (apelsin, äpple) = äpple är mest populära
• vegetabiliska
  • (purjolök, kål, sallad, grönkål) = kål är mest populära
  • (purjolök, kål, sallad, grönkål) = sallad är mest populära
  • (purjolök, kål, sallad, grönkål) = sallad är mest populära
  • (purjolök, kål, sallad, grönkål) = grönkål är mest populära
  • /li>
  • (purjolök, kål, sallad, kale)=kale är mest populär

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 |+----------------------------------------------------+

detta exempel returnerar samma resultat som föregående exempel, men det inkluderarett namngivet fönster som heter item_window. Några av fönsterspecifikationerna ärdefinieras direkt iOVER och vissa definieras i det namngivna fönstret.

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)

Compute rank

detta exempel beräknar rankningen för varje anställd inom deras avdelning,baserat på deras startdatum. Fönsterspecifikationen definieras direktiOVER – klausulen. TabellenEmployees refereras till.

• avdelning 1
  • (Jacob, Anthony, Andrew) = tilldela rang 1 till Jacob
  • (Jacob, Anthony, Andrew) = tilldela rang 2 till Anthony
  • (Jacob, Anthony, Andrew) = tilldela rang 3 till Andrew
• avdelning 2
  • (Isabella, Daniel, Jose) = tilldela rang 1 till Isabella
  • (Isabella, Daniel, Jose) = tilldela rang 2 till Daniel
  • (Isabella, Daniel, Jose) = tilldela rang 3 till 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 |+--------------------------------------------+

Använd ett namngivet fönster i en fönsterramsklausul

Du kan definiera en del av din logik i ett namngivet fönster och en del av det i ett namngivet fönster awindow frame klausul. Denna logik kombineras. Här är ett exempel medProduce – tabellen.

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 |+-------------------------------------------------------+

Du kan också få de tidigare resultaten med dessa exempel:

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)

följande exempel ger ett fel eftersom en fönsterramsklausul har definierats två gånger:

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)