Ap

Requêtes analytiques

Documentation

• Livre: Oracle Database Data Warehousing Guide, chapitre 18 : SQL for Analysis and Reporting ; voir, en particulier :

  • les fonctions de classement Ranking,
  • les fonctions de fenétrage Windowing Aggregate avec aggrégation de groupes non disjoints.
• Le schéma et la spécification 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 ou 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.

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.
• 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 est-il correct? Justifier. Fixer le format d'affichage du rang avec la commande:

  column rang_pourcent format 9.99

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.

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.
• Pour chaque jour, quel est le chiffre d'affaire des 30 derniers jours ? Voir a6-res2.
• (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