Discussion :

[yoshï]

Bonjour

Pour l'exemple, je ne mentionne que les tables et les champs utiles pour décrire le problème

J'ai une table avec le profil de commerciaux (agents).
Table: agent_profil
Champs: agent_id ...

J'ai une table de description de sociétés (company).
Table: company
Champs: company_id ...

J'ai une table avec les entreprises démarchées par les commerciaux
Table: prospecting
Champs: agent_id / Company_id ...

J'ai une table avec la liste des transactions entre sociétés
Table: transactions
Champs: id_seller / id_buyer / amount ...

Note importante : id_seller, id_buyer sont des champs permettant d'identifier une société

A partir de ces tables, je souhaiterai 2 informations:

• Le volume d'affaire d'une société X

= Montant cumulé de toutes les transactions où la société a été vendeur ou acheteur

Code :

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

1
2
3
4
5
 
SELECT SUM(A.business)
	FROM (SELECT id_buyer,id_seller,SUM(amount) as business FROM transactions WHERE id_seller= X
	      UNION ALL
	      SELECT id_buyer,id_seller,SUM(amount) as business FROM transactions WHERE id_buyer= X ) as A

• Le classement de tous les agents en fonction d'un score déterminé par la formule suivante

(score_agent = NB de société démarchées par l'agent + somme des volumes d'affaire de toutes les sociétés démarchées par l'agent)

Cette requête me semble beaucoup plus compliquée... Il faut faire une requête qui va renvoyer la table des agents avec leurs scores et donner la position d'un agent dans cette table.
Si on tente de décomposer le problème:

1-nb de société parrainé par les agents

Code :

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

1
2
3
SELECT agent_id, count(company_id)
	FROM prospecting
	GROUP BY agent_id

2-volume d'affaire de toutes les sociétés

Code :

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

1
2
3
4
5
6
7
SELECT A.company, SUM(A.business)
	FROM (SELECT id_buyer as company,SUM(amount) as business FROM transactions
	      GROUP BY company
	      UNION ALL
	      SELECT id_seller as company,SUM(amount) as business FROM transactions
	      GROUP BY company) as A
	GROUP BY A.company

3- Cumuler tous les volumes d'affaire pour les sociétés prospectées par un agent Y

5- Renvoyer la table avec les scores des agents (score = nb société prospectée par l'agent + volume d'affaire généré par l'agent)

6- Obtenir la position d'un agent dans cette table (le classement de l'agent)

J'ai un peu de mal avec les 3 dernières étapes. De plus je me pose la question de la performance... Sur des tableaux "agent_profil" et "transactions" avec quelques milliers de lignes, est ce raisonnable de lancer ce type de requête?

Merci beaucoup pour votre aide.
De mon coté, je continue à faire des essais pour le classement des agents et je reviens vers vous si je trouve une solution.

[aieeeuuuuu]

Bonjour,

(score_agent = NB de société démarchées par l'agent + somme des volumes d'affaire de toutes les sociétés démarchées par l'agent)

essayez ceci :

Code :

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

1
2
3
4
5
6
7
8
9
10
 
SELECT 
        ap.agent_id
        ,COUNT(DISTINCT p.company_id) + SUM(t.amount) AS Score_agent
FROM agent_profil ap
INNER JOIN prospecting p
    ON p.agent_id = ap.agent_id
INNER JOIN transactions t
    ON p.Company_id IN (t.id_seller , t.id_buyer )
GROUP BY ap.agent_id

Si Vous voulez les agents n'ayant fait aucune prospection et/ou dont les entreprises prospectées n'ont fait aucune transaction, il faudra utiliser des jointures externes.

[yoshï]

Merci beaucoup aieeeuuuuu!

Voici quelques variantes de votre solution afin d'obtenir les informations utiles:

Le score d'un agent (agent:655)

Code :

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

1
2
3
4
5
6
7
8
 
SELECT COUNT(DISTINCT p.company_id) + SUM(t.amount) AS Score_agent
FROM agent_profiles ap
INNER JOIN prospecting p
    ON p.agent_id = ap.agent_id
INNER JOIN transactions t
    ON p.company_id IN (t.id_seller , t.id_buyer )
WHERE ap.agent_id=655

La position d'un agent (agent:655) dans le tableau des scores:

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
 
SELECT count(*)
FROM ( SELECT ap.agent_id
	   ,COUNT(DISTINCT p.company_id) + SUM(t.amount_euros) AS Score_agent
           FROM agent_profiles ap
           INNER JOIN prospecting p
               ON p.agent_id = ap.agent_id
           INNER JOIN transactions t
               ON p.Company_id IN (t.id_seller , t.id_buyer )
           GROUP BY ap.agent_id
           ORDER BY Score_agent) AS Ranking
WHERE Score_agent > (SELECT COUNT(DISTINCT p.company_id) + SUM(t.amount_euros) AS Score_agent
							FROM agent_profiles ap
							INNER JOIN prospecting p
    							    ON p.agent_id = ap.agent_id
							INNER JOIN transactions t
    							    ON p.Company_id IN (t.id_seller , t.id_buyer )
							WHERE ap.agent_id=655)