Problème SQL de base

**dbaenbois** · 07/07/2009, 11h11

Bonjour,

J'ai la table suivante :

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
MATABLE (
NUMCOMPTE VARCHAR2(20)
DATEVAL DATE
VALEUR NUMBER
)
 
NUMCOMPTE            DATEVAL                 VALEUR
-------------------- ------------------- ----------
007                  08/05/2009 10:46:15         10
007                  28/05/2009 10:46:15         21
007                  17/06/2009 10:46:15         13
007                  07/07/2009 10:46:15         53
008                  18/05/2009 10:46:15         43
008                  07/06/2009 10:46:15         32
008                  27/06/2009 10:46:15         56
008                  07/07/2009 10:46:15          5

Je souhaiterai avoir le champ 'valeur' pour min(DATEVAL) et max(DATEVAL)
Pour l'un ou l'autre je ferai bien comme ca :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT a.NUMCOMPTE, a.DATEVAL, a.VALEUR 
FROM MATABLE a,
(
SELECT NUMCOMPTE, min(DATEVAL) DATEVALMIN
FROM MATABLE  
group by NUMCOMPTE
) b
where a.DATEVAL = b.DATEVALMIN
 
NUMCOMPTE            DATEVAL                 VALEUR
-------------------- ------------------- ----------
007                  08/05/2009 10:46:15         10
008                  18/05/2009 10:46:15         43

La récupération de la valeur min est-elle correcte, ou existe-t-il une manière plus élégante, évidente ?

Existe-t-il un moyen de récupérer le champ 'VALEUR' pour la min et le max de ma dateval en une seule requête ?

Merci à vous.

**Waldar** · 07/07/2009, 11h39

Je pense que votre problème est mal formulé.

Vous voulez connaître par compte les valeurs aux dates minimale et maximale ?

Est-ce que vous souhaitez obtenir ceci :

Code :

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

1
2
3
4
5
6
NUMCOMPTE            DATEVAL                 VALEUR
-------------------- ------------------- ----------
007                  08/05/2009 10:46:15         10
007                  07/07/2009 10:46:15         53
008                  18/05/2009 10:46:15         43
008                  07/07/2009 10:46:15          5

Ou ceci :

Code :

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

1
2
3
4
NUMCOMPTE     DATEVAL_MIN VALEUR_MIN DATEVAL_MAX VALEUR_MAX 
------------- ----------- ---------- ----------- ----------
007           08/05/2009          10 07/07/2009          53
008           18/05/2009          43 07/07/2009           5

Ou encore autre chose ?

**dbaenbois** · 07/07/2009, 15h02

Bonjour,

Je souhaiterai obtenir ceci :

Code :

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

1
2
3
4
5
6
7
 
NUMCOMPTE            DATEVAL                 VALEUR
-------------------- ------------------- ----------
007                  08/05/2009 10:46:15         10
007                  07/07/2009 10:46:15         53
008                  18/05/2009 10:46:15         43
008                  07/07/2009 10:46:15          5

Effectivement, après relecture cela n'était pas clair, désolé.

Merci à vous.

**orafrance** · 07/07/2009, 15h21

Ca ?

Code :

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

1
2
3
4
5
6
7
SELECT NUMCOMPTE, min(DATEVAL) DATEVALMIN
FROM MATABLE  
GROUP BY NUMCOMPTE
UNION ALL 
SELECT NUMCOMPTE, max(DATEVAL) DATEVALMAX
FROM MATABLE  
GROUP BY NUMCOMPTE

Edit : oups... il manque valeur

**dbaenbois** · 07/07/2009, 15h31

Bonjour,

Pas tout à fait orafrance....

Il me faut le champ 'VALEUR'

Pour info :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE TABLE MATABLE (
NUMCOMPTE VARCHAR2(20),
DATEVAL DATE,
VALEUR NUMBER
);
 
insert into MATABLE  values ('007',sysdate-60,10);
insert into MATABLE  values ('007',sysdate-40,21);
insert into MATABLE  values ('007',sysdate-20,13);
insert into MATABLE  values ('007',sysdate,53);
insert into MATABLE  values ('008',sysdate-50,43);
insert into MATABLE  values ('008',sysdate-30,32);
insert into MATABLE  values ('008',sysdate-10,56);
insert into MATABLE  values ('008',sysdate,5);

Merci de votre contribution.

**orafrance** · 07/07/2009, 15h35

Et toi il ne te manque que le MAX

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
SELECT a.NUMCOMPTE, a.DATEVAL, a.VALEUR
FROM MATABLE a,
(
SELECT NUMCOMPTE, min(DATEVAL) DATEVALMIN, max(DATEVAL) DATEVALMAX
FROM MATABLE
GROUP BY NUMCOMPTE
) b
WHERE a.DATEVAL IN (b.DATEVALMIN, b.DATEVALMAX)
  AND a.NUMCOMPTE = b.NUMCOMPTE
ORDER BY 1,2

Attention à ne pas oublier la jointure sur NUMCOMPTE

**salim11** · 07/07/2009, 15h43

Salut,

Essaie cette requête.

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
 
SELECT   a.numcompte, MIN (a.dateval) dateval,
         MIN (a.valeur)KEEP (DENSE_RANK FIRST ORDER BY a.dateval) valeur
    FROM matable a
GROUP BY a.numcompte
UNION
SELECT   a.numcompte, MAX (a.dateval) dateval,
         MAX (a.valeur)KEEP (DENSE_RANK FIRST ORDER BY a.dateval DESC) valeur
    FROM matable a
GROUP BY a.numcompte;
 
 
NUMCOMPTE            DATEVAL        VALEUR
-------------------- ---------- ----------
007                  2009-05-08         10
007                  2009-07-07         53
008                  2009-05-18         43
008                  2009-07-07          5
 
 
4 rows selected.

**dbaenbois** · 07/07/2009, 15h47

Merci à vous,

Les 2 requêtes marchent...

Il ne me reste plus qu'à les comprendre...

Encore merci pour votre aide !

**mnitu** · 07/07/2009, 15h57

Un seul passage de la table matable suffit

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
 
Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0 
Connected as mni
 
SQL> 
SQL> With pivot As (
  2    Select level x from dual connect by level <= 2
  3  )
  4  Select numcompte,
  5         Case When x = 1 Then dmin Else Dmax End As Dat,
  6         Case When x = 1 Then valmin Else Valmax End As Valuer
  7    From (
  8          Select numcompte, min(dateval) dmin, min(valeur) KEEP (DENSE_RANK FIRST ORDER BY dateval) valmin,
  9                 max(dateval) dmax, max(valeur) KEEP (DENSE_RANK LAST ORDER BY dateval) valmax
 10            From matable
 11          Group by numcompte
 12  ), pivot
 13  order by numcompte, x
 14  /
 
NUMCOMPTE            DAT             VALUER
-------------------- ----------- ----------
007                  08/05/2009          10
007                  07/07/2009          53
008                  18/05/2009          43
008                  07/07/2009           5
 
SQL>

**orafrance** · 07/07/2009, 15h58

UNION ALL plutôt qu'UNION devrait suffir dans la proposition de salim, par contre coté parf j'suis pas convaincu.

Si il n'y a pas d'index elle est probablement plus intéressante en revanche

Edit :

mnitu, ça me donnes un coup de vieux à chaque fois que je vois ce genre de requête

**salim11** · 07/07/2009, 15h59

Salut,

Cette requête avec un full scan table, meilleure en performance

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
 
SELECT numcompte, dateval, valeur
  FROM (SELECT numcompte, dateval, valeur,
               ROW_NUMBER () OVER (PARTITION BY numcompte ORDER BY dateval)
                                                                       rn_min,
               ROW_NUMBER () OVER (PARTITION BY numcompte ORDER BY dateval DESC)
                                                                       rn_max
          FROM matable)
 WHERE rn_min = 1 OR rn_max = 1
 
NUMCOMPTE            DATEVAL        VALEUR
-------------------- ---------- ----------
007                  2009-05-08         10
007                  2009-07-07         53
008                  2009-05-18         43
008                  2009-07-07          5
 
 
4 rows selected.

**laurentschneider** · 07/07/2009, 16h23

Envoyé par salim11

Cette requête avec un full scan table, meilleure en performance

parfois ... mais rien n'est sûr! le row_number risque d'être plus gourmant en cpu qu'un bon vieux EXISTS, donc je dirais qu'il faut tester au cas par cas

Code :

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

1
2
3
INSERT INTO matable
select '008', SYSDATE - 10, rownum from dual connect by level<1000000;
commit;

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
  SELECT numcompte, dateval, valeur
FROM (SELECT numcompte, dateval, valeur,
ROW_NUMBER () OVER (PARTITION BY numcompte ORDER BY dateval) rn_min,
ROW_NUMBER () OVER (PARTITION BY numcompte ORDER BY dateval DESC)rn_max
FROM matable)
WHERE rn_min = 1 OR rn_max = 1
 
NUMCOMPTE            DATEVAL       VALEUR
-------------------- --------- ----------
007                  08-MAY-09         10
007                  07-JUL-09         53
008                  18-MAY-09         43
008                  07-JUL-09          5
 
Elapsed: 00:00:03.43

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
SELECT *
FROM matable a
WHERE EXISTS (SELECT 1
                    FROM matable b
                   WHERE a.numcompte = b.numcompte
                  HAVING a.dateval IN (MAX (b.dateval), MIN (b.dateval)))
 
NUMCOMPTE            DATEVAL       VALEUR
-------------------- --------- ----------
007                  08-MAY-09         10
007                  07-JUL-09         53
008                  18-MAY-09         43
008                  07-JUL-09          5
 
Elapsed: 00:00:02.15

**salim11** · 07/07/2009, 16h32

Envoyé par laurentschneider

parfois ... mais rien n'est sûr! le row_number risque d'être plus gourmant en cpu qu'un bon vieux EXISTS, donc je dirais qu'il faut tester au cas par cas

T'as raison Laurent.

J'ai remarqué que la requête de mnitu qui consomme moins de temps.
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
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
69
70
71
72
73
74
75
76
 
SQL> SELECT numcompte, dateval, valeur
  2  FROM (SELECT numcompte, dateval, valeur,
  3  ROW_NUMBER () OVER (PARTITION BY numcompte ORDER BY dateval) rn_min,
  4  ROW_NUMBER () OVER (PARTITION BY numcompte ORDER BY dateval DESC)rn_max
  5  FROM matable)
  6  WHERE rn_min = 1 OR rn_max = 1
  7  /
 
NUMCOMPTE            DATEVAL        VALEUR
-------------------- ---------- ----------
007                  2009-05-08         10
007                  2009-07-07         53
008                  2009-05-18         43
008                  2009-07-07          5
 
Ecoulé : 00 :00 :07.68
SQL> SELECT *
  2  FROM matable a
  3  WHERE EXISTS (SELECT 1
  4                      FROM matable b
  5                     WHERE a.numcompte = b.numcompte
  6                    HAVING a.dateval IN (MAX (b.dateval), MIN (b.dateval)))
  7  /
 
NUMCOMPTE            DATEVAL        VALEUR
-------------------- ---------- ----------
007                  2009-05-08         10
007                  2009-07-07         53
008                  2009-05-18         43
008                  2009-07-07          5
 
Ecoulé : 00 :00 :03.17
SQL> SELECT   a.numcompte, MIN (a.dateval) dateval,
  2           MIN (a.valeur)KEEP (DENSE_RANK FIRST ORDER BY a.dateval) valeur
  3      FROM matable a
  4  GROUP BY a.numcompte
  5  UNION
  6  SELECT   a.numcompte, MAX (a.dateval) dateval,
  7           MAX (a.valeur)KEEP (DENSE_RANK FIRST ORDER BY a.dateval DESC) valeur
  8      FROM matable a
  9  GROUP BY a.numcompte;
 
NUMCOMPTE            DATEVAL        VALEUR
-------------------- ---------- ----------
007                  2009-05-08         10
007                  2009-07-07         53
008                  2009-05-18         43
008                  2009-07-07          5
 
Ecoulé : 00 :00 :01.34
SQL> WITH pivot AS (
  2        SELECT level x FROM dual connect BY level <= 2
  3      )
  4      SELECT numcompte,
  5             Case When x = 1 Then dmin Else Dmax End AS Dat,
  6             Case When x = 1 Then valmin Else Valmax End AS Valuer
  7        FROM (
  8             SELECT numcompte, min(dateval) dmin, min(valeur) KEEP (DENSE_RANK FIRST ORDER BY dat
eval) valmin,
  9                     max(dateval) dmax, max(valeur) KEEP (DENSE_RANK LAST ORDER BY dateval) valma
x
 10               FROM matable
 11             GROUP BY numcompte
 12    ), pivot
 13     ORDER BY numcompte, x;
 
NUMCOMPTE            DAT            VALUER
-------------------- ---------- ----------
007                  2009-05-08         10
007                  2009-07-07         53
008                  2009-05-18         43
008                  2009-07-07          5
 
Ecoulé : 00 :00 :01.21
SQL>

**dbaenbois** · 07/07/2009, 17h12

... pas mal les gars ... je vois qu'il y a toujours plusieurs solutions à un problème. Et que cela mérite d'être potassé...

Dernière petite question, sans vouloir abuser, comment pourrais-je faire pour avoir le résultat suivant :

Code :

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

1
2
3
4
5
 
NUMCOMPTE            DATEVALMIN VALEURMIN DATEVALMAX VALEURMAX 
-------------------- ---------- ---------- ---------- ----------
007                  2009-05-08         10 2009-07-07 53
008                  2009-05-18         43 2009-07-07  5

Merci à vous.

**orafrance** · 07/07/2009, 17h18

Très simple

Code :

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

1
2
3
SELECT NUMCOMPTE, MIN(DATEVAL) DATEVALMIN ,MIN(VALEUR) VALEURMIN,  MAX(DATEVAL) DATEVALMAX ,MAX(VALEUR) VALEURMAX
FROM MATABLE
GROUP BY NUMCOMPTE

Edit : ha non, pardon, j'avais pas vérifier le résultat, VALEURMIN = valeur de la date mini

**salim11** · 07/07/2009, 17h19

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
 
SELECT   a.numcompte, MIN (a.dateval) dateval_min,
         MIN (a.valeur)KEEP (DENSE_RANK FIRST ORDER BY a.dateval) valeur_min,
          MAX (a.dateval) dateval_max,
         MAX (a.valeur)KEEP (DENSE_RANK FIRST ORDER BY a.dateval DESC) valeur_max
    FROM matable a
GROUP BY a.numcompte
 
 
NUMCOMPTE            DATEVAL_MI VALEUR_MIN DATEVAL_MA VALEUR_MAX
-------------------- ---------- ---------- ---------- ----------
007                  2009-05-08         10 2009-07-07         53
008                  2009-05-18         43 2009-07-07          5
 
 
2 rows selected.

**salim11** · 07/07/2009, 17h21

Envoyé par orafrance

Très simple

Code :

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

1
2
3
SELECT NUMCOMPTE, MIN(DATEVAL) DATEVALMIN ,MIN(VALEUR) VALEURMIN,  MAX(DATEVAL) DATEVALMAX ,MAX(VALEUR) VALEURMAX
FROM MATABLE
GROUP BY NUMCOMPTE