Plage de nombre consécutifs

Version imprimable

22/12/2009, 15h58
Gandahar

Plage de nombre consécutifs

Bonjour à tous,

je voudrai construire une requete permettant de dégager les plages de nombres consécutifs non utilisés sur un champ number d'une table.

Any idea ?
merci pour votre aide.
22/12/2009, 17h00
mnitu

Utilisez les fonctions analytiques pour ça. Un petit exemple sera bien venu pour éviter des confusions.
22/12/2009, 17h09
dgi77
Voici une requête qui pourrait être un point de départ :
Code:

1 2 3 4 select level from dual connect by level <= 100 minus select col_num from nom_table
A adapter bien sûr...
22/12/2009, 17h27
Gandahar

dgi77 : excellent et simplissime !! effectivement c'est un très bon point de départ. J'obtiens ainsi tous les nombres non utilisés effectivement mais comment en faire ressortir les extrêmes des plages et uniquement les extrêmes ?

Pour mnitu : voici un exemple.
Table client (client_id, nom, prenom, ....)
Le champ client id est affecté par une séquence mais dans certains cas (supression physique du client, non utilisation de la valeur nextval, ...) des trous sont générés. Je me retouve avec :

Code:

select * from client order by client_id

Citation:

client_id;nom;prenom
1;NOM_CLIENT1;PRENOM_CLIENT1
2;NOM_CLIENT2;PRENOM_CLIENT2
9;NOM_CLIENT9;PRENOM_CLIENT9
15;NOM_CLIENT15;PRENOM_CLIENT15

Je souhaite que la requete me renvoie par exemple

Plage 3 à 8 disponible
Plage 10 à 14 disponible
22/12/2009, 17h30
Waldar

Si vous voulez boucher les trous, c'est très mauvaise idée !
22/12/2009, 20h45
Gandahar

Non non ce n'est pas le cas.
Ceci dit pourquoi est ce une mauvaise idée ?

Celà signifierait que vous avez plusieurs façon de renseigner cette PK, et un jour où l'autre, ça finira par flancher.

Les trous de séquence ne sont pas gênant tant que l'id est unique, et ça c'est garanti par Oracle avec la séquence.

Pour votre besoin, vous pouvez utiliser une fonction analytique :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
with client as
(
select  1 as client_id from dual union all
select  2              from dual union all
select  9              from dual union all
select 15              from dual
)
  ,  sr1 as
(
select client_id,  lag(client_id, 1, 0) over(order by client_id asc) as lg,
       client_id - lag(client_id, 1, 0) over(order by client_id asc) as df
  from client
)
  select 'Plage' || to_char(lg + 1, '90') ||
            ' à' || to_char(client_id - 1, '90') || ' disponible' as res
    from sr1
   where df > 1
order by client_id asc;
 
RES
------------------------
Plage  3 à  8 disponible
Plage 10 à 14 disponible

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
 
SQL> With Data As (
  2    Select 1 client_id, 'NOM_CLIENT1' as nom, 'PRENOM_CLIENT1' as prenom From dual union all
  3    Select 2, 'NOM_CLIENT2', 'PRENOM_CLIENT2'  From dual union all
  4    Select 9, 'NOM_CLIENT9', 'PRENOM_CLIENT9'  From dual union all
  5    Select 15, 'NOM_CLIENT15', 'PRENOM_CLIENT15'  From dual
  6  )
  7  Select client_id + 1 , ld - 1
  8   From (
  9    Select client_id, lead(client_id) over( order by client_id) ld
 10      from Data
 11      )
 12  Where client_id != ld - 1
 13  /
 
CLIENT_ID+1       LD-1
----------- ----------
          3          8
         10         14

L'utilisation d'une séquence ne peut pas garantir la continuité des valeurs dans la table.

24/12/2009, 10h57
Gandahar

Woaw !! Bravo !
Faut vraiment que je me mettes aux fonctions analytiques....:ccool: