Requete pour statistique

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

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, 17h40

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, 20h38

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, 01h49

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, 02h28

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, 02h41

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

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

Merci encore

Requete pour statistique

MS SQL Server

Vue hybride

Discussions similaires

Partager

Partager