mxrrzxYX

1-- Es 4, 25-09-2014
2-- Una materialized view MV_MERITO(Specializzazione, TotaleVisite, NuoviPazienti) contiene le specializzazioni della clinica
3-- i cui medici, nel corso della settimana precedente, hanno totalizzato un numero di visite superiore
4-- alla media delle visite per specializzazione nella stessa settimana (sett. precedente),
5-- e tale numero di visite abbia coinvolto almeno cinque nuovi pazienti (dalla specializzazione o dal medico?).
6-- Creare la materialized view e implementare l’incremental refresh:
7-- (i) creando la log table; 
8-- (ii) scrivendo il codice per la gestione della log table;
9-- (iii) scrivendo il temporal trigger per il full refresh, eseguito ogni lunedì alle ore 2:00 del mattino.
10
11CREATE TABLE MV_MERITO (
12	Specializzazione VARCHAR(100) NOT NULL,
13    TotaleVisite INT NOT NULL,
14    NuoviPazienti INT NOT NULL,
15    PRIMARY KEY (Specializzazione)
16);
17
18CREATE TABLE LOG_MERITO LIKE visita;
19ALTER TABLE LOG_MERITO 
20	DROP Mutuata;
21ALTER TABLE LOG_MERITO 
22	ADD COLUMN IsNew TINYINT(1) NOT NULL,
23    ADD COLUMN Specializzazione VARCHAR(30) NOT NULL; -- 1 no, 0 si, per il paziente se nuovo o no
24
25DROP TRIGGER IF EXISTS trigger_log_merito;
26DELIMITER $$
27
28CREATE TRIGGER trigger_log_merito
29AFTER INSERT ON visita
30FOR EACH ROW
31BEGIN
32	DECLARE Specializzazione VARCHAR(30) DEFAULT '';
33    
34    SELECT M.Specializzazione INTO Specializzazione
35    FROM	medico M
36    WHERE	M.Matricola = NEW.Medico;
37    
38	INSERT INTO LOG_MERITO
39		SELECT	NEW.Medico, NEW.Paziente, NEW.Data,
40				IF (NEW.Paziente NOT IN (
41											SELECT	DISTINCT Paziente
42                                            FROM	visita V
43													INNER JOIN
44                                                    medico M
45											WHERE	M.Specializzazione = Specializzazione
46													AND V.Data <= NEW.Data
47                                        
48                                        ), 1, 0), Specializzazione; -- se il paz non è stato visitato dalla specializzazione --> 1, altrimento 0
49END $$
50
51DROP EVENT IF EXISTS evento_MV_Merito $$
52CREATE EVENT evento_MV_Merito
53ON SCHEDULE EVERY 1 WEEK
54STARTS CURRENT_DATE + INTERVAL (9 - IF(DAYOFWEEK(CURRENT_DATE)=1, 8, DAYOFWEEK(CURRENT_DATE))) DAY
55DO
56BEGIN
57	DECLARE average_visite_spec FLOAT DEFAULT 0;
58    
59    SET average_visite_spec = (
60		SELECT AVG(D.NumVisiteSpec)
61        FROM	(
62				SELECT	LM.Specializzazione, COUNT(*) AS NumVisiteSpec
63				FROM	LOG_MERITO LM
64				WHERE	LM.Data + INTERVAL 1 WEEK > CURRENT_DATE()
65				GROUP BY LM.Specializzazione
66                ) AS D
67        );
68	-- flushing della materialized view 
69	TRUNCATE TABLE MV_MERITO;
70	
71	INSERT INTO MV_MERITO
72		SELECT	LM.Specializzazione, COUNT(*), SUM(LM.IsNew)
73		FROM	LOG_MERITO LM
74		WHERE	LM.Data + INTERVAL 1 WEEK > CURRENT_DATE()
75		GROUP BY LM.Specializzazione
76		HAVING COUNT(*) > average_visite_spec
77			   AND SUM(LM.IsNew) >= 5; -- maggiore della media e >= 5 pazienti
78	
79	-- flushing della log table
80	TRUNCATE TABLE LOG_MERITO;
81END $$