Fonctions LEAD et LAG avec un UPDATE [Résolu]

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) :

Citation:

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 :

Citation:

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).

Sh4dow49 · 27/08/2008, 15h41

En effet le tuto m'a aidé à comprendre la sous requête.

Par contre l'exécution de la requête ne me retourne pas juste le 1er et le dernier enregistrement de chaque groupe mais tous les enregistrements du couple projet / application en enlevant le 1er et le dernier de tous les enregistrements concernés.

J'ai essayé un ou deux trucs comme

Code :

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

WHERE rang = 1 OR rang = nb_occurences

mais ce n'est pas ça (logique après réflexion, car cela ne renverra que 2 enregistrements en tout, le tout premier et le tout dernier)

skuatamad · 27/08/2008, 15h56

Citation:

Envoyé par Sh4dow49

Par contre l'exécution de la requête ne me retourne pas juste le 1er et le dernier enregistrement de chaque groupe mais tous les enregistrements du couple projet / application en enlevant le 1er et le dernier de tous les enregistrements concernés.

J'ai essayé un ou deux trucs comme

Code :

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

WHERE rang = 1 OR rang = nb_occurences

mais ce n'est pas ça (logique après réflexion, car cela ne renverra que 2 enregistrements en tout, le tout premier et le tout dernier)

Ca doit être à cause de la clause

Code :

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

WHERE appname='nom_appli' AND projname='nom_projet'

car ça doit te renvoyer 2 enregistrements par (appname, projname)

Sh4dow49 · 27/08/2008, 16h06

Citation:

Ca doit être à cause de la clause

Code :

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

WHERE appname='nom_appli' AND projname='nom_projet'

car ça doit te renvoyer 2 enregistrements par (appname, projname)

C'est à dire ? (je ne vois pas trop ce que tu veux dire)

skuatamad · 27/08/2008, 16h10

Ben si tu selectionnes un couple particulier de (appname, projname) dans la sous-requête, forcément la requête finale ne contiendra que 2 lignes.
Si tu retires cette clause where, tu auras 2 lignes par couple (appname, projname).

[EDIT]

Citation:

je veux donc créer une requête qui update tous les enregistrements entre le 1er et le dernier

Citation:

Par contre l'exécution de la requête ne me retourne pas juste le 1er et le dernier enregistrement de chaque groupe mais tous les enregistrements du couple projet / application en enlevant le 1er et le dernier de tous les enregistrements concernés.

En fait y a un truc que je comprends pas

Sh4dow49 · 27/08/2008, 16h18

ah oui d'accord.

Ce que je veux ce n'est pas 2 lignes par couple mais deux ligne par groupe de valeur du champ appstatus.

Pour illustrer si je reprends mon exemple :

Citation:

je veux ici avoir en retour la première et la dernière ligne de chacun de ces 5 groupes

mais je n'y arrive pas ...

EDIT

En fait ce que je veux c'est, par exemple, modifier la valeur de compte pour les lignes

Citation:

et le passer de 1 à zero.

Je fais cela en php derrière :
liste des premiers enregistrements de chaque groupe (donc ceux dont les id sont 1, 20, 50, 80 et 120) et en cliquant sur un bouton, j'envoie en paramètre le nom de l'appli de la ligne, le nom du projet, et l'id de la ligne.

Je veux ensuite récupérer l'id de la dernière ligne du groupe dont j'ai sélectionné la ligne (exemple, si j'ai pris la ligne avec l'id 20, je récupère l'id 40) et le modifie le champ compte (qui vaut 1) de toutes les lignes entre (ayant le même nom d'appli et de projet) pour le passer à zero.

Il y a peut être plus simple (pour si vous avez compris ce que je souhaite faire) et si c'est le cas merci de m'indiquer comment faire car je ne vois pas trop ... (je rappele que je suis débutant en sql oracle ... )

skuatamad · 27/08/2008, 16h38

J'ai nommé les applis / projets pour que ce soit plus clair (je mme doute que plusieurs applis peuvent appartenir au même projet ou l'inverse mais bon ça simplifie)

Citation:

Si tu veux obtenir ça, tu n'as pas à t'embetter avec row_number & co.

Citation:

Si tu veux ça la requête de snipah fonctionnait non?

Citation:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
 
UPDATE statsappli SET compte=decode(compte,1,0,1) WHERE id IN (
SELECT id FROM 
        ( 
                SELECT 
                     id, 
                     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='appli2' AND projname='projet2' 
        ) 
WHERE rang > 1
AND rang < nb_occurence);

Bon c'est une idée, il faut peut être modifier encore un peu.

Sh4dow49 · 27/08/2008, 16h42

non non l'extrait que je t'ai fourni était avec le même nom d'appli et de projet partout
j'ai juste épuré en enlevant toutes les autres applis de la table et tous les autres projets et en mettant un nom simplifié (c'est vrai que j'aurais pu mettre le même nom dans les requêtes pour plus de cohérence, désolé)

EDIT

Citation:

je mme doute que plusieurs applis peuvent appartenir au même projet

oui tout a fait c'est la raison de l'épuration (pour plus de lisibilité, j'adapterai ensuite, ce n'est pas le plus dur de récupérer le nom de l'appli et du projet ^^)

skuatamad · 27/08/2008, 16h44

Alors quelle est ta notion de groupe ?

[EDIT] Comment fais tu pour ne sélectionner que les id 20,30,40?

Sh4dow49 · 27/08/2008, 16h47

ma notion de groupe (c'est vrai que je m'exprime mal) c'est en fait groupé selon la valeur de appstatus.

si tu reprends mon tout premier extrait, chaque "groupe" est séparé par un saut de ligne (il y a donc 5 groupes, en considérant la dernière ligne toute seule comme un groupe)

Sh4dow49 · 27/08/2008, 16h52

Citation:

[EDIT] Comment fais tu pour ne sélectionner que les id 20,30,40?

en réfléchissant à quoi te répondre je me rends compte que je sais le faire, mais pas avec la fonction de Snipah, avec les deux fonctions que j'ai donné au début ...

j'ai juste à ajouter la sélection de l'id dans les deux requêtes en fait

Il aura fallu une 12aine de post pour que je m'en rende compte ... je crois que je cherche trop de complication ...

Je reposterai si en fait ce n'est pas bon mais ca devrait le faire en fait.

Merci pour votre temps passé pour rien sur mon post

Snipah · 27/08/2008, 17h04

Ok, je crois que j'ai fini par comprendre ce que tu voulais

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
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 suivant, 
                     LAG(appstatus) OVER (partition BY appname, projname ORDER BY datestatus) AS precedent 
                FROM statsappli WHERE appname='nom_appli' AND projname='nom_projet' 
        ) 
WHERE appstatus = suivant AND appstatus = precedent
AND compte = 1;

Sh4dow49 · 27/08/2008, 17h10

à peine,

à la place de

Code :

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

WHERE appstatus = suivant AND appstatus = precedent

j'ai mis

Code :

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

WHERE (appstatus = suivant AND appstatus <> precedent OR appstatus <> suivant AND appstatus = precedent)

ce qui me retourne uniquement le 1er et le dernier de chaque "groupe" (et ca m'évite de faire deux requêtes donc le post n'était pas si inutile que cela ^^ )

Snipah · 27/08/2008, 17h22

Ok, donc j'avais pas tout à fait compris, ma requête était faite pour retourner seulement les enregistrements entre le premier et le dernier, et non pas seulement le premier et le dernier.
La condition serait donc plutot :

Code :

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

WHERE (appstatus <> precedent OR appstatus <> suivant)

EDIT
Ta condition fonctionne car tu n'as pas de groupe de seulement deux lignes, donc lorsque appstatus <> precedent, necessairement appstatus = suivant. Mais si tu as un groupe de 2 enregistrements, il ne te l'affichera pas.

Sh4dow49 · 27/08/2008, 17h25

nan en fait je vais récupérer le 1er et le dernier seulement, et je ferais un update avec dans la clause where

Code :

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

WHERE appname=nom_appli AND projname=nom_projet AND id BETWEEN id_début AND id_fin

et sa devrait suffir à updater tous les champs d'un groupe donné (si je ne me trompe pas)

27/08/2008, 14h42	#4
Snipah Membre émérite Inscription : août 2008 Messages : 835 Détails du profil Informations forums : Inscription : août 2008 Messages : 835 Points : 823 Points : 823	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.)
	00

27/08/2008, 15h41	#7
Sh4dow49 Membre actif Inscription : avril 2007 Messages : 483 Détails du profil Informations personnelles : Âge : 24 Informations forums : Inscription : avril 2007 Messages : 483 Points : 189 Points : 189	En effet le tuto m'a aidé à comprendre la sous requête. Par contre l'exécution de la requête ne me retourne pas juste le 1er et le dernier enregistrement de chaque groupe mais tous les enregistrements du couple projet / application en enlevant le 1er et le dernier de tous les enregistrements concernés. J'ai essayé un ou deux trucs comme Code : Sélectionner tout - Visualiser dans une fenêtre à part WHERE rang = 1 OR rang = nb_occurences mais ce n'est pas ça (logique après réflexion, car cela ne renverra que 2 enregistrements en tout, le tout premier et le tout dernier)
	00

27/08/2008, 17h10	#18
Sh4dow49 Membre actif Inscription : avril 2007 Messages : 483 Détails du profil Informations personnelles : Âge : 24 Informations forums : Inscription : avril 2007 Messages : 483 Points : 189 Points : 189	à peine, à la place de Code : Sélectionner tout - Visualiser dans une fenêtre à part WHERE appstatus = suivant AND appstatus = precedent j'ai mis Code : Sélectionner tout - Visualiser dans une fenêtre à part WHERE (appstatus = suivant AND appstatus <> precedent OR appstatus <> suivant AND appstatus = precedent) ce qui me retourne uniquement le 1er et le dernier de chaque "groupe" (et ca m'évite de faire deux requêtes donc le post n'était pas si inutile que cela ^^ )
	00

27/08/2008, 17h22	#19
Snipah Membre émérite Inscription : août 2008 Messages : 835 Détails du profil Informations forums : Inscription : août 2008 Messages : 835 Points : 823 Points : 823	Ok, donc j'avais pas tout à fait compris, ma requête était faite pour retourner seulement les enregistrements entre le premier et le dernier, et non pas seulement le premier et le dernier. La condition serait donc plutot : Code : Sélectionner tout - Visualiser dans une fenêtre à part WHERE (appstatus <> precedent OR appstatus <> suivant) EDIT Ta condition fonctionne car tu n'as pas de groupe de seulement deux lignes, donc lorsque appstatus <> precedent, necessairement appstatus = suivant. Mais si tu as un groupe de 2 enregistrements, il ne te l'affichera pas.
	00

27/08/2008, 17h25	#20
Sh4dow49 Membre actif Inscription : avril 2007 Messages : 483 Détails du profil Informations personnelles : Âge : 24 Informations forums : Inscription : avril 2007 Messages : 483 Points : 189 Points : 189	nan en fait je vais récupérer le 1er et le dernier seulement, et je ferais un update avec dans la clause where Code : Sélectionner tout - Visualiser dans une fenêtre à part WHERE appname=nom_appli AND projname=nom_projet AND id BETWEEN id_début AND id_fin et sa devrait suffir à updater tous les champs d'un groupe donné (si je ne me trompe pas)
	00

27/08/2008, 14h57	#5
skuatamad Membre Expert Inscription : août 2008 Messages : 1 276 Détails du profil Informations forums : Inscription : août 2008 Messages : 1 276 Points : 1 936 Points : 1 936	En attendant, regarde le tuto de lalystar http://lalystar.developpez.com/fonctionsAnalytiques/
	00

27/08/2008, 15h26	#6
Snipah Membre émérite Inscription : août 2008 Messages : 835 Détails du profil Informations forums : Inscription : août 2008 Messages : 835 Points : 823 Points : 823	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).
	00

27/08/2008, 16h44	#14
skuatamad Membre Expert Inscription : août 2008 Messages : 1 276 Détails du profil Informations forums : Inscription : août 2008 Messages : 1 276 Points : 1 936 Points : 1 936	Alors quelle est ta notion de groupe ? [EDIT] Comment fais tu pour ne sélectionner que les id 20,30,40?
	00

27/08/2008, 16h47	#15
Sh4dow49 Membre actif Inscription : avril 2007 Messages : 483 Détails du profil Informations personnelles : Âge : 24 Informations forums : Inscription : avril 2007 Messages : 483 Points : 189 Points : 189	ma notion de groupe (c'est vrai que je m'exprime mal) c'est en fait groupé selon la valeur de appstatus. si tu reprends mon tout premier extrait, chaque "groupe" est séparé par un saut de ligne (il y a donc 5 groupes, en considérant la dernière ligne toute seule comme un groupe)
	00

Outils de la discussion
Afficher une version imprimable Envoyer un lien vers cette page par email