Analytiske funktioner

analytiske funktioner beregner en samlet værdi baseret på en gruppe rækker. De adskiller sig fra aggregatfunktioner, idet de returnerer flere rækker for hver gruppe. Gruppen af rækker kaldes et vindue og er defineret af analytic_clause. For hver række defineres et glidende vindue af rækker. Vinduet bestemmer rækkevidden af rækker, der bruges til at udføre beregningerne for den aktuelle række. Vinduestørrelser kan baseres på enten et fysisk antal rækker eller et logisk interval som tid.

analytiske funktioner er det sidste sæt operationer, der udføres i en forespørgsel undtagen den endeligeORDERBY klausul. Alle joinforbindelser og alleWHEREGROUPBY ogHAVING klausuler er afsluttet, før de analytiske funktioner behandles. Derfor kan analytiske funktioner kun vises i select list ellerORDERBY klausul.

analytiske funktioner bruges ofte til at beregne kumulative, bevægelige, centrerede og rapporterende aggregater.

analytic_function::=

beskrivelse af illustrationen “analytic_function.gif “

analytic_clause::=

beskrivelse af illustrationen “analytic_clause.gif “

spørgsmål_partition_clause::=

beskrivelse af illustrationen “forespørg_partition_clause.gif “

order_by_clause::=

beskrivelse af illustrationen “order_by_clause.gif “

vindue_klause:: =

beskrivelse af illustrationen “vindue_klause.gif “

semantikken i denne syntaks diskuteres i de følgende afsnit.

analytic_function

Angiv navnet på en analytisk funktion (se listen over analytiske funktioner efter denne diskussion af semantik).

argumenter

analytiske funktioner tager 0 til 3 argumenter. Argumenterne kan være en hvilken som helst numerisk datatype eller en hvilken som helst ikke-numerisk datatype, der implicit kan konverteres til en numerisk datatype. Oracle bestemmer argumentet med den højeste numeriske forrang og konverterer implicit de resterende argumenter til den pågældende datatype. Returtypen er også den datatype, medmindre andet er angivet for en individuel 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 angive analytiske funktioner med denne klausul i select list ellerORDERBY klausul. Hvis du vil filtrere resultaterne af en forespørgsel baseret på en analytisk funktion, skal du indlejre disse funktioner i den overordnede forespørgsel og derefter filtrere resultaterne af den indlejrede underforespørgsel.

noter om analytic_clause: følgende noter gælder foranalytic_clause:

• Du kan ikke indlejre analytiske funktioner ved at angive nogen analytisk funktion i nogen del afanalytic_clause. Du kan dog angive en analytisk funktion i en underforespørgsel og beregne en anden analytisk funktion over den.

• Du kan angive OVERanalytic_clause med brugerdefinerede analytiske funktioner samt indbyggede analytiske funktioner. Se Opret funktion.

forespørg_partition_clause

brugPARTITIONBY klausulen til at opdele forespørgselsresultatet i grupper baseret på en eller flerevalue_expr. Hvis du udelader denne klausul, behandler funktionen alle rækker i forespørgselsresultatet som en enkelt gruppe.

for at bruge query_partition_clause i en analytisk funktion skal du bruge den øverste gren af syntaksen (uden parenteser). For at bruge denne klausul i en modelforespørgsel (i model_column_clauses) eller en partitioneret ydre sammenføjning (i outer_join_clause) skal du bruge den nederste gren af syntaksen (med parenteser).

Du kan angive flere analytiske funktioner i samme forespørgsel, hver med samme eller forskelligePARTITIONBY nøgler.

hvis de objekter, der forespørges, har den parallelle attribut, og hvis du angiver en analytisk funktion med query_partition_clause, er funktionsberegningerne også paralleliserede.

gyldige værdier af value_expr er konstanter, kolonner, ikke-analytiske funktioner, funktionsudtryk eller udtryk, der involverer nogen af disse.

order_by_clause

brugorder_by_clause til at specificere, hvordan data bestilles i en partition. For alle analytiske funktioner kan du bestille værdierne i en partition på flere taster, hver defineret af en value_expr og hver kvalificeret af en ordresekvens.

inden for hver funktion kan du angive flere ordreudtryk. Dette er især nyttigt, når du bruger funktioner, der rangerer værdier, fordi det andet udtryk kan løse bånd mellem identiske værdier for det første udtryk.

Når order_by_clause resulterer i identiske værdier for flere rækker, fungerer funktionen som følger:

• CUME_DISTDENSE_RANKNTILEPERCENT_RANK ogRANK returner det samme resultat for hver af rækkerne.

• ROW_NUMBERtildeler hver række en særskilt værdi, selvom der er et slips baseret påorder_by_clause. Værdien er baseret på den rækkefølge, som rækken behandles i, hvilket kan være ikke-bestemmende, hvis ORDERBY ikke garanterer en samlet bestilling.

• for alle andre analytiske funktioner afhænger resultatet af vinduesspecifikationen. Hvis du angiver et logisk vindue med nøgleordet RANGE, returnerer funktionen det samme resultat for hver af rækkerne. Hvis du angiver et fysisk vindue med nøgleordet ROWS, er resultatet ikke-deterministisk.

Begrænsninger på ordren efter Klausul følgende begrænsninger gælder forORDERBY klausul:

• når det bruges i en analytisk funktion, skalorder_by_clausetage et udtryk (expr). NøgleordetSIBLINGS er ikke gyldigt (det er kun relevant i hierarkiske forespørgsler). Position (position) og kolonnealiaser (c_alias) er også ugyldige. Ellers er dette order_by_clause det samme som det, der blev brugt til at bestille den samlede forespørgsel eller underforespørgsel.

• 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

  vinduesgrænser bortset fra disse fire kan kun have en sorteringsnøgle i ORDERBY klausul om den analytiske funktion. Denne begrænsning gælder ikke for vinduesgrænser, der er specificeret af nøgleordet ROW.

ASC / DESC Angiv ordresekvensen (stigende eller faldende). ASCer standard.

NULLS FIRST / NULLS LAST Angiv, om returnerede rækker, der indeholder nulls, skal vises først eller sidst i ordresekvensen.

NULLSLAST er standard for stigende rækkefølge, ogNULLSFIRST er standard for faldende rækkefølge.

analytiske funktioner fungerer altid på rækker i den rækkefølge, der er angivet iorder_by_clause af funktionen. Men order_by_clause af funktionen garanterer ikke rækkefølgen af resultatet. Brugorder_by_clause af forespørgslen for at garantere det endelige resultat bestilling.

Se også:

order_by_clause of SELECT for mere information om denne klausul

vindue_clause

nogle analytiske funktioner tilladerwindowing_clause. I listen over analytiske funktioner i slutningen af dette afsnit efterfølges de funktioner, der tillader windowing_clause, af en stjerne (*).

rækker / rækkevidde disse nøgleord definerer for hver række et vindue (et fysisk eller logisk sæt rækker), der bruges til at beregne funktionsresultatet. Funktionen anvendes derefter på alle rækker i vinduet. Vinduet bevæger sig gennem forespørgselsresultatsættet eller partitionen fra top til bund.

• ROWS angiver vinduet i fysiske enheder (rækker).

• RANGE angiver vinduet som en logisk forskydning.

Du kan ikke angive denne klausul, medmindre du har angivetorder_by_clause. Nogle vinduesgrænser defineret af RANGE klausul lader dig kun angive et udtryk i order_by_clause. Der henvises til “begrænsninger på ordren efter Klausul”.

værdien, der returneres af en analytisk funktion med en logisk forskydning, er altid deterministisk. Den værdi, der returneres af en analytisk funktion med en fysisk forskydning, kan dog give ikke-deterministiske resultater, medmindre ordreudtrykket resulterer i en unik bestilling. Du skal muligvis angive flere kolonner i order_by_clause for at opnå denne unikke bestilling.

mellem … Og brug BETWEENAND klausul for at angive et startpunkt og slutpunkt for vinduet. Det første udtryk (før AND) definerer startpunktet, og det andet udtryk (efter AND) definerer slutpunktet.

Hvis du udeladerBETWEEN og kun angiver et slutpunkt, betragter Oracle det som startpunktet, og slutpunktet er standard til den aktuelle række.

ubegrænset foregående Angiv UNBOUNDEDPRECEDING for at angive, at vinduet starter ved den første række af partitionen. Dette er startpunktsspecifikationen og kan ikke bruges som en slutpunktsspecifikation.

ubegrænset følgende AngivUNBOUNDEDFOLLOWING for at angive, at vinduet slutter i den sidste række af partitionen. Dette er slutpunktsspecifikationen og kan ikke bruges som startpunktsspecifikation.

aktuel række som udgangspunkt angiver CURRENTROW, at vinduet begynder ved den aktuelle række eller værdi (afhængigt af om du har angivet ROW eller RANGE). I dette tilfælde kan slutpunktet ikke være value_exprPRECEDING.

som slutpunkt angiverCURRENTROW, at vinduet slutter ved den aktuelle række eller værdi (afhængigt af om du har angivetROW ellerRANGE). I dette tilfælde kan startpunktet ikke være value_exprFOLLOWING.

værdi_ekspr foregående eller værdi_ekspr følgende for RANGE eller ROW:

• Hvisvalue_exprFOLLOWING er startpunktet, skal slutpunktet værevalue_exprFOLLOWING.

• Hvisvalue_exprPRECEDINGer slutpunktet, skal startpunktet værevalue_exprPRECEDING.

Hvis du definerer et logisk vindue defineret af et tidsinterval i numerisk format, skal du muligvis bruge konverteringsfunktioner.

Se også:

NUMTOYMINTERVAL og NUMTODSINTERVAL for information om konvertering af numeriske tider til intervaller

Hvis du har angivetROWS:

• value_expr er en fysisk forskydning. Det skal være en konstant eller udtryk og skal evalueres til en positiv numerisk værdi.

• Hvis value_expr er en del af startpunktet, skal det evalueres til en række før slutpunktet.

Hvis du har angivetRANGE:

• value_expr er en logisk forskydning. Det skal være en konstant eller et udtryk, der evalueres til en positiv numerisk værdi eller et interval bogstaveligt. Se “bogstaver” for information om intervallitteraler.

• Du kan kun angive et udtryk iorder_by_clause.

• Hvis value_expr evaluerer til en numerisk værdi, skal ORDERBYexpr være en numerisk eller DATE datatype.

• 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. På listen over analytiske funktioner, der følger, tillader funktioner efterfulgt af en stjerne (*) den fulde syntaks, inklusive windowing_clause.

AVG *
CORR *
COUNT *
COVAR_POP *
COVAR_SAMP *
CUME_DIST
DENSE_RANK
first
FIRST_VALUE *
LAG
LAST
LAST_VALUE *
LEAD
list
maks *
MEDIAN
MIN *
NTH_VALUE *
NTILE
percent_rank
PERCENTILE_CONT
percentile_disc
rang
ratio_to_report
regr_ (lineær regression) funktioner *
RÆKKE_NUMMER
stddev_pop *
STDDEV_SAMP *
sum *
var_pop *
VAR_SAMP *
varians *

se også:

Oracle database datalagringsvejledning for mere information om disse funktioner og for scenarier, der illustrerer deres anvendelse