Requête d'aggrégat conditions différentes

**tinwul** · 30/07/2009, 17h57

Bonjour,

je souhaite récupérer différentes informations agrégées (sum) à partir de plusieurs tables et selon différentes conditions dans une même requête sql. Et ceci afin d'alimenter une vue.

En clair :

J'ai trois tables : T_Principale, T1, T2

T_Principale contient les champs 'date', 'lieu', 'produit' et 'montant_vente'
T1 contient des infos sur le produit comme 'type_produit'
et T2 contient des infos sur le lieu comme 'pays_lieu'

T_Principale est liée à T1 et T2 par des clés étrangères.

et je voudrais obtenir quelque chose du genre :

pour chaque ensemble (date,lieu,produit), je voudrais les colonnes :

- 'montant_vente_typeProduit1' correspondant à sum(montant_vente) avec une jointure T_Principale - T1 where type_produit = 1
- 'montant_vente_typeProduit2' correspondant à sum(montant_vente) avec une jointure T_Principale - T1 where type_produit = 2
- 'montant_vente_PaysA' correspondant à sum(montant_vente) avec une jointure T_Principale - T2 where lieu_produit = A
- 'montant_vente_PaysB' correspondant à sum(montant_vente) avec une jointure T_Principale - T2 where lieu_produit = B

exemple :

T_Principale

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
 
date                  lieu                    produit              montant_vente
15/07/09            1                       1                      50
15/07/09            1                       2                      100
15/07/09            1                       1                      20
17/07/09            2                       1                      300
17/07/09            1                       1                      50
17/07/09            2                       1                      400
17/07/09            2                       1                      500
 
T1
produit           type_produit
1                   1
2                   2
 
T2
lieu                type_lieu
1                   A
2                   B

En résultat :

Code :

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

1
2
3
4
5
6
 
date                  lieu                    produit              montant_vente
15/07/09            1                       1                      70
15/07/09            1                       2                      100
17/07/09            2                       1                      1200
17/07/09            1                       1                      50

Je ne sais pas du tout quel type de requête utiliser : sous-requête, table temporaire, union?

J'ai tenté quelque chose du genre :

Code :

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

1
2
3
4
5
6
7
8
 
select date, lieu, produit, 
(select sum(...) from ...requête renvoyant la somme pour le produit type 1),
(select sum(...) from ...requête renvoyant la somme pour le produit type 2),
(select sum(...) from ...requête renvoyant la somme pour le produit lieu A),
(select sum(...) from ...requête renvoyant la somme pour le produit lieu A)
from T_Principale
group by date, lieu, produit

sachant que j'ai essayé avec un group by et sans group by à l'intérieur des requêtes, sans succès.

ça me parait logique que ça soit faux mais je ne vois pas comment y remédier.

Toute idée sera la bienvenue.

**elsuket** · 30/07/2009, 18h42

Bonjour,

Vous devriez plutôt spécifier votre vue comme 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
CREATE VIEW VProduit
	WITH SCHEMABINDING
AS
SELECT T.date,
		T.lieu,
		T.produit,
		LIEU.pays_lieu
		SUM(montant_vente)
FROM dbo.T_Principale AS T
JOIN dbo.T1 AS TYPE_PRODUIT
	ON T.produit = TYPE_PRODUIT.Produit
JOIN dbo.T2 AS LIEU
	ON T.lieu = LIEU.lieu
GO
 
CREATE UNIQUE CLUSTERED INDEX IXC_VProduit_date
ON dbo.VProduit(date)
GO

En ensuite réaliser les agrégats sur cette vue.

Si vous nous aviez donné la vraie structure de vos tables ainsi que votre requête, nous aurions probablement pu aller plus loin.

@++

**tinwul** · 31/07/2009, 10h15

Eh bien merci elsuket, ta réponse m'a bien fait avancer. Mais effectivement je voudrais aller plus loin.

J'ai essayé de simplifier hier mais voici la structure réelle.

En fait, c'est un schéma en étoile (décisionnel) avec au centre une table de fait faits_vente, reliée à deux tables de dimension dim_paiement et dim_offre.

la table fait_vente contient toutes les ventes avec la date, l'heure, le site où a été conclu la vente, l'offre concernée, le résultat de la vente (état du paiement) et le montant de la vente.

la table dim_paiement contient les infos sur le paiement, en particulier son état : 'effectué', 'abandonné', 'refusé'...

la table dim_offre nous renseigne sur l'offre notamment la catégorie de l'offre : 'offre abonnement', 'offre sans engagement' ...

Etant donné que la table de fait enregistre à la minute près, je voudrais obtenir une vue journalière des ventes, et ceci exactement :

Code :

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

1
2
 
date  |  site  |  offre  |  CA  |  Mtt_abandon  |  MttRefus  |  QtéAbo  |  QtéSsEngagmt

C'est à dire chaque jour, pour chacun des sites et chacune des offres, le chiffre d'affaire généré (la somme de toutes les ventes dont le paiement est 'effectué'), le manque à gagner par abandon (la somme de toutes les ventes dont le paiement est 'abandonné'), le manque à gagner par refus (la somme de toutes les ventes dont le paiement est 'refusé'), la quantité d'abonnement (nombre de ventes de type 'abonnement') et la quantité de vente de type 'sans engagement'.

Voilà maintenant tu sais tout. Pour ce qui est de ma requête, elle est à oublier, je préfère celle que tu m'a donné, que j'ai modifié en conséquence :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
SELECT T.no_date,
		T.no_site,
		T.no_offre,
		PAIEMENT.etat_paiement,OFFRE.categorie_offre,
		SUM(T.montant_souscription) as total_montant, count(T.no_souscription) as Qte
FROM dbo.faits_venteweb AS T
JOIN dbo.dim_offre AS OFFRE
	ON T.no_offre = OFFRE.no_offre
JOIN dbo.dim_paiement AS PAIEMENT
	ON T.no_paiement = PAIEMENT.no_paiement
group by T.no_date, T.no_site, T.no_offre, PAIEMENT.etat_paiement,OFFRE.categorie_offre

Ce qui me donne :

Code :

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

1
2
3
4
5
6
 
no_date no_site no_offre etat_paiement categorie_offre total_montant  Qte
21860	7	7	abandonné	       Abonnement	          6,00	            3
21860	7	9	effectué	       Abonnement	          44,70            3
21869	7	7	refusé       	       Abonnement	          2,00            1
21869	8	7	effectué	       Ss Engagement	          34,00            17

C'est pas vraiment ce que je recherchais au départ, mais peut-être puis_je partir de ça?

Par ailleurs, qu'entends-tu par

ensuite réaliser les agrégats sur cette vue.

?

Je n'ai pas bien saisi la notion d'agrégats je pense car pour moi, la vue est déjà un agrégat. Peux-tu éclairer ma lanterne? Merci!

**elsuket** · 31/07/2009, 11h45

un schéma en étoile (décisionnel) avec au centre une table de fait faits_vente, reliée à deux tables de dimension dim_paiement et dim_offre.

Donc je suppose que vous allez faire des rapports (peut-être avec SQL Server Reporting Services ?)
Même si je ne suis pas franchement calé sur ce que SQL Server offre en terme de business intelligence, je vous conseille plutôt de faire un cube sous SQL Server Analysis Services, car cela vous facilitera à mon avis grandement la vie, même si vous passerez un moment à apprendre comment créer un cube et le requêter.

Néanmoins, si dans un premier temps vous souhaitez terminer votre démarche, vous pouvez par exemple créer la vue indexée suivante :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
SELECT VENTE.date,
		VENTE.heure,
		LIEU.nomLieu,
		OFFRE.nomOffre,
		PAIEMENT.etat
FROM dbo.faits_venteweb AS VENTE
JOIN dbo.dim_offre AS OFFRE
	ON VENTE.no_offre = OFFRE.no_offre
JOIN dbo.dim_paiement AS PAIEMENT
	ON VENTE.no_paiement = PAIEMENT.no_paiement
JOIN dbo.dim_lieu AS LIEU
	ON VENTE.no_lieu = LIEU.no_lieu

Vous pouvez ensuite effectuer tous les calculs à partir de cette vue.
Ainsi pour ce que vous souhaitez obtenir, nous écririons :

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
SELECT VENTE.date,
		LIEU.nomLieu,
		OFFRE.nomOffre,
		SUM
		(
			CASE PAIEMENT.etat
				WHEN 'effectué' THEN PAIMENT.montant
				ELSE 0
			END
		) AS CA,
		SUM
		(
			CASE PAIEMENT.etat
				WHEN 'abandonné' THEN PAIMENT.montant
				ELSE 0
			END
		) AS Mtt_abandon,
		SUM
		(
			CASE 
				WHEN 'refusé' THEN PAIMENT.montant
				ELSE 0
			END			
		) AS Mtt_refus,
		SUM
		(
			CASE OFFRE.categorie
				WHEN 'offre abonnement' THEN 1
				ELSE 0
			END
		) AS QtéAbo,
		SUM
		(
			CASE OFFRE.categorie
				WHEN 'offre sans engagement' THEN 1
				ELSE 0
			END
		) AS QtéSsEngagmt
FROM dbo.faits_venteweb

On peut se servir de cette requête pour spécifier une vue qui ne serait pas indexée, mais je doute des performances que vous obtiendriez.

Je n'ai pas bien saisi la notion d'agrégats je pense car pour moi, la vue est déjà un agrégat. Peux-tu éclairer ma lanterne?

Une vue n'a d'existence physique que si elle est indexée.
En d'autres termes si vous créez une vue sans l'indexer, elle n'a aucune existence physique en base de données : c'est une abstraction des données d'une ou plusieurs tables, et elle n'occupe aucun espace disque.

On peut se servir d'une vue pour d'autres besoins que du calcul d'agrégats (pour des jointures que l'on spécifie très souvent de la même façon et dont les données des tables sous-jacentes changent peu ou pas), donc la notion d'agrégat est dissociée de la notion de vue

Il reste que l'on tire un très bon parti des vues indexées si celles-ci spécifient un calcul d'agrégat (fonctions COUNT(), SUM(), AVG(), ...)

Mais vous ne devez pas créer un index sur une colonne de votre vue indexée sur celle-ci participe déjà à la clé d'un index.
La conception de vues indexées est donc complexe, et c'est aussi pour cela que je vous conseille de créer un cube.

Pour vous donnez une petite idée de comment créer un cube, vous pouvez regarder une vidéo ici

@++

**tinwul** · 31/07/2009, 11h57

Merci pour ta réponse, je vais avancer dans cette direction. A vrai dire, je travaille sous BO donc je vais créer un univers et des rapports à l'aide de la suite BO.
Je pense plus tard faire un cube avec sql server mais juste à titre pédagogique, pour savoir en faire.
Quoiqu'il en soit, je compte garder la structure que tu m'as donné, je vais également tester les performance avec une vue non indexée ou peut-être ne garder que la vue indexée et créer les calculs au stade de l'univers BO.

Merci, je crois que mon problème est résolu! (jusqu'au prochain

)

Excuse-moi de te tutoyer, mais j'ai commencé donc je termine