Ce cours permet de connaitre et de maitriser les fonctionnalités décisionnelles de SQL sous Oracle. Les groupements avancés des données (ROLLUP, CUBE), les fonctions analytiques, la clause MODEL ainsi que la nouvelle clause MATCH_RECOGNIZE de 12c constituent les piliers du cours.
Ce cours s’adresse à toute personne désirant développer ses compétences en SQL sous Oracle dans le domaine du décisionnel. De bonnes connaissances de SQL dans l’environnement Oracle sont indispensables.
Plan détaillé
Introduction et rappels
- Introduction au langage SQL et les différentes clauses d’une instruction SELECT.
- Modélisation, les dimensions, les mesures, et la notion de CUBE.
- Groupement de données et fonctions d’agrégats.
- Fonctions LAST et FIRST.
Travaux pratiques : GROUP BY, HAVING, Fonctions LAST et FIRST.
Groupement avancé des données
- Utilisation de GROUPING SETS.
- Utilisation de ROLLUP.
- Utilisation de CUBE.
- Fonctions de groupement : grouping(), grouping_id() et goup_id().
Travaux pratiques : les travaux pratiques concernent tous les points énumérés.
Fonctions analytiques
- Concepts de base : partitionnement, tri, fenêtre de cacule par défaut.
- Spécification explicite des fenêtres de calcul : par nombre de lignes (ROWS), par intervalle (RANGE).
- Fonctions analytiques de classement : rank(), dense_rank(), percent_rank(), row_number(), ntile().
- Fonctions analytiques statistiques : avg(), sum(), variance(), var_samp(), var_pop(), stddev(), stddev_pop(), stddev_samp(), cume_dist(), ratio_to_report(), covar_pop(), covar_samp(), corr().
- Fonctions analytiques accédant à d’autres lignes : lead(), lag(), first, last, first_value(), last_value(), nth_value().
Travaux pratiques : les travaux pratiques concernent tous les points énumérés.
Objets et mise à jour
- ETL (Extraction, Transformation, Loading).
- Tables externes.
- Vues matérialisées.
- Dimensions et hiérarchies.
- Insertions multitables.
- Utilisation de DBMS_ERRLOG.
Travaux pratiques : tables externes, vues matérialisées, insertions multitables, utilisation de DBMS_ERRLOG.
Analyse des données
- Utilisation de la clause MODEL.
- Transformation d’un résultat en tableaux. Définition des mesures et des dimensions.
- Filtrage des cellules à afficher. La clause RETURN
- Utilisation des règles de la clause MODEL. Ordre d’application des règles.
- Itération de l’application des règles et l’indice iteration_number.
- Traitement des cellules absentes.
- Utilisation de la boucle FOR avec la clause MODEL.
- Utilisation de MATCH_RECOGNIZE (12c).
- La recherche de motifs (patterns) dans le résultat.
- Partitionnement et tri.
- Choix des lignes à afficher (… PER MATCH)
- Choix des valeurs à afficher (la clause MEASURES)
- Définition des motifs à rechercher. Les clauses DEFINE et PATTERN.
- AFTER MATCH et la suite après avoir trouvé un motif.
Travaux pratiques: les travaux pratiques concernent l’ensemble des points énumérés.
