Fonctions analytiques

Les fonctions analytiques calculent une valeur agrégée basée sur un groupe de lignes. Elles diffèrent des fonctions d’agrégation en ce qu’elles renvoient plusieurs lignes pour chaque groupe. Le groupe de lignes s’appelle une fenêtre et est défini par analytic_clause. Pour chaque ligne, une fenêtre coulissante de lignes est définie. La fenêtre détermine la plage de lignes utilisées pour effectuer les calculs pour la ligne en cours. La taille des fenêtres peut être basée sur un nombre physique de lignes ou sur un intervalle logique tel que le temps.

Les fonctions analytiques sont le dernier ensemble d’opérations effectuées dans une requête à l’exception de la clause finale ORDERBY. Toutes les jointures et toutes les clauses WHEREGROUPBY et HAVING sont terminées avant que les fonctions analytiques ne soient traitées. Par conséquent, les fonctions analytiques ne peuvent apparaître que dans la liste select ou la clause ORDERBY.

Les fonctions analytiques sont couramment utilisées pour calculer des agrégats cumulatifs, mobiles, centrés et de rapports.

analytic_function::=

Description de l’illustration « fonction analytique.je ne sais pas si vous avez besoin d’un identifiant de

Description de l’illustration « analytic_clause.je n’ai pas de problème avec la configuration de la partie de requête, mais je n’ai pas de problème avec la configuration de la partie de requête.gif suit
Description de l’illustration « query_partition_clause.gif »

order_by_clause::=

Description de l’illustration « order_by_clause.je n’ai pas de problème avec la configuration de la fenêtre, mais je n’ai pas de problème avec la configuration de la fenêtre.gif suit
Description de l’illustration « windowing_clause.gif »

La sémantique de cette syntaxe est discutée dans les sections qui suivent.

fonction analytique

Spécifiez le nom d’une fonction analytique (voir la liste des fonctions analytiques suite à cette discussion de la sémantique).

arguments

Les fonctions analytiques prennent 0 à 3 arguments. Les arguments peuvent être n’importe quel type de données numériques ou n’importe quel type de données non numériques qui peut être implicitement converti en un type de données numériques. Oracle détermine l’argument ayant la priorité numérique la plus élevée et convertit implicitement les arguments restants en ce type de données. Le type de retour est également ce type de données, sauf indication contraire pour une fonction individuelle.

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. Vous pouvez spécifier des fonctions analytiques avec cette clause dans la liste select ou la clause ORDERBY. Pour filtrer les résultats d’une requête basée sur une fonction analytique, imbriquez ces fonctions dans la requête parent, puis filtrez les résultats de la sous-requête imbriquée.

Notes sur la clause analytique : Les notes suivantes s’appliquent au analytic_clause :

• Vous ne pouvez pas imbriquer les fonctions analytiques en spécifiant une fonction analytique dans n’importe quelle partie du analytic_clause. Cependant, vous pouvez spécifier une fonction analytique dans une sous-requête et calculer une autre fonction analytique dessus.

• Vous pouvez spécifier OVERanalytic_clause avec des fonctions analytiques définies par l’utilisateur ainsi que des fonctions analytiques intégrées. Voir CRÉER UNE FONCTION.

query_partition_clause

Utilisez la clause PARTITIONBY pour partitionner le jeu de résultats de la requête en groupes basés sur un ou plusieurs value_expr. Si vous omettez cette clause, la fonction traite toutes les lignes du jeu de résultats de la requête comme un seul groupe.

Pour utiliser le query_partition_clausedans une fonction analytique, utilisez la branche supérieure de la syntaxe (sans parenthèses). Pour utiliser cette clause dans une requête de modèle (dans le model_column_clauses) ou une jointure externe partitionnée (dans le outer_join_clause), utilisez la branche inférieure de la syntaxe (avec des parenthèses).

Vous pouvez spécifier plusieurs fonctions analytiques dans la même requête, chacune avec les mêmes clés ou différentes PARTITIONBY.

Si les objets interrogés ont l’attribut parallel et si vous spécifiez une fonction analytique avec le query_partition_clause, les calculs de fonction sont également parallélisés.

Les valeurs valides de value_expr sont des constantes, des colonnes, des fonctions non analytiques, des expressions de fonction ou des expressions impliquant l’une de celles-ci.

order_by_clause

Utilisez le order_by_clause pour spécifier comment les données sont ordonnées dans une partition. Pour toutes les fonctions analytiques, vous pouvez ordonner les valeurs d’une partition sur plusieurs clés, chacune définie par un value_expr et chacune qualifiée par une séquence d’ordre.

Dans chaque fonction, vous pouvez spécifier plusieurs expressions de commande. Cela est particulièrement utile lorsque vous utilisez des fonctions qui classent des valeurs, car la deuxième expression peut résoudre les liens entre des valeurs identiques pour la première expression.

Chaque fois que order_by_clause donne des valeurs identiques pour plusieurs lignes, la fonction se comporte comme suit:

• CUME_DISTDENSE_RANKNTILEPERCENT_RANK, et RANK renvoie le même résultat pour chacune des lignes.

• ROW_NUMBER attribue à chaque ligne une valeur distincte même s’il existe une égalité basée sur le order_by_clause. La valeur est basée sur l’ordre dans lequel la ligne est traitée, ce qui peut être non déterministe si ORDERBY ne garantit pas un ordre total.

• Pour toutes les autres fonctions analytiques, le résultat dépend de la spécification de la fenêtre. Si vous spécifiez une fenêtre logique avec le mot clé RANGE, la fonction renvoie le même résultat pour chacune des lignes. Si vous spécifiez une fenêtre physique avec le mot clé ROWS, le résultat est non déterministe.

Restrictions sur la clause ORDER by Les restrictions suivantes s’appliquent à la clause ORDERBY:

• Lorsqu’il est utilisé dans une fonction analytique, le order_by_clausedoit prendre une expression (expr). Le mot clé SIBLINGS n’est pas valide (il n’est pertinent que dans les requêtes hiérarchiques). La position (position) et les alias de colonne (c_alias) sont également invalides. Sinon, cette order_by_clause est la même que celle utilisée pour ordonner la requête globale ou la sous-requête.

• 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

  Les limites de fenêtre autres que celles-ci ne peuvent avoir qu’une seule clé de tri dans la clause ORDERBY de la fonction analytique. Cette restriction ne s’applique pas aux limites de fenêtre spécifiées par le mot clé ROW.

ASC/DESC Spécifie la séquence de commande (ascendante ou descendante). ASC est la valeur par défaut.

Les valeurs NULLES EN PREMIER /NULLES EN DERNIER Spécifient si les lignes renvoyées contenant des valeurs nulles doivent apparaître en premier ou en dernier dans la séquence d’ordre.

NULLSLAST est la valeur par défaut pour l’ordre croissant, et NULLSFIRST est la valeur par défaut pour l’ordre décroissant.

Les fonctions analytiques fonctionnent toujours sur les lignes dans l’ordre spécifié dans le order_by_clause de la fonction. Cependant, le order_by_clause de la fonction ne garantit pas l’ordre du résultat. Utilisez le order_by_clause de la requête pour garantir l’ordre du résultat final.

Voir aussi:

order_by_clause de SELECT pour plus d’informations sur cette clause

windowing_clause

Certaines fonctions analytiques permettent le windowing_clause. Dans la liste des fonctions analytiques à la fin de cette section, les fonctions qui autorisent le windowing_clause sont suivies d’un astérisque (*).

LIGNES/PLAGE Ces mots clés définissent pour chaque ligne une fenêtre (un ensemble physique ou logique de lignes) utilisée pour calculer le résultat de la fonction. La fonction est ensuite appliquée à toutes les lignes de la fenêtre. La fenêtre se déplace de haut en bas dans l’ensemble de résultats de requête ou la partition.

• ROWS spécifie la fenêtre en unités physiques (lignes).

• RANGE spécifie la fenêtre comme un décalage logique.

Vous ne pouvez pas spécifier cette clause sauf si vous avez spécifié le order_by_clause. Certaines limites de fenêtre définies par la clause RANGE vous permettent de spécifier une seule expression dans order_by_clause. Reportez-vous à la  » Clause de restriction de la COMMANDE PAR « .

La valeur renvoyée par une fonction analytique avec un décalage logique est toujours déterministe. Cependant, la valeur renvoyée par une fonction analytique avec un décalage physique peut produire des résultats non déterministes à moins que l’expression d’ordre ne donne un ordre unique. Vous devrez peut-être spécifier plusieurs colonnes dans order_by_clause pour obtenir cet ordre unique.

ENTRE… ET utilisez le BETWEEN… clause AND pour spécifier un point de départ et un point de fin pour la fenêtre. La première expression (avant AND) définit le point de départ et la deuxième expression (après AND) définit le point final.

Si vous omettez BETWEEN et spécifiez un seul point final, Oracle le considère comme le point de départ et le point final est par défaut la ligne en cours.

NON BORNÉ PRÉCÉDENT Spécifiez UNBOUNDEDPRECEDING pour indiquer que la fenêtre commence à la première ligne de la partition. Il s’agit de la spécification de point de départ et ne peut pas être utilisée comme spécification de point final.

NON BORNÉ SUIVANT Spécifiez UNBOUNDEDFOLLOWING pour indiquer que la fenêtre se termine à la dernière ligne de la partition. Il s’agit de la spécification de point final et ne peut pas être utilisée comme spécification de point de départ.

LIGNE ACTUELLE Comme point de départ, CURRENTROW spécifie que la fenêtre commence à la ligne ou à la valeur actuelle (selon que vous avez spécifié respectivement ROW ou RANGE). Dans ce cas, le point final ne peut pas être value_exprPRECEDING.

En tant que point final, CURRENTROW spécifie que la fenêtre se termine à la ligne ou à la valeur actuelle (selon que vous avez spécifié ROW ou RANGE, respectivement). Dans ce cas, le point de départ ne peut pas être value_exprFOLLOWING.

value_expr PRÉCÉDANT ou value_expr SUIVANT Pour RANGE ou ROW:

• Si value_exprFOLLOWING est le point de départ, alors le point final doit être value_exprFOLLOWING.

• Si value_exprPRECEDING est le point final, alors le point de départ doit être value_exprPRECEDING.

Si vous définissez une fenêtre logique définie par un intervalle de temps au format numérique, vous devrez peut-être utiliser des fonctions de conversion.

Voir aussi:

NUMTOYMINTERVAL et NUMTODSINTERVAL pour des informations sur la conversion des temps numériques en intervalles

Si vous avez spécifié ROWS:

• value_expr est un décalage physique. Il doit s’agir d’une constante ou d’une expression et doit être évalué à une valeur numérique positive.

• Si value_expr fait partie du point de départ, alors il doit être évalué à une ligne avant le point final.

Si vous avez spécifié RANGE:

• value_expr est un décalage logique. Il doit s’agir d’une constante ou d’une expression qui s’évalue à une valeur numérique positive ou à un littéral d’intervalle. Reportez-vous à « Littéraux » pour plus d’informations sur les littéraux à intervalles.

• Vous ne pouvez spécifier qu’une seule expression dans le order_by_clause.

• Si value_expr évalue à une valeur numérique, alors ORDERBYexpr doit être une valeur numérique ou DATE type de données.

• 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. Dans la liste des fonctions analytiques qui suit, les fonctions suivies d’un astérisque (*) autorisent la syntaxe complète, y compris le windowing_clause.

MOYENNE*
CORR *
NOMBRE *
COVAR_POP *
COVAR_SAMP *
CUME_DIST
DENSE_RANK
PREMIER
FIRST_VALUE *
DÉCALAGE
DERNIER
LAST_VALUE *
AVANCE
LISTAGG
MAX *
MÉDIANE
MIN *
NTH_VALUE *
NTILE
PERCENT_RANK
PERCENTILE_CONT
PERCENTILE_DISC
RANK
RATIO_TO_REPORT
REGR_ (Régression linéaire) Fonctions *
ROW_NUMBER
STDDEV*
STDDEV_POP *
STDDEV_SAMP*
SOMME*
VAR_POP*
VAR_SAMP*
VARIANCE *

Voir aussi :

Données de Base de données Oracle Guide d’entreposage pour plus d’informations sur ces fonctions et pour des scénarios illustrant leur utilisation