Calculer Déplacement Moyen Dans Accès Sql

22 SQL pour l'analyse et le reporting Oracle a amélioré les capacités de traitement analytique SQL en introduisant une nouvelle famille de fonctions SQL analytiques. Ces fonctions analytiques vous permettent de calculer: Classement et percentiles Calcul des fenêtres mobiles Statistiques de régression linéaire Les fonctions de classement incluent les distributions cumulatives, le pourcentage de rang et les N-tiles. Les calculs de fenêtres mobiles vous permettent de trouver des agrégations mobiles et cumulatives, telles que des sommes et des moyennes. L'analyse Laglead permet d'effectuer des références directes entre les lignes afin de pouvoir calculer les changements période-période. L'analyse Firstlast vous permet de trouver la première ou la dernière valeur dans un groupe ordonné. Les autres améliorations apportées à SQL comprennent l'expression CASE et la jointure externe partitionnée. Les expressions CASE fournissent une logique if-then utile dans de nombreuses situations. La jointure externe partitionnée est une extension de la syntaxe de jointure externe ANSI qui permet aux utilisateurs de densifier sélectivement certaines dimensions tout en gardant les autres clairsemés. Cela permet aux outils de reporting de densifier de manière sélective les dimensions, par exemple celles qui apparaissent dans leurs rapports croisés tout en gardant les autres clairsemés. Pour améliorer la performance, les fonctions analytiques peuvent être parallélisées: plusieurs processus peuvent exécuter simultanément toutes ces instructions. Ces fonctionnalités rendent les calculs plus faciles et plus efficaces, améliorant ainsi les performances, l'évolutivité et la simplicité de la base de données. Les fonctions analytiques sont classées comme décrit dans le Tableau 22-1. Tableau 22-1 Fonctions analytiques et leurs utilisations Pour effectuer ces opérations, les fonctions analytiques ajoutent plusieurs nouveaux éléments au traitement SQL. Ces éléments s'appuient sur le SQL existant pour permettre des expressions de calcul flexibles et puissantes. A quelques exceptions près, les fonctions analytiques ont ces nouveaux éléments. Le flux de traitement est représenté à la figure 22-1. Figure 22-1 Ordre de traitement Les concepts essentiels utilisés dans les fonctions analytiques sont: Le traitement des requêtes à l'aide de fonctions analytiques a lieu en trois étapes. Tout d'abord, toutes les jointures, OÙ. Les clauses GROUP BY et HAVING sont remplies. Deuxièmement, le jeu de résultats est mis à la disposition des fonctions analytiques, et tous leurs calculs ont lieu. Troisièmement, si la requête a une clause ORDER BY à sa fin, le ORDER BY est traité pour permettre un ordre de sortie précis. L'ordre de traitement est illustré à la figure 22-1. Partitions de jeu de résultats Les fonctions analytiques permettent aux utilisateurs de diviser les ensembles de résultats de requête en groupes de lignes appelés partitions. Notez que le terme partitions utilisées avec les fonctions analytiques n'est pas lié à la fonction de partition de table. Tout au long de ce chapitre, le terme «partitions» désigne uniquement la signification des fonctions analytiques. Les partitions sont créées après les groupes définis avec les clauses GROUP BY, de sorte qu'elles sont disponibles pour tous les résultats agrégés tels que les sommes et les moyennes. Les divisions de partition peuvent être basées sur toutes les colonnes ou expressions souhaitées. Un ensemble de résultats de requête peut être partitionné en une seule partition contenant toutes les lignes, quelques grandes partitions ou plusieurs petites partitions contenant seulement quelques lignes chacune. Pour chaque ligne d'une partition, vous pouvez définir une fenêtre glissante de données. Cette fenêtre détermine la plage de lignes utilisées pour effectuer les calculs de la ligne en cours. Les tailles de fenêtres peuvent être basées soit sur un nombre physique de lignes, soit sur un intervalle logique tel que le temps. La fenêtre a une ligne de départ et une ligne de fin. Selon sa définition, la fenêtre peut se déplacer à une ou aux deux extrémités. Par exemple, une fenêtre définie pour une fonction de somme cumulée aurait sa ligne de départ fixée à la première ligne de sa partition et sa ligne de fin serait glisser depuis le point de départ jusqu'à la dernière ligne de la partition. En revanche, une fenêtre définie pour une moyenne mobile aurait à la fois ses points de départ et d'extrémité coulissant de sorte qu'ils maintiennent une plage physique ou logique constante. Une fenêtre peut être définie aussi grande que toutes les lignes dans une partition ou juste une fenêtre coulissante d'une ligne dans une partition. Quand une fenêtre est près d'une bordure, la fonction renvoie les résultats pour seulement les lignes disponibles, plutôt que de vous avertir que les résultats ne sont pas ce que vous voulez. Lorsque vous utilisez des fonctions de fenêtre, la ligne en cours est incluse pendant les calculs, vous devez donc spécifier (n -1) lorsque vous utilisez n éléments. Chaque calcul effectué avec une fonction analytique est basé sur une ligne courante dans une partition. La ligne courante sert de point de référence déterminant le début et la fin de la fenêtre. Par exemple, un calcul de la moyenne mobile centré peut être défini avec une fenêtre contenant la ligne courante, les six lignes précédentes et les six lignes suivantes. Cela créerait une fenêtre coulissante de 13 lignes, comme le montre la figure 22-2. Figure 22-2 Fenêtre coulissante Exemple de fonctions de classement, de fenêtrage et de génération de rapports Cette section illustre les fonctions analytiques de base pour le classement, le fenêtrage et la génération de rapports. Exemple de calcul de régression linéaire Dans cet exemple, nous calculons une droite de régression de moindres carrés ordinaire 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. Agrégats statistiques Oracle fournit 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. 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 possède un 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. Agrégats définis par l'utilisateur Oracle offre la possibilité de 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ées comme fonctions analytiques ou agrégats dans des vues matérialisées. Pour plus d'informations sur la syntaxe et les restrictions, reportez-vous au Guide des développeurs de la cartouche de données de la base de données Oracle. Les avantages de ces fonctions sont les suivantes: Des fonctions très complexes peuvent être programmées 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. Si vous avez créé un agrégat défini par l'utilisateur appelé udskew et l'avez appliqué aux données de limite de crédit dans l'exemple précédent, l'instruction SQL et les résultats peuvent ressembler à ceci: Avant de créer des fonctions agrégées définies par l'utilisateur, vous devriez considérer si vos besoins peuvent être satisfaits En SQL régulier. De nombreux calculs complexes sont possibles directement dans SQL, en particulier à l'aide de l'expression CASE. Rester avec SQL régulière permettra un développement plus simple, et de nombreuses opérations d'interrogation sont déjà bien parallélisés dans SQL. Même l'exemple précédent, la statistique d'inclinaison, peut être créé en utilisant standard, quoique long, SQL. Opérations de pivotement Le d ata renvoyé par les requêtes de business intelligence est souvent le plus utilisable s'il est présenté sous forme de tableau croisé. Le pivotclause de l'instruction SELECT vous permet d'écrire des requêtes d'interrogation croisée qui font pivoter des lignes en colonnes, agrégant des données dans le processus de la rotation. Le pivotement est une technique clé dans les entrepôts de données. Dans celui-ci, vous transformer plusieurs lignes d'entrée en moins et généralement plus larges lignes dans l'entrepôt de données. Lors du pivotement, un opérateur d'agrégation est appliqué pour chaque élément dans la liste des valeurs de la colonne pivot. La colonne pivot ne peut pas contenir d'expression arbitraire. Si vous devez pivoter sur une expression, vous devez alias l'expression dans une vue avant l'opération PIVOT. La syntaxe de base est la suivante: Pour illustrer l'utilisation du pivotement, créez la vue suivante comme base pour des exemples ultérieurs: Exemple: Pivoting L'instruction suivante illustre un pivot typique sur la colonne de canal: Notez que la sortie a créé quatre nouvelles colonnes d'alias , LES VENTES DIRECTES. INTERNETSALES. CATALOGSALES. Et TELESALES. Un pour chacune des valeurs de pivotement. La sortie est une somme. Si aucun alias n'est fourni, l'en-tête de colonne sera les valeurs de IN-list. Faire pivoter sur plusieurs colonnes Vous pouvez pivoter sur plusieurs colonnes. L'instruction suivante illustre un pivot de colonne multiple typique: Notez que cet exemple spécifie une colonne IN-list à colonnes multiples avec des en-têtes de colonnes conçus pour correspondre aux membres IN-list. Pivotant: agrégats multiples Vous pouvez pivoter avec plusieurs agrégats, comme indiqué dans l'exemple suivant: Notez que la requête crée des en-têtes de colonne en concaténant les valeurs de pivot (ou alias) avec l'alias de la fonction agrégée et un trait de soulignement. Distinguer les valeurs NULL générées par PIVOT des Null dans les données source Vous pouvez distinguer les valeurs null générées de l'utilisation de PIVOT et celles qui existent dans les données source. L'exemple suivant illustre les valeurs nulles générées par PIVOT. La requête suivante retourne des lignes de 5 colonnes, colonne prodid. Et faire pivoter les colonnes résultantes Q1. Q1COUNTTOTAL. Q2. Q2COUNTTOTAL. Pour chaque valeur unique de prodid. Q1COUNTTOTAL renvoie le nombre total de lignes dont la valeur qtr est Q1. C'est-à-dire, et Q2COUNTTOTAL renvoie le nombre total de lignes dont la valeur qtr est Q2. Supposons que nous avons une table de vente2 de la structure suivante: D'après le résultat, nous savons que pour le prodid 100, il ya 2 lignes de vente pour le trimestre Q1. Et 1 ligne de vente pour le trimestre Q2 pour 200 prodides, il ya 1 rang de ventes pour le trimestre Q1. Et aucune ligne de vente pour le trimestre Q2. Ainsi, dans Q2COUNTTOTAL. Vous pouvez identifier que NULLlt1gt provient d'une ligne dans la table d'origine dont la mesure est de valeur nulle, alors que NULLlt2gt est dû à aucune rangée étant présente dans la table d'origine pour 200 prodid dans trimestre Q2. Opérations unpivotées Un unpivot n'interrompt pas une opération PIVOT. Au lieu de cela, il tourne les données des colonnes en lignes. Si vous travaillez avec des données pivotantes, une opération UNPIVOT ne peut pas annuler les agrégations qui ont été effectuées par PIVOT ou par tout autre moyen. Pour illustrer le non-pivotement, créez d'abord une table pivotante qui comprend quatre colonnes, pour les trimestres de l'année: Le contenu des tables ressemble à ce qui suit: L'opération UNPIVOT suivante fait tourner les colonnes de quarts en lignes. Pour chaque produit, il y aura quatre lignes, une pour chaque trimestre. Notez l'utilisation de INCLUDE NULLS dans cet exemple. Vous pouvez également utiliser EXCLUDE NULLS. Qui est le paramètre par défaut. En outre, vous pouvez également ne pas pivoter en utilisant deux colonnes, comme suit: Wildcard et sous-requête Pivotant avec des opérations XML Si vous souhaitez utiliser un argument générique ou une sous-requête dans vos colonnes pivotantes, vous pouvez le faire avec la syntaxe PIVOT XML. Avec PIVOT XML, la sortie de l'opération est formatée correctement XML. L'exemple suivant illustre l'utilisation du mot clé générique, ANY. Il produit XML qui inclut toutes les valeurs de canal dans salesview: Notez que le mot clé ANY est disponible dans les opérations PIVOT uniquement dans le cadre d'une opération XML. Cette sortie inclut des données pour les cas où le canal existe dans l'ensemble de données. Notez également que les fonctions d'agrégation doivent spécifier une clause GROUP BY pour renvoyer plusieurs valeurs, et pourtant l'article pivot ne contient pas de clause GROUP BY explicite. Au lieu de cela, le pivotclause effectue un GROUP BY implicite. L'exemple suivant illustre l'utilisation d'une sous-requête. Il produit XML qui comprend toutes les valeurs de canal et les données de vente correspondant à chaque canal: La sortie densifie les données pour inclure tous les canaux possibles pour chaque produit. Densification des données pour le rapport Les données sont normalement stockées sous forme dispersée. Autrement dit, si aucune valeur n'existe pour une combinaison donnée de valeurs de dimension, aucune ligne n'existe dans la table de faits. Toutefois, vous souhaiterez peut-être afficher les données sous forme dense, avec des lignes pour toutes les combinaisons de valeurs de cote affichées même si aucune donnée de fait n'existe pour elles. Par exemple, si un produit n'a pas vendu pendant une période donnée, vous pouvez toujours vouloir voir le produit pour cette période avec une valeur de vente nulle à côté d'elle. En outre, les calculs de séries chronologiques peuvent être effectués plus facilement lorsque les données sont denses le long de la dimension temporelle. En effet, les données denses contiennent un nombre de lignes cohérent pour chaque période, ce qui facilite l'utilisation des fonctions analytiques de fenêtrage avec des décalages physiques. La densification des données est le processus de conversion des données clairsemées en forme dense. Pour surmonter le problème de la sparsité, vous pouvez utiliser une jointure externe partitionnée pour combler les lacunes d'une série temporelle ou de toute autre dimension. Une telle jointure étend la syntaxe de jointure externe conventionnelle en appliquant la jointure externe à chaque partition logique définie dans une requête. Oracle partitionne logiquement les lignes de votre requête en fonction de l'expression spécifiée dans la clause PARTITION BY. Le résultat d'une jointure externe partitionnée est UN UNION des jointures externes de chacune des partitions dans la table logiquement partitionnée avec la table de l'autre côté de la jointure. Notez que vous pouvez utiliser ce type de jointure pour combler les lacunes dans n'importe quelle dimension, pas seulement la dimension de temps. La plupart des exemples ici se concentrent sur la dimension temporelle parce que c'est la dimension la plus fréquemment utilisée comme base pour les comparaisons. Syntaxe de jointure de partition La syntaxe de jointure externe partitionnée étend la clause ANSI SQL JOIN avec la phrase PARTITION BY suivie d'une liste d'expression. Les expressions dans la liste spécifient le groupe auquel la jointure externe est appliquée. Voici les deux formes de syntaxe normalement utilisées pour la jointure externe partitionnée: Notez que FULL OUTER JOIN n'est pas pris en charge avec une jointure externe partitionnée. Exemple de données clairsemées Une situation typique avec une dimension clairsemée est illustrée dans l'exemple suivant, qui calcule les ventes hebdomadaires et les ventes cumulatives du produit rebond pour les semaines 20-30 en 2000 et 2001: Dans cet exemple, nous S'attendrait à 22 rangées de données (11 semaines chacune à partir de 2 ans) si les données étaient denses. Cependant, nous obtenons seulement 18 lignes parce que les semaines 25 et 26 sont manquantes en 2000, et les semaines 26 et 28 en 2001. Remplir les lacunes dans les données Nous pouvons prendre les données clairsemées de la requête précédente et faire une jointure externe partitionnée avec un ensemble dense de Temps. Dans la requête suivante, nous alias notre requête d'origine comme v et nous sélectionnons les données de la table des temps, que nous appelons alias t. Ici, nous récupérons 22 lignes car il n'y a pas de lacunes dans la série. Les quatre lignes ajoutées ont chacune 0 comme leur valeur de vente définie à 0 en utilisant la fonction NVL. Notez que dans cette requête, une condition WHERE a été placée pour des semaines entre 20 et 30 dans la vue en ligne pour la dimension de temps. Ceci a été introduit pour maintenir le résultat réduit. Remplir les intervalles en deux dimensions Les données N-dimensionnelles sont généralement affichées sous la forme d'une croix transversale bidimensionnelle dense de (n - 2) pages. Cela nécessite que toutes les valeurs de dimension pour les deux dimensions apparaissant dans l'onglet croisé soient remplies. Voici un autre exemple où la capacité de jointure externe partitionnée peut être utilisée pour remplir les espaces sur deux dimensions: Dans cette requête, la clause de factorisation de sous-requête WITH V1 résume les données de vente au niveau du produit, du pays et de l'année. Ce résultat est faible, mais les utilisateurs peuvent vouloir voir toutes les combinaisons pays, année pour chaque produit. Pour ce faire, nous prenons chaque partition de v1 sur la base des valeurs du produit et externe le rejoindre sur la dimension du pays d'abord. Cela nous donnera toutes les valeurs de pays pour chaque produit. Ensuite, nous prenons ce résultat et le partitionner sur les valeurs du produit et du pays puis le rejoindre sur la dimension temporelle. Cela nous donnera toutes les valeurs de temps pour chaque produit et combinaison de pays. Remplir les lacunes dans une table d'inventaire Une table d'inventaire suit généralement la quantité d'unités disponibles pour divers produits. Ce tableau est peu répandu: il ne stocke qu'une ligne pour un produit lorsqu'il ya un événement. Pour une table de vente, l'événement est une vente et, pour la table d'inventaire, l'événement est un changement de quantité disponible pour un produit. Par exemple, considérez le tableau d'inventaire suivant: Le tableau d'inventaire comprend maintenant les lignes suivantes: Pour les besoins de l'établissement de rapports, les utilisateurs peuvent souhaiter afficher ces données d'inventaire différemment. Par exemple, ils peuvent vouloir voir toutes les valeurs de temps pour chaque produit. Cela peut être accompli en utilisant une jointure externe partitionnée. En outre, pour les lignes nouvellement insérées de périodes manquantes, les utilisateurs peuvent vouloir voir les valeurs de la colonne de quantité d'unités à transférer de la période de temps existante la plus récente. Ce dernier peut être réalisé en utilisant la fonction de fenêtre analytique LASTVALUE. Voici la requête et la sortie souhaitée: La requête interne calcule une jointure externe partitionnée à l'heure dans chaque produit. La requête interne densifie les données sur la dimension temporelle (ce qui signifie que la dimension temporelle aura désormais une ligne pour chaque jour de la semaine). Cependant, la quantité de colonne de mesure aura des valeurs nulles pour les lignes nouvellement ajoutées (voir la sortie dans la quantité de colonne dans les résultats suivants. La requête externe utilise la fonction analytique LASTVALUE. L'application de cette fonction partitionne les données par produit et ordonne les données sur la Pour chaque ligne, la fonction trouve la dernière valeur non nulle dans la fenêtre en raison de l'option IGNORE NULLS, que vous pouvez utiliser à la fois avec LASTVALUE et FIRSTVALUE. La sortie suivante: calculer les valeurs de données pour combler les lacunes Les exemples de la section précédente illustrent comment utiliser la jointure externe partitionnée pour combler les lacunes dans une ou plusieurs dimensions. Toutefois, les ensembles de résultats produits par une jointure externe partitionnée ont des valeurs nulles pour les colonnes qui ne sont pas incluses dans La liste PARTITION BY. Par exemple, la requête suivante calcule les totaux mensuels pour les produits 64 Mo de carte mémoire et DVD-R Disques (ID produits 122 et 136) pour l'année 2000. Il utilise la jointure externe partitionnée pour densifier les données pour tous les mois. Pour les mois manquants, il utilise alors la fonction SQL analytique AVG pour calculer les ventes et les unités comme la moyenne des mois où le produit a été vendu. Si vous travaillez dans SQLPlus, les deux commandes suivantes enveloppent les en-têtes de colonnes pour une meilleure lisibilité des résultats: Les calculs de séries temporelles sur la densification de données densifiées ne sont pas uniquement à des fins de rapport. Il permet également certains types de calculs, en particulier les calculs de séries chronologiques. Les calculs de séries chronologiques sont plus faciles lorsque les données sont denses le long de la dimension temporelle. Les données denses ont un nombre constant de lignes pour chaque période de temps qui, à son tour, rendent simple l'utilisation de fonctions de fenêtre analytique avec des décalages physiques. Pour illustrer, prenons d'abord l'exemple de Remplissage de lacunes dans les données. Et ajoute une fonction analytique à cette requête. Dans la version améliorée suivante, nous calculons les ventes cumulatives hebdomadaires cumulées avec les ventes hebdomadaires. Les valeurs NULL que la jointure externe partitionnée insère en rendant la série temporelle dense sont traitées de la manière habituelle: la fonction SUM les traite comme 0s. Comparaison d'une période à l'autre pour un seul niveau: Exemple Comment pouvons-nous utiliser cette fonction pour comparer les valeurs entre les différentes périodes? Précisément, comment calculer une comparaison d'une année sur l'autre au niveau de la semaine La requête suivante renvoie sur la même ligne , Pour chaque produit, les ventes depuis le début de l'année pour chaque semaine de 2001 et celle de 2000. Notez que dans cet exemple, nous commençons par une clause WITH. Cela améliore la lisibilité de la requête et nous permet de nous concentrer sur la jointure externe partitionnée. Si vous travaillez dans SQLPlus, la commande suivante enveloppe les en-têtes de colonne pour une meilleure lisibilité des résultats: Dans la clause FROM de la vue en ligne densesales. Nous utilisons une jointure externe partitionnée de la vue agrégée v et de la vue temporelle t pour combler les lacunes dans les données de ventes le long de la dimension temporelle. La sortie de la jointure externe partitionnée est alors traitée par la fonction analytique SUM. OVER pour calculer les ventes hebdomadaires depuis le début de l'année (la colonne weeklyytdsales). Ainsi, la vue densesales calcule les données sur les ventes de l'année à ce jour pour chaque semaine, y compris celles qui sont manquantes dans la vue agrégée. Le yearlineearsearsales de vue en ligne calcule ensuite les ventes year-to-date hebdomadaires d'année en utilisant la fonction de LAG. La fonction LAG intitulée weeklyytdsalesprioryear spécifie une clause PARTITION BY associant des lignes pour la même semaine des années 2000 et 2001 en une seule partition. Nous passons alors un décalage de 1 à la fonction LAG pour obtenir les ventes hebdomadaires à ce jour pour l'année précédente. Le bloc de requête le plus externe sélectionne les données de yearoveryearsales avec la condition yr 2001. Ainsi, la requête renvoie, pour chaque produit, Des ventes cumulatives depuis le début de l'exercice pour les années indiquées 2001 et 2000. Comparaison période-période pour plusieurs niveaux de temps: Exemple Si l'exemple précédent nous montre un moyen de créer des comparaisons pour un seul niveau de temps, il serait encore plus Utile pour gérer plusieurs niveaux de temps dans une seule requête. Par exemple, nous pourrions comparer les ventes par rapport à la période précédente aux niveaux de l'année, du trimestre, du mois et du jour. Comment créer une requête qui effectue une comparaison d'une année à l'autre des ventes de l'année à l'autre pour tous les niveaux de notre hiérarchie de temps Nous prendrons plusieurs mesures pour effectuer cette tâche. L'objectif est une seule requête avec comparaisons au jour, semaine, mois, trimestre et année. Les étapes sont les suivantes: Nous allons créer une vue appelée cubeprodtime. Qui détient un cube hiérarchique de ventes agrégées à travers les temps et les produits. Ensuite, nous créerons une vue de la dimension temporelle à utiliser comme bord du cube. L'arête de temps, qui contient un ensemble complet de dates, sera partitionnée externe jointe aux données éparses dans la vue cubeprodtime. Enfin, pour une performance maximale, nous allons créer une vue matérialisée, mvprodtime. Construit en utilisant la même définition que cubeprodtime. Pour plus d'informations sur les cubes hiérarchiques, reportez-vous au Chapitre 21, SQL for Aggregation dans Data Warehouses. La vue matérialisée est définie à l'étape 1 de la section suivante. Étape 1 Création de la vue hiérarchique du cube La vue matérialisée illustrée ci-dessous peut déjà exister dans votre système sinon, créez-la maintenant. Si vous devez le générer, notez que nous limitons la requête à deux produits seulement pour réduire le temps de traitement: Étant donné que cette vue est limitée à deux produits, elle renvoie un peu plus de 2200 lignes. Notez que la colonne HierarchicalTime contient des représentations de temps de chaîne de tous les niveaux de la hiérarchie temporelle. L'expression CASE utilisée pour la colonne HierarchicalTime ajoute un marqueur (0, 1.) à chaque chaîne de date pour indiquer le niveau de temps de la valeur. A 0 représente le niveau de l'année, 1 est le trimestre, 2 le mois et 3 le jour. Notez que la clause GROUP BY est un ROLLUP concaténé qui spécifie la hiérarchie de cumul des dimensions de temps et de produit. La clause GROUP BY est ce qui détermine le contenu hiérarchique du cube. Étape 2 Créez la vue edgetime, qui est un ensemble complet de valeurs de date edgetime est la source pour remplir les intervalles de temps dans le cube hiérarchique à l'aide d'une jointure externe partitionnée. La colonne HierarchicalTime dans edgetime sera utilisée dans une jointure partitionnée avec la colonne HierarchicalTime dans la vue cubeprodtime. L'instruction suivante définit edgetime: Étape 3 Créez la vue matérialisée mvprodtime pour supporter des performances plus rapides La définition de vue matérialisée est un duplicata de la vue cubeprodtime définie précédemment. Parce qu'il s'agit d'une requête en double, les références à cubeprodtime seront réécrites pour utiliser la vue matérialisée mvprodtime. Ce qui suit matérialisé peut déjà exister dans votre système sinon, le créer maintenant. Si vous devez le générer, notez que nous limitons la requête à deux produits seulement pour réduire le temps de traitement. Étape 4 Créer la requête de comparaison Nous avons maintenant préparé le terrain pour notre requête de comparaison. Nous pouvons obtenir des calculs de comparaison période-période à tous les niveaux de temps. Il faut appliquer des fonctions analytiques à un cube hiérarchique avec des données denses le long de la dimension temporelle. Certains des calculs que nous pouvons réaliser pour chaque niveau de temps sont: Somme des ventes pour la période antérieure à tous les niveaux de temps. Variation des ventes par rapport à la période précédente. Somme des ventes de la même période il ya un an à tous les niveaux de temps. Variation des ventes sur la même période l'an dernier. L'exemple suivant effectue les quatre de ces calculs. Il utilise une jointure externe partitionnée des vues cubeprodtime et edgetime pour créer une vue en ligne des données denses appelées densecubeprodtime. La requête utilise alors la fonction LAG de la même manière que l'exemple précédent de niveau unique. La clause WHERE externe spécifie l'heure à trois niveaux: les jours d'août 2001, le mois entier et tout le troisième trimestre de 2001. Notez que les deux dernières lignes des résultats contiennent le niveau du mois et les agrégations de niveau trimestre. Notez que pour faire Les résultats plus faciles à lire si vous utilisez SQLPlus, les en-têtes de colonne doivent être ajustés avec les commandes suivantes. Les commandes vont plier les en-têtes de colonne pour réduire la longueur de ligne: Voici la requête comparant les ventes actuelles aux ventes précédentes et précédentes: La première fonction LAG (périodeprimeprivile) partitionne les données sur gidp. chat. Subcat. Prod. Gidt et ordonne les rangées sur toutes les colonnes de dimension temporelle. Il obtient la valeur de vente de la période précédente en passant un décalage de 1. La deuxième fonction LAG (salessameperiodprioryear) partitionne les données sur les colonnes supplémentaires qtrnum. Monnum. Et daynum et ordonne-le sur yr afin que, avec un décalage de 1, il peut calculer l'année il ya des ventes pour la même période. La clause SELECT la plus à l'extérieur calcule les écarts. Création d'un membre personnalisé dans une dimension: exemple Dans de nombreuses tâches analytiques SQL, il est utile de définir des membres personnalisés dans une dimension. Par exemple, vous pouvez définir une période de temps spécialisée pour les analyses. Vous pouvez utiliser une jointure externe partitionnée pour ajouter temporairement un membre à une dimension. Notez que la nouvelle clause SQL MODEL convient pour créer des scénarios plus complexes impliquant de nouveaux membres dans les dimensions. Pour plus d'informations sur ce sujet, reportez-vous au Chapitre 23, SQL for Modeling. En tant qu'exemple d'une tâche, que faire si nous voulons définir un nouveau membre pour notre dimension temporelle Nous voulons créer un 13e membre du niveau Mois dans notre dimension temporelle. Ce 13e mois est défini comme la somme des ventes de chaque produit au cours du premier mois de chaque trimestre de 2001. La solution comporte deux étapes. Notez que nous allons construire cette solution en utilisant les vues et les tables créées dans l'exemple précédent. Deux étapes sont nécessaires. Tout d'abord, créez une vue avec le nouveau membre ajouté à la dimension appropriée. La vue utilise une opération UNION ALL pour ajouter le nouveau membre. Pour interroger à l'aide du membre personnalisé, utilisez une expression CASE et une jointure externe partitionnée. Notre nouveau membre pour la dimension de temps est créé avec la vue suivante: Dans cette instruction, le timec de vue est défini en exécutant UNION ALL de la vue edgetime (définie dans l'exemple précédent) et le 13ème mois défini par l'utilisateur. La valeur gidt de 8 a été choisie pour différencier le membre personnalisé des membres standard. UNION ALL spécifie les attributs d'un membre de 13 mois en effectuant un SELECT à partir de la table DUAL. Notez que l'id de regroupement, colonne gidt. Est défini sur 8 et le numéro de quart est défini sur 5. Ensuite, la deuxième étape consiste à utiliser une vue en-ligne de la requête pour effectuer une jointure externe partitionnée de cubeprodtime avec timec. Cette étape crée des données de ventes pour le 13ème mois à chaque niveau d'agrégation de produit. Dans la requête principale, la fonction analytique SUM est utilisée avec une expression CASE pour calculer le 13ème mois, qui est défini comme la somme des ventes des premiers mois de chaque trimestre. La fonction SUM utilise un CASE pour limiter les données aux mois 1, 4, 7 et 10 au cours de chaque année. En raison de l'ensemble de données minuscules, avec seulement 2 produits, les valeurs cumulatives des résultats sont nécessairement des répétitions des agrégations de niveau inférieur. Pour obtenir un ensemble plus réaliste de valeurs cumulatives, vous pouvez inclure plus de produits dans les sous-catégories Game Console et Y Box Games dans la vue matérialisée sous-jacente. Fonctionnalités diverses d'analyse et de génération de rapports Cette section illustre les fonctionnalités analytiques supplémentaires suivantes: Fonction WIDTHBUCKET Pour une expression donnée, la fonction WIDTHBUCKET renvoie le nombre de seaux que le résultat de cette expression sera assigné après l'évaluation. 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 (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 22-24. Dans la table des clients. La colonne custcreditlimit contient des valeurs entre 1500 et 15000, et nous pouvons affecter 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 22-3 pour une illustration graphique de la façon dont les seaux sont affectés. Figure 22-3 Bucket Assignments You can specify the bounds in the reverse order, for example, WIDTHBUCKET ( custcreditlimit. 20000. 0. 4 ). When the bounds are reversed, the buckets will be open-closed intervals. In this example, bucket number 1 is ( 15000,20000 , bucket number 2 is ( 10000,15000 , and bucket number 4, is ( 0 ,5000 . The overflow bucket will be numbered 0 ( 20000. infinity ), and the underflow bucket will be numbered 5 (- infinity. 0 . It is an error if the bucket count parameter is 0 or negative. Example 22-24 WIDTHBUCKET The followin g query shows the bucket numbers for the credit limits in the customers table for both cases where the boundaries are specified in regular or reverse order. We use a range of 0 to 20,000. Linear Algebra Linear algebra is a branch of mathematics with a wide range of practical applications. Many areas have tasks that can be expressed using linear algebra, and here are some examples from several fields: statistics (multiple linear regression and principle components analysis), data mining (clustering and classification), bioinformatics (analysis of microarray data), operations research (supply chain and other optimization problems), econometrics (analysis of consumer demand data), and finance (asset allocation problems). Various libraries for linear algebra are freely available for anyone to use. Oracles UTLNLA package exposes matrix PLSQL data types and wrapper PLSQL subprograms for two of the most popular and robust of these libraries, BLAS and LAPACK. Linear algebra depends on matrix manipulation. Performing matrix manipulation in PLSQL in the past required inventing a matrix representation based on PLSQLs native data types and then writing matrix manipulation routines from scratch. This required substantial programming effort and the performance of the resulting implementation was limited. If developers chose to send data to external packages for processing rather than create their own routines, data transfer back and forth could be time consuming. Using the UTLNLA package lets data stay within Oracle, removes the programming effort, and delivers a fast implementation. Example 22-25 Linear Algebra Here is an example of how Oracles linear algebra support could be used for business analysis. It invokes a multiple linear regression application built using the UTLNLA package. The multiple regression application is implemented in an object called OLSRegression. Note that sample files for the OLS Regression object can be found in ORACLEHOMEplsqldemo . Consider the scenario of a retailer analyzing the effectiveness of its marketing program. Each of its stores allocates its marketing budget over the following possible programs: media advertisements ( media ), promotions ( promo ), discount coupons ( disct ), and direct mailers ( dmail ). The regression analysis builds a linear relationship between the amount of sales that an average store has in a given year ( sales ) and the spending on the four components of the marketing program. Suppose that the marketing data is stored in the following table: Then you can build the following sales-marketing linear model using coefficients: This model can be implemented as the following view, which refers to the OLS regression object: Using this view, a marketing program manager can perform an analysis such as Is this sales-marketing model reasonable for year 2004 data That is, is the multiple-correlation greater than some acceptable value, say, 0.9 The SQL for such a query might be as follows: You could also solve questions such as What is the expected base-line sales revenue of a store without any marketing programs in 2003 or Which component of the marketing program was the most effective in 2004 That is, a dollar increase in which program produced the greatest expected increase in sales See Oracle Database PLSQL Packages and Types Reference for further information regarding the use of the UTLNLA package and linear algebra. 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 CASE statements is: Simple CASE expressions test if the expr value equals the comparisonexpr . The syntax for searched CASE statements is: You can use any kind of condition in a searched CASE expression, not just an equality test. You can specify only 65,535 arguments and each WHEN. THEN pair counts as two arguments. To avoid exceeding this limit, you can nest CASE expressions so that the returnexpr itself is a CASE expression. Example 22-26 CASE 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 might choose to write this query as follows: Note that this runs against the hr sample schema. 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. Example 22-27 CASE for Aggregating Independent Subsets Using CASE inside aggregate functions is a convenient way to perform aggregates on multiple subsets of data when a plain GROUP BY will not suffice. For instance, the preceding example could have included multiple AVG columns in its SELECT list, each with its own CASE expression. We might have had a query find the average salary for all employees in the salary ranges 0-2000 and 2000-5000. It would look like: Although this query places the aggregates of independent subsets data into separate columns, by adding a CASE expression to the GROUP BY clause we can display the aggregates as the rows of a single column. The next section shows the flexibility of this approach with two approaches to creating histograms with CASE . Creating Histograms 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 22-28 Histogram Example 1 Example 22-29 Histogram Example 2 Frequent Itemsets Instead of counting how often a given event occurs (for example, how often someone has purchased milk at the grocery), you may find it useful to count how often multiple events occur together (for example, how often someone has purchased both milk and cereal together at the grocery store). You can count these multiple events using what is called a frequent itemset, which is, as the name implies, a set of items. Some examples of itemsets could be all of the products that a given customer purchased in a single trip to the grocery store (commonly called a market basket), the web pages that a user accessed in a single session, or the financial services that a given customer utilizes. The practical motivation for using a frequent itemset is to find those itemsets that occur most often. If you analyze a grocery stores point-of-sale data, you might, for example, discover that milk and bananas are the most commonly bought pair of items. Frequent itemsets have thus been used in business intelligence environments for many years, with the most common one being for market basket analysis in the retail industry. Frequent itemset calculations are integrated with the database, operating on top of relational tables and accessed through SQL. This integration provides the following key benefits: Applications that previously relied on frequent itemset operations now benefit from significantly improved performance as well as simpler implementation. SQL-based applications that did not previously use frequent itemsets can now be easily extended to take advantage of this functionality. Frequent itemsets analysis is performed with the PLSQL package DBMSFREQUENTITEMSETS. See Oracle Database PLSQL Packages and Types Reference for more information. In addition, there is an example of frequent itemset usage in Frequent itemsets . Scripting on this page enhances content navigation, but does not change the content in any way. Calculate Exchange Online requirements for bandwidth Bandwidth planning for Exchange Online migrations can be a dry subject, but its essential for a successful move. It isnt as simple as plugging the values into the Microsoft calculators. Admins need to use values and other data to understand where the weak points are and remediate. Its important to look at end users across the organization and perform calculations using Microsoft tools to understand the impact of these Exchange Online requirements. Admins can then use the results to understand if theyll need to make upgrades. Well focus on how to perform this planning and understand the ongoing requirements for using Office 365. Before we begin, its essential to understand how clients access Office 365. Typically, an Outlook client will connect from its local site directly to the Internet, or, if its on a wide area network (WAN), at the closest site with Internet breakout. The client will perform a DNS lookup to obtain the nearest Office 365 point of presence and connect over HTTPS to the service. This typically provides reasonably low latency when accessing the service. During a migration, its typical to switch on features such as Cached Mode and upgrade clients in advance. The Offline Cache stays intact after a hybrid migration, so this removes the need for every client to re-download the mailbox en masse. Collect information to use as input Before calculating the bandwidth necessary for these Exchange Online requirements, we need good input data. Collect the following information about each site with end users, each data center with Exchange and each site with an Internet breakout that end users will use (or used to migrate mailboxes): List of sites with end users, Exchange and Internet breakouts List of end users at each site Define the route to the Internet from each site For each site that traverses the WAN to another site with Internet breakout, define the WAN link speed For each site with an Internet breakout, define the Internet link and WAN link speeds Determine the average mailbox profile for end users either as a whole or based on a site. Admins can use one of Microsofts tools to perform this analysis, such as the Generate Message Profile script for Exchange 2010 and Exchange 2013 and Collate the average and total mailbox sizes per site. The goal for collecting the above data should be to have a picture of the end users, mailboxes, Exchange sites and enough information to determine the bandwidth likely to be required. Calculate Exchange workloads Admins can use the raw data collected with Microsofts Exchange Client Network Bandwidth Calculator to understand the per-site requirements for end users. On the Input sheet, update the Exchange Service field to state Office 365 if required, admins can then use their Exchange mailbox message profile data to update the User Profile information (Figure 1). On the Client Mix sheet, enter each site, accompanied by the sites user profile and the number of end users. This should give you the per-site bandwidth requirements (Figure 2). Well use the results in the next step. Collating per-site user requirements For the per-site end user requirements, well collate the results from the previous step along with other information about the site into a new spreadsheet. Well typically expect this to have the following fields. Country: To help group sites together in larger organizations. Site Name: The physical site name as used in the Exchange calculator. Number of Users: The same number of end users used in the Exchange calculator. Exchange Download Bandwidth Required: The Exchange-to-Client bandwidth required in the calculator. Exchange Upload Bandwidth Required: The Client-to-Exchange bandwidth required in the calculator. Site Type (Internet Breakout Downstream WAN): If the clients access the Internet directly at this site, choose Internet Breakout. If the Internet is accessed via another site or data center, choose Downstream WAN . Total Download Bandwidth: The total download bandwidth available. Total Upload Bandwidth: The total upload bandwidth available. Average Download Bandwidth Utilization: The average download utilization of the relevant link. Average Upload Bandwidth Utilization: The average upload utilization of the relevant link. Upstream Internet Breakout Site Name: If this is a downstream WAN site, then record the site this connects to the Internet through. After adding the appropriate fields and then entering data, the results should appear (Figure 3). In the diagram, weve captured the data from the Exchange Client Network Bandwidth Calculator across the first fields and then entered additional data collected above. This immediately provides us with important information about our Exchange Online requirements, such as whether downstream sites connecting via the WAN have enough bandwidth available and whether sites with their own Internet breakout have enough bandwidth. Collate per-Internet breakout requirements What the per-site user requirements dont tell us is whether sites that have multiple sites connecting through to the Internet can cope. Well need to collate the information on a second worksheet to make this determination. This summary spreadsheet will have the following fields and contain only Internet breakout sites. Pays. To help group sites together in larger organizations. Site Name. The physical site name used in the previous spreadsheets. Combined Number of Users. The total number of end users, including end users on this site and those who use this site as Internet breakout. Combined Exchange Download Bandwidth Required. The combined Exchange download bandwidth required across this site and those connecting through it. Combined Exchange Upload Bandwidth Required. The combined Exchange upload bandwidth required across this site and those connecting through it. Total Internet Download Bandwidth. The total download bandwidth available. Total Internet Upload Bandwidth. The total up load bandwidth available. Average Internet Download Bandwidth Utilization. The average download utilization of the relevant link. Average Internet Upload Bandwidth Utilization. The average upload utilization of the relevant link. Downstream Capacity Available Checking to see if the combined Exchange download bandwidth and average current utilization is less than the total available download bandwidth. Upload Capacity Available This should include the same as above, but it checks if enough upload capacity is available. After collating the data, were able to easily identify the Internet breakout sites that will have capacity problems in our example (Figure 4). Our next step as part of our preparation will be to upgrade both the Internet links that fall short and in downstream WAN sites. About the author: Steve Goodman is an Exchange MVP and works as a technical architect for one of the U. K.s leading Microsoft Gold partners. Goodman has worked extensively with Microsoft Exchange since version 5.5 and with Office 365 since its origins in Exchange Labs and LiveEDU. Next Steps Limit bandwidth use during Outlook offline address book syncs