IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

SQL Oracle Discussion :

Fonctions LEAD et LAG avec un UPDATE


Sujet :

SQL Oracle

Vue hybride

Message précédent Message précédent   Message suivant Message suivant
  1. #1
    Membre éclairé
    Inscrit en
    Avril 2007
    Messages
    483
    Détails du profil
    Informations personnelles :
    Âge : 38

    Informations forums :
    Inscription : Avril 2007
    Messages : 483
    Par défaut Fonctions LEAD et LAG avec un UPDATE
    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) :
    1 | appli | projet | 1 | date | 1 | commentaire
    10 | appli | projet | 1 | date+1 | 1 | commentaire

    20 | appli | projet | 0 | date+2 | 1 | commentaire
    30 | appli | projet | 0 | date+3 | 1 | commentaire
    40 | appli | projet | 0 | date+4 | 1 | commentaire

    50 | appli | projet | 1 | date+5 | 1 | commentaire
    60 | appli | projet | 1 | date+6 | 1 | commentaire
    70 | appli | projet | 1 | date+7 | 1 | commentaire

    80 | appli | projet | 0 | date+8 | 1 | commentaire
    90 | appli | projet | 0 | date+9 | 1 | commentaire
    100 | appli | projet | 0 | date+10 | 1 | commentaire
    110 | appli | projet | 0 | date+11 | 1 | commentaire

    120 | appli | projet | 1 | date+12 | 1 | commentaire
    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;
    Ma première question :

    Comment faire en sorte que la deuxième requête prenne en compte le premier enregistrement de la première série ? (car il ne le fait pas actuellement)

    Ma deuxième :

    Je dois modifier tous les champs compte du groupe
    20 | appli | projet | 0 | date+2 | 1 | commentaire
    30 | appli | projet | 0 | date+3 | 1 | commentaire
    40 | appli | projet | 0 | date+4 | 1 | commentaire
    (par exemple)

    je veux donc créer une requête qui update tous les enregistrements entre le 1er et le dernier (d'où l'apparition de mes deux requêtes) mais je ne vois pas du tout comment construire la requête (il faut que la modif ne soit que sur cette appli la, de ce projet la)

    Merci d'avance pour votre aide car je galère un peu (je continue de chercher de mon coté évidement)

  2. #2
    Membre émérite
    Profil pro
    Inscrit en
    Août 2008
    Messages
    861
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Août 2008
    Messages : 861
    Par défaut
    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

  3. #3
    Membre éclairé
    Inscrit en
    Avril 2007
    Messages
    483
    Détails du profil
    Informations personnelles :
    Âge : 38

    Informations forums :
    Inscription : Avril 2007
    Messages : 483
    Par défaut
    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

  4. #4
    Membre émérite
    Profil pro
    Inscrit en
    Août 2008
    Messages
    861
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Août 2008
    Messages : 861
    Par défaut
    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.)

  5. #5
    Expert confirmé
    Profil pro
    Inscrit en
    Août 2008
    Messages
    2 955
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Août 2008
    Messages : 2 955
    Par défaut
    En attendant, regarde le tuto de lalystar

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

  6. #6
    Membre émérite
    Profil pro
    Inscrit en
    Août 2008
    Messages
    861
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Août 2008
    Messages : 861
    Par défaut
    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).

+ Répondre à la discussion
Cette discussion est résolue.

Discussions similaires

  1. [PRO*C] probléme avec la fonction Lead | Lag
    Par Sixclopes dans le forum Interfaces de programmation
    Réponses: 1
    Dernier message: 28/05/2010, 11h52
  2. Simulation de la fonction LIMIT de MySQL avec SQL Server
    Par Le Pharaon dans le forum MS SQL Server
    Réponses: 6
    Dernier message: 15/11/2005, 10h43
  3. Fonction JS qui fonctionne avec Mozilla mais pas avec IE.
    Par etiennegaloup dans le forum Général JavaScript
    Réponses: 3
    Dernier message: 31/10/2005, 13h58
  4. requete avec un update
    Par letudiant1 dans le forum ASP
    Réponses: 4
    Dernier message: 06/10/2005, 16h33
  5. Probleme avec l'update
    Par belabyad dans le forum ASP
    Réponses: 8
    Dernier message: 02/05/2005, 10h18

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo