een analytische functie berekent waarden over een groep rijen en retourneert een enkel resultaat voor elke rij. Dit verschilt van een aggregaatfunctie, die een enkel resultaat voor een groep rijen retourneert.

een analytische functie bevat eenOVER clausule, die een venster van rijen definieert rond de rij die wordt geëvalueerd. Voor elke rij wordt het resultaat van de analytische functie berekend met behulp van het geselecteerde venster van rijen als invoer, mogelijkdoing aggregatie.

met analytische functies kunt u voortschrijdende gemiddelden berekenen, items rangschikken, sommen berekenen en andere analyses uitvoeren.

de volgende functies kunnen als analytische functies worden gebruikt:navigatiefuncties, nummering, en aggregate analytische functies

analytische functie syntaxis

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

Notatieregels

• vierkante haakjes “” geven optionele clausules aan.
• haakjes “() ” geven letterlijke haakjes aan.
• de verticale balk ” | ” geeft een logische OR aan.
• accolades “{ } ” omsluiten een reeks opties.
• een komma gevolgd door een ellips tussen vierkante haakjes “” geeft aan dat het vorige item kan herhalen in een door komma ‘ s gescheiden lijst.

beschrijving

een analytische functie berekent resultaten over een groep rijen. U kunt de volgende syntaxis gebruiken om een analytische functie te bouwen:

• analytic_function_name: de functie die een analytische bewerking uitvoert.Bijvoorbeeld, de nummering functie RANK() kan hier worden gebruikt.
• argument_list: argumenten die specifiek zijn voor de analytische functie.Sommige functies hebben ze, sommige niet.
• OVER: sleutelwoord vereist in de analytische functie syntaxis voorafgaand aan de OVER clausule.
• over_clause: verwijst naar een venster dat een groep rijen in een tabel definieert waarop een analytische functie moet worden gebruikt.
• window_specification: definieert de specificaties voor het venster.
• window_frame_clause: definieert het vensterframe voor het venster.
• rows_range: definieert de fysieke rijen of alogisch bereik voor een vensterframe.

notities

een analytische functie kan verschijnen als een scalaire expressieoperand op twee plaatsen in de query:

• De SELECT lijst. Als de analytische functie in de SELECT lijst voorkomt, kunnen de lijst met argumenten en OVER niet verwijzen naar aliassen die in dezelfde selectielijst zijn geïntroduceerd.
• deORDER BY clausule. Als de analytische functie voorkomt in de ORDER BY clausule van de query, kan de lijst met argumenten verwijzen naar SELECT lijstaliassen.

een analytische functie kan niet verwijzen naar een andere analytische functie in zijnargument-lijst of zijn OVER clausule, zelfs niet indirect via een alias.

een analytische functie wordt geëvalueerd na aggregatie. Bijvoorbeeld, deGROUP BY clausule en niet-analytische aggregaat functies worden eerst geëvalueerd.Omdat geaggregeerde functies vóór analytische functies worden geëvalueerd,kunnen geaggregeerde functies worden gebruikt als inputoperanden voor analytische functies.

geeft

een enkel resultaat voor elke rij in de invoer.

Defining the OVER clause

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

Description

The OVER clause references a window that defines a group of rows in a table upon which to use an analytic function. U kunt eennamed_window opgeven die is gedefinieerd in uw zoekopdracht, of u kunt de specificaties voor een nieuw venster definiëren.

Notes

als geen vensterspecificatie met naam of vensterspecificatie is opgegeven, worden alle invoerrijen voor elke rij in het venster opgenomen.

voorbeelden met behulp van de OVER clausule

Deze queries gebruiken vensterspecificaties:

• Bereken een algemeen totaal
• Bereken een subtotaal
• Bereken een cumulatieve Som
• Bereken een voortschrijdend gemiddelde
• Bereken het aantal items binnen een bereik
• haal het meest populaire item in elk categorie
• haal de laatste waarde in een bereik
• bereken rang

deze queries gebruiken een venster met naam:

• haal de laatste waarde in een bereik
• gebruik een venster met naam in een vensterframe-clausule

Defining the window specification

window_specification: ] ] 

Description

definieert de specificaties voor het venster.

• named_window: de naam van een bestaand venster dat was gedefinieerd met een WINDOW clausule.

• PARTITION BY: splitst de invoerrijen op in afzonderlijke partities, waarover de analytische functie onafhankelijk wordt geëvalueerd.
  • meerdere partitie expressies zijn toegestaan in dePARTITION BY clausule.
  • een expressie kan geen floating-point types, niet-groepeerbare types,constanten of analytische functies bevatten.
  • als deze optionele clausule niet wordt gebruikt, comprimeren alle rijen in de invoertafel een enkele partitie.
• ORDER BY: definieert hoe rijen worden geordend binnen een partitie.Deze bepaling is in de meeste situaties optioneel, maar is in sommige gevallen vereist voor navigatiefuncties.
• window_frame_clause: Voor aggregate analyticfuncties definieert u het vensterframe binnen de huidige partitie.Het vensterraam bepaalt wat er in het venster moet worden opgenomen.Als deze clausule wordt gebruikt, is ORDER BY vereist, behalve voor volledig niet-gebundelde vensters.

Notes

Als noch deORDER BY clausule noch vensterframe clausule aanwezig zijn,bevat het vensterframe alle rijen in die partitie.

voor geaggregeerde analytische functies, als deORDER BY clausule aanwezig is, maar de window frame clausule niet, wordt de volgende window frame clausule standaard gebruikt:

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

bijvoorbeeld, de volgende query ‘ s zijn equivalent:

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

Regels voor het gebruik van een venster met de naam in het venster specificaties

Als u een venster met de naam in uw venster specificaties, deze regels zijn van toepassing:

• De specificaties in de naam in het venster kan worden extendedwith nieuwe specificaties die u opgeeft in het venster bestektekst.
• u kunt geen overbodige definities hebben. Als u een ORDER BY clausin het named window en de window specification clause hebt, wordt anerror gegooid.

• De volgorde van de clausules is van belang. PARTITION BY moet eerst komen,gevolgd door ORDER BY en window_frame_clause. Als u een venster met naam toevoegt, worden de vensterspecificaties eerst verwerkt.

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

• een venster met naam en PARTITION BY kunnen niet samen verschijnen in de Windows-specificatie. Als u PARTITION BY nodig hebt, voeg het dan toe aan het benoemde venster.

• u kunt niet verwijzen naar een venster met een naam in een ORDER BY clausule, een externe query of een subquery.

voorbeelden met behulp van de vensterspecificatie

Deze queries definiëren partities in een analytische functie:

• Bereken een subtotaal
• Bereken een cumulatieve Som
• haal het meest populaire item in elke categorie
• haal de laatste waarde in een bereik
• Bereken rang
• gebruik een genoemd venster in een vensterframe-clausule

deze queries bevatten een venster met een naam in een vensterspecificatie:

• haal de laatste waarde in een bereik
• gebruik een venster met naam in een vensterframe-clausule

Deze queries bepalen hoe rijen worden geordend in een partitie:

• het Berekenen van een subtotaal
• het Berekenen van de cumulatieve som
• het Berekenen van een zwevend gemiddelde
• het Berekenen van het aantal items binnen een bereik
• de meest populaire items in elke categorie
• de laatste waarde in een bereik
• Bereken de rang
• Gebruik een benoemde raam in een kozijn-component

Definiëren van de kozijn-component

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

Het venster frame clausule bepaalt het venster kader rond de huidige rij withina partitie, waarop de analytische functie wordt geëvalueerd.Alleen geaggregeerde analytische functies kunnen een window frame-clausule gebruiken.

• rows_range: een clausule die een vensterframe definieert met fysieke rijen of een logisch bereik.

  • ROWS: berekent het vensterframe op basis van fysieke offsets van de huidige rij. U kunt bijvoorbeeld twee rijen voor en na de huidige rij opnemen.
  • RANGE: berekent het vensterframe op basis van een logisch rijbereik rond de huidige rij, gebaseerd op de huidige rij ORDER BY sleutelwaarde.De opgegeven bereikwaarde wordt toegevoegd of afgetrokken aan de sleutelwaarde van de huidige rij om een begin-of eindbereikgrens voor het window-frame te definiëren. In een range-based window frame, moet er precies oneexpression zijn in deORDER BY clausule, en de expressie moet anumeriek type hebben.

  Tip: Als u een bereik met een datum wilt gebruiken, gebruik dan ORDER BY met de functieUNIX_DATE(). Als u een bereik met een tijdstempel wilt gebruiken,gebruikt u de functie UNIX_SECONDS()UNIX_MILLIS(), of UNIX_MICROS().

• frame_between: maakt een vensterframe aan met een onder-en bovengrens.De eerste grens vertegenwoordigt de ondergrens. De tweede grens vertegenwoordigt de bovengrens. Alleen bepaalde grenscombinaties kunnen worden gebruikt, zoals weergegeven in de voorgaande syntaxis.

  • Definieer het begin van het vensterframe met unbounded_precedingnumeric_precedingnumeric_following, of current_row.
    • unbounded_preceding: het vensterframe begint aan het begin van de partitie.
    • numeric_preceding or numeric_following: het begin van het windowframe is relatief aan de huidige rij.
    • current_row: het vensterraam begint op de huidige rij.
  • Definieer het einde van het vensterframe met numeric_precedingnumeric_followingcurrent_row, of unbounded_following.
    • numeric_preceding or numeric_following: het einde van het windowframe is relatief aan de huidige rij.
    • current_row: Het vensterraam eindigt op de huidige rij.
    • unbounded_following: het vensterframe eindigt aan het einde van de partitie.

• frame_start: maakt een vensterframe aan met een ondergrens.Het vensterraam eindigt op de huidige rij.

  • unbounded_preceding: het vensterframe begint aan het begin van de partitie.
  • numeric_preceding: het begin van het vensterframe is relatief aan de huidige rij.
  • current_row: het vensterraam begint op de huidige rij.

• numeric_expression: een uitdrukking die een numeriek type vertegenwoordigt.De numerieke uitdrukking moet een constante, niet-negatieve integeror parameter zijn.

Notes

als een grens verder reikt dan het begin of het einde van een partitie,zal het vensterframe alleen rijen van binnen die partitie bevatten.

u kunt geen vensterframe-clausule gebruiken met navigatie-functies en nummering-functies, zoals RANK().

voorbeelden met behulp van de window frame clausule

deze queries berekenen waarden met ROWS:

• Bereken een cumulatieve Som
• Bereken een voortschrijdend gemiddelde
• haal het meest populaire item in elke categorie
• haal de laatste waarde in een bereik
• gebruik een venster met naam in een vensterframe-clausule

Deze queries berekenen waarden met RANGE:

• bereken het aantal items binnen een bereik

deze queries berekenen waarden met een gedeeltelijk of volledig ongebonden venster:

• Bereken een algemeen totaal
• Bereken een subtotaal
• Bereken een cumulatieve Som
• haal het meest populaire item in elke categorie
• Bereken rang

Deze queries bereken waarden met numerieke grenzen:

• Bereken een cumulatieve Som
• Bereken een voortschrijdend gemiddelde
• Bereken het getal van items binnen een bereik
• krijg de laatste waarde in een bereik
• gebruik een venster met naam in een Vensterframe-Clausule

deze queries berekenen waarden met de huidige rij als grens:

• Bereken een algemeen totaal
• Bereken een subtotaal
• Bereken een cumulatieve Som

refererend aan een venster met naam

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

een venster met naam vertegenwoordigt een groep rijen in een tabel waarop ananalytische functie moet worden gebruikt. Een venster met naam wordt gedefinieerd in deWINDOW clausule, en er wordt verwezen naar OVER clause.In eenOVER clausule, een venster met naam kan op zichzelf verschijnen of ingebed zijn in een vensterspecificatie.

voorbeelden

• haal de laatste waarde in een bereik
• gebruik een venster met naam in een vensterframe-clausule

Navigatiefunctieconcepten

navigatiefuncties berekenen over het algemeen watvalue_expression over een andere rij in het vensterframe dan de huidige rij. De syntaxis van de OVER varieert tussen navigatiefuncties.

vereisten voor deOVER clausule:

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

voor alle navigatiefuncties is het gegevenstype resultaat hetzelfde alsvalue_expression.

nummering functieconcepten

nummering functies toewijzen gehele waarden aan elke rij op basis van hun positie binnen het opgegeven venster.

voorbeeld vanRANK()DENSE_RANK(), enROW_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(): Voor x=5, is rank 4, omdat RANK() verhogingen door het aantal peers in de vorige venstervolgorde groep.
• DENSE_RANK(): Voor x=5, dense_rank is 3, aangezien DENSE_RANK() altijd eisen door 1, nooit overslaan van een waarde.
• ROW_NUMBER(): Voor x=5, row_num is 4.

geaggregeerde analytische functieconcepten

een geaggregeerde functie is een functie die een berekening uitvoert op aset van waarden. De meeste geaggregeerde functies kunnen in ananalytische functie worden gebruikt. Deze aggregaatfuncties worden aggregaatanalytische functies genoemd.

bij geaggregeerde analytische functies wordt de clausule OVER toegevoegd aan de functieaanroep; de syntaxis van de functieaanroep blijft verder ongewijzigd.Net als hun aggregate functie tegenhangers, voeren deze analytische functies aggregaties uit, maar specifiek over het relevante raamkader voor elke rij.De types van de resultaatgegevens van deze analytische functies zijn hetzelfde als hun tegenhangers van de gegregate functie.

voorbeelden van analytische functies

in deze voorbeelden is het gemarkeerde item de huidige rij. De boldeditems zijn de rijen die zijn opgenomen in de analyse.

gemeenschappelijke tabellen gebruikt in voorbeelden

de volgende tabellen worden gebruikt in de volgende voorbeelden: ProduceEmployees,en Farm.

produceer tabel

enkele voorbeelden verwijzen naar een tabel genaamd 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 |+-------------------------------------+

werknemers tabel

enkele voorbeelden refereren een tabel genaamd 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 tabel

enkele voorbeelden refereren een tabel genaamd 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 |+-------------------------------------+

Bereken een totaal

Dit berekent een totaal voor alle items in de tabelProduce.

• (sinaasappel, appel, prei, kool, sla, boerenkool) = 54 totale aankopen
• (sinaasappel, appel, prei, kool, sla, boerenkool) = 54 totale aankopen
• (sinaasappel, appel, prei, kool, sla, boerenkool) = 54 totale aankopen
• (sinaasappel, appel, prei, kool, sla, boerenkool) = 54 totale aankopen
• (sinaasappel, appel, prei, kool, sla, boerenkool) = 54 totale aankopen
• (sinaasappel, appel, prei, kool, sla, boerenkool) = 54 totale aankopen

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

het Berekenen van een subtotaal

Dit berekent een subtotaal voor elke categorie in deProduce tabel.

• fruit
  • (sinaasappel, appel) = 10 totale aankopen
  • (sinaasappel, appel) = 10 totale aankopen
• plantaardige
  • (prei, kool, sla, boerenkool) = 44 Totale aankopen
  • (prei, kool, sla, boerenkool) = 44 totaal aankopen
  • (prei, kool, sla, boerenkool) = 44 Totale aankopen

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

bereken een cumulatieve Som

deze berekent een cumulatieve som voor elke categorie in de tabelProduce. De som wordt berekend met betrekking tot de volgorde die is gedefinieerd met behulp van de ORDER BY clausule.

• fruit
  • (sinaasappel, appel) = 2 totale aankopen
  • (sinaasappel, appel) = 10 totaal aankopen
• plantaardige
  • (prei, kool, sla, boerenkool) = 2 totale aankopen
  • (prei, kool, sla, boerenkool) = 11 totaal aankopen
  • (prei, kool, sla, boerenkool) = 21 de totale aankopen
  • (prei, kool, sla, boerenkool) = 44 totaal van de aankopen

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

Dit doet hetzelfde als het vorige voorbeeld. U hoeftCURRENT ROW niet toe te voegen als een grens, tenzij u dat wilt voor de leesbaarheid.

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

In dit voorbeeld zijn alle items in de tabel Produce opgenomen in de partitie. Alleen voorgaande rijen worden geanalyseerd. De analyse begint twee keer voor de huidige rij in de partitie.

• (oranje, prei, appel, kool, sla, boerenkool) = = NULL
• (oranje, prei, appel, kool, sla, boerenkool) = = NULL
• (oranje, prei, appel, kool, sla, boerenkool) = 2
• (oranje, prei, appel, kool, sla, boerenkool) = 4
• (oranje, prei, appel, kool, sla, boerenkool) = 12
• (oranje, prei, appel, kool, sla, boerenkool) = 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 |+-------------------------------------------------------+

het Berekenen van een zwevend gemiddelde

Deze berekent van een voortschrijdend gemiddelde in de Produce tabel.De ondergrens is 1 rij voor de huidige rij. De bovengrens is 1 rij na de huidige rij.

• (oranje, prei, appel, kool, sla, boerenkool) = 2 gemiddelde aankopen
• (oranje, prei, appel, kool, sla, boerenkool) = 4 gemiddelde aankopen
• (oranje, prei, appel, kool, sla, boerenkool) = 6.3333 gemiddelde aankopen
• (oranje, prei, appel, kool, sla, boerenkool) = 9 gemiddelde aankopen
• (oranje, prei, appel, kool, sla, boerenkool) = 14 gemiddelde aankopen
• (oranje, prei, appel, kool, sla, boerenkool) = 16.5 gemiddelde aankopen

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

Bereken het aantal items binnen een bereik

Dit voorbeeld krijgt het aantal dieren met een vergelijkbare populatie in de Farm tabel.

• (gans, hond, OS, geit, eend, Kat) = 4 dieren tussen populatie 0-2.
• (gans, hond, OS, geit, eend, kat) = 5 Dieren tussen populatie 1-3.
• (gans, hond, OS, geit, eend, kat) = 5 Dieren tussen populatie 1-3.
• (gans, hond, OS, geit, eend, kat) = 5 Dieren tussen populatie 1-3.
• (gans, hond, OS, geit, eend, Kat) = 4 dieren tussen populatie 2-4.
• (gans, hond, OS, geit, eend, Kat) = 1 dier tussen populatie 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 |+----------------------------------------------------------+

haal het meest populaire item in elke Categorie

dit voorbeeld krijgt het meest populaire item in elke categorie. Het definieert hoe rijen in een venster worden gepartitioneerd en geordend in elke partitie. Er wordt verwezen naar de tabelProduce.

• fruit
  • (sinaasappel, appel) = apple is het meest populair
  • (sinaasappel, appel) = apple is het meest populair
• plantaardige
  • (prei, kool, sla, boerenkool) = boerenkool is het meest populair
  • (prei, kool, sla, boerenkool) = boerenkool is het meest populair
  • (prei, kool, sla, boerenkool) = boerenkool is het meest populair
  • (prei, kool, sla, boerenkool) = boerenkool is het meest populair

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

Voor de laatste waarde in een bereik

in Dit voorbeeld krijgt de meest populaire items in een specifieke kozijn, usingthe Produce tabel. Het raamframe analyseert tot drie keer per keer. Bekijk de kolom most_popular voor groenten.In plaats van het meest populaire item in een specifieke categorie te krijgen, krijgt het het meest populaire item in een specifiek bereik in die categorie.

• fruit
  • (sinaasappel, appel) = apple is het meest populair
  • (sinaasappel, appel) = apple is het meest populair
• plantaardige
  • (prei, kool, sla, boerenkool) = kool is het meest populaire
  • (prei, kool, sla, boerenkool) = sla is het meest populair
  • (prei, kool, sla, boerenkool) = boerenkool is het meest populair
  • (prei, kool, sla, boerenkool) = boerenkool is het meest populair

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

in Dit voorbeeld geeft dezelfde resultaten als in het voorgaande voorbeeld, maar het includesa genoemde venster met de naam item_window. Sommige vensterspecificaties zijn direct gedefinieerd in deOVER clausule en sommige zijn gedefinieerd in het genoemde venster.

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

dit voorbeeld berekent de rang van elke werknemer binnen hun afdeling,op basis van hun startdatum. De vensterspecificatie wordt direct gedefinieerd in de OVER clausule. Er wordt verwezen naar de tabel Employees.

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

Gebruik een naam raam in een kozijn-component

kunt U een aantal van uw logica in een venster met de naam en een deel van het in een window frame clausule. Deze logica wordt gecombineerd. Hier is een voorbeeld, met behulp van deProduce tabel.

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

u kunt de vorige resultaten ook krijgen met deze voorbeelden:

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)

het volgende voorbeeld geeft een fout aan omdat een vensterframe-clausule tweemaal is gedefinieerd:

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)