Problème de bouclage dans une requête

Version imprimable

18/11/2008, 16h45
lbar012001

Problème de bouclage dans une requête

bonjour,
je dois extraire des données dans une table très grosse (+120 000 000 enr) et je souhaite limiter le volume extrait de cette façon
ma structure (simplifiée)
UNIT - DATE - LIEU
X - 15/10 - A
X - 10/10 - A
X - 05/10 - A
X - 08/09 - B
X - 02/09 - C
X - 05/08 - C
X - 02/07 - A
X - 06/07 - A
Y - 15/10 - B
Y - 08/10 - B
Y - 15/09 - A

Je souhaite récupérer les enregistrements ou le dernier lieu est identique par UNIT, donc les 3 plus récents A uniquement dans le cas de la UNIT X (pas les vieux A de juillet) et les 2 plus récents LIEU B de la UNIT Y, bref des que le LIEU change, je stoppe et passe à la UNIT suivante

et je n'y arrive pas

si vous savez, merci par avance

cdlt

laurent

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
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
 
 
SQL*Plus: Release 10.1.0.4.2 - Production on Mar. Nov. 18 11:16:09 2008
 
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
 
 
Connecté à :
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
 
SQL> /* Formatted on 2008/11/18 11:15 (Formatter Plus v4.8.8) */
SQL> WITH t AS
  2       (SELECT 'X' unit, '15/10' dt, 'A' lieu
  3          FROM DUAL
  4        UNION ALL
  5        SELECT 'X', '10/10', 'A'
  6          FROM DUAL
  7        UNION ALL
  8        SELECT 'X', '05/10', 'A'
  9          FROM DUAL
 10        UNION ALL
 11        SELECT 'X', '08/09', 'B'
 12          FROM DUAL
 13        UNION ALL
 14        SELECT 'X', '02/09', 'C'
 15          FROM DUAL
 16        UNION ALL
 17        SELECT 'X', '05/08', 'C'
 18          FROM DUAL
 19        UNION ALL
 20        SELECT 'X', '02/07', 'A'
 21          FROM DUAL
 22        UNION ALL
 23        SELECT 'X', '06/07', 'A'
 24          FROM DUAL
 25        UNION ALL
 26        SELECT 'Y', '15/10', 'B'
 27          FROM DUAL
 28        UNION ALL
 29        SELECT 'Y', '08/10', 'B'
 30          FROM DUAL
 31        UNION ALL
 32        SELECT 'Y', '15/09', 'A'
 33          FROM DUAL)
 34  SELECT *
 35    FROM (SELECT   t.*,
 36                   LAG (lieu) OVER (PARTITION BY unit ORDER BY TO_DATE
 37                                                               (dt,
 38                                                                'dd/mm'
 39                                                               ) DESC) suivant,
 40                   ROW_NUMBER () OVER (PARTITION BY unit ORDER BY TO_DATE
 41                                                                    (dt,
 42                                                                     'dd/mm'
 43                                                                    ) DESC) rn
 44              FROM t
 45          ORDER BY unit)
 46   WHERE rn <= 3 AND (lieu = suivant OR suivant IS NULL);
 
U DT    L S         RN
- ----- - - ----------
X 15/10 A            1
X 10/10 A A          2
X 05/10 A A          3
Y 15/10 B            1
Y 08/10 B B          2
 
SQL>

Salut Salim11,

En comparant rn avec un rn(unit,lieu), c'a à l'air un peu plus générique :

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
SQL> with t as (select 'X' unit, '15/10' dat, 'A' lieu from dual
  2  union select 'X' unit, '15/10' dat, 'A' lieu from dual
  3  union select 'X' unit, '10/10' dat, 'A' lieu from dual
  4  union select 'X' unit, '05/10' dat, 'A' lieu from dual
  5  union select 'X' unit, '08/09' dat, 'B' lieu from dual
  6  union select 'X' unit, '02/09' dat, 'C' lieu from dual
  7  union select 'X' unit, '05/08' dat, 'C' lieu from dual
  8  union select 'X' unit, '02/07' dat, 'A' lieu from dual
  9  union select 'X' unit, '06/07' dat, 'A' lieu from dual
 10  union select 'Y' unit, '15/10' dat, 'B' lieu from dual
 11  union select 'Y' unit, '08/10' dat, 'B' lieu from dual
 12  union select 'Y' unit, '15/09' dat, 'A' lieu from dual)
 13  select * from (
 14  select t.*,
 15         LAG (lieu) OVER (PARTITION BY unit ORDER BY TO_DATE(dat,'dd/mm') DESC) suivant,
 16         ROW_NUMBER () OVER (PARTITION BY unit ORDER BY TO_DATE(dat,'dd/mm') DESC) rn,
 17         ROW_NUMBER () OVER (PARTITION BY unit,lieu ORDER BY TO_DATE(dat,'dd/mm') DESC) rn2
 18  FROM t
 19  ORDER BY unit
 20  )
 21  where rn=rn2 and (lieu=suivant or suivant is null);
 
U DAT   L S         RN        RN2
- ----- - - ---------- ----------
X 15/10 A            1          1
X 10/10 A A          2          2
X 05/10 A A          3          3
Y 15/10 B            1          1
Y 08/10 B B          2          2

Mais merci pour ta soluce, j'étais en train de me misérer avec dense_rank :aie:

18/11/2008, 18h37
salim11

Citation:

Envoyé par skuatamad

Salut Salim11,

En comparant rn avec un rn(unit,lieu), c'a à l'air un peu plus générique :
:

Oui t'as raison, j'ai lu l’énonce rapidement, j'ai pensé qu'il cherche uniquement les trois premiers :king:
19/11/2008, 08h28
lbar012001

merci
ne peut on pas faire cela dans un SELECT classique ?
mes connaissances en SQL sont limitées et je vais vite me noyer
et le volumes de données à balayer est très important
a+
laurent
19/11/2008, 08h42
lbar012001

sinon, ce qui pourrait peut etre m' aider si c'est plus simple, ce serait d'arriver à générer dans mon select un champ calculé de type ROWNUM mais qui repartirait à 1 à chaque nouveau UNIT et après je me débrouille.
19/11/2008, 09h25
fatsora

SELECT *
FROM
(SELECT unit,
date ,
lieu,
row_number() over(partition BY unit ORDER BY date desc) RN
FROM ma_grosse_table

)
WHERE rn <= 3;
19/11/2008, 10h15
lbar012001

merci
ça m'a l'air pas mal comme soluce
je vais essayer d'adapter ça à mon cas
cdlt
laurent
19/11/2008, 11h12
lbar012001

critère supplémentaire :

comment rajouter un critère de date sur le rang 1 uniquement du type
si rn = 1 alors date between 1/10 and 31/10
pour les x rang suivants, pas de critère

merci encore
laurent