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

Développement SQL Server Discussion :

Obtention des bornes d'une séquence


Sujet :

Développement SQL Server

  1. #1
    Membre habitué
    Obtention des bornes d'une séquence
    Bonjour,

    Je vous demande votre aide avisée car je pense que l'opération est assez complexe.

    Je cherche à trouver les bornes min et max d'une sous-séquence.
    Avec les données suivantes:
    Numero CodeGroupe
    070220/001 1
    070220/002 1
    070220/003 1
    070220/004 2
    070220/006 2
    070220/007 3
    070220/008 3
    070220/009 1

    Veuillez noter qu'il n'existe pas de numéro 070220/005

    Je cherche à obtenir, via une procédure stockée qui fait déjà toute la préparation pour obtenir les données de cette table temporaire, le résultat suivant:
    CodeGroupe Num Min Num Max
    1 070220/001 070220/003
    1 070220/009 070220/009
    2 070220/004 070220/004
    2 070220/006 070220/006
    3 070220/007 070220/008

    J'ai déjà essayé l'utilisation du ROW_NUMBER mais je n'obtiens pas le résultat attendu.

    En parcourant le web, je suis tombé sur ceci:
    WITH T
    AS (SELECT ROW_NUMBER() OVER (ORDER BY number) - number AS Grp,
    number
    FROM mytable
    WHERE status = 0)
    SELECT MIN(number) AS [From],
    MAX(number) AS [To]
    FROM T
    GROUP BY Grp
    ORDER BY MIN(number)

    Mais, en l'adaptant à mon cas cela ne peut pas fonctionner puisque le champs number qu'ils utilisent est de type bigint alors que dans mon cas c'est un varchar. Donc le "-Numero" ne fonctionnera pas.
    Ai-je tord?

    Pouvez-vous m'aider?

    Je vous remercie d'avance.

  2. #2
    Rédacteur

    Postez le DDL de vos tables et non des tableaux… Indentez vos requêtes !

    A +
    Cette signature n'a pas pu être affichée car elle comporte des erreurs.

  3. #3
    Modérateur

    bonjour,

    La moédalisation serait à revoir, la colonne Numero n'étant pas atomique.

    Essayez ceci :

    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
     
    WITH  Tmp AS (
    	SELECT *, RIGHT(Numero, 3) AS Num, LAG(RIGHT(Numero, 3)) OVER(PARTITION BY CodeGroupe ORDER BY Numero) AS NumPrec
    	FROM mytable
    ), Grp AS (
    	SELECT *, SUM(CASE WHEN NumPrec + 1 = Num THEN 0 ELSE 1 END) OVER(PARTITION BY CodeGroupe ORDER BY Numero) AS Groupe
    	FROM Tmp
    )
    SELECT CodeGroupe, MIN(Numero), MAX(Numero)
    FROM Grp
    GROUP BY CodeGroupe, Groupe
    ORDER BY CodeGroupe

  4. #4
    Membre expert
    Application habituelle du Tabibitosan :

    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
    41
     
    with data as (
        select '070220/001' as num, 1 as grp from dual 
        union all
        select '070220/002' as num, 1 as grp from dual 
        union all
        select '070220/003' as num, 1 as grp from dual 
        union all
        select '070220/004' as num, 2 as grp from dual 
        union all
        select '070220/006' as num, 2 as grp from dual 
        union all
        select '070220/007' as num, 3 as grp from dual 
        union all
        select '070220/008' as num, 3 as grp from dual 
        union all
        select '070220/009' as num, 1 as grp from dual 
    )
    select * from (
        select distinct grp, code || substr('000' || (min(ord) over (partition by subgrp)),-3) as nummin, 
            code || substr('000' || (max(ord) over(partition by subgrp)),-3) as nummax from (
     
            select code, ord, ord - rn as subgrp, grp
            from (
                select substr(num,1,7) as code, to_number(substr(num,-3)) as ord, grp,
                    row_number() over(partition by grp order by num) as rn
                from data 
            )
        )
    )
    order by grp, nummin 
    ;
     
     
           GRP NUMMIN     NUMMAX    
    ---------- ---------- ----------
             1 070220/001 070220/003
             1 070220/009 070220/009
             2 070220/004 070220/004
             2 070220/006 070220/006
             3 070220/007 070220/008


    (ok je sais : syntaxe ORACLE pour les to_number, to_char, substr et C° mais vraiment pas envie de relancer une VM SQL Server rien que pour cela…)
    (
    et sinon pour les curieux la solution ORACLE avec match_recognize:

    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
     
    with data as (
        select '070220/001' as num, 1 as grp from dual 
        union all
        select '070220/002' as num, 1 as grp from dual 
        union all
        select '070220/003' as num, 1 as grp from dual 
        union all
        select '070220/004' as num, 2 as grp from dual 
        union all
        select '070220/006' as num, 2 as grp from dual 
        union all
        select '070220/007' as num, 3 as grp from dual 
        union all
        select '070220/008' as num, 3 as grp from dual 
        union all
        select '070220/009' as num, 1 as grp from dual 
    )
    select * from data 
    match_recognize (
        partition by grp
        order by num
        measures first(num) as min, last(num) as max
        pattern( STRT A* )
        define 
            A as to_number(substr(num,-3)) - 1 = to_number(substr(prev(num),-3))
    )
    ;
     
           GRP MIN        MAX       
    ---------- ---------- ----------
             1 070220/001 070220/003
             1 070220/009 070220/009
             2 070220/004 070220/004
             2 070220/006 070220/006
             3 070220/007 070220/008

###raw>template_hook.ano_emploi###