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 :

Fonction analytique et fenetrage


Sujet :

SQL Oracle

Vue hybride

Message précédent Message précédent   Message suivant Message suivant
  1. #1
    Membre averti
    Homme Profil pro
    Développeur Java
    Inscrit en
    Décembre 2013
    Messages
    36
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France

    Informations professionnelles :
    Activité : Développeur Java
    Secteur : Finance

    Informations forums :
    Inscription : Décembre 2013
    Messages : 36
    Par défaut Fonction analytique et fenetrage
    Bonjour J'ai une petit problème que je souhaiterais résoudre en sql mais je bloque depuis deux jours, j'ai le sentiment que c'est impossible à écrire en sql :

    j'ai donc une table
    identifiant, date_debut, date_fin, valeur1

    la clef primaire de cette table est (identifiant, date_debut)

    Le but est d'obtenir le recap des modif de la valeur1 avec la date de debut et la date de fin de la valeur1

    exemple: table de travail :
    identifiant,date_debut,date_fin,valeur1
    toto,12/11/2011,12/11/2012, 300
    toto,13/11/2012,15/11/2012, 350
    toto,16/11/2012,15/11/2014, 350
    toto,16/11/2014,15/11/2015, 350
    toto,16/11/2015,17/12/2015, 400

    si au fil du temps (au fil de la date de debut) la valeur1 ne régresse pas, on ne peut donc pas retomber sur une valeur1 qui existait déjà avant et ça se fait sans réèl problème pour qui connait la fonction analytique partition by :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
     
     
    select 
    table_de_travail.identifiant, 
    min(table_de_travai.date_debut) over(partition by table_de_travail.identifiant,table_de_travail.valeur1) as min_date_debut ,
    max(table_de_travai.date_fin) over(partition by table_de_travail.identifiant,table_de_travail.valeur1) as max_date_fin,
    table_de_travail.valeur1
    from table_de_travail trable_de_travail



    On obtient assez facilement

    toto,12/11/2011,12/11/2012, 300
    toto,13/11/2012,15/11/2015, 350
    toto,16/11/2015,17/12/2015, 400


    Mais là où ça se corse véritablement, c'est qd on a en entree par exemple


    toto,12/11/2011,12/11/2012, 300
    toto,13/11/2012,15/11/2012, 350
    toto,16/11/2012,15/11/2014, 350
    toto,16/11/2014,15/11/2015, 350
    toto,16/11/2015,17/12/2015, 400
    toto,18/12/2015,12/11/2016, 300



    et que l'on souhaiterais obtenir :

    toto,12/11/2011,12/11/2012, 300
    toto,13/11/2012,15/11/2015, 350
    toto,16/11/2015,17/12/2015, 400
    toto,18/12/2015,12/11/2016, 300



    Car la requete que j'ai écrite va traité (toto,300) sans prendre en compte la rupture avec date de debut.
    J'ai regardé à coté des clause de fenetrage.

    Quelqun a il une idée?

    Au pire je traiterais ce problème d'une manière séquentiel. Merci de votre lecture!

  2. #2
    Modérateur
    Avatar de Waldar
    Homme Profil pro
    Sr. Specialist Solutions Architect @Databricks
    Inscrit en
    Septembre 2008
    Messages
    8 454
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 47
    Localisation : France, Val de Marne (Île de France)

    Informations professionnelles :
    Activité : Sr. Specialist Solutions Architect @Databricks
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Septembre 2008
    Messages : 8 454
    Par défaut
    Comme ceci :
    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
    31
    32
    33
    with cte_matable (identifiant, date_debut, date_fin, valeur1) as
    (
    select 'toto', date '2011-11-12', date '2012-11-12', 300 from dual union all
    select 'toto', date '2012-11-13', date '2012-11-15', 350 from dual union all
    select 'toto', date '2012-11-16', date '2014-11-15', 350 from dual union all
    select 'toto', date '2014-11-16', date '2015-11-15', 350 from dual union all
    select 'toto', date '2015-11-16', date '2015-12-17', 400 from dual union all
    select 'toto', date '2015-12-18', date '2016-11-12', 300 from dual
    )
      ,  cte_tmp (identifiant, date_debut, date_fin, valeur1, grp) as
    (
    select identifiant, date_debut, date_fin, valeur1
         , row_number() over(partition by identifiant          order by date_debut asc)
         - row_number() over(partition by identifiant, valeur1 order by date_debut asc)
      from cte_matable
    )
      select identifiant
           , min(date_debut) as date_debut
           , max(date_fin)   as date_fin
           , valeur1
        from cte_tmp
    group by identifiant
           , valeur1
           , grp
    order by identifiant asc
           , date_debut  asc;
     
    IDENTIFIANT DATE_DEBUT DATE_FIN   VALEUR1
    ----------- ---------- ---------- -------
    toto        2011-11-12 2012-11-12     300
    toto        2012-11-13 2015-11-15     350
    toto        2015-11-16 2015-12-17     400
    toto        2015-12-18 2016-11-12     300

  3. #3
    Membre averti
    Homme Profil pro
    Développeur Java
    Inscrit en
    Décembre 2013
    Messages
    36
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France

    Informations professionnelles :
    Activité : Développeur Java
    Secteur : Finance

    Informations forums :
    Inscription : Décembre 2013
    Messages : 36
    Par défaut
    Bonjour,

    Merci Waldar.

  4. #4
    Rédacteur

    Homme Profil pro
    Développeur et DBA Oracle
    Inscrit en
    Octobre 2006
    Messages
    878
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Algérie

    Informations professionnelles :
    Activité : Développeur et DBA Oracle

    Informations forums :
    Inscription : Octobre 2006
    Messages : 878
    Par défaut
    Bonjour,

    Une autre solution:
    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
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    with cte_matable as
    (
    select 'toto' identifiant,  '2011-11-12' date_debut,  '2012-11-12' date_fin, 300 valeur1 from dual union all
    select 'toto',  '2012-11-13',  '2012-11-15', 350 from dual union all
    select 'toto',  '2012-11-16',  '2014-11-15', 350 from dual union all
    select 'toto',  '2014-11-16',  '2015-11-15', 350 from dual union all
    select 'toto',  '2015-11-16',  '2015-12-17', 400 from dual union all
    select 'toto',  '2015-12-18',  '2016-11-12', 300 from dual
    )
    SELECT  distinct identifiant, date_debut, date_fin,valeur1
      FROM cte_matable
    MODEL
       PARTITION BY (identifiant)
       DIMENSION BY (ROW_NUMBER ()
                        OVER (PARTITION BY identifiant ORDER BY date_debut) rn)
       MEASURES (valeur1, date_debut, date_fin, 0 grp)
       RULES AUTOMATIC ORDER                                      -- iterate(1000)
          (GRP [ANY]
             ORDER BY rn =
                CASE
                   WHEN valeur1[CV ()] = NVL (valeur1[CV () - 1], 0)
                   THEN
                      GRP[CV () - 1]
                   ELSE
                      NVL (GRP[CV () - 1], 0) + 1
                END,
          date_debut [ANY] =
                MIN (date_debut) OVER (PARTITION BY GRP),
          date_fin [ANY] =
                MAX (date_fin) OVER (PARTITION BY GRP))
                order by identifiant,date_debut;
     
    IDENTIFIANT DATE_DEBUT DATE_FIN      VALEUR1
    ----------- ---------- ---------- ----------
    toto        2011-11-12 2012-11-12        300
    toto        2012-11-13 2015-11-15        350
    toto        2015-11-16 2015-12-17        400
    toto        2015-12-18 2016-11-12        300
     
    4 rows selected.

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

Discussions similaires

  1. [Fonctions analytiques] - Clause Where / Having
    Par jacquesh dans le forum Oracle
    Réponses: 5
    Dernier message: 06/10/2006, 13h29
  2. [Fonctions analytiques] : inhibe les indexs ?
    Par PpPool dans le forum Oracle
    Réponses: 18
    Dernier message: 20/04/2006, 18h22
  3. Réponses: 3
    Dernier message: 24/11/2005, 12h19
  4. Réponses: 7
    Dernier message: 21/11/2005, 14h21
  5. Réponses: 4
    Dernier message: 18/08/2005, 16h11

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