Requete pour statistique

**tralala232** · 20/08/2009, 17h17

Bonjour,
Je solicite votre aide pour realisee une requete qui me parait complexe

j'ai 4 table
1)Campagne
-------------------
RefCampagne (int)

2)U]Rem[/U]
--------------------
RefRem (int)
RefCampagne (int)
GrDefault (bit)

3) RemParam
--------------------
RemParam (int)
RefRem (int)
Evenement (varchar)
Remuneration (float)

4) Stat
---------------------
RefStat (int)
Idaff (int)
Affichage (int)
Clic (int)
DtEvent (DateTime)

dans la table RemParam j'ai de 2 lignes

RefParam RefRem Evenement Remuneration
-----------------------------------------------------
258 | 35 | 'Affichage' | 0,3
259 | 35 | 'Clic' | 0,4

dans la table Stat j'ai 3 lignes

RefStat Idaff Affichage Clic DtEvent
-----------------------------------------------------
332 | 8 | 1 | NULL | 17/08/2009 00:00:00
333 | 8 | NULL | 1 | 17/08/2009 00:00:00
334 | 8 | 1 | NULL | 17/08/2009 00:00:00
335 | 8 | 1 | NULL | 18/08/2009 00:00:00
336 | 8 | NULL | 1 | 18/08/2009 00:00:00

J'aimerai faire un requete qui me donnerai le resultat suvant mais j'ai du mal

Datevent | Idaff | NbrAffichage | GainAffichage | NbrClic | GainClic
-------------------------------------------------------------------------
17/08/2009 | 8 | 2 | 0,6 | 1 | 0,8
18/08/2009 | 8 | 1 | 0,3 | 1 | 0,4

Merci

**Waldar** · 20/08/2009, 18h40

Pas terrible comme modélisation :

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
WITH RemParam (RefParam, RefRem, Evenement, Remuneration)
AS
(
SELECT 258, 35, 'Affichage', 0.3 UNION ALL
SELECT 259, 35, 'Clic'     , 0.4
),
     Stat (RefStat, Idaff, Affichage, Clic, DtEvent)
AS
(
SELECT 332, 8, 1   , NULL, CAST('17/08/2009' AS SMALLDATETIME) UNION ALL
SELECT 333, 8, NULL, 1   , CAST('17/08/2009' AS SMALLDATETIME) UNION ALL
SELECT 334, 8, 1   , NULL, CAST('17/08/2009' AS SMALLDATETIME) UNION ALL
SELECT 335, 8, 1   , NULL, CAST('18/08/2009' AS SMALLDATETIME) UNION ALL
SELECT 336, 8, NULL, 1   , CAST('18/08/2009' AS SMALLDATETIME)
)
SELECT
    ST.DtEvent,
    ST.Idaff,
    COALESCE(SUM(ST.Affichage), 0) AS NbrAffichage,
    COALESCE(SUM(ST.Affichage), 0) * PA.Remuneration AS GainAffichage,
    COALESCE(SUM(ST.Clic), 0) AS NbrClic,
    COALESCE(SUM(ST.Clic), 0) * PC.Remuneration AS GainClic
FROM
    Stat AS ST
    INNER JOIN RemParam AS PA
      ON PA.Evenement = 'Affichage'
    INNER JOIN RemParam AS PC
      ON PC.Evenement = 'Clic'
GROUP BY
    ST.DtEvent,
    ST.Idaff,
    PA.Remuneration,
    PC.Remuneration
ORDER BY
    ST.DtEvent ASC;
 
DtEvent		Idaff	NbrAffichage	GainAffichage	NbrClic	GainClic
17/08/2009	8	2		0.6		1	0.4
18/08/2009	8	1		0.3		1	0.4

**tralala232** · 20/08/2009, 21h38

Merci de ta reponse mais en fait c'est un peu plus complexe que ca
dans mon 1er message j'ai essayé de simplifier
mais j'aurai du rentrer un peu plus dans le detail

stat doit etre liée à la table campagne
campagne liée à rem
Rem liée à RemParam

STAT:IdAnn -------------RefUser:CAMPAGNE
CAMPAGNE:Refcampagne-------------Refcampagner:REM
REM:REFREM-------------REFREM:RemParam

en il y a l'annonceur(Idann) et le l'affilie(Idaff)

Au depart l'annonceur crée une campagne(campagne), un ou des groupe de rémunération(REM) dont 1 qui sera par defaut, dans ces groupes il crée des rémunération
l'affilié passe des bannieres sur sur site quand la banniere s'affiche ou un clic se produit, une ligne s'ajoute dans la table stat en donnant Idann Idaff et evenement (clic ou affichage)

le but de la requete et d'afficher les stat de affilie par jour ou autre(semaine,mois etc..) pour l'affilié et pareil pour l'annonceur

J'ai reussi ma requete mais elle ne fonctionne que sur 1 jour
ca te permettra peut etre de comprendre car c complique d'expliquer par ecrit

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
select dtdate,
 
(SELECT  COUNT(Stat.A) AS A 
FROM  CampagneRemuneration 
INNER JOIN CampagneRemunerationParam ON CampagneRemuneration.RefRem = CampagneRemunerationParam.RefRem 
INNER JOIN Campagne ON CampagneRemuneration.RefCampagne = Campagne.RefCampagne 
RIGHT OUTER JOIN Stat ON Campagne.RefUser = Stat.IdAnn 
WHERE (Stat.IdAff = 8) 
AND (Stat.DtDate BETWEEN '17/08/2009' AND '17/08/2009') 
AND (CampagneRemuneration.GrDefault = 1) 
and Stat.A=1 
and CampagneRemunerationParam.Evenement ='Affichage' 
GROUP BY  Stat.DtDate, CampagneRemunerationParam.Evenement, Campagne.Titre) as nbrAff,
 
(SELECT     SUM(CampagneRemunerationParam.Rem) AS Expr1  
FROM         CampagneRemuneration 
INNER JOIN CampagneRemunerationParam ON CampagneRemuneration.RefRem = CampagneRemunerationParam.RefRem 
INNER JOIN Campagne ON CampagneRemuneration.RefCampagne = Campagne.RefCampagne 
RIGHT OUTER JOIN Stat ON Campagne.RefUser = Stat.IdAnn 
WHERE  (Stat.IdAff = 8) 
AND (Stat.DtDate BETWEEN '17/08/2009' AND '17/08/2009') 
AND (CampagneRemuneration.GrDefault = 1) 
AND (Stat.A = 1) 
AND (CampagneRemunerationParam.Evenement = 'Affichage') 
GROUP BY Stat.DtDate, CampagneRemunerationParam.Evenement, Campagne.Titre)as aff,
 
(SELECT      COUNT(Stat.c) AS c 
FROM         CampagneRemuneration 
INNER JOIN CampagneRemunerationParam ON CampagneRemuneration.RefRem = CampagneRemunerationParam.RefRem 
INNER JOIN Campagne ON CampagneRemuneration.RefCampagne = Campagne.RefCampagne 
RIGHT OUTER JOIN Stat ON Campagne.RefUser = Stat.IdAnn 
WHERE (Stat.IdAff = 8) 
AND (Stat.DtDate BETWEEN '17/08/2009' AND '17/08/2009') 
AND (CampagneRemuneration.GrDefault = 1) 
And Stat.c=1 
And CampagneRemunerationParam.Evenement ='Clic' 
GROUP BY  Stat.DtDate, CampagneRemunerationParam.Evenement, Campagne.Titre) as nbrClic,
 
(SELECT      sum(CampagneRemunerationParam.Rem) AS Expr1 
FROM         CampagneRemuneration 
INNER JOIN CampagneRemunerationParam ON CampagneRemuneration.RefRem = CampagneRemunerationParam.RefRem 
INNER JOIN Campagne ON CampagneRemuneration.RefCampagne = Campagne.RefCampagne 
RIGHT OUTER JOIN Stat ON Campagne.RefUser = Stat.IdAnn 
WHERE (Stat.IdAff = 8) 
AND (Stat.DtDate BETWEEN '17/08/2009' AND '17/08/2009') 
AND (CampagneRemuneration.GrDefault = 1) 
And Stat.c=1 
And CampagneRemunerationParam.Evenement ='Clic' 
GROUP BY  Stat.DtDate, CampagneRemunerationParam.Evenement, Campagne.Titre) as clic  
 
from stat where dtdate='17/08/2009'
group by dtdate

je pense que ta methode doit etre la bonne mais j'ai vraiment du mal

merci

**Waldar** · 21/08/2009, 02h49

C'est la catastrophe votre requête...
Vous faites quasiment quatre fois la même chose !

Pourquoi faire des jointures externes, c'est possible d'avoir des stats sans campagne ? D'après ce que vous dites, non.
Quelques alias facilitent aussi la lecture de votre requête.

Il reste des points d'ombre puisqu'entre vos deux posts des choses bougent des choses restent mais tout n'est pas clair.

Néanmoins la base de votre requête doit être quelque chose de ce type là :

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
SELECT
    -- CG.Idann, -- Pure supposition, vous distillez l'information par petits bouts
    CG.Titre,
    ST.DtDate, -- Si vous voulez regrouper sur un mois appliquez une fonction à cette date ici et dans le GROUP BY
    ST.IdAff,
    SUM(ST.A) AS NbrAff,
    SUM(ST.A * CPA.Rem) AS Aff,
    SUM(ST.C) AS NbrClic,
    SUM(ST.C * CPC.Rem) AS Clic
FROM
    Campagne AS CG
    INNER JOIN Stat AS ST
      ON ST.IdAnn = CG.RefUser
    INNER JOIN CampagneRemuneration AS CR
      ON CR.RefCampagne = CG.RefCampagne 
    INNER JOIN CampagneRemunerationParam AS CPA
      ON CPA.RefRem = CR.RefRem
     AND CPA.Evenement = 'Affichage' -- je le mets ici pour que ce soit bien vu, mais ça aurait du être dans le WHERE
    INNER JOIN CampagneRemunerationParam AS CPC
      ON CPC.RefRem = CR.RefRem
     AND CPC.Evenement = 'Clic'
WHERE
    ST.DtDate BETWEEN CAST('01/08/2009' AS SMALLDATETIME) -- début de votre période
                  AND CAST('31/08/2009' AS SMALLDATETIME) --   fin de votre période
AND CR.GrDefault = 1
GROUP BY
    -- CG.Idann,
    CG.Titre,
    ST.DtDate,
    ST.IdAff

J'ai mis l'IdAff en sortie de requête mais si c'est une entrée il vous suffit de le réintégrer dans le Where.

**tralala232** · 21/08/2009, 03h28

Merci beaucoup je teste juste comme ca et ca a l'air de fonctionner
en effet je pense que ma stucture et un peu bancal
mais si tu a une idée c'est avec plaisir car j'ai soif d'apprendre
Je te dis bravo

**tralala232** · 21/08/2009, 03h41

J'aimerai afficher les stat sur 30 jours , meme si jour ne sont pas present

par exemple j'ai des stat pour le 01/08/2009 et pour 03/08/2009 donc pas pour 02/08/2009

et afficher :
01/08/2009 (et les stat associé si il y en a)
02/08/2009 (et les stat associé si il y en a)
03/08/2009 (et les stat associé si il y en a)
04/08/2009 (et les stat associé si il y en a)
etc... (pour 1 mois par exemple ou semaines ou trimeste)

car actuellement la base me renvoi que les lignes prensentes

Merci

**elsuket** · 21/08/2009, 10h38

Bonjour,

Je suppose que vous avez créé une procédure stockée pour cela, et que vous avez en paramètre d'entrée une date de début et de fin de recherche.

Dès lors vous avez deux solutions :

- soit vous vous créez une table de dates, comme je l'ai montré ici, et écrire une demi-jointure avec votre table,
- écrire dans votre procédure stockée une expression de table commune récursive qui vous calcule toutes les dates entre les bornes que vous avez passées en paramètre :

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
DECLARE @dateDeb DATETIME,
		@dateFin DATETIME
 
SELECT @dateDeb = '20090101 00:00:00.000',
		@dateFin = '20091231 23:59:59.997'
 
;WITH
	CTE_DATES_PERIODE AS
	(
			SELECT @dateDeb AS date
		UNION ALL
			SELECT DATEADD(day, 1, date)
			FROM CTE_DATES_PERIODE
			WHERE date <= @dateFin
	)
SELECT date
FROM CTE_DATES_PERIODE
WHERE date <= @dateFin
OPTION (MAXRECURSION 366)

Et de la même façon faire une demi-jointure avec votre table

@++

**tralala232** · 21/08/2009, 12h30

merci encore de partager votre savoir

mais concernant mes stats ca va se compliquer encore un peu
car on a parlé de d'affichage et de clic mais il y a aussi les vente, incription au formulaire et double clic
car apres reflexion je me suis dit
que l'ideal serai de passer par des paliers

c'est a dire

STAT:IdAnn -------------RefUser:CAMPAGNE
CAMPAGNE:Refcampagne-------------Refcampagne:REM
REM:Refrem-------------RefRem:REMPALIER
REMPALIER:Refpalier ----------Refpalier:REMPARAM

les palier ca resemble a ca
-1er palier) de 1 à 10 (correspond au nombre de vente ou formulaire realiser)
-2er palier) de 11 à plus (ou sur 1 seul palier -1er palier) de 1 à plus)

ensuite chaque palier aura sa remunération associé :
1er) de 1 a 10 (remunéré pour la vente 1€)
2er) de 11 a 15 (remunéré pour la vente 2€)
3er) de 16 a plus (remunéré pour la vente 3€)

donc la compléxite est que pour savoir la remunération il faut d'abord savoir le nombre de vente realisé entre le premier jours et le dernier jour du mois en cours

J'ai mis en forme la table PALIER

PALIER
------
Refpalier (int)
RefRem (int)
Refcampagne (int) --(mais je pense que ca sert à rien)--
RefUser (int) -- qui correspond a IdAnn (mais pareil je pense que ca sert à rien)--
Val1 (int)
Val2 (varchar) (il est en varchar car ca peut une valeur numerique ou le mots cléf "plus")

MERCI ENCORE

**tralala232** · 31/08/2009, 18h33

Merci encore

Requete pour statistique

MS SQL Server

Discussions similaires

Partager

Partager