Pl Sql Mobile Moyenne

Il s'agit d'une question Evergreen Joe Celko. J'ignore quelle plate-forme DBMS est utilisée. Mais dans tous les cas Joe a réussi à répondre il ya plus de 10 ans avec SQL standard. Joe Celko SQL Puzzles et Réponses citation: Cette dernière tentative de mise à jour suggère que nous pourrions utiliser le prédicat pour construire une requête qui nous donnerait une moyenne mobile: La colonne supplémentaire ou l'approche de requête mieux La requête est techniquement mieux parce que l'approche UPDATE sera Dénormaliser la base de données. Toutefois, si les données historiques enregistrées ne vont pas changer et que le calcul de la moyenne mobile est onéreux, vous pouvez envisager d'utiliser l'approche par colonne. SQL requête Puzzle: par tous les moyens uniformes. Vous venez de jeter au godet de poids approprié en fonction de la distance du point de temps actuel. Par exemple quottake weight1 pour datapoints dans 24 heures à partir du point de données actuel weight0.5 pour datapoints dans 48hrsquot. Dans ce cas, il importe combien de points de données consécutifs (comme 6:12 am et 11:48 pm) sont distants les uns des autres Un cas d'utilisation, je peux penser serait une tentative de lisser l'histogramme partout où datapoints ne sont pas denses assez ndash msciwoj May 27 15 at 22:22 Je ne suis pas sûr que votre résultat attendu (sortie) montre classique simple déplacer (rolling) moyenne pour 3 jours. Parce que, par exemple, le premier triple de nombres par définition donne: mais vous attendez 4.360 et sa confusion. Néanmoins, je suggère la solution suivante, qui utilise la fonction de fenêtre AVG. Cette approche est beaucoup plus efficace (claire et moins intensive en ressources) que SELF-JOIN introduit dans d'autres réponses (et Im surpris que personne n'a donné une meilleure solution). Vous voyez que AVG est enveloppé avec le cas lorsque rownum gt p. days alors à forcer NULL s dans les premières rangées, où 3 jours Moyenne mobile est vide de sens. Répondre fév 23 16 at 13:12 Nous pouvons appliquer Joe Celkos dirty left jointure méthode (comme cité plus haut par Diego Scaravaggi) pour répondre à la question telle qu'elle a été posée. Génération de la sortie demandée: Réponse Jan 9 16 à 0:33 Votre réponse 2017 Stack Exchange, IncExponential Moving Average Il y avait une question intéressante sur OTN aujourd'hui au sujet de savoir s'il existe une fonction Oracle standard pour calculer la moyenne mobile exponentielle. La réponse est qu'il n'existe pas de telle fonction, mais avec la clause modèle, vous pouvez calculer très facilement. Et c'est un excellent exemple de ce que je veux dire avec le nombre variable de calculs basés sur des valeurs calculées, écrit dans ma troisième partie du tutoriel de la clause modèle. Avant aujourd'hui, je ne savais même pas ce qu'est une moyenne exponentielle mobile était exactement. Vous pouvez en lire plus ici sur Wikipedia ou ici avec un bel exemple. Du premier lien: Une moyenne mobile exponentielle (EMA), applique des facteurs de pondération qui diminuent exponentiellement. La pondération de chaque point de données plus ancien diminue de façon exponentielle, donnant beaucoup plus d'importance aux observations récentes tout en ne rejetant pas entièrement les observations plus anciennes. A partir du deuxième lien: La formule pour calculer une moyenne mobile exponentielle (EMA) est: X EMA courante (c.-à-d. EMA à calculer) C Valeur de données d'origine actuelle K Constante de lissage P EMA précédente (La première EMA de la plage à calculer est K Smoothing Constant 2 (1 n) Et cette formule est suivie d'un exemple que j'ai étendu un peu, en utilisant ce tableau: Les enregistrements du produit A Correspond à l'exemple dans le lien. J'ai composé les nombres à partir du produit B. Voici la requête de clause modèle qui implémente la formule. Notez comment la formule traduire directement dans la seule règle de la clause de modèle. La constante de lissage K est définie À .5, sur la base d'une fenêtre de valeurs (n) égale à 3. Défi: essayez ceci sans la clause modèle et voyez si vous pouvez trouver quelque chose de plus complet 5 commentaires: 11.2 fonctionnalités en usage avec dat as (select 39A39 product Date 392009-01-0139 mois, 10 montant de double union tous sélectionner 39A39, date 392009-02-0139, 15 de dual union tous sélectionner 39A39, date 392009-03-0139, 17 de dual union tous sélectionner 39A39, date 392009 -04-0139, 20 de double union tous sélectionnés 39A39, date 392009-05-0139, 22 de double union tous sélectionnés 39A39, date 392009-06-0139, 20 de double union tous sélectionnés 39A39, date 392009-07-0139, 25 de dual union tous sélectionnés 39A39, date 392009-08-0139, 27 de dual union tous sélectionnés 39A39, date 392009-09-0139, 30 de dual union tous sélectionnés 39A39, date 392009-10-0139, 35 de dual union tous Sélectionnez 39A39, date 392009-11-0139, 37 de double union tous sélectionnés 39A39, date 392009-12-0139, 40 de double union tous sélectionnés 39B39, date 392009-01-0139, 0 de double union tous sélectionnés 39B39, date 392009 -02-0139, 50 de double union tous sélectionnés 39B39, date 392009-03-0139, 10 de double union tous sélectionnés 39B39, date 392009-04-0139, 40 de double union tous sélectionnés 39B39, date 392009-05-0139, 15 de dual union tous sélectionnés 39B39, date 392009-06-0139, 35 de dual union tous sélectionnés 39B39, date 392009-07-0139, 30 de dual union tous sélectionnés 39B39, date 392009-08-0139, 30 de dual union tous Sélectionnez 39B39, date 392009-09-0139, 20 de dual union tous sélectionnés 39B39, date 392009-10-0139, 20 de double union tous sélectionnés 39B39, date 392009-11-0139, 20 de double union tous sélectionnés 39B39, date 392009 -12-0139, 20 de dual), rns as (select dat. . Rownumber () over (partition par ordre de produit par mois) rn -. 2 (1count () sur (partition par produit)) k. (Produit, mois, montant, rn, x) comme (sélectionner r. produit, r. month, r. amount, r. rn, r. amount x de rns r où rn 1 union all select Ns. product, ns. month, ns. amount, ns. rn, ns. k (ns. amount - es. x) es. xx de rns ns, res es où ns. rn es. rn 1 et ns. product es (X, 3) EMA de res commande par produit, mois après le calcul de la forme fermée Je suis venu avec le code suivant qui, si plus comme une obscurcissement que rien d'exhaustif. L'idée est de créer des multiples en cours d'exécution en utilisant une concaténation de chaîne et la fonction xml-eval. Les formes fermées des cas spéciaux ne nécessitent que des sommes courantes. Il ya un cas général et deux cas spéciaux qui sont beaucoup plus faciles: avec t1 comme (sélectionner le produit, le mois, le montant, le montant ci, le nombre de rownumber () sur (partition par ordre de produit par mois) rn, --2 (1 rownumber (Par ordre de produit par mois)) ki 0,5 ki des ventes), t2 comme (choisissez le produit, le mois, le montant, (cas où rn 1 puis 1 autre ki fin ci) ai, cas où rn 1 puis 1 autre Ki) fin bi à partir de t1), t3 comme (produit SELECT, MOIS, quantité, ai, xmlquery), 39,39, 3939 (REMPLACER (wmconcat (bi) ) RETOUR au contenu).getnumberval () mi FROM t2), t4 comme (sélectionner le produit, le mois, le montant, mi, (mi) xi de t3) SELECT product, MONTH, amount, round Par produit COMMANDE PAR MOIS lignes entre ENTRÉE non précisée et COURANTE ACTUELLE), 3) ema FROM t4 Cas particulier K 0.5: avec t1 comme (sélectionner le produit, le mois, le montant, le numéro de rope () sur (partition par ordre de produit par mois) rn, 1, 0), 1)) ci de la vente) choisir le produit, le mois, le montant, le tour (somme (ci) sur (partition par (2, rn), 3) ema de t1 Cas particulier K 2 (1 i): avec t1 comme (sélectionner le produit, le mois, le montant, le numéro de rownumber () sur (partition Par ordre de produit par mois) rn, nombre rownumber () sur (partition par ordre de produit par mois) ci de la vente) select product, month, amount, round Et la ligne courante) 2 (rn (rn 1)), 3) ema de t1 I39ll afficher la preuve de la forme fermée si quelqu'un est intéressé à elle. C'est un excellent exemple de quotfun avec SQLquot :-) Une combinaison de XMLQuery, le wmconcat non documenté, et des fonctions analytiques avec la clause windowing. J'aime ça. Bien qu'il ne soit pas aussi complet que la variante de la clause modèle et le Rafu39s récursif avec un, comme vous l'avez dit vous-même. Et bien sûr, j'aimerais voir la preuve de la forme fermée. J'ai abordé une autre question: comment optimiser la constante de lissage SELECT k - lissage constant. Mse - erreur quadratique moyenne FROM (SÉLECTIONNER À partir des ventes MODÈLE DIMENSION BY (produit ROWNUMBER () PARTIE (PARTITION PAR produit ORDRE PAR mois ASC) rn) MESURES (montant - montant des ventes. AS - erreur de carré - - ligne de travail et attributs - a) ligne de travail est le produit 39X39, rn 1 - b) les attributs de travail sont les suivants:. 0 AS SSE - somme SE pour tous les mois de produits. 0 AS MSE - moyenne SSE pour tous les mois de produits. 0 AS k - pour tous les produits mois. 0 AS PreMSE - avant MS39 k39s pour tous les mois produits. 0 AS diff - entre MSE actuel et antérieur. 0.1 AS delta - incrément initial. 0 AS priorpt - point de départ initial -) RÈGLES ITERATE (99) JUSQU'À (abs (diff39A39,1) lt 0,00010) (Cany, rn montantcv (), cv () K39A39,1 priorpt39A39,1 delta39A39,1. , Rn ORDRE BY produit, rn ASC COALESCE (K39A39,1 Ccv (), cv () (1-K39A39,1) Xcv (), cv () - 1, Ccv (), cv ()) Pproduct, rn Xcv (), Cv () - 1. SEproduct, rn PUISSANCE (Ccv (), cv () - Xcv (), cv () - 1, 2) SSE39A39,1 SUM (SE) any, any MSE39A39,1 SUM (SE) any, any 24. diff39A39,1 CASE itérationnumber WHEN 0 alors NULL ELSE preMSE39A39,1 - MSE39A39,1 END. PreMSE39A39,1 MSE39A39,1 delta39A39,1 CASE WHEN diff39A39,1 lt 0 THEN - abs (delta39A39, 12) ELSE abs (delta39A39,1) ENDT39A39,1 K39A39,1)) où produit 39A39 et rn 1 K MSE ---------- .599999237 174.01609421 SQL pour l'analyse et la génération de rapports Traitement des NULL en tant que fonctions d'entrée vers la fenêtre Fonctions de fenêtre La sémantique NULL correspond à la sémantique NULL pour les fonctions d'agrégation SQL. D'autres sémantiques peuvent être obtenues par des fonctions définies par l'utilisateur, ou en utilisant l'expression DECODE ou CASE dans la fonction de fenêtre. Fonctions de fenêtrage avec décalage logique Un décalage logique peut être spécifié avec des constantes comme la GAMME 10 PRECEDING. Ou une expression qui évalue à une constante, ou par une spécification d'intervalle comme RANGE INTERVAL N JOUR MOIS ANNEE PRECEDING ou une expression qui évalue à un intervalle. Avec le décalage logique, il ne peut y avoir qu'une seule expression dans la liste ORDER BY dans la fonction, avec un type compatible NUMERIC si offset est numérique ou DATE si un intervalle est spécifié. Exemple 21-7 Fonction agrégée cumulative Voici un exemple de montants cumulés par ID de client par trimestre en 1999: Dans cet exemple, la fonction analytique SUM définit, pour chaque ligne, une fenêtre qui commence au début de la partition (UNBOUNDED PRECEDING ) Et se termine, par défaut, à la ligne en cours. Les SONS imbriqués sont nécessaires dans cet exemple puisque nous effectuons une SOMME sur une valeur qui est elle-même une SOMME. Les agrégats imbriqués sont utilisés très souvent dans les fonctions agrégées analytiques. Exemple 21-8 Déplacement de la fonction agrégat Cet exemple de fenêtre temporelle indique, pour un client, la moyenne mobile des ventes pour le mois en cours et les deux mois précédents: Notez que les deux premières lignes du calcul de la moyenne mobile sur trois mois Les données de sortie sont basées sur une taille d'intervalle plus petite que celle spécifiée car le calcul de la fenêtre ne peut pas dépasser les données récupérées par la requête. Vous devez considérer les différentes tailles de fenêtre trouvées aux frontières des jeux de résultats. En d'autres termes, vous devrez peut-être modifier la requête pour inclure exactement ce que vous voulez. Fonction d'agrégation centralisée Calculer les fonctions d'agrégation de fenêtres centrées autour de la ligne courante est simple. Cet exemple calcule pour tous les clients une moyenne mobile centrée des ventes pour une semaine fin décembre 1999. Il trouve une moyenne du total des ventes pour la journée précédant la ligne en cours et un jour suivant la ligne courante, y compris la ligne courante. Exemple 21-9 agrégat centré Les lignes de début et de fin de chaque calcul de la moyenne mobile centrée sur les produits dans les données de sortie sont basées sur deux jours seulement, car le calcul de la fenêtre ne peut pas dépasser les données extraites par la requête. Les utilisateurs doivent considérer les différentes tailles de fenêtres trouvées aux frontières des jeux de résultats: la requête peut devoir être ajustée. Fonctions d'agrégation de fenêtrage en présence de doublons L'exemple suivant illustre comment les fonctions d'agrégat de fenêtre calculent les valeurs lorsqu'il ya des doublons, c'est-à-dire lorsque plusieurs lignes sont renvoyées pour une valeur de commande unique. La requête récupère la quantité vendue à plusieurs clients pendant une période de temps spécifiée. (Bien que nous utilisons une vue en ligne pour définir notre ensemble de données de base, il n'a pas de signification particulière et peut être ignoré.) La requête définit une fenêtre mobile qui va de la date de la ligne en cours à 10 jours plus tôt. Notez que le mot clé RANGE Est utilisé pour définir la clause windowing de cet exemple. Cela signifie que la fenêtre peut potentiellement contenir plusieurs lignes pour chaque valeur dans la plage. Dans ce cas, il existe trois paires de lignes avec des valeurs de date en double. Exemple 21-10 Fenaison des fonctions agrégées avec décalages logiques Dans la sortie de cet exemple, toutes les dates, sauf le 6 mai et le 12 mai, renvoient deux lignes avec des dates dupliquées. Examinez les nombres commentés à droite de la sortie pour voir comment les valeurs sont calculées. Notez que chaque groupe entre parenthèses représente les valeurs retournées pour une seule journée. Notez que cet exemple s'applique uniquement lorsque vous utilisez le mot clé RANGE plutôt que le mot-clé ROWS. Il est également important de se rappeler que, avec RANGE. Vous ne pouvez utiliser qu'une expression ORDER BY dans la clause ORDER BY des fonctions analytiques. Avec le mot-clé ROWS, vous pouvez utiliser plusieurs ordres par expressions dans la clause ORDER BY des fonctions analytiques. Taille variable de fenêtre pour chaque rangée Il existe des situations où il est utile de varier la taille d'une fenêtre pour chaque ligne, en fonction d'une condition spécifiée. Par exemple, vous voudrez peut-être rendre la fenêtre plus grande pour certaines dates et plus petite pour d'autres. Supposons que vous souhaitez calculer la moyenne mobile du cours de l'action sur trois jours ouvrables. Si vous avez un nombre égal de lignes pour chaque jour pour tous les jours ouvrables et que des jours non ouvrables sont stockés, vous pouvez utiliser une fonction de fenêtre physique. Cependant, si les conditions notées ne sont pas remplies, vous pouvez toujours calculer une moyenne mobile en utilisant une expression dans les paramètres de taille de fenêtre. Les expressions dans une spécification de taille de fenêtre peuvent être faites dans plusieurs sources différentes. L'expression peut être une référence à une colonne dans une table, comme un tableau de temps. Il peut également s'agir d'une fonction qui renvoie la limite appropriée pour la fenêtre en fonction des valeurs de la ligne courante. L'instruction suivante pour une base de données hypothétique des prix des actions utilise une fonction définie par l'utilisateur dans sa clause RANGE pour définir la taille de la fenêtre: Dans cette déclaration, ttimekey est un champ de date. Ici, fn pourrait être une fonction PLSQL avec la spécification suivante: 4 si ttimekey est lundi, mardi Si l'un des jours précédents sont des jours fériés, il ajuste le compte correctement. Notez que lorsque la fenêtre est spécifiée à l'aide d'un nombre dans une fonction de fenêtre avec ORDER BY sur une colonne de date, elle est convertie pour indiquer le nombre de jours. Vous pouvez également avoir utilisé la fonction de conversion littérale d'intervalle, comme NUMTODSINTERVAL (fn (ttimekey), JOUR) au lieu de simplement fn (ttimekey) pour signifier la même chose. Vous pouvez également écrire une fonction PLSQL qui renvoie une valeur de type de données INTERVAL. Fenaison des fonctions agrégées avec compensations physiques Pour les fenêtres exprimées en lignes, les expressions de commande doivent être uniques pour produire des résultats déterministes. Par exemple, la requête suivante n'est pas déterministe parce que timeid n'est pas unique dans ce jeu de résultats. Exemple 21-11 Fenaison des fonctions agrégées avec compensations physiques Une façon de traiter ce problème serait d'ajouter la colonne prodid à l'ensemble de résultats et à l'ordre sur timeid et prodid. Fonctions FIRSTVALUE et LASTVALUE Les fonctions FIRSTVALUE et LASTVALUE vous permettent de sélectionner la première et la dernière ligne d'une fenêtre. Ces lignes sont particulièrement utiles parce qu'elles sont souvent utilisées comme lignes de base dans les calculs. Par exemple, avec une partition tenant les données de vente commandées par jour, vous pourriez demander Combien était chaque jours de ventes par rapport au premier jour de vente (FIRSTVALUE) de la période Ou vous souhaiteriez peut-être savoir, pour un ensemble de lignes dans l'ordre client croissant , Quelle était la taille en pourcentage de chaque vente dans la région par rapport à la vente la plus importante (LASTVALUE) dans la région Si l'option IGNORE NULLS est utilisée avec FIRSTVALUE. Il renverra la première valeur non nulle dans l'ensemble, ou NULL si toutes les valeurs sont NULL. Si IGNORE NULLS est utilisé avec LASTVALUE. Il renverra la dernière valeur non nulle dans l'ensemble, ou NULL si toutes les valeurs sont NULL. L'option IGNORE NULLS est particulièrement utile pour remplir correctement une table d'inventaire. Reporting des fonctions agrégées Une fois qu'une requête a été traitée, des valeurs agrégées comme le nombre de lignes résultantes ou une valeur moyenne dans une colonne peuvent être facilement calculées au sein d'une partition et mises à disposition d'autres fonctions de génération de rapports. Les fonctions d'agrégation de rapport renvoient la même valeur d'agrégation pour chaque ligne d'une partition. Leur comportement par rapport à NULL est le même que les fonctions d'agrégation SQL. La syntaxe est: En outre, les conditions suivantes s'appliquent: Un astérisque () n'est autorisé que dans COUNT () DISTINCT n'est pris en charge que si les fonctions d'agrégation correspondantes lui permettent d'évaluer l'expression1 et la valeur expression2 peut être toute expression valide impliquant des références de colonne ou des agrégats. La clause PARTITION BY définit les groupes sur lesquels les fonctions de fenêtrage seraient calculées. Si la clause PARTITION BY est absente, la fonction est calculée sur l'ensemble du jeu de résultats de la requête. Les fonctions de rapport peuvent apparaître uniquement dans la clause SELECT ou la clause ORDER BY. L'avantage majeur des fonctions de rapport est leur capacité à effectuer plusieurs passes de données dans un bloc de requête unique et à accélérer les performances des requêtes. Les requêtes telles que Compter le nombre de vendeurs avec des ventes de plus de 10 de ventes de la ville ne nécessitent pas des jointures entre blocs de requête distincts. Par exemple, considérez la question Pour chaque catégorie de produit, trouvez la région dans laquelle il avait des ventes maximales. La requête interne équivalente avec la fonction d'agrégation de rapports MAX (SUM (quantités)) renvoie: Les résultats complets de la requête sont les suivants: Exemple 21-12 Reporting Agrégat Exemple Reporting des agrégats combinés avec des requêtes imbriquées activées Pour répondre efficacement à des requêtes complexes. Par exemple, si vous voulez connaître les produits les plus vendus dans vos sous-catégories de produit les plus significatives Voici une requête qui trouve les 5 produits les plus vendus pour chaque sous-catégorie de produit qui contribue à plus de 20 des ventes dans sa catégorie de produit: RATIOTOREPORT Fonction La fonction RATIOTOREPORT calcule le rapport d'une valeur à la somme d'un ensemble de valeurs. Si l'expression value value est NULL. RATIOTOREPORT est également évalué à NULL. Mais elle est traitée comme nulle pour calculer la somme des valeurs pour le dénominateur. Sa syntaxe est: Dans ce cas, ce qui suit s'applique: expr peut être toute expression valide impliquant des références de colonne ou des agrégats. La clause PARTITION BY définit les groupes sur lesquels la fonction RATIOTOREPORT doit être calculée. Si la clause PARTITION BY est absente, la fonction est calculée sur l'ensemble du jeu de résultats de la requête. Pour calculer RATIOTOREPORT des ventes pour chaque canal, vous pouvez utiliser la syntaxe suivante: Fonctions LAGLEAD Les fonctions LAG et LEAD sont utiles pour comparer les valeurs lorsque les positions relatives des lignes peuvent être connues de manière fiable. Ils fonctionnent en spécifiant le nombre de lignes qui séparent la ligne cible de la ligne courante. Parce que les fonctions fournissent l'accès à plus d'une ligne d'une table en même temps sans une auto-jointure, ils peuvent améliorer la vitesse de traitement. La fonction LAG permet d'accéder à une ligne à un décalage donné avant la position courante et la fonction LEAD permet d'accéder à une ligne à un décalage donné après la position courante. Syntaxe de LAGLEAD Ces fonctions ont la syntaxe suivante: offset est un paramètre optionnel et par défaut 1. default est un paramètre facultatif et est la valeur retournée si le décalage tombe en dehors des limites de la table ou de la partition. Pour plus d'informations, reportez-vous à la section Densification des données pour la création de rapports pour savoir comment utiliser les fonctions LEAD de LAG pour effectuer des requêtes de comparaison période-période sur des données clairsemées. Fonctions de FIRSTLAST Les fonctions d'agrégation de FIRSTLAST vous permettent de classer un ensemble de données et de travailler avec ses rangs classés en premier ou en bas. Après avoir trouvé les rangées supérieures ou inférieures classées, une fonction agrégée est appliquée à n'importe quelle colonne souhaitée. C'est-à-dire, FIRST LAST vous permet de classer sur la colonne A, mais renvoie le résultat d'un agrégat appliqué sur les rangs en premier ou dernier rang de la colonne B. Cela est précieux parce qu'il évite la nécessité d'une auto-jointure ou sous-requête, donc Améliorer les performances. La syntaxe de ces fonctions commence par une fonction agrégée régulière (MIN MAX, SUM, AVG, COUNT, VARIANCE, STDDEV) qui produit une seule valeur de retour par groupe. Pour spécifier le classement utilisé, les fonctions FIRST LAST ajoutent une nouvelle clause commençant par le mot KEEP. Syntaxe FIRSTLAST Ces fonctions ont la syntaxe suivante: Notez que la clause ORDER BY peut prendre plusieurs expressions. FIRSTLAST En tant qu'agrégats réguliers Vous pouvez utiliser la famille FIRST LAST des agrégats comme fonctions agrégées régulières. Exemple 21-15 FIRSTLAST Exemple 1 La requête suivante nous permet de comparer le prix minimum et le prix catalogue de nos produits. Pour chaque sous-catégorie de produits de la catégorie Vêtements pour hommes, elle retourne ce qui suit: Prix de liste du produit avec le prix minimum le plus bas Prix minimum le plus bas Prix de vente du produit avec le prix minimum le plus élevé Prix plus élevé FIRSTLAST Comme agrégats déclarants Vous pouvez également utiliser le PREMIÈRE DERNIÈRE famille d'agrégats en tant que fonctions agrégées déclarantes. Un exemple est le calcul des mois qui ont eu la plus grande et la moindre augmentation du nombre de têtes tout au long de l'année. La syntaxe de ces fonctions est similaire à la syntaxe pour tout autre agrégat de rapport. Considérons l'exemple de l'Exemple 21-15 pour FIRSTLAST. Que faire si nous voulons trouver les prix de liste des produits individuels et de les comparer aux prix de liste des produits dans leur sous-catégorie qui avait le plus bas et le plus bas des prix minimum La requête suivante nous permet de trouver ces informations pour la sous-catégorie Documentation en utilisant FIRSTLAST comme rapports Agrégats. Exemple 21-16 FIRSTLAST Exemple 2 L'utilisation des fonctions FIRST et LAST comme agrégats de reporting facilite l'inclusion des résultats dans des calculs tels que le Salaire en pourcentage du salaire le plus élevé. Fonctions de percentiles inverses La fonction CUMEDIST permet de trouver la distribution cumulative (percentile) d'un ensemble de valeurs. Cependant, l'opération inverse (trouver quelle valeur calcule à un certain centile) n'est ni facile à faire ni efficacement calculée. Pour surmonter cette difficulté, les fonctions PERCENTILECONT et PERCENTILEDISC ont été introduites. Ceux-ci peuvent être utilisés à la fois comme des fonctions de rapport de fenêtre ainsi que des fonctions agrégées normales. Ces fonctions nécessitent une spécification de tri et un paramètre qui prend une valeur de centile entre 0 et 1. La spécification de tri est traitée en utilisant une clause ORDER BY avec une expression. Lorsqu'il est utilisé comme une fonction agrégée normale, il renvoie une valeur unique pour chaque ensemble ordonné. PERCENTILECONT. Qui est une fonction continue calculée par interpolation, et PERCENTILEDISC. Qui est une fonction d'étape qui prend des valeurs discrètes. Comme d'autres agrégats, PERCENTILECONT et PERCENTILEDISC fonctionnent sur un groupe de lignes dans une requête groupée, mais avec les différences suivantes: Ils nécessitent un paramètre entre 0 et 1 (inclus). Un paramètre spécifié hors de cette plage entraînera une erreur. Ce paramètre doit être spécifié comme une expression qui est évaluée à une constante. Ils nécessitent une spécification de tri. Cette spécification de tri est une clause ORDER BY avec une seule expression. Les expressions multiples ne sont pas autorisées. Syntaxe d'agrégation normale Inverse Percentile Exemple Base Nous utilisons la requête suivante pour renvoyer les 17 lignes de données utilisées dans les exemples de cette section: PERCENTILEDISC (x) est calculé en parcourant les valeurs CUMEDIST dans chaque groupe jusqu'à ce que vous trouviez le premier plus grand que Ou égal à x. Où x est la valeur de centile spécifiée. Pour l'exemple de requête où PERCENTILEDISC (0.5), le résultat est 5.000, comme suit: Le résultat de PERCENTILECONT est calculé par interpolation linéaire entre les lignes après leur commande. Pour calculer PERCENTILECONT (x). Nous calculons d'abord le nombre de lignes RN (1x (n-1)), où n est le nombre de lignes dans le groupe et x est la valeur de centile spécifiée. Le résultat final de la fonction agrégat est calculé par interpolation linéaire entre les valeurs des lignes aux numéros de ligne CRN CEIL (RN) et FRN FLOOR (RN). Le résultat final sera: PERCENTILECONT (X) if (CRN FRN RN), puis (valeur d'expression de la ligne à RN) else (CRN-RN) (valeur d'expression pour la ligne à FRN) (RN - FRN) Expression pour ligne à CRN). Considérons l'exemple précédent de requête, où nous calculons PERCENTILECONT (0.5). Ici n est 17. Le numéro de rang RN (1 0,5 (n-1)) 9 pour les deux groupes. En mettant cela dans la formule, (FRNCRN9), nous renvoyons la valeur de la ligne 9 comme résultat. Un autre exemple est, si vous voulez calculer PERCENTILECONT (0.66). Le nombre de lignes calculées RN (1 0,66 (n -1)) (1 0,6616) 11,67. PERCENTILECONT (0,66) (12-11,67) (valeur de la rangée 11) (11,67-11) (valeur de la rangée 12). Ces résultats sont les suivants: Les fonctions d'agrégats à percentiles inverses peuvent apparaître dans la clause HAVING d'une requête comme les autres fonctions d'agrégation existantes. Comme agrégats de reporting Vous pouvez également utiliser les fonctions agrégées PERCENTILECONT. PERCENTILEDISC en tant que fonctions agrégées déclarantes. Lorsqu'il est utilisé comme fonctions d'agrégation de rapports, la syntaxe est similaire à celles des autres agrégats de rapport. Cette requête calcule la même chose (limite de crédit médiane pour les clients dans ce jeu de résultats, mais rapporte le résultat de chaque ligne dans le jeu de résultats, comme indiqué dans la sortie suivante: Restrictions de percentiles inverses Pour PERCENTILEDISC., L'expression dans la clause ORDER BY peut Être de n'importe quel type de données que vous pouvez trier (numériques, chaîne, date, etc.) Toutefois, l'expression dans la clause ORDER BY doit être un type numérique ou datetime (y compris les intervalles) parce que l'interpolation linéaire est utilisée pour évaluer PERCENTILECONT. Si l'expression est de type DATE, le résultat interpolé est arrondi à la plus petite unité pour le type. Pour un type DATE, la valeur interpolée sera arrondie à la seconde la plus proche, pour les types d'intervalles à la seconde la plus proche (INTERVAL JOUR À SECOND) Par exemple, lorsque vous voulez trouver la valeur médiane dans un ensemble, la base de données Oracle ignore les valeurs NULL et trouve la médiane Parmi les valeurs non nulles. Vous pouvez utiliser l'option NULLS FIRST NULLS LAST dans la clause ORDER BY, mais ils seront ignorés car NULLs sont ignorés. Fonctions de répartition et de répartition hypothétiques Ces fonctions fournissent des fonctionnalités utiles pour l'analyse de simulation. A titre d'exemple, quel serait le rang d'une rangée, si la ligne était insérée hypothétiquement dans un ensemble d'autres lignes Cette famille d'agrégats prend un ou plusieurs arguments d'une rangée hypothétique et d'un groupe ordonné de lignes, renvoyant le RANG. DENSERANK. PERCENTRANK ou CUMEDIST de la ligne comme si elle avait été insérée dans le groupe. Syntaxe hypothétique et distribution Syntaxe Ici, expression constante fait référence à une expression qui évalue à une constante, et il peut y avoir plus d'une de ces expressions qui sont passées comme arguments à la fonction. La clause ORDER BY peut contenir une ou plusieurs expressions qui définissent l'ordre de tri sur lequel le classement sera basé. ASC. DESC. NULLS D'ABORD. Les options NULLS LAST seront disponibles pour chaque expression dans le ORDER BY. Exemple 21-17 Classement et distribution hypothétique Exemple 1 À l'aide des données de prix de liste du tableau des produits utilisées dans cette section, vous pouvez calculer le RANG. PERCENTRANK et CUMEDIST pour un pull hypothétique avec un prix de 50 pour la façon dont il s'inscrit dans chacune des sous-catégories sweater. La requête et les résultats sont: Contrairement aux agrégats de percentiles inverses, la clause ORDER BY dans la spécification de tri pour les fonctions de rang et de distribution hypothétiques peut prendre plusieurs expressions. Le nombre d'arguments et les expressions dans la clause ORDER BY doivent être identiques et les arguments doivent être des expressions constantes du même type ou compatibles avec l'expression ORDER BY correspondante. Voici un exemple utilisant deux arguments dans plusieurs fonctions de classement hypothétiques. Exemple 21-18 Rang et distribution hypothétiques Exemple 2 Ces fonctions peuvent apparaître dans la clause HAVING d'une requête comme les autres fonctions d'agrégation. Ils ne peuvent pas être utilisés comme des fonctions agrégées de rapport ou des fonctions d'agrégation de fenêtrage. Fonctions de régression linéaire Les fonctions de régression supportent l'ajustement d'une droite de régression de moindres carrés ordinaires à un ensemble de paires de nombres. Vous pouvez les utiliser à la fois comme fonctions agrégées ou comme fonctions de fenêtrage ou de génération de rapports. Les fonctions sont les suivantes: Oracle applique la fonction à l'ensemble des couples (e1. E2) après avoir éliminé toutes les paires pour lesquelles e1 ou e2 est nul. E1 est interprété comme une valeur de la variable dépendante (une valeur y), et e2 est interprété comme une valeur de la variable indépendante (une valeur x). Les deux expressions doivent être des nombres. Les fonctions de régression sont toutes calculées simultanément pendant une seule passe à travers les données. Ils sont souvent combinés avec le COVARPOP. COVARSAMP. Et les fonctions CORR. REGRCOUNT La fonction REGRCOUNT renvoie le nombre de paires de nombres non nuls utilisées pour ajuster la ligne de régression. Si elle est appliquée à un ensemble vide (ou s'il n'y a pas de paires (e1, e2) où ni e1 ni e2 est nul), la fonction renvoie 0. REGRAVGY et REGRAVGX Les fonctions REGRAVGY et REGRAVGX calculent les moyennes de la variable dépendante et de l'indépendant Variable de la droite de régression, respectivement. REGRAVGY calcule la moyenne de son premier argument (e1) après avoir éliminé (e1. E2) les paires où e1 ou e2 est nul. De même, REGRAVGX calcule la moyenne de son deuxième argument (e2) après élimination nulle. Les deux fonctions renvoient NULL si elles sont appliquées à un ensemble vide. REGRSLOPE et REGRINTERCEPT Fonctions La fonction REGRSLOPE calcule la pente de la droite de régression ajustée aux couples non nuls (e1, e2). La fonction REGRINTERCEPT calcule l'intersection y de la droite de régression. REGRINTERCEPT renvoie NULL lorsque la pente ou les moyennes de régression sont NULL. Fonction REGRR2 La fonction REGRR2 calcule le coefficient de détermination (habituellement appelé R-carré ou qualité d'ajustement) pour la droite de régression. REGRR2 renvoie des valeurs comprises entre 0 et 1 lorsque la droite de régression est définie (la pente de la ligne n'est pas nulle) et renvoie NULL autrement. Plus la valeur est proche de 1, meilleure est la ligne de régression qui correspond aux données. REGRSXX, REGRSYY et REGRSXY Fonctions REGRSXX. Les fonctions REGRSYY et REGRSXY sont utilisées dans le calcul de diverses statistiques de diagnostic pour l'analyse de régression. Après avoir éliminé les paires (e1, e2) où e1 ou e2 est nul, ces fonctions effectuent les calculs suivants: Statistiques de régression linéaire Exemples Certaines statistiques de diagnostic courantes qui accompagnent l'analyse de régression linéaire sont données au Tableau 21-2, Statistiques diagnostiques communes et leurs Expressions . Notez que ces nouvelles fonctions permettent de calculer toutes ces fonctions. Tableau 21-2 Statistiques de diagnostic communes et leurs expressions Exemple de calcul de régression linéaire Dans cet exemple, nous calculons une droite de régression des moindres carrés ordinaires qui exprime la quantité vendue d'un produit en tant que fonction linéaire du prix de liste des produits. Les calculs sont regroupés par canal de vente. Les valeurs PENTE. INTCPT. RSQR sont respectivement la pente, l'intersection et le coefficient de détermination de la droite de régression. La valeur (entière) COUNT correspond au nombre de produits dans chaque canal pour lequel les données de quantité vendues et de prix de liste sont disponibles. Ensembles d'articles fréquents Au lieu de compter combien de fois un événement donné se produit (par exemple, combien de fois quelqu'un a acheté du lait à l'épicerie), les articles fréquents fournissent un mécanisme pour compter combien de fois plusieurs événements se produisent ensemble (par exemple, Et des céréales ensemble à l'épicerie). L'entrée de l'opération des éléments fréquents est un ensemble de données qui représente des collections d'éléments (ensembles d'éléments). Quelques exemples de itemsets pourraient être tous les produits qu'un client donné a acheté en un seul voyage à l'épicerie (communément appelé panier de marché), les pages Web auxquelles un utilisateur a accédé en une seule session, ou les services financiers qu'un Compte tenu du client utilise. La notion d'itemset fréquent est de trouver les itemsets qui se produisent le plus souvent. Si vous appliquez l'opérateur d'articles fréquents à des données de point de vente d'épiceries, vous pourriez par exemple découvrir que le lait et les bananes sont la paire la plus achetée. Des itemsets fréquents ont ainsi été utilisés dans les environnements de business intelligence pendant de nombreuses années, le plus courant étant pour l'analyse de panier de marché dans l'industrie de détail. Des jeux d'items fréquents sont intégrés à la base de données, fonctionnant au-dessus des tables relationnelles et accessibles via SQL. Cette intégration offre quelques avantages clés: les applications qui comptaient auparavant sur des opérations fréquentes d'itemset bénéficient désormais d'une amélioration significative des performances ainsi que d'une implémentation plus simple. Les applications basées sur SQL qui n'utilisaient pas de jeu d'items fréquents peuvent désormais être facilement étendues pour tirer parti de cette fonctionnalité. L'analyse des itemsets fréquents est effectuée avec le package PLSQL DBMSFREQUENTITEMSETS. Pour plus d'informations, reportez-vous à la section Référence des packages et types PLSQL. Autres fonctions statistiques Oracle introduit un ensemble de fonctions statistiques SQL et un ensemble de statistiques, DBMSSTATFUNCS. Cette section répertorie certaines des nouvelles fonctions ainsi que la syntaxe de base. Pour plus d'informations sur le package DBMSSTATFUNCS et la référence de base de données Oracle Database pour la syntaxe et la sémantique, reportez-vous à la section Référence des packages et des types PLSQL. Statistiques descriptives Vous pouvez calculer les statistiques descriptives suivantes: Médiane d'un mode de jeu de données d'un ensemble de données Vous pouvez calculer les statistiques paramétriques suivantes: Spearmans rho Coefficient Kendalls tau-b Coefficient Outre les fonctions, cette version a un nouveau package PLSQL, DBMSSTATFUNCS. Il contient la fonction statistique descriptive RESUME ainsi que des fonctions pour supporter le couplage de distribution. La fonction RÉSUMÉ résume une colonne numérique d'une table avec une variété de statistiques descriptives. Les cinq fonctions d'ajustement de distribution supportent des distributions normales, uniformes, Weibull, Poisson et exponentielles. WIDTHBUCKET Fonction Pour une expression donnée, la fonction WIDTHBUCKET renvoie le nombre de seau que le résultat de cette expression sera attribué après qu'il a été évalué. Vous pouvez générer des histogrammes d'équidistance avec cette fonction. Les histogrammes d'écoute divisent les ensembles de données en seaux dont la taille d'intervalle (la valeur la plus élevée à la valeur la plus basse) est égale. Le nombre de lignes détenues par chaque seau variera. Une fonction liée, NTILE. Crée des seaux equiheight. Les histogrammes d'Equiwidth peuvent être générés uniquement pour les types numériques, date ou datetime. Les trois premiers paramètres doivent donc être toutes les expressions numériques ou toutes les expressions de date. D'autres types d'expressions ne sont pas autorisés. Si le premier paramètre est NULL. Le résultat est NULL. Si le second ou le troisième paramètre est NULL. Un message d'erreur est renvoyé, car une valeur NULL ne peut indiquer un point final (ou un point quelconque) pour une plage dans une dimension de date ou de valeur numérique. Le dernier paramètre (nombre de seaux) doit être une expression numérique qui évalue à un entier positif 0, NULL. Ou une valeur négative entraînera une erreur. Les godets sont numérotés de 0 à (n 1). Le godet 0 contient le nombre de valeurs inférieures au minimum. Seau (n 1) contient le nombre de valeurs supérieures ou égales à la valeur maximale spécifiée. WIDTHBUCKET Syntaxe Le WIDTHBUCKET prend quatre expressions comme paramètres. Le premier paramètre est l'expression que l'histogramme d'équidistance est pour. Les deuxième et troisième paramètres sont des expressions qui désignent les points finaux de la plage acceptable pour le premier paramètre. Le quatrième paramètre indique le nombre de seaux. Considérez les données suivantes des clients de la table. Qui montre les limites de crédit de 17 clients. Ces données sont rassemblées dans la requête présentée dans l'exemple 21-19. Dans la table des clients. La colonne custcreditlimit contient des valeurs entre 1500 et 15000, et nous pouvons assigner les valeurs à quatre seaux d'équidistance, numérotés de 1 à 4, en utilisant WIDTHBUCKET (custcreditlimit, 0, 20000, 4). Idéalement, chaque seau est un intervalle fermé-ouvert de la ligne de nombre réel, par exemple, le seau numéro 2 est attribué à des scores entre 5000.0000 et 9999.9999. Parfois désigné par 5000, 10000) pour indiquer que 5 000 sont compris dans l'intervalle et 10 000 sont exclus. Pour affecter des valeurs en dehors de la plage 0, 20 000), des valeurs inférieures à 0 sont attribuées à un seau de débordement désigné qui est numéroté 0 et des valeurs supérieures ou égales à 20 000 sont affectées à un seau de débordement désigné qui est numéroté 5 (nombre de seaux 1 en général). Voir Figure 21-3 pour une illustration graphique de la façon dont les seaux sont affectés. Vous pouvez spécifier les bornes dans l'ordre inverse, par exemple, WIDTHBUCKET (custcreditlimit. 20000. 0. 4). Lorsque les limites sont inversées, les seaux seront des intervalles ouverts-fermés. Dans cet exemple, le nombre de seaux 1 est (15000,20000, le nombre de seaux 2 est (10000,15000 et le seau numéro 4, est (0, 5000. Le seau de débordement sera numéroté 0 (20000. infini) et le seau de débordement Sera numérotée 5 (- infini 0. Il s'agit d'une erreur si le paramètre de compte de compteur est 0 ou négatif. La requête suivante indique les numéros de compteur pour les limites de crédit dans la table clients pour les deux cas où les limites sont spécifiées en Ou ordre inverse. Nous utilisons une plage de 0 à 20 000. Fonctions d'agrégation définies par l'utilisateur Oracle offre une facilité pour créer vos propres fonctions, appelées fonctions agrégées définies par l'utilisateur. Ces fonctions sont écrites dans des langages de programmation tels que PLSQL, Java et C , Et peuvent être utilisés comme des fonctions analytiques ou des agrégats dans des vues matérialisées. Voir Oracle Data Cartridge Developers Guide pour plus d'informations sur la syntaxe et les restrictions. Les avantages de ces fonctions sont: Des fonctions hautement complexes peuvent être programmés en utilisant un langage entièrement procédural. Plus grande évolutivité que les autres techniques lorsque les fonctions définies par l'utilisateur sont programmées pour le traitement parallèle. Les types de données objet peuvent être traités. En tant qu'exemple simple d'une fonction agrégée définie par l'utilisateur, considérez la statistique d'inclinaison. Ce calcul mesure si un ensemble de données a une distribution déséquilibrée autour de sa moyenne. Il vous dira si une queue de la distribution est beaucoup plus grande que l'autre. If you created a user-defined aggregate called udskew and applied it to the credit limit data in the prior example, the SQL statement and results might look like this: Before building user-defined aggregate functions, you should consider if your needs can be met in regular SQL. Many complex calculations are possible directly in SQL, particularly by using the CASE expression. Staying with regular SQL will enable simpler development, and many query operations are already well-parallelized in SQL. Even the earlier example, the skew statistic, can be created using standard, albeit lengthy, SQL. CASE Expressions Oracle now supports simple and searched CASE statements. CASE statements are similar in purpose to the DECODE statement, but they offer more flexibility and logical power. They are also easier to read than traditional DECODE statements, and offer better performance as well. They are commonly used when breaking categories into buckets like age (for example, 20-29, 30-39, and so on). The syntax for simple statements is: The syntax for searched statements is: You can specify only 255 arguments and each WHEN. THEN pair counts as two arguments. For a workaround to this limit, see Oracle Database SQL Reference . Suppose you wanted to find the average salary of all employees in the company. If an employees salary is less than 2000, you want the query to use 2000 instead. Without a CASE statement, you would have to write this query as follows, In this, foo is a function that returns its input if the input is greater than 2000, and returns 2000 otherwise. The query has performance implications because it needs to invoke a function for each row. Writing custom functions can also add to the development load. Using CASE expressions in the database without PLSQL, this query can be rewritten as: Using a CASE expression lets you avoid developing custom functions and can also perform faster. Creating Histograms With User-Defined Buckets You can use the CASE statement when you want to obtain histograms with user-defined buckets (both in number of buckets and width of each bucket). The following are two examples of histograms created with CASE statements. In the first example, the histogram totals are shown in multiple columns and a single row is returned. In the second example, the histogram is shown with a label column and a single column for totals, and multiple rows are returned. Example 21-21 Histogram Example 1 Example 21-22 Histogram Example 2 Data Densification for Reporting Data is normally stored in sparse form. That is, if no value exists for a given combination of dimension values, no row exists in the fact table. However, you may want to view the data in dense form, with rows for all combination of dimension values displayed even when no fact data exist for them. For example, if a product did not sell during a particular time period, you may still want to see the product for that time period with zero sales value next to it. Moreover, time series calculations can be performed most easily when data is dense along the time dimension. This is because dense data will fill a consistent number of rows for each period, which in turn makes it simple to use the analytic windowing functions with physical offsets. Data densification is the process of converting spare data into dense form. To overcome the problem of sparsity, you can use a partitioned outer join to fill the gaps in a time series or any other dimension. Such a join extends the conventional outer join syntax by applying the outer join to each logical partition defined in a query. Oracle logically partitions the rows in your query based on the expression you specify in the PARTITION BY clause. The result of a partitioned outer join is a UNION of the outer joins of each of the partitions in the logically partitioned table with the table on the other side of the join. Note that you can use this type of join to fill the gaps in any dimension, not just the time dimension. Most of the examples here focus on the time dimension because it is the dimension most frequently used as a basis for comparisons. Partition Join Syntax The syntax for partitioned outer join extends the ANSI SQL JOIN clause with the phrase PARTITION BY followed by an expression list. The expressions in the list specify the group to which the outer join is applied. The following are the two forms of syntax normally used for partitioned outer join: Note that FULL OUTER JOIN is not supported with a partitioned outer join. Sample of Sparse Data A typi cal situation with a sparse dimension is shown in the following example, which computes the weekly sales and year-to-date sales for the product Bounce for weeks 20-30 in 2000 and 2001: In this example, we would expect 22 rows of data (11 weeks each from 2 years) if the data were dense. However we get only 18 rows because weeks 25 and 26 are missing in 2000, and weeks 26 and 28 in 2001. Filling Gaps in Data We can take the sparse data of the preceding query and do a partitioned outer join with a dense set of time data. In the following query, we alias our original query as v and we select data from the times table, which we alias as t. Here we retrieve 22 rows because there are no gaps in the series. The four added rows each have 0 as their Sales value set to 0 by using the NVL function. Note that in this query, a WHERE condition was placed for weeks between 20 and 30 in the inline view for the time dimension. This was introduced to keep the result set small. Filling Gaps in Two Dimensions N-dimensional data is typically displayed as a dense 2-dimensional cross tab of (n - 2) page dimensions. This requires that all dimension values for the two dimensions appearing in the cross tab be filled in. The following is another example where the partitioned outer join capability can be used for filling the gaps on two dimensions: In this query, the WITH sub-query factoring clause v1. summarizes sales data at the product, country, and year level. This result is sparse but users may want to see all the country, year combinations for each product. To achieve this, we take each partition of v1 based on product values and outer join it on the country dimension first. This will give us all values of country for each product. We then take that result and partition it on product and country values and then outer join it on time dimension. This will give us all time values for each product and country combination. Filling Gaps in an Inventory Table An inventory table typically tracks quantity of units available for various products. This table is sparse: it only stores a row for a product when there is an event. For a sales table, the event is a sale, and for the inventory table, the event is a change in quantity available for a product. For example, consider the following inventory table: The inventory table now has the following rows: For reporting purposes, users may want to see this inventory data differently. For example, they may want to see all values of time for each product. This can be accomplished using partitioned outer join. In addition, for the newly inserted rows of missing time periods, users may want to see the values for quantity of units column to be carried over from the most recent existing time period. The latter can be accomplished using analytic window function LASTVALUE value. Here is the query and the desired output: The inner query computes a partitioned outer join on time within each product. The inner query densifies the data on the time dimension (meaning the time dimension will now have a row for each day of the week). However, the measure column quantity will have nulls for the newly added rows (see the output in the column quantity in the following results. The outer query uses the analytic function LASTVALUE. Applying this function partitions the data by product and orders the data on the time dimension column ( timeid ). For each row, the function finds the last non-null value in the window due to the option IGNORE NULLS. which you can use with both LASTVALUE and FIRSTVALUE. We see the desired output in the column repeatedquantity in the following output: Computing Data Values to Fill Gaps Examples in previous section illustrate how to use partitioned outer join to fill gaps in one or more dimensions. However, the result sets produced by partitioned outer join have null values for columns that are not included in the PARTITION BY list. Typically, these are measure columns. Users can make use of analytic SQL functions to replace those null values with a non-null value. For example, the following query computes monthly totals for products 64MB Memory card and DVD-R Discs (product IDs 122 and 136) for the year 2000. It uses partitioned outer join to densify data for all months. For the missing months, it then uses the analytic SQL function AVG to compute the sales and units to be the average of the months when the product was sold. If working in SQLPlus, the following two commands will wrap the column headings for greater readability of results: Time Series Calculations on Densified Data Densificatio n is not just for reporting purpose. It also enables certain types of calculations, especially, time series calculations. Time series calculations are easier when data is dense along the time dimension. Dense data has a consistent number of rows for each time periods which in turn make it simple to use analytic window functions with physical offsets. To illustrate, lets first take the example on Filling Gaps in Data. and lets add an analytic function to that query. In the following enhanced version, we calculate weekly year-to-date sales alongside the weekly sales. The NULL values that the partitioned outer join inserts in making the time series dense are handled in the usual way: the SUM function treats them as 0s. Period-to-Period Comparison for One Time Level: Example How do we use this feature to compare values across time periods Specifically, how do we calculate a year-over-year sales comparison at the week level The following query returns on the same row, for each product, the year-to-date sales for each week of 2001 with that of 2000. Note that in this example we start with a WITH clause. This improves readability of the query and lets us focus on the partitioned outer join. If working in SQLPlus, the following command will wrap the column headings for greater readability of results: In the FROM clause of the in-line view densesales. we use a partitioned outer join of aggregate view v and time view t to fill gaps in the sales data along the time dimension. The output of the partitioned outer join is then processed by the analytic function SUM. OVER to compute the weekly year-to-date sales (the weeklyytdsales column). Thus, the view densesales computes the year-to-date sales data for each week, including those missing in the aggregate view s. The in-line view yearoveryearsales then computes the year ago weekly year-to-date sales using the LAG function. The LAG function labeled weeklyytdsalesprioryear specifies a PARTITION BY clause that pairs rows for the same week of years 2000 and 2001 into a single partition. We then pass an offset of 1 to the LAG function to get the weekly year to date sales for the prior year. The outermost query block selects data from yearoveryearsales with the condition yr 2001, and thus the query returns, for each product, its weekly year-to-date sales in the specified weeks of years 2001 and 2000. Period-to-Period Comparison for Multiple Time Levels: Example While the prior example shows us a way to create comparisons for a single time level, it would be even more useful to handle multiple time levels in a single query. For example, we could compare sales versus the prior period at the year, quarter, month and day levels. How can we create a query which performs a year-over-year comparison of year-to-date sales for all levels of our time hierarchy We will take several steps to perform this task. The goal is a single query with comparisons at the day, week, month, quarter, and year level. The steps are as follows: We will create a view called cubeprodtime. which holds a hierarchical cube of sales aggregated across times and products . Then we will create a view of the time dimension to use as an edge of the cube. The time edge, which holds a complete set of dates, will be partitioned outer joined to the sparse data in the view cubeprodtime . Finally, for maximum performance, we will create a materialized view, mvprodtime. built using the same definition as cubeprodtime . For more information regarding hierarchical cubes, see Chapter 20, SQL for Aggregation in Data Warehouses. The materialized view is defined using the following statement: Step 1 Create the hierarchical cube view The materialized view shown in the following may already exist in your system if not, create it now. If you must generate it, please note that we limit the query to just two products to keep processing time short: Because this view is limited to two products, it returns just over 2200 rows. Note that the column HierarchicalTime contains string representations of time from all levels of the time hierarchy. The CASE expression used for the HierarchicalTime column appends a marker (0, 1. ) to each date string to denote the time level of the value. A 0 represents the year level, 1 is quarters, 2 is months, and 3 is day. Note that the GROUP BY clause is a concatenated ROLLUP which specifies the rollup hierarchy for the time and product dimensions. The GROUP BY clause is what determines the hierarchical cube contents. Step 2 Create the view edgetime, which is a complete set of date values edgetime is the source for filling time gaps in the hierarchical cube using a partitioned outer join. The column HierarchicalTime in edgetime will be used in a partitioned join with the HierarchicalTime column in the view cubeprodtime. The following statement defines edgetime : Step 3 Create the materialized view mvprodtime to support faster performance The materialized view definition is a duplicate of the view cubeprodtime defined earlier. Because it is a duplicate query, references to cubeprodtime will be rewritten to use the mvprodtime materialized view. The following materialized may already exist in your system if not, create it now. If you must generate it, please note that we limit the query to just two products to keep processing time short. Step 4 Create the comparison query We have now set the stage for our comparison query. We can obtain period-to-period comparison calculations at all time levels. It requires applying analytic functions to a hierarchical cube with dense data along the time dimension. Some of the calculations we can achieve for each time level are: Sum of sales for prior period at all levels of time. Variance in sales over prior period. Sum of sales in the same period a year ago at all levels of time. Variance in sales over the same period last year. The following example performs all four of these calculations. It uses a partitioned outer join of the views cubeprodtime and edgetime to create an in-line view of dense data called densecubeprodtime. The query then uses the LAG function in the same way as the prior single-level example. The outer WHERE clause specifies time at three levels: the days of August 2001, the entire month, and the entire third quarter of 2001. Note that the last two rows of the results contain the month level and quarter level aggregations. Note: To make the results easier to read if you are using SQLPlus, the column headings should be adjusted with the following commands. The commands will fold the column headings to reduce line length: Here is the query comparing current sales to prior and year ago sales: The first LAG function ( salespriorperiod ) partitions the data on gidp. cat. subcat. prod. gidt and orders the rows on all the time dimension columns. It gets the sales value of the prior period by passing an offset of 1. The second LAG function ( salessameperiodprioryear ) partitions the data on additional columns qtrnum. monnum. and daynum and orders it on yr so that, with an offset of 1, it can compute the year ago sales for the same period. The outermost SELECT clause computes the variances. Creating a Custom Member in a Dimension: Example In many OLAP tasks, it is helpful to define custom members in a dimension. For instance, you might define a specialized time period for analyses. You can use a partitioned outer join to temporarily add a member to a dimension. Note that the new SQL MODEL clause is suitable for creating more complex scenarios involving new members in dimensions. See Chapter 22, SQL for Modeling for more information on this topic. As an example of a task, what if we want to define a new member for our time dimension We want to create a 13th member of the Month level in our time dimension. This 13th month is defined as the summation of the sales for each product in the first month of each quarter of year 2001. The solution has two steps. Note that we will build this solution using the views and tables created in the prior example. Two steps are required. First, create a view with the new member added to the appropriate dimension. The view uses a UNION ALL operation to add the new member. To query using the custom member, use a CASE expression and a partitioned outer join. Our new member for the time dimension is created with the following view: In this statement, the view timec is defined by performing a UNION ALL of the edgetime view (defined in the prior example) and the user-defined 13th month. The gidt value of 8 was chosen to differentiate the custom member from the standard members. The UNION ALL specifies the attributes for a 13th month member by doing a SELECT from the DUAL table. Note that the grouping id, column gidt. is set to 8, and the quarter number is set to 5. Then, the second step is to use an inline view of the query to perform a partitioned outer join of cubeprodtime with timec. This step creates sales data for the 13th month at each level of product aggregation. In the main query, the analytic function SUM is used with a CASE expression to compute the 13th month, which is defined as the summation of the first months sales of each quarter. The SUM function uses a CASE to limit the data to months 1, 4, 7, and 10 within each year. Due to the tiny data set, with just 2 products, the rollup values of the results are necessarily repetitions of lower level aggregations. For more realistic set of rollup values, you can include more products from the Game Console and Y Box Games subcategories in the underlying materialized view.