Group By selon un tri

Version imprimable

Bonjour,

imaginons une table T_VENTE :

Code:

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:

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:

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:

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 ?

31/07/2007, 13h54
McM

Oui (avec les fonctions analytiques), mais pas de version, pas de réponse.
31/07/2007, 14h02
jedoo

Ah oui désolé !

Ma version : Oracle8i Enterprise Edition Release 8.1.7.4.0

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:

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

Citation:

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 :mouarf:

Code:

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.

31/07/2007, 14h48
McM

Citation:

Envoyé par pifor

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

:oops: 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..
31/07/2007, 14h52
Bruno2r

j'ai adoré le sql de McM

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 ?
31/07/2007, 14h53
Garuda

Citation:

Envoyé par pifor

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

Pas sur ! Oui en Entreprise, non en standard !

Citation:

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:

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

31/07/2007, 15h05
McM

Avec les fcts analytiques, j'arrive pas pour l'instant.
Je vais laisser tomber.
31/07/2007, 15h18
pifor

Citation:

Envoyé par Garuda

Pas sur ! Oui en Entreprise, non en standard !

Pourtant d'après la note Metalink :

Citation:

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.
31/07/2007, 15h22
jedoo

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.

Bonsoir,

Je vous propose

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