1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
|
CREATE OR REPLACE FORCE VIEW "AVI_EVALUATION" ("DEPARTEMENT_COURS", "RESOURCE_ID", "ENSEIGNANT", "TYPE_RESSOURCE", "PERIODE", "PROMOTION", "CODE_COURS", "CODE_OFFRE_DE_COURS", "LIBELLE_DU_COURS", "NB_ELEVES", "NB_EVAL", "QUALITE_PEDAGO", "APPRECIATION_GLOBALE", "CONTENU_COURS", "QUALITE_OUTILS", "VALEUR", "MOYENNE_GENERALE", "EVALUATION_TERMINEE","DEPARTEMENT_INTERVENANT","HEURES_PHYSIQUES","TYPE_COURS","PERIODE_ACADEMIC_COURS","GROUPE_INTERVENANT") AS
SELECT
a.departement_cours,
a.resource_id,
a.surname || ' ' || a.first_name AS Enseignant,
a.type_ressource AS Type_ressource,
a.term_id AS Periode,
CASE When a.service_id LIKE 'MB01%' then 'CORE'
When a.service_id LIKE 'MB02%' then 'PP'
When a.service_id LIKE 'GE%' then substr(a.service_id,3,2)
When a.service_id LIKE 'DOC%' then 'DOCT'
When a.service_id LIKE 'MS%' then 'MS-' || substr(a.service_id,3,2)
Else 'Autre Promotion' end AS Promotion,
a.service_id AS Code_cours,
a.serviceoff_id AS Code_offre_de_cours,
a.offering_desc AS Libelle_du_cours,
--to_char(b.effectifs) AS Nb_eleves,
to_number(b.effectifs) AS Nb_eleves,
--nb_eval est divisé par 4 car on traite 4 questions
--to_char(COUNT (*)/4) AS Nb_eval,
to_number(COUNT (*)/4) AS Nb_eval,
--to_char(AVG (a.qualite_pedagogique),'9.99') AS Qualite_pedago,
--rajout des autres questions(ajouter aussi dans les alias)
--to_char(AVG (a.appreciation_globale),'9.99') AS Qualite_globale,
--to_char(AVG (a.contenu_cours),'9.99') AS Contenu_cours,
--to_char(AVG (a.qualite_outils),'9.99') AS Qualite_outils,
--to_char(((COUNT (*)/4) / b.effectifs * 100),'999.99') || '%' AS valeur,
--to_char(((AVG (a.qualite_pedagogique)+AVG (a.appreciation_globale)+AVG (a.contenu_cours)+AVG (a.qualite_outils)) / 4),'9.99') AS moyenne_generale,
round(AVG (a.qualite_pedagogique),2) AS Qualite_pedago,
round(AVG (a.appreciation_globale),2) AS Qualite_globale,
round(AVG (a.contenu_cours),2) AS Contenu_cours,
round(AVG (a.qualite_outils),2) AS Qualite_outils,
to_char(((COUNT (*)/4) / b.effectifs * 100),'999.99') || '%' AS valeur,
round(((AVG (a.qualite_pedagogique)+AVG (a.appreciation_globale)+AVG (a.contenu_cours)+AVG (a.qualite_outils)) / 4),2) AS moyenne_generale,
case
when a.DATE_FIN_EVAL <= sysdate then 1
else 0
end case,
a.departement_intervenant,
a.heures_physiques,
a.type_cours,
a.periode_academic_cours,
a.groupe_intervenant
FROM avi_eval_eleve a, avi_eval_effectifs b
WHERE a.service_id = b.service_id AND a.serviceoff_id = b.serviceoff_id
---AND a.service_id = 'GEM1FIN001' AND a.serviceoff_id like '0910%' AND a.groupe_intervenant='P'
GROUP BY a.service_id,a.serviceoff_id,a.resource_id,b.effectifs,a.surname,a.first_name,a.offering_desc,a.term_id,a.type_ressource,a.departement_cours,a.DATE_FIN_EVAL,a.departement_intervenant,a.heures_physiques,a.type_cours,a.periode_academic_cours,a.groupe_intervenant; |
Requête sur une vue et temps de réponse
Répondre avec citation
Envoyé par beegood
Partager