Discussion :

[LEK]

Bonsoir,
j'ai une table de trajet comme cela :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
table traject( trajet_id, arrival_time,departure_time,stop_id)

elle contient des descriptions de trajets (identifiés par traject_id) similaires, et j'aimerai attribuer un identifiant unique à ces sets de trajets similaires.
par exemple si dans ma table j'ai les données suivantes :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
trajet_id, arrival_time,departure_time,stop_id
1,8:00,8:05,'ARRET 1'
1,8:10,8:20,'ARRET 2'
2,8:00,8:05,'ARRET 1'
2,8:10,8:20,'ARRET 2'
3,8:20,8:25,'ARRET 1'

j'aimerais généré un identifiant pour chaque groupe similaire que je rajoute ici en premiere colonne :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
ID_DESC_TRAJET,trajet_id, arrival_time,departure_time,stop_id
1,1,8:00,8:05,'ARRET 1'
1,1,8:10,8:20,'ARRET 2'
1,2,8:00,8:05,'ARRET 1'
1,2,8:10,8:20,'ARRET 2'
2,3,8:20,8:25,'ARRET 1'

Est-ce possible d'obtenir cette identifiant avec une requête ensembliste? Ou bien est ce que je dois passer forcément par du pl /sql ?
Merci d'avance pour toute aide sur ce sujet.

[McM]

Je pense qu'avec les fonctions Analytiques tu pourrais t'en sortir, mais il faudrait au préalable définir ce qu'est un "Trajet" et peut-être utiliser Listagg

[LEK]

Bonjour, j'ai essayé avec les fonctions analytiques sans vraiment de résultat.
Quoique à la réflexion avec un rank partionné par arrival_time,departure_time,stop_id je devrais effectivement avoir mon bonheur.
J'ai essayé aussi les fonctions type grouping set...
Un trajet décrit le passage par différents points d'un véhicule.
Un trajet est identifié par un identifiant : trajet_id.
Il est décrit comme une suite de points (stop_id) qui sont parcourus dans un ordre donné.
Pour chaque point du trajet, je dispose des heures d'arrivée et de départ à et de ce point.
Mon objectif : j'aimerai pouvoir regrouper plusieurs trajets qui passent exactement par les mêmes points aux mêmes heures et leur affecter un identifiant de groupe...

Est-ce que ma description est plus claire ?
Je réessaye avec la fonction rank et fais un retour.

[LEK]

Non je n'y arrive pas, voilà ce que j'avais écris :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
 
with traject as (
  select 1 as trajet_id,'8:00' as arrival_time,'8:05' as departure_time,'ARRET 1' as stop_id from dual union all
  select 1,'8:10','8:20','ARRET 2' from dual union all
  select 2,'8:00','8:05','ARRET 1' from dual union all
  select 2,'8:10','8:20','ARRET 2' from dual union all
  select 3,'8:20','8:25','ARRET 1' from dual 
)
SELECT 
      DENSE_RANK() OVER (PARTITION BY arrival_time,departure_time,stop_id order by trajet_id) AS ID_DESC,
    trajet_id ,
      arrival_time,departure_time,stop_id
FROM   traject;

qui ne peut fonctionner car la partition fonctionne sur des groupes de valeurs de colonnes identiques alors que je veux identifier les suite les de lignes identiques ....

[McM]

je veux identifier les suite les de lignes identiques ....

Bein oui, c'est LE problème. C'est pourquoi je t'ai dit qu'il faudrait passer par du LISTAGG (concaténation sur regroupement)

Voici une requête qui sort les trajets en doublon

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 WITH traject AS (
  SELECT 1 AS trajet_id,'8:00' AS arrival_time,'8:05' AS departure_time,'ARRET 1' AS stop_id FROM dual UNION ALL
  SELECT 1,'8:10','8:20','ARRET 2' FROM dual UNION ALL
  SELECT 2,'8:00','8:05','ARRET 1' FROM dual UNION ALL
  SELECT 2,'8:10','8:20','ARRET 2' FROM dual UNION ALL
  SELECT 3,'8:20','8:25','ARRET 1' FROM dual 
)
SELECT lst, listagg(trajet_id, ' ') WITHIN GROUP (ORDER BY trajet_id) lst_id
FROM (
SELECT trajet_id, listagg(arrival_time ||'-'|| departure_time ||':'|| stop_id, '#') WITHIN GROUP (ORDER BY arrival_time) lst
FROM   traject
GROUP BY trajet_id
)
GROUP BY lst having count(*) > 1

PS : Attention le listagg est limité à 2000 caractères et plante si la concaténation dépasse les 2000 (un SUBSTR ne servira à rien)

[skuatamad]

Avec la requête de MCM on y est presque :

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
SQL>   WITH traject AS (
SELECT 1 AS trajet_id,'8:00' AS arrival_time,'8:05' AS departure_time,'ARRET 1' AS stop_id FROM dual UNION ALL
SELECT 1,'8:10','8:20','ARRET 2' FROM dual UNION ALL
SELECT 2,'8:00','8:05','ARRET 1' FROM dual UNION ALL
SELECT 2,'8:10','8:20','ARRET 2' FROM dual UNION ALL
SELECT 3,'8:20','8:25','ARRET 1' FROM dual
),
       aggreg_trajet as (
SELECT trajet_id
     , listagg(arrival_time ||'-'|| departure_time ||':'|| stop_id, '#') WITHIN GROUP (ORDER BY arrival_time) lst
  FROM traject
 GROUP BY trajet_id
),
       liste_trajet_identique as (
SELECT lst
     , listagg(trajet_id, ' ') WITHIN GROUP (ORDER BY trajet_id) lst_id
     , row_number() over(order by lst) as grp
  FROM aggreg_trajet
 GROUP BY lst
)
select l.grp, t.*
  from traject t
  join liste_trajet_identique l
    on instr(l.lst_id, t.trajet_id) > 0;
 
       GRP  TRAJET_ID ARRI DEPA STOP_ID
---------- ---------- ---- ---- -------
         1          1 8:00 8:05 ARRET 1
         1          1 8:10 8:20 ARRET 2
         1          2 8:00 8:05 ARRET 1
         1          2 8:10 8:20 ARRET 2
         2          3 8:20 8:25 ARRET 1
 
SQL>

A voir sur du volume les perfs que ça peut avoir...

[McM]

Attention au

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

on instr(l.lst_id, t.trajet_id) > 0;

car instr('11', '1') > 0
Il faut faire un

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

on instr(' '|| l.lst_id ||' ', ' '||t.trajet_id ||' ') > 0;

pour être sûr de prendre exactement le chiffre.

[skuatamad]

Bien vu, merci

[LEK]

Merci, cela fonctionne parfaitement!