Group By selon un tri

**jedoo** · 31/07/2007, 14h08

Bonjour,

imaginons une table T_VENTE :

Code :

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

1
2
3
4
5
6
7
8
9
 
DATEVENTE   QTEVENDUE  PRIXVENTE
----------  ---------  ---------
02/07/2007          4         10
05/07/2007          6         10
14/07/2007          7         11
17/07/2007          3         11
20/07/2007          5         10
23/07/2007          4         10

On constate que le prix est passé à 11 le 14/07 mais qu'il est revenu à 10 le 20/07.
Je veux faire une requête pour surveiller l'évolution du prix de vente dans le temps.

Si je fais :

Code :

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

1
2
3
4
5
6
7
8
 
SELECT   MIN(DATEVENTE)  DU,
         MAX(DATEVENTE)  AU,
         SUM(QTEVENDUE)  QTE,
         PRIXVENTE       PRIX
FROM     T_VENTE
GROUP BY PRIXVENTE
ORDER BY MIN(DATEVENTE) DESC

J'obtiendrais :

Code :

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

1
2
3
4
5
 
DU          AU          QTE  PRIX
----------  ----------  ---  ----
14/07/2007  17/07/2007   10    11
02/07/2007  23/07/2007   19    10

Mais ce résultat est faux !
Ca n'est pas l'évolution du prix de vente.

Ce que j'aurais voulu c'est :

Code :

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

1
2
3
4
5
6
 
DU          AU          QTE  PRIX
----------  ----------  ---  ----
20/07/2007  23/07/2007    9    10
14/07/2007  17/07/2007   10    11
02/07/2007  05/07/2007   10    10

Y a-t-il une requête qui donnerait ce résultat ?

**McM** · 31/07/2007, 14h54

Oui (avec les fonctions analytiques), mais pas de version, pas de réponse.

**jedoo** · 31/07/2007, 15h02

Ah oui désolé !

Ma version : Oracle8i Enterprise Edition Release 8.1.7.4.0

**McM** · 31/07/2007, 15h33

Donc pas de fonction analytique. Ca va être plus compliqué

Le with c'est pour me créer un jeu de donnée (c'est du 9i).
Occupes toi du SELECT.

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
WITH tvente AS 
	(SELECT TO_DATE('02/07/2007', 'DD/MM/RRRR') AS dte, 
			4 AS qte, 10 AS px FROM dual
	UNION ALL
SELECT TO_DATE('05/07/2007', 'DD/MM/RRRR'), 6, 10 FROM dual
	UNION ALL
SELECT TO_DATE('14/07/2007', 'DD/MM/RRRR'), 7, 11 FROM dual
	UNION ALL
SELECT TO_DATE('17/07/2007', 'DD/MM/RRRR'), 3, 11 FROM dual
	UNION ALL
SELECT TO_DATE('20/07/2007', 'DD/MM/RRRR'), 5, 10 FROM dual
	UNION ALL
SELECT TO_DATE('23/07/2007', 'DD/MM/RRRR'), 4, 10 FROM dual
)
SELECT dte_max, px, SUM(qte), MIN(dte)
FROM (
	SELECT dte, qte, px, (SELECT MAX(dte) FROM tvente t2
			WHERE t2.px = t1.px AND t2.dte >= t1.dte
			AND NOT EXISTS (SELECT 1 FROM tvente t3 WHERE t3.dte BETWEEN t1.dte AND t2.dte
							AND px <> t2.px)
			) AS dte_max
	FROM tvente t1
	ORDER BY dte
	)
GROUP BY dte_max, px
 
DTE_MAX		PX	SUM(QTE)MIN(DTE)
05/07/2007	10	10	02/07/2007
17/07/2007	11	10	14/07/2007
23/07/2007	10	9	20/07/2007

**pifor** · 31/07/2007, 15h39

Donc pas de fonction analytique

Les fonctions analytiques existent bien depuis la version 8.1.6 au moins.

J'ai une demi-solution uniquement avec SQL*Plus et qui doit marcher même avec Oracle 6

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
dev001> break on pv;
dev001> compute sum of qv on pv;
dev001> select * from t;
 
DV                 QV         PV
---------- ---------- ----------
02/07/2007          4         10
05/07/2007          6
           ---------- **********
                   10 sum
14/07/2007          7         11
17/07/2007          3
           ---------- **********
                   10 sum
20/07/2007          5         10
23/07/2007          4
           ---------- **********
 
DV                 QV         PV
---------- ---------- ----------
                    9 sum
 
6 rows selected.

**McM** · 31/07/2007, 15h48

Envoyé par pifor

Les fonctions analytiques existent bien depuis la version 8.1.6 au moins.

Désolé, j'étais persuadé que c'était en 9.. j'ai revérifié sur le tuto de lalystar et tu as tout a fait raison..

**Bruno2r** · 31/07/2007, 15h52

Le résultat obtenu correspond parfaitement au souhait de jedoo
DTE_MAX PX SUM(QTE)MIN(DTE)
05/07/2007 10 10 02/07/2007
17/07/2007 11 10 14/07/2007
23/07/2007 10 9 20/07/2007

Une question si on voulait une évolution des prix sans trous
DTE_MAX PX SUM(QTE)MIN(DTE)
14/07/2007 10 10 02/07/2007
20/07/2007 11 10 14/07/2007
23/07/2007 10 9 20/07/2007

sans les fonctions analytiques de 10 g ?
et avec ?

**Garuda** · 31/07/2007, 15h53

Envoyé par pifor

Les fonctions analytiques existent bien depuis la version 8.1.6 au moins.

Pas sur ! Oui en Entreprise, non en standard !

**McM** · 31/07/2007, 16h04

Envoyé par BRUNO2R

Le résultat obtenu correspond parfaitement au souhait de jedoo
DTE_MAX PX SUM(QTE)MIN(DTE)
05/07/2007 10 10 02/07/2007
17/07/2007 11 10 14/07/2007
23/07/2007 10 9 20/07/2007

Une question si on voulait une évolution des prix sans trous
DTE_MAX PX SUM(QTE)MIN(DTE)
14/07/2007 10 10 02/07/2007
20/07/2007 11 10 14/07/2007
23/07/2007 10 9 20/07/2007

sans les fonctions analytiques de 10 g ?
et avec ?

Facile (bien qu'il faudrait mettre 1 jour de moins non ?

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
WITH tvente AS 
	(SELECT TO_DATE('02/07/2007', 'DD/MM/RRRR') AS dte, 
			4 AS qte, 10 AS px FROM dual
	UNION ALL
SELECT TO_DATE('05/07/2007', 'DD/MM/RRRR'), 6, 10 FROM dual
	UNION ALL
SELECT TO_DATE('14/07/2007', 'DD/MM/RRRR'), 7, 11 FROM dual
	UNION ALL
SELECT TO_DATE('17/07/2007', 'DD/MM/RRRR'), 3, 11 FROM dual
	UNION ALL
SELECT TO_DATE('20/07/2007', 'DD/MM/RRRR'), 5, 10 FROM dual
	UNION ALL
SELECT TO_DATE('23/07/2007', 'DD/MM/RRRR'), 4, 10 FROM dual
)
SELECT MIN(dte), dte_max, px, SUM(qte)
FROM (
	SELECT dte, qte, px, NVL(
				(
				SELECT MIN(dte) - 1 FROM tvente t2
				WHERE t2.px <> t1.px AND t2.dte >= t1.dte
				),		
				(SELECT MAX(dte) FROM tvente t2
				WHERE t2.px = t1.px AND t2.dte >= t1.dte
				AND NOT EXISTS (SELECT 1 FROM tvente t3 WHERE t3.dte BETWEEN t1.dte AND t2.dte
				AND px <> t2.px
				)
			)
			) AS dte_max
	FROM tvente t1
	ORDER BY dte
	)
GROUP BY dte_max, px
 
 
MIN(DTE)	DTE_MAX		PX	SUM(QTE)
02/07/2007	13/07/2007	10	10
14/07/2007	19/07/2007	11	10
20/07/2007	23/07/2007	10	9

**McM** · 31/07/2007, 16h05

Avec les fcts analytiques, j'arrive pas pour l'instant.
Je vais laisser tomber.

**pifor** · 31/07/2007, 16h18

Envoyé par Garuda

Pas sur ! Oui en Entreprise, non en standard !

Pourtant d'après la note Metalink :

Subject: Differences Between Standard Edition and Enterprise Edition in 8i
Doc ID: Note:112591.1 Type: REFERENCE
Last Revision Date: 13-JUL-2004 Status: PUBLISHED

les fonctions analytiques font partie de la Enterprise Edition, Standard Edition et Personal Edition.

**jedoo** · 31/07/2007, 16h22

Merci c'est exactement ce qu'il me fallait.

Si jamais quelqu'un sait le faire avec les fonctions analytiques, ça m'intéresse de le voir pour tester si c'est plus rapide.

**lalystar** · 03/08/2007, 01h40

Bonsoir,

Je vous propose

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
 
select	
	date_ouv, date_clo, qtevendue, prixvente
from
	(				
		select
			datevente, prixvente,
			sum(qtevendue) over(partition by categorie) qtevendue,
			date_ouv, 
			max(date_clo) over(partition by categorie) date_clo
		from
			(
				select
					datevente, qtevendue, prixvente,
					count(date_ouv) over(order by datevente) categorie,
					date_ouv, date_clo
				from
					(
						select 
							datevente, qtevendue, prixvente, 
							case
								when prixvente != lag(prixvente, 1, -1) over(order by datevente) then datevente
							end date_ouv,
							case
								when prixvente != lead(prixvente, 1, -1) over(order by datevente) then datevente
							end date_clo
						from
							T_VENTE
					)										
			)									
	)
where
	date_ouv is not null and
	date_clo is not null				
order by date_ouv
/

C'est pas super lisible mais ca a l'air de marcher...

Laly.

Group By selon un tri

SQL Oracle

Discussions similaires

Partager

Partager