Funkcje analityczne

funkcje analityczne obliczają zagregowaną wartość na podstawie grupy wierszy. Różnią się one od funkcji agregujących tym, że zwracają wiele wierszy dla każdej grupy. Grupa wierszy nazywa się oknem i jest zdefiniowana przez analytic_clause. Dla każdego wiersza zdefiniowane jest przesuwane okno wierszy. Okno określa zakres wierszy używanych do wykonywania obliczeń dla bieżącego wiersza. Rozmiary okien mogą być oparte na fizycznej liczbie wierszy lub interwale logicznym, takim jak czas.

funkcje analityczne są ostatnim zestawem operacji wykonywanych w zapytaniu, z wyjątkiem końcowej klauzuli ORDERBY. Wszystkie połączenia i wszystkie WHEREGROUPBY oraz HAVING klauzule są uzupełniane przed przetworzeniem funkcji analitycznych. Dlatego funkcje analityczne mogą pojawiać się tylko na liście select lub ORDERBY.

funkcje analityczne są powszechnie używane do obliczania agregatów skumulowanych, ruchomych, wyśrodkowanych i raportujących.

analytic_function::=

opisem ilustracji „analytic_function.gif „

analytic_clause::=

opis ilustracji „analytic_clause.gif „

query_partition_clause::=

opisem ilustracji „query_partition_clause.gif „

order_by_clause::=

opis ilustracji „order_by_clause.gif „

windowing_clause ::=

opis ilustracji „windowing_clause.gif „

semantyka tej składni jest omówiona w poniższych sekcjach.

analytic_function

określa nazwę funkcji analitycznej (Zobacz listę funkcji analitycznych po omówieniu semantyki).

argumenty

funkcje analityczne przyjmują od 0 do 3 argumentów. Argumentami mogą być dowolny numeryczny typ danych lub dowolny nonnumeryczny typ danych, który można w domyśle przekształcić NA numeryczny typ danych. Oracle określa argument o najwyższym priorytecie liczbowym i domyślnie konwertuje pozostałe argumenty na ten typ danych. Zwracanym typem jest również ten typ danych, o ile nie zaznaczono inaczej dla pojedynczej funkcji.

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. Możesz określić funkcje analityczne za pomocą tej klauzuli na liście select lubORDERBY. Aby filtrować wyniki zapytania w oparciu o funkcję analityczną, zagnieżdżaj te funkcje w zapytaniu nadrzędnym, a następnie filtruj wyniki zagnieżdżonego zapytania podrzędnego.

uwagi dotyczące analytic_clause: poniższe uwagi dotycząanalytic_clause:

• nie można zagnieżdżać funkcji analitycznych, określając dowolną funkcję analityczną w dowolnej części analytic_clause. Można jednak określić funkcję analityczną w podquery i obliczyć nad nią inną funkcję analityczną.

• możesz określić OVERanalytic_clause z definiowanymi przez użytkownika funkcjami analitycznymi, a także wbudowanymi funkcjami analitycznymi. Zobacz Tworzenie funkcji.

query_partition_clause

użyj PARTITIONBYaby podzielić wynik zapytania na grupy oparte na jednym lub więcej value_expr. Jeśli pominiesz tę klauzulę, wtedy funkcja traktuje wszystkie wiersze wyniku zapytania ustawionego jako jedną grupę.

aby użyćquery_partition_clause w funkcji analitycznej, użyj górnej gałęzi składni (bez nawiasów). Aby użyć tej klauzuli w zapytaniu modelu (w model_column_clauses) lub partycjonowanym zewnętrznym połączeniu (w outer_join_clause), użyj dolnej gałęzi składni (z nawiasami).

możesz określić wiele funkcji analitycznych w tym samym zapytaniu, każda z tymi samymi lub różnymi kluczamiPARTITIONBY.

Jeśli zapytane obiekty mają atrybut parallel i jeśli podasz funkcję analityczną za pomocą query_partition_clause, to obliczenia funkcji są również równoległe.

poprawne wartości value_expr są stałymi, kolumnami, funkcjami nieanalitycznymi, wyrażeniami funkcyjnymi lub wyrażeniami zawierającymi którąkolwiek z nich.

order_by_clause

użyj order_by_clause, aby określić sposób porządkowania danych na partycji. Dla wszystkich funkcji analitycznych można zamówić wartości na partycji na wielu kluczach, każdy zdefiniowany przez value_expr I Każdy zakwalifikowany przez sekwencję zamawiania.

w ramach każdej funkcji można określić wiele wyrażeń porządkowych. Jest to szczególnie przydatne przy użyciu funkcji, które porządkują wartości, ponieważ drugie wyrażenie może rozwiązać powiązania między identycznymi wartościami dla pierwszego wyrażenia.

ilekroćorder_by_clause daje identyczne wartości dla wielu wierszy, funkcja zachowuje się następująco:

• CUME_DISTDENSE_RANKNTILEPERCENT_RANK I RANK zwraca ten sam wynik dla każdego wiersza.

• ROW_NUMBERprzypisuje każdemu wierszu odrębną wartość, nawet jeśli istnieje remis oparty na order_by_clause. Wartość jest oparta na kolejności, w jakiej wiersz jest przetwarzany, co może być nieterministyczne, jeśli ORDERBY nie gwarantuje całkowitego zamówienia.

• dla wszystkich innych funkcji analitycznych wynik zależy od specyfikacji okna. Jeśli określisz okno logiczne ze słowem kluczowymRANGE, wtedy funkcja zwróci ten sam wynik dla każdego wiersza. Jeśli określisz okno fizyczne ze słowem kluczowym ROWS, wynik będzie nieterministyczny.

Ograniczenia dotyczące klauzuli ORDER BY poniższe ograniczenia dotyczą klauzuli ORDERBY :

• gdy używana jest funkcja analityczna, order_by_clausemusi przyjmować wyrażenie (expr). Słowo kluczoweSIBLINGS jest niepoprawne (ma znaczenie tylko w zapytaniach hierarchicznych). Pozycja (position) I aliasy kolumn (c_alias) są również nieprawidłowe. W przeciwnym razie order_by_clause jest taki sam, jak ten używany do zamówienia ogólnego zapytania lub zapytania podrzędnego.

• 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

  granice okien inne niż te cztery mogą mieć tylko jeden klucz sortowania w ORDERBY klauzuli funkcji analitycznej. Ograniczenie to nie ma zastosowania do granic okien określonych słowem kluczowym ROW.

ASC / DESC określa kolejność kolejności (rosnąco lub malejąco). ASC jest wartością domyślną.

NULLS FIRST | nulls LAST określa, czy zwracane wiersze zawierające nulls powinny pojawić się jako pierwsze, czy jako ostatnie w kolejności kolejności.

NULLSLAST jest wartością domyślną dla porządku rosnącego, aNULLSFIRST jest wartością domyślną dla porządku malejącego.

funkcje analityczne zawsze działają na wierszach w kolejności określonej w order_by_clause funkcji. Jednak order_by_clause funkcji nie gwarantuje kolejności wyniku. Użyj order_by_clause zapytania, aby zagwarantować kolejność końcowego wyniku.

windowing_clause

niektóre funkcje analityczne zezwalają nawindowing_clause. Na liście funkcji analitycznych na końcu tej sekcji po funkcjach, które umożliwiają windowing_clause następuje gwiazdka (*).

ROWS | RANGE te słowa kluczowe definiują dla każdego wiersza okno (fizyczny lub logiczny zestaw wierszy) używane do obliczania wyniku funkcji. Funkcja jest następnie stosowana do wszystkich wierszy w oknie. Okno przechodzi przez zestaw wyników zapytania lub partycję od góry do dołu.

• ROWS określa okno w jednostkach fizycznych (wierszach).

• RANGE określa okno jako logiczne przesunięcie.

nie możesz podać tej klauzuli, chyba że podałeś order_by_clause. Niektóre granice okien zdefiniowane przezRANGE pozwalają określić tylko jedno wyrażenie worder_by_clause. Zobacz „Ograniczenia dotyczące klauzuli ORDER BY”.

wartość zwracana przez funkcję analityczną z przesunięciem logicznym jest zawsze deterministyczna. Jednak wartość zwracana przez funkcję analityczną z przesunięciem fizycznym może dawać wyniki niedeterministyczne, chyba że wyrażenie porządkujące powoduje unikalne uporządkowanie. Może być konieczne podanie wielu kolumn w order_by_clause, aby osiągnąć tę unikalną kolejność.

między … I użyj BETWEENAND klauzula określająca punkt początkowy i końcowy okna. Pierwsze wyrażenie (przed AND) określa punkt początkowy, a drugie wyrażenie (po AND) określa punkt końcowy.

jeśli pominiesz BETWEEN I określisz tylko jeden punkt końcowy, Oracle uzna go za punkt początkowy, a punkt końcowy będzie domyślnie ustawiony na bieżący wiersz.

UNBOUNDED previous SpecifyUNBOUNDEDPRECEDING to indicate that the window starts at the first row of the partition. Jest to specyfikacja punktu początkowego i nie może być używana jako Specyfikacja punktu końcowego.

poniżej podanoUNBOUNDEDFOLLOWING, aby wskazać, że okno kończy się w ostatnim wierszu partycji. Jest to specyfikacja punktu końcowego i nie może być używana jako Specyfikacja punktu początkowego.

bieżący wiersz jako punkt początkowy, CURRENTROW określa, że okno zaczyna się od bieżącego wiersza lub wartości (w zależności od tego, czy podano odpowiednio ROW lub RANGE). W tym przypadku punktem końcowym nie może być value_exprPRECEDING.

jako punkt końcowy,CURRENTROW określa, że okno kończy się na bieżącym wierszu lub wartości (w zależności od tego, czy podano odpowiednioROW lubRANGE). W tym przypadku punktem startowym nie może być value_exprFOLLOWING.

value_expr poprzedzające lub VALUE_EXPR następujące dlaRANGE lub ROW:

• Jeślivalue_exprFOLLOWING jest punktem początkowym, to punktem końcowym musi byćvalue_exprFOLLOWING.

• Jeśli value_exprPRECEDINGjest punktem końcowym, to punktem początkowym musi być value_exprPRECEDING.

Jeśli definiujesz okno logiczne zdefiniowane przez przedział czasu w formacie numerycznym, może być konieczne użycie funkcji konwersji.

jeśli podałeśROWS:

• value_expr jest fizycznym przesunięciem. Musi być stałą lub wyrażeniem i musi być obliczona na dodatnią wartość liczbową.

• Jeśli value_expr jest częścią punktu początkowego, to musi obliczyć wiersz przed punktem końcowym.

jeśli podałeśRANGE:

• value_expr jest przesunięciem logicznym. Musi to być stała lub wyrażenie, które zwraca dodatnią wartość liczbową lub literał interwału. Patrz „literały”, aby uzyskać informacje na temat literałów interwałowych.

• W order_by_clause można podać tylko jedno wyrażenie.

• Jeśli value_expr Zwraca wartość liczbową, to ORDERBYexpr musi być liczbą lub typ danych.

• 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. Na poniższej liście funkcji analitycznych funkcje, po których następuje gwiazdka ( * ), umożliwiają pełną składnię, w tym windowing_clause.

AVG *
CORR *
COUNT *
COVAR_POP *
COVAR_SAMP *
CUME_DIST
DENSE_RANK
FIRST
FIRST_VALUE *
LAG
LAST
LAST_VALUE *
LEAD
LISTAGG
MAX *
mediana
MIN *
NTH_VALUE *
NTILE
percent_rank
percentile_cont
percentile_disc
rank
RATIO_TO_REPORT
REGR_ (regresja liniowa) funkcje *
ROW_NUMBER
STDDEV *
STDDEV_POP *
stddev_samp *
suma *
VAR_POP *
var_samp *
wariancja *