Analytische functies

analytische functies berekenen een geaggregeerde waarde op basis van een groep rijen. Ze verschillen van geaggregeerde functies in die zin dat ze meerdere rijen voor elke groep retourneren. De groep rijen wordt een venster genoemd en wordt gedefinieerd door de analytic_clause. Voor elke rij wordt een schuifraam van rijen gedefinieerd. Het venster bepaalt het bereik van de rijen die worden gebruikt om de berekeningen voor de huidige rij uit te voeren. Vensterformaten kunnen gebaseerd zijn op een fysiek aantal rijen of een logisch interval zoals tijd.

analytische functies zijn de laatste reeks bewerkingen die in een query worden uitgevoerd, met uitzondering van de uiteindelijke ORDERBY clausule. Alle joins en alleWHEREGROUPBY, enHAVING clausules zijn voltooid voordat de analytische functies worden verwerkt. Daarom kunnen analytische functies alleen verschijnen in de select list of ORDERBY clausule.

analytische functies worden vaak gebruikt om cumulatieve, bewegende, gecentreerde en rapportage-aggregaten te berekenen.

analytic_function::=

beschrijving van de illustratie ” analytic_function.gif “

analytic_clause::=

beschrijving van de illustratie ” analytic_clause.gif “

query_partition_clause::=

beschrijving van de illustratie ” query_partition_clause.gif “

order_by_clause::=

beschrijving van de illustratie ” order_by_clause.gif “

windowing_clause ::=

beschrijving van de illustratie ” windowing_clause.gif “

de semantiek van deze syntaxis wordt besproken in de volgende secties.

analytische functie

specificeer de naam van een analytische functie (zie de lijst van analytische functies na deze bespreking van de semantiek).

argumenten

analytische functies hebben 0 tot 3 argumenten. De argumenten kunnen elk numeriek gegevenstype zijn of elk niet-numeriek gegevenstype dat impliciet kan worden geconverteerd naar een numeriek gegevenstype. Oracle bepaalt het argument met de hoogste numerieke voorrang en converteert impliciet de resterende argumenten naar dat gegevenstype. Het retourtype is ook dat gegevenstype, tenzij anders vermeld voor een individuele functie.

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. U kunt analytische functies opgeven met deze clausule in de select list of ORDERBY clausule. Als u de resultaten van een query wilt filteren op basis van een analytische functie, plaatst u deze functies in de bovenliggende query en filtert u vervolgens de resultaten van de geneste subquery.

opmerkingen over de analytic_clause: de volgende opmerkingen zijn van toepassing op de analytic_clause:

• u kunt geen analytische functies nestelen door een analytische functie op te geven in een deel van de analytic_clause. U kunt echter een analytische functie opgeven in een subquery en er een andere analytische functie over berekenen.

• u kunt OVERanalytic_clause opgeven met door de gebruiker gedefinieerde analytische functies en ingebouwde analytische functies. Zie functie maken.

query_partition_clause

Gebruik de PARTITIONBY clausule om het queryresultaat te verdelen in groepen op basis van een of meer value_expr. Als u deze clausule weglaat, behandelt de functie alle rijen van het querieresultaatset als een enkele groep.

om de query_partition_clause in een analytische functie te gebruiken, gebruikt u de bovenste tak van de syntaxis (zonder haakjes). Om deze clausule te gebruiken in een model query (in de model_column_clauses) of een gepartitioneerde buitenste join (in de outer_join_clause), gebruik de onderste tak van de syntaxis (met haakjes).

u kunt meerdere analytische functies opgeven in dezelfde query, elk met dezelfde of verschillende PARTITIONBY sleutels.

als de objecten die worden opgevraagd het parallelle attribuut hebben, en als u een analytische functie specificeert met de query_partition_clause, dan worden de functieberekeningen ook parallelliseerd.

geldige waarden van value_expr zijn constanten, kolommen, niet-analytische functies, functie-expressies, of expressies met een van deze.

order_by_clause

Gebruik de order_by_clause om aan te geven hoe gegevens worden geordend binnen een partitie. Voor alle analytische functies kunt u de waarden in een partitie op meerdere sleutels bestellen, elk gedefinieerd door een value_expr en elk gekwalificeerd door een volgorde.

binnen elke functie kunt u meerdere orderuitdrukkingen opgeven. Dit doen is vooral handig bij het gebruik van functies die waarden rangschikken, omdat de tweede expressie banden tussen identieke waarden voor de eerste expressie kan oplossen.

wanneer de order_by_clause resulteert in identieke waarden voor meerdere rijen, gedraagt de functie zich als volgt:

• CUME_DISTDENSE_RANKNTILEPERCENT_RANK, en RANK retourneren hetzelfde resultaat voor elk van de rijen.

• ROW_NUMBER kent elke rij een aparte waarde toe, zelfs als er een gelijkspel is gebaseerd op order_by_clause. De waarde is gebaseerd op de volgorde waarin de rij wordt verwerkt, die niet-deterministisch kan zijn als de ORDERBY geen volledige volgorde garandeert.

• voor alle andere analytische functies hangt het resultaat af van de vensterspecificatie. Als u een logisch venster opgeeft met het RANGE sleutelwoord, dan geeft de functie hetzelfde resultaat terug voor elk van de rijen. Als u een fysiek venster opgeeft met hetROWS sleutelwoord, dan is het resultaat niet-deterministisch.

Restrictions on the ORDER BY Clause de volgende restrictions zijn van toepassing op deORDERBY clausule:

• indien gebruikt in een analytische functie, moet order_by_clause een expressie hebben (expr). Het sleutelwoord SIBLINGS is niet geldig (Het is alleen relevant in hiërarchische query ‘ s). Positie (position) en kolom aliassen (c_alias) zijn ook ongeldig. Anders is deze order_by_clause dezelfde als die gebruikt wordt om de Algemene query of subquery te bestellen.

• 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

  andere venstergrenzen dan deze Vier kunnen slechts één sorteersleutel hebben in de ORDERBY clausule van de analytische functie. Deze beperking is niet van toepassing op venstergrenzen gespecificeerd door het ROW sleutelwoord.

ASC / DESC specificeer de volgorde (oplopend of aflopend). ASC is de standaard.

NULLS FIRST / NULLS LAST specificeer of geretourneerde rijen met nulls als eerste of als laatste moeten verschijnen in de volgorde.

NULLSLAST is de standaard voor aflopende volgorde, en NULLSFIRST is de standaard voor aflopende volgorde.

analytische functies werken altijd op rijen in de volgorde die is opgegeven in de order_by_clause van de functie. De order_by_clause van de functie garandeert echter niet de volgorde van het resultaat. Gebruik de order_by_clause van de query om het uiteindelijke resultaat te garanderen.

zie ook:

order_by_clause of SELECT voor meer informatie over deze clausule

windowing_clause

sommige analytische functies staan de windowing_clausetoe. In de lijst van analytische functies aan het einde van deze paragraaf, worden de functies die windowing_clause toestaan, gevolgd door een sterretje (*).

rijen / bereik deze sleutelwoorden definiëren voor elke rij een venster (een fysieke of logische reeks rijen) dat wordt gebruikt voor het berekenen van het functieresultaat. De functie wordt vervolgens toegepast op alle rijen in het venster. Het venster beweegt door de query resultaat set of partitie van boven naar beneden.

• ROWS specificeert het venster in fysieke eenheden (rijen).

• RANGE specificeert het venster als een logische verschuiving.

U kunt deze clausule niet specificeren tenzij u de order_by_clausehebt opgegeven. Sommige venstergrenzen gedefinieerd door de RANGE clausule laten u slechts één expressie specificeren in de order_by_clause. Zie “beperkingen op de ORDER BY clausule”.

de waarde die wordt geretourneerd door een analytische functie met een logische offset is altijd deterministisch. De waarde die door een analytische functie met een fysieke offset wordt geretourneerd, kan echter niet-deterministische resultaten opleveren, tenzij de orderuitdrukking resulteert in een unieke volgorde. Mogelijk moet u meerdere kolommen opgeven in de order_by_clause om deze unieke volgorde te bereiken.

tussen … En gebruik de BETWEENAND clausule om een begin-en eindpunt voor het venster te specificeren. De eerste expressie (vóór AND) definieert het beginpunt en de tweede expressie (na AND) definieert het eindpunt.

Als u BETWEEN weglaat en slechts één eindpunt opgeeft, dan beschouwt Oracle dit als het beginpunt en staat het eindpunt standaard op de huidige rij.

UNBOUNDED PREFORE Specificeer UNBOUNDEDPRECEDING om aan te geven dat het venster begint op de eerste rij van de partitie. Dit is de beginpunt specificatie en kan niet worden gebruikt als een eindpunt specificatie.

Unbound following Specificeer UNBOUNDEDFOLLOWING om aan te geven dat het venster eindigt op de laatste rij van de partitie. Dit is de eindpuntspecificatie en kan niet als beginpuntspecificatie worden gebruikt.

huidige rij als startpunt geeft CURRENTROW aan dat het venster begint op de huidige rij of waarde (afhankelijk van of u ROW of RANGE hebt opgegeven). In dit geval kan het eindpunt niet value_exprPRECEDINGzijn.

als eindpunt geeft CURRENTROW aan dat het venster eindigt op de huidige rij of waarde (afhankelijk van of u ROW of RANGE hebt opgegeven). In dit geval kan het beginpunt niet value_exprFOLLOWINGzijn.

value_expr PREFORE or value_expr FOLLOWING For RANGE or ROW:

• als value_exprFOLLOWING het beginpunt is, moet het eindpunt value_exprFOLLOWINGzijn.

• als value_exprPRECEDING het eindpunt is, moet het beginpunt value_exprPRECEDINGzijn.

als u een logisch venster definieert dat wordt gedefinieerd door een tijdsinterval in numeriek formaat, dan moet u mogelijk conversiefuncties gebruiken.

zie ook:

NUMTOYMINTERVAL en NUMTODSINTERVAL voor informatie over het omzetten van numerieke tijden in intervallen

Als u ROWSopgeeft:

• value_expr is een fysieke offset. Het moet een constante of uitdrukking zijn en moet een positieve numerieke waarde hebben.

• als value_expr deel uitmaakt van het beginpunt, moet het evalueren tot een rij voor het eindpunt.

Als u RANGEopgeeft:

• value_expr is een logische verschuiving. Het moet een constante of uitdrukking zijn die evalueert naar een positieve numerieke waarde of een interval letterlijke. Refereer naar “Literals” voor informatie over interval literals.

• u kunt slechts één expressie opgeven in de order_by_clause.

• als value_expr evalueert tot een numerieke waarde, dan moet ORDERBYexpr Een numerieke waarde zijn of DATE gegevenstype.

• 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. In de lijst met analytische functies die volgt, staan functies gevolgd door een sterretje (*) de volledige syntaxis toe, inclusief de windowing_clause.

AVG *
CORR *
GRAAF *
COVAR_POP *
COVAR_SAMP *
REGR_SXX
DENSE_RANK
EERSTE
EERSTE_WAARDE *
LAG
LAATSTE
LAST_VALUE *
LEIDEN
LISTAGG
MAX *
MEDIAAN
MIN *
NTH_VALUE *
NTILE
PERCENT_RANK
PERCENTILE_CONT
PERCENTILE_DISC
RANK
RATIO_TO_REPORT
REGR_ (Lineaire Regressie) Functies *
ROW_NUMBER
STDDEV *
STDDEV_POP *
STDDEV_SAMP *
SUM *
VAR_POP *
VAR_SAMP *
VARIANTIE *

Zie Ook:

Oracle Database Data Warehousing Guide voor meer informatie over deze functies en scenario ‘ s ter illustratie van hun gebruik