Advertisement
Guest User

Untitled

a guest
Oct 18th, 2019
293
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 6.24 KB | None | 0 0
  1. -- On se propose d'analyser le nombre d'exercices produits en fonction la localité de l'etablissement,
  2. -- le niveau, l'année de naissance de l'enseignant et la notion abordée.
  3.  
  4. -- Exercice 1
  5.  
  6. -- Mesure :
  7. --      Nombre d'exercice produit
  8. -- Dimensions :
  9. --      Localité de l'établissement
  10. --      Niveau
  11. --      Année de naissance de l'enseignant
  12. --      Notion abordée
  13.  
  14. -- Exercice 2
  15.  
  16. -- GROUP BY Normal
  17. SELECT et.localite, ex.niveau, p.annee_naissance, n.notion, COUNT(ex.niveau)
  18. FROM exercice ex, prof p, etablissement et, notion n
  19. WHERE ex.proprietaire = p.idp
  20. AND p.rne = et.rne
  21. AND ex.idex = n.idexo
  22. GROUP BY et.localite, ex.niveau, p.annee_naissance, n.notion
  23.  
  24. -- GROUP BY ROLLUP
  25. -- reduit les dimensions
  26. SELECT et.localite, ex.niveau, p.annee_naissance, n.notion, COUNT(ex.niveau)
  27. FROM exercice ex, prof p, etablissement et, notion n
  28. WHERE ex.proprietaire = p.idp
  29. AND p.rne = et.rne
  30. AND ex.idex = n.idexo
  31. GROUP BY ROLLUP (et.localite, ex.niveau, p.annee_naissance, n.notion)
  32.  
  33. -- GROUP BY CUBE
  34. -- Donne toute les combinaisons possibles
  35. SELECT et.localite, ex.niveau, p.annee_naissance, n.notion, COUNT(ex.niveau)
  36. FROM exercice ex, prof p, etablissement et, notion n
  37. WHERE ex.proprietaire = p.idp
  38. AND p.rne = et.rne
  39. AND ex.idex = n.idexo
  40. GROUP BY CUBE (et.localite, ex.niveau, p.annee_naissance, n.notion)
  41.  
  42. -- GROUP BY GROUPING SETS
  43. -- Donne la mesure pour une unique dimension
  44. SELECT et.localite, ex.niveau, p.annee_naissance, n.notion, COUNT(ex.niveau)
  45. FROM exercice ex, prof p, etablissement et, notion n
  46. WHERE ex.proprietaire = p.idp
  47. AND p.rne = et.rne
  48. AND ex.idex = n.idexo
  49. GROUP BY GROUPING SETS (et.localite, ex.niveau, p.annee_naissance, n.notion)
  50.  
  51. -- DECODE
  52. SELECT et.localite, ex.niveau, p.annee_naissance, DECODE(n.notion, NULL , 'TOUTE NOTION', n.notion) , COUNT(ex.niveau)
  53. FROM exercice ex, prof p, etablissement et, notion n
  54. WHERE ex.proprietaire = p.idp
  55. AND p.rne = et.rne
  56. AND ex.idex = n.idexo
  57. GROUP BY CUBE (et.localite, ex.niveau, p.annee_naissance, n.notion)
  58.  
  59. -- 1. Quelle est la densité de votre "cube" ?
  60.     -- Nombre rendu par le GROUP BY : 8
  61.     -- Nombre de localité : 3
  62.     -- Nombre de niveau : 4
  63.     -- Nombre d'année de n : 4
  64.     -- Nombre de notion : 3
  65.     -- densité = 8 / (3*4*4*3) = 0,05555
  66.  
  67.  
  68. -- 2.Fonctions d'analyse :
  69. -- (a) Utilisez la fonction RANK pour ordonner les cellules du cube
  70.     SELECT et.localite, ex.niveau, p.annee_naissance, DECODE(n.notion, NULL , 'TOUTE NOTION', n.notion) , RANK() OVER (ORDER BY localite)
  71.     FROM exercice ex, prof p, etablissement et, notion n
  72.     WHERE ex.proprietaire = p.idp
  73.     AND p.rne = et.rne
  74.     AND ex.idex = n.idexo
  75.     GROUP BY CUBE (et.localite, ex.niveau, p.annee_naissance, n.notion)
  76.  
  77. -- (b)  Utilisez la fonctions RATIO_TO_REPORT pour donner, pour chaque élève,
  78. --      la proportion d'exercices qu'il a passés par rapport à la totalité des exercices pour son niveau.
  79. -- Dans le OVERer il faudrait mettre le nombre totale d'exercice pour le niveau de l'eleve.
  80. SELECT el.nom, el.niveau, COUNT(*), RATIO_TO_REPORT(COUNT(*)) OVER(PARTITION BY (el.niveau))
  81. FROM eleve el, exercice ex, passage pa, niveau ni, contenu co
  82. WHERE el.IDEL = pa.IDEL
  83. AND pa.IDD = co.IDD
  84. AND ex.IDEX = co.IDEX
  85. AND ex.NIVEAU = ni.NIVEAU
  86. AND el.NIVEAU = ex.NIVEAU
  87. GROUP BY (el.nom, el.niveau)
  88.  
  89. -- (c) Affichez le nombre d’exercices niveau par niveau en indiquant également le nombre d'exercices cumulés à chaque ligne
  90. -- (sum(count(x)) pour vous aider).
  91.  
  92. --SELECT el.nom, el.niveau, count(*) as "Nombre d'exercice passé", SUM(count(*)) over (partition by el.niveau) as "nb dexercice passé cumulé"
  93. SELECT exercice.niveau, COUNT(*) AS "Nombre d'exercice", SUM(COUNT(*)) OVER (partition BY exercice.niveau) AS "nb dexercice cumulé"
  94. FROM exercice
  95. GROUP BY (niveau)
  96.  
  97. --  VI Oracle permet de créer des vues matérialisées :
  98.  
  99. -- 1. A quoi cela peut-il servir ?
  100. -- De gros volumes de données modélisés sous la forme d’un schéma en  étoile ou flocon
  101. -- Cela permet de conserver en mémoire le résultat d'une requête, pour ne pas avoir à la refaire et pouvoir faire des opérations dessus
  102.  
  103. -- 2. Créez une vue matérialisée pour stocker les informations nécessaires à notre analyse
  104. CREATE MATERIALIZED VIEW MA_VUE
  105. AS
  106. SELECT el.nom, el.niveau, COUNT(*)
  107. FROM eleve el, exercice ex, passage pa, niveau ni, contenu co
  108. WHERE el.IDEL = pa.IDEL
  109. AND pa.IDD = co.IDD
  110. AND ex.IDEX = co.IDEX
  111. AND ex.NIVEAU = ni.NIVEAU
  112. AND el.NIVEAU = ex.NIVEAU
  113. GROUP BY (el.nom, el.niveau)
  114.  
  115. -- 3. Vous  pouvez visualiser si les  vues  créées ont  bien  été  créée  en  consultant  la table  user_mviews.
  116. -- En  particulier,  l'attribut  STALENESS  permet  de  savoir si la vue est à jour ou non.
  117. SELECT STALENESS
  118. FROM user_mviews
  119.  
  120. -- Cette requette renvoie "FRESH"
  121. -- Cela signifie que la vue materialisée est à jour.
  122.  
  123.  
  124. -- 4. Comment  gérer  les  rafraîchissements  pour  qu'ils  s'exécutent  une  fois  par jour ?
  125. -- Pour que le rafraichissement s'execute tous les jour, il faut executer la commande tous les jours.
  126.  
  127. -- 5. Insérez un exercicedans la table EXERCICE et réafficher le contenu de la vue matérialisée.
  128. INSERT INTO EXERCICE VALUES(12, 'contenu', '3eme', 2, '08/02/2019')
  129.  
  130.  
  131. -- 6. La vue est-elle à jour ? Vous pouvez le confirmer ou l'infirmer à l'aide d'une requête du type
  132. SELECT STALENESS
  133. FROM user_mviews
  134.  
  135. -- L'etat est à "NEEDS_COMPILE"
  136. -- Cela signifie que la vue a besoind d'être refresh
  137.  
  138. -- 7. Forcez le rafraîchissement à l'aide de dbms_mview.refresh  
  139.  
  140. -- 8. Les modifications ont-elles été prises en compte ? Affichez le contenu de la vue et les méta-données stockées par Oracle (staleness dans user_mviews).
  141. -- Oui
  142.  
  143. -- VII.Slow Changing Dimensions
  144. -- 1. Rappelez ce que signifie “Slow Changing Dimensions”
  145. -- Cela veut dire qu'une dimension peut subir des modifications. On peut alors accéder à l'historique des modifications.
  146.  
  147. -- 2. Donnez un exemple dans le cadre des données bibliothèque
  148. -- Si on change le numéro de telephone d'un abonné on aura accès à son ancien numéro de telephone
  149.  
  150. -- 3. Quelles  sont  les  différentes  façons  de  gérer  ce  phénomène  ?
  151. -- Quels  seraient les biais possibles sur les données bibliothèque selon le choix de gestion ?
  152.  
  153. -- VIII.ETL
  154.  
  155. -- Qu'est-ce qu'un ETL ?
  156. -- Extract-transform-load
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement