Fonctions LEAD et LAG avec un UPDATE

**Sh4dow49** · 27/08/2008, 11h20

Bonjour,

Je vais commencer par un exemple d'extrait de base de données, puis vous montrer deux requêtes SQL que j'ai avant de vous poser ma question

Voici l'exemple

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
 
CREATE TABLE StatsAppli	(Id int, 
			 AppName varchar(10),
			 ProjName varchar(30), 
			 AppStatus number(1),
			 DateStatus date,
			 Compte number(1),
			 MaintComment varchar(200),
			 CONSTRAINT pk_StatsAppli PRIMARY KEY(Id, AppName, ProjName),
			 CONSTRAINT fk_SA_AppName FOREIGN KEY(AppName, ProjName) REFERENCES Application(AppName, ProjName) ON DELETE CASCADE
			);
 
--pour auto incrémenter l'id
CREATE SEQUENCE	seq_StatsAppli
	START WITH 1
	MINVALUE 1
	NOCYCLE
	NOCACHE
	ORDER;
 
 
--pour insérer l'id
CREATE OR REPLACE TRIGGER trg_StatsAppli
	BEFORE INSERT ON StatsAppli
	REFERENCING OLD AS old NEW AS new
	FOR EACH ROW
	BEGIN
		SELECT seq_StatsAppli.nextval INTO :new.Id FROM DUAL;
	END;
/

un exemple de données (pour une seule appli, données séparées par un pipe) :

l'id est bien auto incrémenté 1 par 1 mais c'est pour montrer qu'il peut y avoir d'autres données entre chaque.

Enfin mes requêtes avec commentaire :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
--cette requête me permet de sélectionner le dernier enregistrement dans une série de 1 ou de zéro (ici les lignes dont l'id est 10, 40, 70 et 110)
SELECT day, hour, maintcomment, compte, appstatus FROM 
        ( 
                SELECT appstatus, datestatus, to_char(datestatus, 'DD/MM/YYYY') day, to_char(datestatus, 'HH24:MI') hour, maintcomment, compte, LEAD(appstatus) OVER (partition BY appname, projname ORDER BY datestatus) AS teststatus FROM statsappli WHERE appname='nom_appli' AND projname='nom_projet' 
        ) 
WHERE teststatus<>appstatus AND compte=1;
 
--cette requête fait l'inverse, elle sélectionne le 1er de chaque série (20, 50, 80 et 120)
SELECT day, hour, maintcomment, compte, appstatus FROM 
        ( 
                SELECT appstatus, datestatus, to_char(datestatus, 'DD/MM/YYYY') day, to_char(datestatus, 'HH24:MI') hour, maintcomment, compte, LAG(appstatus) OVER (partition BY appname, projname ORDER BY datestatus) AS teststatus FROM statsappli WHERE appname='nom_appli' AND projname='nom_projet' 
        ) 
WHERE teststatus<>appstatus AND compte=1;

**Snipah** · 27/08/2008, 14h02

Salut,
Il serait plus simple dans ta requete, pour exclure le premier et le dernier enregistrement de procéder comme ça :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT day, hour, maintcomment, compte, appstatus FROM 
        ( 
                SELECT 
                     appstatus, 
                     datestatus, 
                     to_char(datestatus, 'DD/MM/YYYY') day, 
                     to_char(datestatus, 'HH24:MI') hour, 
                     maintcomment, 
                     compte, 
                     row_number(appstatus) OVER (partition BY appname, projname ORDER BY datestatus) AS rang, 
                     count(*) over (partition by appname, projname) as nb_occurences 
                FROM statsappli WHERE appname='nom_appli' AND projname='nom_projet' 
        ) 
WHERE rang > 1
and rang < nb_occurence 
AND compte=1;

Tu assignes un numéro de ligne à chacune de tes occurence ordonnées par date (rang) et calcule le nombre total d'occurences (nb_occurences).
Tu exclues ensuite facilement la première et la dernière ligne.

En passant, pour tes alias évite les mots réservés comme day et hour dans tes requêtes, ça coute pas cher

**Sh4dow49** · 27/08/2008, 14h35

pourrais tu m'expliquer ce que fais la requête (surtout la partie modifiée) car je suis encore débutant en sql oracle (les deux requêtes que j'ai montré ne sont pas entièrement issues de moi ^^")

J'aimerais comprendre ce qu'elle fait exactement avant de l'utiliser (également pour l'expliquer dans la doc).

De plus, lorsque je l'exécute, oracle indique l'erreur suivante :

missing right parenthesis

sir la ligne

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

row_number(appstatus) OVER (partition BY appname, projname ORDER BY datestatus) AS rang,

(plus précisément, l'étoile est en dessous de la première parenthèse de la ligne)

J'ai recompté les parenthèses et il y a le bon nombre ...

Merci d'avance

**Snipah** · 27/08/2008, 14h42

Ah oui, j'ai fait une erreur, row_number() n'attend rien entre les parenthèses.
Le copier/coller, c'est mal

Donc, la ligne devient :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

row_number() OVER (partition BY appname, projname ORDER BY datestatus) AS rang,

(Je t'expliquerai en détail dès que j'ai un moment.)

**skuatamad** · 27/08/2008, 14h57

En attendant, regarde le tuto de lalystar

http://lalystar.developpez.com/fonctionsAnalytiques/

**Snipah** · 27/08/2008, 15h26

Le tutoriel que t'a indiqué skuatamad devrait suffire.

On va quand même expliquer brièvement la sous requete.
On sélectionne les champs de la table statsappli normalement, en ajoutant deux champs 'rang' et 'nb_occurences'.

rang est donné par la fonction row_number(), qui 'numérote' tes enregistrements selon une notion de groupe (PARTITION BY appname, projname) et d'ordre (ORDER BY datestatus).
nb_occurences indique le nombre d'occurences d'un couple appname/projname, et est donnée par la fonction count(*) over (PARTITION BY appname, projname). Ce champs va donc t'indiquer le rang maximum que peut avoir un enregistrement pour un couple appname/projname.

En utilisant ces deux informations dans la requête principale, on exclue facilement les premiers et derniers enregistrements (WHERE rang > 1 AND rang < nb_occurences).

Fonctions LEAD et LAG avec un UPDATE

SQL Oracle

Vue hybride

Discussions similaires

Partager

Partager