Funcții analitice

funcțiile analitice calculează o valoare agregată bazată pe un grup de rânduri. Ele diferă de funcțiile agregate prin faptul că returnează mai multe rânduri pentru fiecare grup. Grupul de rânduri se numește fereastră și este definit de analytic_clause. Pentru fiecare rând, este definită o fereastră glisantă de rânduri. Fereastra determină intervalul de rânduri utilizate pentru a efectua calculele pentru rândul curent. Dimensiunile ferestrelor se pot baza fie pe un număr fizic de rânduri, fie pe un interval logic, cum ar fi timpul.

funcțiile analitice sunt ultimul set de operații efectuate într-o interogare, cu excepția clauzei finaleORDERBY. Toate se alătură și toateWHEREGROUPBY, șiHAVING clauzele sunt finalizate înainte de funcțiile analitice sunt procesate. Prin urmare, funcțiile analitice pot apărea numai în lista select sau ORDERBY clauza.

funcțiile analitice sunt utilizate în mod obișnuit pentru a calcula agregatele cumulative, în mișcare, centrate și de raportare.

analitic_function::=

descrierea ilustrației „analitic_function.gif „

analytic_clause::=

descrierea ilustrației „analitic_clause.gif „

query_partition_clause::=

descrierea ilustrației „query_partition_clause.gif „

order_by_clause::=

descrierea ilustrației „order_by_clause.gif „

windowing_clause:: =

descrierea ilustrației „windowing_clause.gif „

semantica acestei sintaxe sunt discutate în secțiunile care urmează.

analytic_function

specificați numele unei funcții analitice (a se vedea lista funcțiilor analitice în urma acestei discuții de semantică).

argumente

funcțiile analitice iau 0 până la 3 argumente. Argumentele pot fi orice tip de date numerice sau orice tip de date non-numerice care pot fi convertite implicit într-un tip de date numerice. Oracle determină argumentul cu cea mai mare prioritate numerică și convertește implicit argumentele rămase la acel tip de date. Tipul de returnare este, de asemenea, acel tip de date, cu excepția cazului în care se specifică altfel pentru o funcție individuală.

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. Puteți specifica funcții analitice cu această clauză în lista select sauORDERBY clauza. Pentru a filtra rezultatele unei interogări bazate pe o funcție analitică, cuib aceste funcții în interogarea părinte, și apoi se filtrează rezultatele subinterogării imbricate.

Note privind analitic_clause: următoarele note se aplicăanalytic_clause:

• nu puteți cuibări funcții analitice specificând orice funcție analitică în orice parte aanalytic_clause. Cu toate acestea, puteți specifica o funcție analitică într-o subinterogare și puteți calcula o altă funcție analitică peste ea.

• puteți specificaOVERanalytic_clause cu funcții analitice definite de utilizator, precum și funcții analitice încorporate. A se vedea crearea funcției.

query_partition_clause

utilizați clauzaPARTITIONBY pentru a împărți rezultatul interogării setat în grupuri bazate pe unul sau mai multevalue_expr. Dacă omiteți această clauză, atunci funcția tratează toate rândurile setului de rezultate ale interogării ca un singur grup.

pentru a utilizaquery_partition_clause într-o funcție analitică, utilizați ramura superioară a sintaxei (fără paranteze). Pentru a utiliza această clauză într-o interogare de model (în model_column_clauses) sau o asociere exterioară partiționată (în outer_join_clause), utilizați ramura inferioară a sintaxei (cu paranteze).

puteți specifica mai multe funcții analitice în aceeași interogare, fiecare cu aceleași sau diferitePARTITIONBY chei.

dacă obiectele interogate au atributul paralel și dacă specificați o funcție analitică cuquery_partition_clause, atunci și calculele funcției sunt paralelizate.

Valorile valide alevalue_expr sunt constante, coloane, funcții nonanalitice, expresii funcționale sau expresii care implică oricare dintre acestea.

order_by_clause

utilizațiorder_by_clause pentru a specifica modul în care datele sunt ordonate într-o partiție. Pentru toate funcțiile analitice puteți comanda valorile dintr-o partiție pe mai multe taste, fiecare definită printr-un value_expr și fiecare calificată printr-o secvență de ordonare.

în cadrul fiecărei funcții, puteți specifica mai multe expresii de comandă. Acest lucru este util mai ales atunci când se utilizează funcții care clasifică valori, deoarece a doua expresie poate rezolva legăturile dintre valori identice pentru prima expresie.

ori de câte ori order_by_clause are ca rezultat valori identice pentru mai multe rânduri, funcția se comportă după cum urmează:

• CUME_DISTDENSE_RANKNTILEPERCENT_RANK și RANK returnați același rezultat pentru fiecare dintre rânduri.

• ROW_NUMBERatribuie fiecărui rând o valoare distinctă chiar dacă există o egalitate bazată peorder_by_clause. Valoarea se bazează pe ordinea în care este procesat rândul, care poate fi nedeterministă dacă ORDERBY nu garantează o comandă totală.

• pentru toate celelalte funcții analitice, rezultatul depinde de specificația ferestrei. Dacă specificați o fereastră logică cuRANGE cuvânt cheie, atunci funcția returnează același rezultat pentru fiecare dintre rânduri. Dacă specificați o fereastră fizică cuROWS cuvânt cheie, atunci rezultatul este nedeterminist.

restricții privind clauza ORDER BY următoarele restricții se aplică clauzei ORDERBY :

• atunci când este utilizat într-o funcție analitică,order_by_clause trebuie să ia o expresie (expr). Cuvântul cheieSIBLINGS nu este valid (este relevant numai în interogările ierarhice). Poziția (position) și pseudonimele de coloană (c_alias) Sunt de asemenea nevalide. În caz contrar, acest order_by_clause este același cu cel utilizat pentru a comanda interogarea sau subinterogarea generală.

• 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

  limitele ferestrei, altele decât aceste patru, pot avea o singură cheie de sortare în clauzaORDERBY a funcției analitice. Această restricție nu se aplică limitelor ferestrei specificate de cuvântul cheie ROW.

ASC / DESC specifică secvența de ordonare (ascendentă sau descendentă). ASC este implicit.

NULLS FIRST / Nulls LAST specificați dacă rândurile returnate care conțin nulls ar trebui să apară primele sau ultimele în secvența de ordonare.

NULLSLAST este implicit pentru ordinea crescătoare și NULLSFIRST este implicit pentru ordinea descrescătoare.

funcțiile analitice funcționează întotdeauna pe rânduri în ordinea specificată înorder_by_clause a funcției. Cu toate acestea, order_by_clause a funcției nu garantează ordinea rezultatului. Utilizați order_by_clause al interogării pentru a garanta ordonarea rezultatului final.

windowing_clause

unele funcții analitice permitwindowing_clause. În lista funcțiilor analitice de la sfârșitul acestei secțiuni, funcțiile care permit windowing_clause sunt urmate de un asterisc (*).

rânduri / interval aceste cuvinte cheie definesc pentru fiecare rând o fereastră (un set fizic sau logic de rânduri) utilizată pentru calcularea rezultatului funcției. Funcția este apoi aplicată tuturor rândurilor din fereastră. Fereastra se deplasează prin setul de rezultate de interogare sau partiția de sus în jos.

• ROWS specifică fereastra în unități fizice (rânduri).

• RANGE specifică fereastra ca o compensare logică.

nu puteți specifica această clauză decât dacă ați specificatorder_by_clause. Unele limite de fereastră definite deRANGE clauza vă permit să specificați o singură expresie înorder_by_clause. Consultați „restricții privind clauza ORDER BY”.

valoarea returnată de o funcție analitică cu un offset logic este întotdeauna deterministă. Cu toate acestea, valoarea returnată de o funcție analitică cu o compensare fizică poate produce rezultate nedeterministe, cu excepția cazului în care expresia de ordonare are ca rezultat o ordonare unică. Poate fi necesar să specificați mai multe coloane în order_by_clause pentru a realiza această comandă unică.

între … Și utilizați BETWEENAND clauză pentru a specifica un punct de început și un punct final pentru fereastră. Prima expresie (înainte de AND) definește punctul de început și a doua expresie (după AND) definește punctul final.

dacă omiteți BETWEEN și specificați un singur punct final, atunci Oracle îl consideră punctul de pornire, iar punctul final este implicit la rândul curent.

nelimitat precizațiUNBOUNDEDPRECEDING pentru a indica faptul că fereastra începe de la primul rând al partiției. Aceasta este specificația punctului de pornire și nu poate fi utilizată ca specificație a punctului final.

nelimitat în urma specificaUNBOUNDEDFOLLOWING pentru a indica faptul că fereastra se termină la ultimul rând al partiției. Aceasta este specificația punctului final și nu poate fi utilizată ca specificație a punctului de pornire.

rândul curent ca punct de pornire,CURRENTROW specifică faptul că fereastra începe la rândul sau valoarea curentă (în funcție de dacă ați specificatROW sauRANGE, respectiv). În acest caz, punctul final nu poate fi value_exprPRECEDING.

ca punct final,CURRENTROW specifică faptul că fereastra se termină la rândul sau valoarea curentă (în funcție de dacă ați specificatROW sauRANGE, respectiv). În acest caz, punctul de pornire nu poate fi value_exprFOLLOWING.

value_expr precedent sau value_expr următor pentru RANGE sau ROW:

• dacăvalue_exprFOLLOWING este punctul de pornire, atunci punctul final trebuie să fievalue_exprFOLLOWING.

• dacăvalue_exprPRECEDING este punctul final, atunci punctul de pornire trebuie să fievalue_exprPRECEDING.

dacă definiți o fereastră logică definită de un interval de timp în format numeric, atunci poate fi necesar să utilizați funcții de conversie.

dacă ați specificatROWS:

• value_expr este o compensare fizică. Trebuie să fie o constantă sau o expresie și trebuie să evalueze la o valoare numerică pozitivă.

• dacăvalue_expr face parte din punctul de pornire, atunci trebuie să evalueze la un rând înainte de punctul final.

dacă ați specificatRANGE:

• value_expr este o compensare logică. Trebuie să fie o constantă sau o expresie care se evaluează la o valoare numerică pozitivă sau la un interval literal. Consultați „literali” pentru informații despre literali de interval.

• puteți specifica o singură expresie înorder_by_clause.

• dacă value_expr se evaluează la o valoare numerică, atunci ORDERBYexpr trebuie să fie un ID numeric sau DATE tip de date.

• 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. În lista funcțiilor analitice care urmează, funcțiile urmate de un asterisc (*) permit sintaxa completă, inclusiv windowing_clause.

AVG *
CORR *
COUNT *
COVAR_POP *
COVAR_SAMP *
CUME_DIST
DENSE_RANK
FIRST
FIRST_VALUE *
LAG
LAST
LAST_VALUE *
LEAD
LISTAGG
MAX *
MEDIAN
MIN *
NTH_VALUE *
NTILE
percent_rank
PERCENTILE_CONT
percentile_disc
rank
ratio_to_report
regr_ (regresie liniară) funcții *
ROW_NUMBER
stddev *
STDDEV_POP *
stddev_samp *
sumă *
var_pop *
var_samp *
varianță *