Comment grouper ensemble de lignes similaires ?

Version imprimable

01/08/2018, 00h26
LEK

Comment grouper ensemble de lignes similaires ?
Bonsoir,
j'ai une table de trajet comme cela :
Code:

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:

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:

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.
01/08/2018, 10h24
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
01/08/2018, 14h21
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.

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

Code:

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 ....

Citation:

Envoyé par LEK

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:

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)

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

Code:

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...

01/08/2018, 17h51
McM

Attention au

Code:

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

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

Code:

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

pour être sûr de prendre exactement le chiffre.
01/08/2018, 18h05
skuatamad

Bien vu, merci
05/08/2018, 14h12
LEK

Merci, cela fonctionne parfaitement!