Requête de sélection : casse tête 2

**sfoggy** · 03/02/2010, 09h55

Bonjour,

voilà j'ai deux tables avec comme exemple de données

T1 :

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
 
        REF ENTREE              SORTIE                       A          B
---------- ------------------- ------------------- ---------- ----------
     50180 01/02/2010 13:07:30 01/02/2010 13:07:46          1          2
     50180 01/02/2010 13:07:46 01/02/2010 13:45:45          2          5
     50180 01/02/2010 13:45:45 01/02/2010 15:50:01          5          6
     50180 01/02/2010 15:50:01 01/02/2010 15:50:20          6         23
     50180 01/02/2010 15:50:20 01/02/2010 15:50:23         23          2
     50180 01/02/2010 15:50:23 02/02/2010 14:53:13          2          5
     50180 02/02/2010 14:53:13 02/02/2010 15:05:41          5          6
     50180 02/02/2010 15:05:41 02/02/2010 15:06:37          6         23
     50180 02/02/2010 15:06:37 02/02/2010 15:07:12         23          2
     50180 02/02/2010 15:07:12 02/02/2010 15:10:10          2          1
     50180 02/02/2010 15:10:10 02/02/2010 15:18:31          1          2
     50180 02/02/2010 15:18:31 02/02/2010 15:18:35          2          5
     50180 02/02/2010 15:18:35 02/02/2010 15:18:37          5          6
     50180 02/02/2010 15:18:37 02/02/2010 15:18:50          6         23
     50180 02/02/2010 15:18:50 02/02/2010 15:18:53         23          2
     50180 02/02/2010 15:18:53 02/02/2010 15:51:01          2          5
     50180 02/02/2010 15:51:01 02/02/2010 15:51:07          5          6
     50180 02/02/2010 15:51:07 02/02/2010 15:51:17          6         23
     50180 02/02/2010 15:51:17                             23

et T2 avec :

Code :

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

1
2
3
4
5
6
7
8
9
 
       REF CHANGE                   CLASS     SCLASS
---------- ------------------- ---------- ----------
     50180 01/02/2010 13:10:57        164         21
     50180 01/02/2010 15:50:20        165         62
     50180 02/02/2010 15:06:37        165         62
     50180 02/02/2010 15:10:12        203         62
     50180 02/02/2010 15:18:50        203         62
     50180 02/02/2010 15:51:17        165         62

le but étant d'obtenir ce qui suit :

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
 
       REF ENTREE              SORTIE                       A          B CHANGE                   CLASS     SCLASS
---------- ------------------- ------------------- ---------- ---------- ------------------- ---------- ----------
     50180 01/02/2010 13:07:30 01/02/2010 13:07:46          1          2
     50180 01/02/2010 13:07:46 01/02/2010 13:45:45          2          5 01/02/2010 13:10:57        164         21
     50180 01/02/2010 13:45:45 01/02/2010 15:50:01          5          6 01/02/2010 13:10:57        164         21
     50180 01/02/2010 15:50:01 01/02/2010 15:50:20          6         23 01/02/2010 13:10:57        164         21
     50180 01/02/2010 15:50:20 01/02/2010 15:50:23         23          2 01/02/2010 15:50:20        165         62
     50180 01/02/2010 15:50:23 02/02/2010 14:53:13          2          5 01/02/2010 15:50:20        165         62
     50180 02/02/2010 14:53:13 02/02/2010 15:05:41          5          6 01/02/2010 15:50:20        165         62
     50180 02/02/2010 15:05:41 02/02/2010 15:06:37          6         23 01/02/2010 15:50:20        165         62
     50180 02/02/2010 15:06:37 02/02/2010 15:07:12         23          2 02/02/2010 15:06:37        165         62
     50180 02/02/2010 15:07:12 02/02/2010 15:10:10          2          1 02/02/2010 15:06:37        165         62
     50180 02/02/2010 15:10:10 02/02/2010 15:18:31          1          2 02/02/2010 15:06:37        165         62 (1)
     50180 02/02/2010 15:10:10 02/02/2010 15:18:31          1          2 02/02/2010 15:10:12        203         62 (2)
     50180 02/02/2010 15:18:31 02/02/2010 15:18:35          2          5 02/02/2010 15:10:12        203         62
     50180 02/02/2010 15:18:35 02/02/2010 15:18:37          5          6 02/02/2010 15:10:12        203         62
     50180 02/02/2010 15:18:37 02/02/2010 15:18:50          6         23 02/02/2010 15:10:12        203         62
     50180 02/02/2010 15:18:50 02/02/2010 15:18:53         23          2 02/02/2010 15:18:50        203         62
     50180 02/02/2010 15:18:53 02/02/2010 15:51:01          2          5 02/02/2010 15:18:50        203         62
     50180 02/02/2010 15:51:01 02/02/2010 15:51:07          5          6 02/02/2010 15:18:50        203         62
     50180 02/02/2010 15:51:07 02/02/2010 15:51:17          6         23 02/02/2010 15:18:50        203         62
     50180 02/02/2010 15:51:17                             23            02/02/2010 15:51:17        165         62

En clair, pour chaque ligne de T1 je fais correspondre ou pas la ligne de T2 dont la valeur du champ CHANGE est la plus proche mais inférieure au champ sortie de la table T1 avec T1.REF = T2.REF

(1) et (2) indique une ligne dupliquée de T1

Merci.

**Waldar** · 03/02/2010, 10h13

Utilisez plutôt la balise code (#) que la balise quote pour mettre en forme des données, la première utilisant une police à largeur fixe et conservant les espaces.

C'est excellent de fournir un ensemble de données (et c'est trop rare dès le premier post), par contre quand on arrive à un volume de données qui commence à déborder de la fenêtre c'est encore mieux de fournir les scripts de création de table et les inserts qui vont avec, afin que tout à chacun n'ait qu'un copier / coller à faire pour pouvoir se pencher sur votre problème (là il y a bien dix minutes de saisie pour vos données, c'est un peu décourageant).

Par exemple pour votre deuxième table ça donnerait :

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
create table sfo_t2
(
    change date,
    class  number(3),
    sclass number(2)
);
 
insert into sfo_t2 (change, class, sclass)
select to_date('01/02/2010 13:10:57', 'dd/mm/yyyy hh24:mi:ss'), 164, 21 from dual union all
select to_date('01/02/2010 15:50:20', 'dd/mm/yyyy hh24:mi:ss'), 165, 62 from dual union all
select to_date('02/02/2010 15:06:37', 'dd/mm/yyyy hh24:mi:ss'), 165, 62 from dual union all
select to_date('02/02/2010 15:10:12', 'dd/mm/yyyy hh24:mi:ss'), 203, 62 from dual union all
select to_date('02/02/2010 15:18:50', 'dd/mm/yyyy hh24:mi:ss'), 203, 62 from dual union all
select to_date('02/02/2010 15:51:17', 'dd/mm/yyyy hh24:mi:ss'), 165, 62 from dual;
 
commit;

**sfoggy** · 03/02/2010, 10h55

Voici les requêtes de création

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
42
43
44
45
46
47
48
49
 
CREATE TABLE sfo_t1
(
    ref	   number(5),
    entree date,
    sortie date,
    in_state  number(2),
    to_state  number(2)
);
 
INSERT INTO sfo_t1 (ref, entree, sortie, in_state, to_state)
SELECT 50180, TO_DATE('01/02/2010 13:07:30', 'DD/MM/YYYY HH24:mi:ss'), TO_DATE('01/02/2010 13:07:46', 'DD/MM/YYYY HH24:mi:ss'),1,2 FROM DUAL UNION ALL
SELECT 50180, TO_DATE('01/02/2010 13:07:46', 'DD/MM/YYYY HH24:mi:ss'), TO_DATE('01/02/2010 13:45:45', 'DD/MM/YYYY HH24:mi:ss'),2,5 FROM DUAL UNION ALL
SELECT 50180, TO_DATE('01/02/2010 13:45:45', 'DD/MM/YYYY HH24:mi:ss'), TO_DATE('01/02/2010 15:50:01', 'DD/MM/YYYY HH24:mi:ss'),5,6 FROM DUAL UNION ALL
SELECT 50180, TO_DATE('01/02/2010 15:50:01', 'DD/MM/YYYY HH24:mi:ss'), TO_DATE('01/02/2010 15:50:20', 'DD/MM/YYYY HH24:mi:ss'),6,23 FROM DUAL UNION ALL
SELECT 50180, TO_DATE('01/02/2010 15:50:20', 'DD/MM/YYYY HH24:mi:ss'), TO_DATE('01/02/2010 15:50:23', 'DD/MM/YYYY HH24:mi:ss'),23,2 FROM DUAL UNION ALL
SELECT 50180, TO_DATE('01/02/2010 15:50:23', 'DD/MM/YYYY HH24:mi:ss'), TO_DATE('02/02/2010 14:53:13', 'DD/MM/YYYY HH24:mi:ss'),2,5 FROM DUAL UNION ALL
SELECT 50180, TO_DATE('02/02/2010 14:53:13', 'DD/MM/YYYY HH24:mi:ss'), TO_DATE('02/02/2010 15:05:41', 'DD/MM/YYYY HH24:mi:ss'),5,6 FROM DUAL UNION ALL
SELECT 50180, TO_DATE('02/02/2010 15:05:41', 'DD/MM/YYYY HH24:mi:ss'), TO_DATE('02/02/2010 15:06:37', 'DD/MM/YYYY HH24:mi:ss'),6,23 FROM DUAL UNION ALL
SELECT 50180, TO_DATE('02/02/2010 15:06:37', 'DD/MM/YYYY HH24:mi:ss'), TO_DATE('02/02/2010 15:07:12', 'DD/MM/YYYY HH24:mi:ss'),23,2 FROM DUAL UNION ALL
SELECT 50180, TO_DATE('02/02/2010 15:07:12', 'DD/MM/YYYY HH24:mi:ss'), TO_DATE('02/02/2010 15:10:10', 'DD/MM/YYYY HH24:mi:ss'),2,1 FROM DUAL UNION ALL
SELECT 50180, TO_DATE('02/02/2010 15:10:10', 'DD/MM/YYYY HH24:mi:ss'), TO_DATE('02/02/2010 15:18:31', 'DD/MM/YYYY HH24:mi:ss'),1,2 FROM DUAL UNION ALL
SELECT 50180, TO_DATE('02/02/2010 15:18:31', 'DD/MM/YYYY HH24:mi:ss'), TO_DATE('02/02/2010 15:18:35', 'DD/MM/YYYY HH24:mi:ss'),2,5 FROM DUAL UNION ALL
SELECT 50180, TO_DATE('02/02/2010 15:18:35', 'DD/MM/YYYY HH24:mi:ss'), TO_DATE('02/02/2010 15:18:37', 'DD/MM/YYYY HH24:mi:ss'),5,6 FROM DUAL UNION ALL
SELECT 50180, TO_DATE('02/02/2010 15:18:37', 'DD/MM/YYYY HH24:mi:ss'), TO_DATE('02/02/2010 15:18:50', 'DD/MM/YYYY HH24:mi:ss'),6,23 FROM DUAL UNION ALL
SELECT 50180, TO_DATE('02/02/2010 15:18:50', 'DD/MM/YYYY HH24:mi:ss'), TO_DATE('02/02/2010 15:18:53', 'DD/MM/YYYY HH24:mi:ss'),23,2 FROM DUAL UNION ALL
SELECT 50180, TO_DATE('02/02/2010 15:18:53', 'DD/MM/YYYY HH24:mi:ss'), TO_DATE('02/02/2010 15:51:01', 'DD/MM/YYYY HH24:mi:ss'),2,5 FROM DUAL UNION ALL
SELECT 50180, TO_DATE('02/02/2010 15:51:01', 'DD/MM/YYYY HH24:mi:ss'), TO_DATE('02/02/2010 15:51:07', 'DD/MM/YYYY HH24:mi:ss'),5,6 FROM DUAL UNION ALL
SELECT 50180, TO_DATE('02/02/2010 15:51:07', 'DD/MM/YYYY HH24:mi:ss'), TO_DATE('02/02/2010 15:51:17', 'DD/MM/YYYY HH24:mi:ss'),6,23 FROM DUAL UNION ALL
SELECT 50180, TO_DATE('02/02/2010 15:51:17', 'DD/MM/YYYY HH24:mi:ss'), NULL,23,NULL FROM DUAL;
 
 
CREATE TABLE sfo_t2
(
    ref	   number(5),
    CHANGE date,
    class  number(3),
    sclass number(3)
);
 
INSERT INTO sfo_t2 (ref, CHANGE, class, sclass)
SELECT 50180, to_date('01/02/2010 13:10:57', 'dd/mm/yyyy hh24:mi:ss'), 164, 21 FROM dual union ALL
SELECT 50180, to_date('01/02/2010 15:50:20', 'dd/mm/yyyy hh24:mi:ss'), 165, 62 FROM dual union ALL
SELECT 50180, to_date('02/02/2010 15:06:37', 'dd/mm/yyyy hh24:mi:ss'), 165, 62 FROM dual union ALL
SELECT 50180, to_date('02/02/2010 15:10:12', 'dd/mm/yyyy hh24:mi:ss'), 203, 62 FROM dual union ALL
SELECT 50180, to_date('02/02/2010 15:18:50', 'dd/mm/yyyy hh24:mi:ss'), 203, 62 FROM dual union ALL
SELECT 50180, to_date('02/02/2010 15:51:17', 'dd/mm/yyyy hh24:mi:ss'), 165, 62 FROM DUAL;
 
commit;

**Waldar** · 03/02/2010, 11h01

C'est l'icône juste à droite de celle qui permet de mettre la balise quote.
Sinon vous pouvez l'écrire à la main CODE et /CODE entre crochets.

Edit : apparement vous avez trouvé !
Edit2 : il manque un dernier FROM DUAL sur le second insert.
Edit3 : par contre j'ai supposé que le format de date était DD/MM/YYYY, mais ça signifierait que dans votre première table vous avez des entrées après des sorties ? Le format ne serait-il pas MM/DD/YYYY ?

**sfoggy** · 03/02/2010, 11h24

Je remets les exemples avec dd/mm/yyyy hh24:mi:ss ainsi que la requête de création de celle-ci car les dates de T1 doivent se suivre

on doit avoir :

d1 d2
d2 d3 et ....

Merci.

**sfoggy** · 03/02/2010, 11h37

Les exemples ainsi que le code de création de T1 sont corrects maintenant.
Merci pour vos remarques et votre attention.

**Waldar** · 03/02/2010, 11h58

Il manque encore un null dans le dernier select de la première requête avant le from dual.

Qu'est-ce qui fait que cette ligne-ci se retrouve deux fois :

Code :

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

1
2
     50180 02/02/2010 15:10:10 02/02/2010 15:18:31          1          2 02/02/2010 15:06:37        165         62
     50180 02/02/2010 15:10:10 02/02/2010 15:18:31          1          2 02/02/2010 15:10:12        203         62

Ca ne satisfait pas cette règle-ci :

dont la valeur du champ CHANGE est la plus proche mais inférieure au champ sortie

**sfoggy** · 03/02/2010, 12h24

Ajout des null fait.

Pour ces lignes vous avez raison.
La formulation de la règle n'est pas assez complète pour ce cas et on pourrait trouver n lignes si il y avait plusieurs changements dans T2.

En effet, pour la période et l'état indiqués on constate qu'un changement de classe a eu lieu. C'est pourquoi je dois voir deux lignes qui indiquent que pour cette période et cet état il y eu un changement de classe.

Il est vrai qu'une autre manière de représenter l'information qui peut être plus intéressante serait de diviser la période pour obtenir

Code :

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

1
2
3
 
     50180 02/02/2010 15:10:10 02/02/2010 15:10:12          1          2 02/02/2010 15:06:37        165         62
     50180 02/02/2010 15:10:12 02/02/2010 15:18:31          1          2 02/02/2010 15:10:12        203         62

**Waldar** · 03/02/2010, 14h14

Certes mais pour toutes les autres lignes ce serait pareil, je ne perçois rien de spécial sur ces données qui font qu'elles doivent apparaître deux fois.

Néanmoins voici deux requêtes qui donne, à ce duplica près, le résultat escompté.
La première utilise des agrégats, la seconde des fonctions de fenêtrage :

Code :

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

1
2
3
4
5
6
7
8
9
10
select t1.ref, t1.entree, t1.sortie, t1.in_state, t1.to_state,
       max(t2.change) as change,
       max(t2.class ) keep (dense_rank last order by t2.change asc) as class,
       max(t2.sclass) keep (dense_rank last order by t2.change asc) as sclass
  from sfo_t1 t1
       left outer join sfo_t2 t2
         on t2.ref = t1.ref
        and (t2.change < t1.sortie or t1.sortie is null)
group by t1.ref, t1.entree, t1.sortie, t1.in_state, t1.to_state
order by t1.entree asc;

Code :

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

1
2
3
4
5
6
7
8
9
10
select t1.ref, t1.entree, t1.sortie, t1.in_state, t1.to_state,
       last_value(t2.change ignore nulls) over(partition by t1.ref order by t1.entree asc) as change,
       last_value(t2.class  ignore nulls) over(partition by t1.ref order by t1.entree asc) as class,
       last_value(t2.sclass ignore nulls) over(partition by t1.ref order by t1.entree asc) as sclass
  from sfo_t1 t1
       left outer join sfo_t2 t2
         on t2.ref = t1.ref
        and t2.change >= t1.entree
        and (t2.change < t1.sortie or t1.sortie is null)
order by t1.entree asc;

Ça doit être un bon début pour votre besoin.

**sfoggy** · 03/02/2010, 14h44

Les lignes dupliquées de T1 sont importantes car elles montrent qu'au cours de la période la classe de REF a évolué sans pour autant que son état ait changé.

Je teste.

Merci.

**salim11** · 03/02/2010, 15h26

@ Waldar

Je pense que tu as oublié la clause rows between unbounded preceding and unbounded following dans ta fonction last_value.

Cordialement Salim.

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
 
SELECT empno, ename, sal, deptno,
       LAST_VALUE (sal ignore nulls) OVER (PARTITION BY deptno ORDER BY empno)
                                                                sal_der_empno
  FROM emp
 
 
     EMPNO ENAME             SAL     DEPTNO SAL_DER_EMPNO
---------- ---------- ---------- ---------- -------------
      7782 CLARK            2450         10          2450
      7839 KING             5000         10          5000
      7934 MILLER           1300         10          1300
      7369 SMITH             800         20           800
      7566 JONES            2975         20          2975
      7788 SCOTT            3000         20          3000
      7876 ADAMS            1100         20          1100
      7902 FORD             3000         20          3000
      7499 ALLEN            1600         30          1600
      7521 WARD             1250         30          1250
      7654 MARTIN           1250         30          1250
      7698 BLAKE            2850         30          2850
      7844 TURNER           1500         30          1500
      7900 JAMES             950         30           950
 
 
14 rows selected.

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
 
SELECT empno, ename, sal, deptno,
       LAST_VALUE (sal ignore nulls) OVER (PARTITION BY deptno ORDER BY empno
       rows between unbounded preceding and unbounded following)
                                                                sal_der_empno
  FROM emp
 
 
     EMPNO ENAME             SAL     DEPTNO SAL_DER_EMPNO
---------- ---------- ---------- ---------- -------------
      7782 CLARK            2450         10          1300
      7839 KING             5000         10          1300
      7934 MILLER           1300         10          1300
      7369 SMITH             800         20          3000
      7566 JONES            2975         20          3000
      7788 SCOTT            3000         20          3000
      7876 ADAMS            1100         20          3000
      7902 FORD             3000         20          3000
      7499 ALLEN            1600         30           950
      7521 WARD             1250         30           950
      7654 MARTIN           1250         30           950
      7698 BLAKE            2850         30           950
      7844 TURNER           1500         30           950
      7900 JAMES             950         30           950
 
 
14 rows selected.

**Waldar** · 03/02/2010, 16h29

salim11> Avec cette clause le last_value va en fait se comporter comme un max / keep, et ce n'est pas l'effet recherché, je veux vraiment la valeur précédente (pour combler les vides).

La requête sans last_value donne ceci :

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
SELECT t1.ref, t1.entree, t1.sortie, t1.in_state, t1.to_state,
       t2.CHANGE,
       t2.class,
       t2.sclass
  FROM sfo_t1 t1
       LEFT OUTER JOIN sfo_t2 t2
         ON t2.ref = t1.ref
        AND t2.CHANGE >= t1.entree
        AND (t2.CHANGE < t1.sortie OR t1.sortie IS NULL)
ORDER BY t1.entree ASC;
 
REF	ENTREE			SORTIE			IN	TO	CHANGE			CLASS	SCLASS
50180	01/02/2010 13:07:30	01/02/2010 13:07:46	1	2			
50180	01/02/2010 13:07:46	01/02/2010 13:45:45	2	5	01/02/2010 13:10:57	164	21
50180	01/02/2010 13:45:45	01/02/2010 15:50:01	5	6			
50180	01/02/2010 15:50:01	01/02/2010 15:50:20	6	23			
50180	01/02/2010 15:50:20	01/02/2010 15:50:23	23	2	01/02/2010 15:50:20	165	62
50180	01/02/2010 15:50:23	02/02/2010 14:53:13	2	5			
50180	02/02/2010 14:53:13	02/02/2010 15:05:41	5	6			
50180	02/02/2010 15:05:41	02/02/2010 15:06:37	6	23			
50180	02/02/2010 15:06:37	02/02/2010 15:07:12	23	2	02/02/2010 15:06:37	165	62
50180	02/02/2010 15:07:12	02/02/2010 15:10:10	2	1			
50180	02/02/2010 15:10:10	02/02/2010 15:18:31	1	2	02/02/2010 15:10:12	203	62
50180	02/02/2010 15:18:31	02/02/2010 15:18:35	2	5			
50180	02/02/2010 15:18:35	02/02/2010 15:18:37	5	6			
50180	02/02/2010 15:18:37	02/02/2010 15:18:50	6	23			
50180	02/02/2010 15:18:50	02/02/2010 15:18:53	23	2	02/02/2010 15:18:50	203	62
50180	02/02/2010 15:18:53	02/02/2010 15:51:01	2	5			
50180	02/02/2010 15:51:01	02/02/2010 15:51:07	5	6			
50180	02/02/2010 15:51:07	02/02/2010 15:51:17	6	23			
50180	02/02/2010 15:51:17				23		02/02/2010 15:51:17	165	62

Avec last_value :

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
SELECT t1.ref, t1.entree, t1.sortie, t1.in_state, t1.to_state,
       last_value(t2.CHANGE IGNORE nulls) over(partition BY t1.ref ORDER BY t1.entree ASC) AS CHANGE,
       last_value(t2.class  IGNORE nulls) over(partition BY t1.ref ORDER BY t1.entree ASC) AS class,
       last_value(t2.sclass IGNORE nulls) over(partition BY t1.ref ORDER BY t1.entree ASC) AS sclass
  FROM sfo_t1 t1
       LEFT OUTER JOIN sfo_t2 t2
         ON t2.ref = t1.ref
        AND t2.CHANGE >= t1.entree
        AND (t2.CHANGE < t1.sortie OR t1.sortie IS NULL)
ORDER BY t1.entree ASC;
 
REF	ENTREE			SORTIE			IN	TO	CHANGE			CLASS	SCLASS
50180	01/02/2010 13:07:30	01/02/2010 13:07:46	1	2			
50180	01/02/2010 13:07:46	01/02/2010 13:45:45	2	5	01/02/2010 13:10:57	164	21
50180	01/02/2010 13:45:45	01/02/2010 15:50:01	5	6	01/02/2010 13:10:57	164	21
50180	01/02/2010 15:50:01	01/02/2010 15:50:20	6	23	01/02/2010 13:10:57	164	21
50180	01/02/2010 15:50:20	01/02/2010 15:50:23	23	2	01/02/2010 15:50:20	165	62
50180	01/02/2010 15:50:23	02/02/2010 14:53:13	2	5	01/02/2010 15:50:20	165	62
50180	02/02/2010 14:53:13	02/02/2010 15:05:41	5	6	01/02/2010 15:50:20	165	62
50180	02/02/2010 15:05:41	02/02/2010 15:06:37	6	23	01/02/2010 15:50:20	165	62
50180	02/02/2010 15:06:37	02/02/2010 15:07:12	23	2	02/02/2010 15:06:37	165	62
50180	02/02/2010 15:07:12	02/02/2010 15:10:10	2	1	02/02/2010 15:06:37	165	62
50180	02/02/2010 15:10:10	02/02/2010 15:18:31	1	2	02/02/2010 15:10:12	203	62
50180	02/02/2010 15:18:31	02/02/2010 15:18:35	2	5	02/02/2010 15:10:12	203	62
50180	02/02/2010 15:18:35	02/02/2010 15:18:37	5	6	02/02/2010 15:10:12	203	62
50180	02/02/2010 15:18:37	02/02/2010 15:18:50	6	23	02/02/2010 15:10:12	203	62
50180	02/02/2010 15:18:50	02/02/2010 15:18:53	23	2	02/02/2010 15:18:50	203	62
50180	02/02/2010 15:18:53	02/02/2010 15:51:01	2	5	02/02/2010 15:18:50	203	62
50180	02/02/2010 15:51:01	02/02/2010 15:51:07	5	6	02/02/2010 15:18:50	203	62
50180	02/02/2010 15:51:07	02/02/2010 15:51:17	6	23	02/02/2010 15:18:50	203	62
50180	02/02/2010 15:51:17				23		02/02/2010 15:51:17	165	62

Avec ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING :

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
SELECT t1.ref, t1.entree, t1.sortie, t1.in_state, t1.to_state,
       last_value(t2.CHANGE IGNORE nulls) over(partition BY t1.ref ORDER BY t1.entree ASC rows BETWEEN unbounded preceding AND unbounded following) AS CHANGE,
       last_value(t2.class  IGNORE nulls) over(partition BY t1.ref ORDER BY t1.entree ASC rows BETWEEN unbounded preceding AND unbounded following) AS class,
       last_value(t2.sclass IGNORE nulls) over(partition BY t1.ref ORDER BY t1.entree ASC rows BETWEEN unbounded preceding AND unbounded following) AS sclass
  FROM sfo_t1 t1
       LEFT OUTER JOIN sfo_t2 t2
         ON t2.ref = t1.ref
        AND t2.CHANGE >= t1.entree
        AND (t2.CHANGE < t1.sortie OR t1.sortie IS NULL)
ORDER BY t1.entree ASC;
 
REF	ENTREE			SORTIE			IN	TO	CHANGE			CLASS	SCLASS
50180	01/02/2010 13:07:30	01/02/2010 13:07:46	1	2	02/02/2010 15:51:17	165	62
50180	01/02/2010 13:07:46	01/02/2010 13:45:45	2	5	02/02/2010 15:51:17	165	62
50180	01/02/2010 13:45:45	01/02/2010 15:50:01	5	6	02/02/2010 15:51:17	165	62
50180	01/02/2010 15:50:01	01/02/2010 15:50:20	6	23	02/02/2010 15:51:17	165	62
50180	01/02/2010 15:50:20	01/02/2010 15:50:23	23	2	02/02/2010 15:51:17	165	62
50180	01/02/2010 15:50:23	02/02/2010 14:53:13	2	5	02/02/2010 15:51:17	165	62
50180	02/02/2010 14:53:13	02/02/2010 15:05:41	5	6	02/02/2010 15:51:17	165	62
50180	02/02/2010 15:05:41	02/02/2010 15:06:37	6	23	02/02/2010 15:51:17	165	62
50180	02/02/2010 15:06:37	02/02/2010 15:07:12	23	2	02/02/2010 15:51:17	165	62
50180	02/02/2010 15:07:12	02/02/2010 15:10:10	2	1	02/02/2010 15:51:17	165	62
50180	02/02/2010 15:10:10	02/02/2010 15:18:31	1	2	02/02/2010 15:51:17	165	62
50180	02/02/2010 15:18:31	02/02/2010 15:18:35	2	5	02/02/2010 15:51:17	165	62
50180	02/02/2010 15:18:35	02/02/2010 15:18:37	5	6	02/02/2010 15:51:17	165	62
50180	02/02/2010 15:18:37	02/02/2010 15:18:50	6	23	02/02/2010 15:51:17	165	62
50180	02/02/2010 15:18:50	02/02/2010 15:18:53	23	2	02/02/2010 15:51:17	165	62
50180	02/02/2010 15:18:53	02/02/2010 15:51:01	2	5	02/02/2010 15:51:17	165	62
50180	02/02/2010 15:51:01	02/02/2010 15:51:07	5	6	02/02/2010 15:51:17	165	62
50180	02/02/2010 15:51:07	02/02/2010 15:51:17	6	23	02/02/2010 15:51:17	165	62
50180	02/02/2010 15:51:17				23		02/02/2010 15:51:17	165	62

sfoggy> Justement, sur la ligne dont on parle, il n'y a pas plus de changement sur les les autres lignes (la 2, 4, 8 par exemple).
Au niveau logique, qu'essayez-vous en fait d'obtenir ?
Un chevauchement des périodes entre T1 et T2 ?

**salim11** · 03/02/2010, 17h11

Envoyé par Waldar

La première utilise des agrégats, la seconde des fonctions de fenêtrage :

Code :

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

1
2
3
4
5
6
7
8
9
10
select t1.ref, t1.entree, t1.sortie, t1.in_state, t1.to_state,
       max(t2.change) as change,
       max(t2.class ) keep (dense_rank last order by t2.change asc) as class,
       max(t2.sclass) keep (dense_rank last order by t2.change asc) as sclass
  from sfo_t1 t1
       left outer join sfo_t2 t2
         on t2.ref = t1.ref
        and (t2.change < t1.sortie or t1.sortie is null)
group by t1.ref, t1.entree, t1.sortie, t1.in_state, t1.to_state
order by t1.entree asc;

Code :

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

1
2
3
4
5
6
7
8
9
10
select t1.ref, t1.entree, t1.sortie, t1.in_state, t1.to_state,
       last_value(t2.change ignore nulls) over(partition by t1.ref order by t1.entree asc) as change,
       last_value(t2.class  ignore nulls) over(partition by t1.ref order by t1.entree asc) as class,
       last_value(t2.sclass ignore nulls) over(partition by t1.ref order by t1.entree asc) as sclass
  from sfo_t1 t1
       left outer join sfo_t2 t2
         on t2.ref = t1.ref
        and t2.change >= t1.entree
        and (t2.change < t1.sortie or t1.sortie is null)
order by t1.entree asc;

Ça doit être un bon début pour votre besoin.

Salut Waldar,

Je voulais dire, que les deux requêtes ne sont pas équivalentes.

Cordialement Salim.

**Waldar** · 03/02/2010, 17h56

Attention ce n'est pas exactement la même jointure entre les deux requêtes que j'ai proposées.

En considérant que l'ordre des données de T1 sont dépendantes du couple (ref, entree), je pense que les deux requêtes sont équivalentes.

Enfin oui, ça dépend des données et effectivement en fonction de celles-ci ces deux requêtes pourraient fournir des résultats différents.

Sur l'exemple initial, c'est bien pareil !
Mais c'est juste une base de réflexion.

**sfoggy** · 04/02/2010, 09h20

Pour la ligne 2 en effet il devrait y avoir une duplication des données avec la date de la ligne 1 (que je dois récupérer d'une autre table ). Je connais en théorie et en pratique la date qui doit être égale à "entree", mais je ne sais de manière simple et fiable déterminer pour le moment class et sclass.

Cependant considérant que je connaisse les valeurs de class et sclass, les lignes 1 et 2 devrait être en réalité transformées :

Code :

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

1
2
3
4
 
50180 01/02/2010 13:07:30 01/02/2010 13:07:46          1          2 01/02/2010 13:07:30	    165		62
50180 01/02/2010 13:07:46 01/02/2010 13:45:45          2          5 01/02/2010 13:07:30	    165		62
50180 01/02/2010 13:07:46 01/02/2010 13:45:45          2          5 01/02/2010 13:10:57        164         21

Pour les lignes 4 et 8, elles sont comme il faut, règle inférieure à date de sortie.
Je souhaite donc obtenir, en plus des informations récupérées par la requête proposée les chevauchements entre T1 et T2 ( cas où le changement T2 a eu lieu entre entree et sortie de T1)

Merci

**sfoggy** · 05/02/2010, 10h53

C'est bon.
Merci pour votre aide

Requête de sélection : casse tête 2

SQL Oracle

Discussions similaires

Partager

Partager