Discussion :

[touftouf57]

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.

[SQLpro]

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

A +

[aieeeuuuuu]

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

[JeitEmgie]

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