RecentChanges
|
FindPage
|
LikePages
|
BackLinks
View Source:
Ap
Note:
This page has been locked and cannot be edited.
!!!Requêtes analytiques !!Documentation * Livre: Oracle Database Data Warehousing Guide, chapitre 18 : [SQL for Analysis and Reporting | https://docs.oracle.com/database/121/DWHSG/analysis.htm#DWHSG-GUID-20EFBF1E-F79D-4E4A-906C-6E496EECA684] ; voir, en particulier : ** les fonctions de classement [Ranking | https://docs.oracle.com/database/121/DWHSG/analysis.htm#DWHSG8663], ** les fonctions de fenétrage [Windowing Aggregate |https://docs.oracle.com/database/121/DWHSG/analysis.htm#DWHSG0202] avec aggrégation de groupes non disjoints. * Le [schéma | http://www-master.ufr-info-p6.jussieu.fr/2005/IMG/naacke/bdwa/bdwa2006/extra/tme/tpch-schema_entites_associations.pdf] et la [spécification | http://www-master.ufr-info-p6.jussieu.fr/2005/IMG/naacke/bdwa/bdwa2006/extra/tme/tpch-spec-2.3.0.pdf] de TPC-H. * Voir les questions fréquentes ci-dessous !!Exercice 1 Ecrire les requêtes suivantes, exprimées sur le schéma TPC-H !A1: Le top 10 des clients ayant dépensé le plus ([a1.sql | http://www-poleia.lip6.fr/~naacke/ens/phpwiki/wiki/extra/a1.sql] ou [a1.txt | http://www-poleia.lip6.fr/~naacke/ens/phpwiki/wiki/extra/a1.txt]). * Afficher la liste des clients avec le montant total de leurs commandes. Pour chaque client, donner son n° et le montant total de ses commandes. Trier le résultat par ordre décroissant du montant. * Afficher seulement les __10__ premiers clients. * Compléter la requête précédente pour afficher le rang de chaque client. Voir [a1-res | http://www-poleia.lip6.fr/~naacke/ens/phpwiki/wiki/extra/a1-res2.txt]. !A3: Le top 5 des pays avec le plus grand nombre de clients * Afficher la liste des pays, référencés dans ''Nation'', avec leur nombre de clients. Donner le n° du pays, son nom et le nb de clients. Classer le résultat par ordre décroissant du nombre de clients. Y a-t-il des pays ex-aequo ? * Afficher seulement les tuples du résultat dont le rang est inférieur ou égal à 5. Est-ce toujours exactement les 5 premiers tuples du résultat ? Quelle est la différence entre les fonctions rank() et dense_rank() ? Voir [a3-res | http://www-poleia.lip6.fr/~naacke/ens/phpwiki/wiki/extra/a3-res2.txt]. * Expliquer brièvement comment traiter cette requête sans utiliser les fonctions analytiques prédéfinies du SGBD. !A4: Le top 20% des pays avec le plus grand nombre de clients. Afficher seulement les pays (référencés dans Nation) classés parmi les 20% meilleurs. Le résultat [a4-res | http://www-poleia.lip6.fr/~naacke/ens/phpwiki/wiki/extra/a4-res2.txt] est-il correct? Justifier. Fixer le format d'affichage du rang avec la commande: <verbatim> column rang_pourcent format 9.99 </verbatim> La formule calculant le rang relatif (entre 0 et 1 inclus) du tuple ''t'' parmi ''N'' tuples du résultat est : * rang_relatif(''t'') = (rang(t) - 1) / ( N - 1) Pourquoi le dénominateur est-il (N - 1) au lieu de N ? !A5: Classement national des produits vendus en plus grande quantité. Pour chaque pays (référencé dans Nation), donner le classement national des produits les plus achetés par des clients de ce pays. Pour réduire le résultat, on ne veut afficher que les produits dont la quantité achetée (pour un pays et un produit) est supérieure à 180. Afficher les attributs nom du pays, numéro du produit, quantité_achetée et rang. Voir [a5-res | http://www-poleia.lip6.fr/~naacke/ens/phpwiki/wiki/extra/a5-res.txt]. !A6: Fenêtre temporelle glissante Tenir compte seulement des commandes effectuées après le 01/04/1998. * Pour chaque jour donner le prix moyen des commandes effectuées les 90 jours précédents. La moyenne est calculée sur l'ensemble des commandes enregistrées pendant les 90 jours précédents. Voir [a6-res1 | http://www-poleia.lip6.fr/~naacke/ens/phpwiki/wiki/extra/a6-res1.txt]. * Pour chaque jour, quel est le chiffre d'affaire des 30 derniers jours ? Voir [a6-res2 | http://www-poleia.lip6.fr/~naacke/ens/phpwiki/wiki/extra/a6-res2.txt]. * (facultatif) Pour chaque mois, quel est le prix moyen des commandes du dernier trimestre ? Remarque les extraits de résultat a6-res1 et a6-res2 donnés ci-dessus se limitent aux dates comprises entre le 1er juin et le 1er août 1998. !!Questions fréquentes * Comment afficher les ''N'' premiers tuples du résultat de la requête ''R1'' ? ** ajouter une condition de sélection sur l'attribut implicite nommé **rownum** : ** with T as (''requête R1'') select * from T where rownum <= ''N''; * Quelle est la syntaxe de **with** ? ** le with permet de nommer une série de requêtes. Deux requêtes nommées sont séparées par une virgule. ** with T1 as (select * from Nation) **,** T2 as (select * from T1) select * from T2; * Comment obtenir le numéro de mois d'une date ? Soit ''d'' un attribut de type date : ** to_char(d, 'MM') ** extract( month from ''d'') ---- LesTme, [TpcH], [Accueil]