Analytiska funktioner

analytiska funktioner beräkna ett aggregerat värde baserat på en grupp rader. De skiljer sig från aggregerade funktioner genom att de returnerar flera rader för varje grupp. Gruppen av rader kallas ett fönster och definieras av analytic_clause. För varje rad definieras ett skjutfönster med rader. Fönstret bestämmer radintervallet som används för att utföra beräkningarna för den aktuella raden. Fönsterstorlekar kan baseras på antingen ett fysiskt antal rader eller ett logiskt intervall som tid.

analytiska funktioner är den sista uppsättningen operationer som utförs i en fråga förutom den slutligaORDERBY klausul. Alla kopplingar och alla WHEREGROUPBY och HAVING klausuler är färdiga innan de analytiska funktionerna behandlas. Därför kan analytiska funktioner endast visas i select-listan eller ORDERBY klausul.

analytiska funktioner används ofta för att beräkna kumulativa, rörliga, centrerade och rapporterande aggregat.

analytic_function::=

beskrivning av illustrationen ”analytic_function.gif ”

analytic_clause::=

beskrivning av illustrationen ”analytic_clause.gif ”

query_partition_clause::=

beskrivning av illustrationen ”query_partition_clause.gif ”

order_by_clause:: =

beskrivning av illustrationen ”order_by_clause.gif ”

windowing_clause:: =

beskrivning av illustrationen ”windowing_clause.gif ”

semantiken för denna syntax diskuteras i de avsnitt som följer.

analytic_function

Ange namnet på en analytisk funktion (se listan över analytiska funktioner efter denna diskussion om semantik).

argument

analytiska funktioner tar 0 till 3 argument. Argumenten kan vara vilken numerisk datatyp som helst eller vilken icke-numerisk datatyp som implicit kan konverteras till en numerisk datatyp. Oracle bestämmer argumentet med högsta numeriska prioritet och konverterar implicit de återstående argumenten till den datatypen. Returtypen är också den datatypen, om inte annat anges för en enskild funktion.

analytic_clause

Use OVERanalytic_clause to indicate that the function operates on a query result set. This clause is computed after the FROMWHEREGROUPBY, and HAVING clauses. Du kan ange analytiska funktioner med denna klausul i select-listan ellerORDERBY klausul. Om du vill filtrera resultaten av en fråga baserat på en analytisk funktion BoStar du dessa funktioner i den överordnade frågan och filtrerar sedan resultaten från den kapslade underfrågan.

anteckningar om analytic_clause: följande anteckningar gäller för analytic_clause:

• Du kan inte häcka analytiska funktioner genom att ange någon analytisk funktion i någon del av analytic_clause. Du kan dock ange en analytisk funktion i en underfråga och beräkna en annan analytisk funktion över den.

• Du kan ange OVERanalytic_clause med användardefinierade analytiska funktioner samt inbyggda analytiska funktioner. Se skapa funktion.

query_partition_clause

användPARTITIONBY för att partitionera frågeresultatet i grupper baserat på ett eller fleravalue_expr. Om du utelämnar denna klausul behandlar funktionen alla rader i frågeresultatet som en enda grupp.

Om du vill använda query_partition_clause I en analytisk funktion använder du syntaxens övre gren (utan parenteser). För att använda denna klausul i en modellfråga (i model_column_clauses) eller en partitionerad yttre koppling (i outer_join_clause), använd syntaxens nedre gren (med parenteser).

Du kan ange flera analytiska funktioner i samma fråga, var och en med samma eller olika PARTITIONBY nycklar.

om objekten som frågas har parallellattributet, och om du anger en analysfunktion med query_partition_clause, är funktionsberäkningarna också parallelliserade.

giltiga värden för value_expr är konstanter, kolumner, icke-analytiska funktioner, funktionsuttryck eller uttryck som involverar någon av dessa.

order_by_clause

användorder_by_clause för att ange hur data beställs inom en partition. För alla analytiska funktioner kan du beställa värdena i en partition på flera tangenter, var och en definierad av ett value_expr och var och en kvalificerad av en beställningssekvens.

inom varje funktion kan du ange flera beställningsuttryck. Det är särskilt användbart när du använder funktioner som rangordnar värden, eftersom det andra uttrycket kan lösa band mellan identiska värden för det första uttrycket.

När order_by_clause resulterar i identiska värden för flera rader, fungerar funktionen enligt följande:

• CUME_DISTDENSE_RANKNTILEPERCENT_RANK och RANK returnera samma resultat för var och en av raderna.

• ROW_NUMBER tilldelar varje rad ett distinkt värde även om det finns ett slips baserat på order_by_clause. Värdet baseras på den ordning i vilken raden behandlas, vilket kan vara nondeterministiskt om ORDERBY inte garanterar en total beställning.

• för alla andra analytiska funktioner beror resultatet på fönsterspecifikationen. Om du anger ett logiskt fönster med RANGE, returnerar funktionen samma resultat för var och en av raderna. Om du anger ett fysiskt fönster med ROWS, är resultatet nondeterministiskt.

Begränsningar för order BY-klausulen följande begränsningar gäller förORDERBY klausul:

• när den används i en analysfunktion måste order_by_clause ta ett uttryck (expr). Nyckelordet SIBLINGS är inte giltigt (det är endast relevant i hierarkiska frågor). Position (position) och kolumnalias (c_alias) är också ogiltiga. Annars är detta order_by_clause detsamma som det som används för att beställa den övergripande frågan eller underfrågan.

• An analytic function that uses the RANGE keyword can use multiple sort keys in its ORDERBY clause if it specifies any of the following windows:

  • RANGEBETWEENUNBOUNDEDPRECEDINGANDCURRENTROW. The short form of this is RANGEUNBOUNDEDPRECEDING.

  • RANGEBETWEENCURRENTROWANDUNBOUNDEDFOLLOWING

  • RANGEBETWEENCURRENTROWANDCURRENTROW

  • RANGEBETWEENUNBOUNDEDPRECEDINGANDUNBOUNDEDFOLLOWING

  andra fönstergränser än dessa fyra kan bara ha en sorteringsnyckel i ORDERBY klausul i analysfunktionen. Denna begränsning gäller inte fönstergränser som anges av sökordet ROW.

ASC / DESC ange beställningssekvensen (stigande eller fallande). ASC är standardvärdet.

NULLS FIRST / NULLS LAST ange om returnerade rader som innehåller nulls ska visas först eller sist i beställningssekvensen.

NULLSLAST är standard för stigande ordning och NULLSFIRST är standard för fallande ordning.

analytiska funktioner fungerar alltid på rader i den ordning som anges iorder_by_clause för funktionen. Funktionen order_by_clause garanterar dock inte resultatets ordning. Användorder_by_clause för att garantera slutresultatet beställning.

Se även:

order_by_clause of SELECT för mer information om denna klausul

windowing_clause

vissa analytiska funktioner tillåterwindowing_clause. I listan över analytiska funktioner i slutet av detta avsnitt följs de funktioner som tillåter windowing_clause av en asterisk (*).

rader / intervall dessa nyckelord definierar för varje rad ett fönster (en fysisk eller logisk uppsättning rader) som används för att beräkna funktionsresultatet. Funktionen tillämpas sedan på alla rader i fönstret. Fönstret rör sig genom frågeresultatet eller partitionen från topp till botten.

• ROWS anger fönstret i fysiska enheter (rader).

• RANGE anger fönstret som en logisk förskjutning.

Du kan inte ange denna klausul om du inte har angett order_by_clause. Vissa fönstergränser definierade avRANGE – klausulen låter dig bara ange ett uttryck iorder_by_clause. Se ”begränsningar av ORDER BY-klausulen”.

värdet som returneras av en analytisk funktion med en logisk förskjutning är alltid deterministisk. Värdet som returneras av en analytisk funktion med en fysisk förskjutning kan emellertid ge icke-deterministiska resultat om inte beställningsuttrycket resulterar i en unik beställning. Du kan behöva ange flera kolumner i order_by_clause för att uppnå denna unika beställning.

mellan … Och använd BETWEENAND klausul för att ange en startpunkt och slutpunkt för fönstret. Det första uttrycket (före AND) definierar startpunkten och det andra uttrycket (efter AND) definierar slutpunkten.

om du utelämnar BETWEEN och bara anger en slutpunkt, anser Oracle det som startpunkten och slutpunkten är standard för den aktuella raden.

obundet föregående angeUNBOUNDEDPRECEDING för att indikera att fönstret startar vid partitionens första rad. Detta är startpunktsspecifikationen och kan inte användas som en slutpunktsspecifikation.

obundet följande angeUNBOUNDEDFOLLOWING för att indikera att fönstret slutar vid partitionens sista rad. Detta är slutpunktsspecifikationen och kan inte användas som en startpunktsspecifikation.

aktuell rad som startpunkt angerCURRENTROW att fönstret börjar vid den aktuella raden eller värdet (beroende på om du har angettROW ellerRANGE, respektive). I detta fall kan slutpunkten inte vara value_exprPRECEDING.

som slutpunkt anger CURRENTROW att fönstret slutar vid den aktuella raden eller värdet (beroende på om du har angett ROW eller RANGE, respektive). I detta fall kan startpunkten inte vara value_exprFOLLOWING.

value_expr föregående eller value_expr följande för RANGE eller ROW:

• omvalue_exprFOLLOWINGär startpunkten måste slutpunkten varavalue_exprFOLLOWING.

• om value_exprPRECEDING är slutpunkten måste startpunkten vara value_exprPRECEDING.

om du definierar ett logiskt fönster definierat av ett tidsintervall i numeriskt format kan du behöva använda konverteringsfunktioner.

Se även:

NUMTOYMINTERVAL och NUMTODSINTERVAL för information om att konvertera numeriska tider till intervaller

Om du angav ROWS:

• value_expr är en fysisk förskjutning. Det måste vara en konstant eller uttryck och måste utvärdera till ett positivt numeriskt värde.

• om value_expr är en del av startpunkten måste den utvärderas till en rad före slutpunkten.

Om du angav RANGE:

• value_expr är en logisk förskjutning. Det måste vara en konstant eller ett uttryck som utvärderas till ett positivt numeriskt värde eller ett intervall bokstavligt. Se ”Literals” för information om intervalllitteraler.

• Du kan bara ange ett uttryck i order_by_clause.

• omvalue_expr utvärderas till ett numeriskt värde, måsteORDERBYexpr vara ett numeriskt ellerDATE datatyp.

• If value_expr evaluates to an interval value, then the ORDERBYexpr must be a DATE data type.

If you omit the windowing_clause entirely, then the default is RANGEBETWEENUNBOUNDEDPRECEDINGANDCURRENTROW.

Analytic functions are commonly used in data warehousing environments. I listan över analytiska funktioner som följer tillåter funktioner följt av en asterisk (*) hela syntaxen, inklusive windowing_clause.

AVG *
CORR *
räkna *
COVAR_POP *
COVAR_SAMP *
CUME_DIST
DENSE_RANK
första
FIRST_VALUE *
LAG
sista
LAST_VALUE *
bly
LISTAGG
MAX *
MEDIAN
MIN *
NTH_VALUE *
NTILE
percentile_cont
PERCENTILE_DISC
rank
ratio_to_report
regr_ (linjär regression) funktioner *
ROW_NUMBER
STDDEV *
stddev_pop *
STDDEV_SAMP *
summa *
var_pop *
var_samp *
varians *

Se även:

Oracle Database datalagringsguide för mer information om dessa funktioner och för scenarier som illustrerar deras användning