PostgreSQL Somme groupée
Le
Etienne
Salut.
j'ai une table du style
CREATE TABLE "matable" (
"idclient" integer,
"refproduit" text,
"ca" integer,
PRIMARY key (idclient, idproduit)
);
Comment puis faire pour avoir pour chaque client le ca généré par les 3
principaux produit.
supponsons que la table soit remplie ainsi
idclient, refproduit, ca
1, A, 10
1, B, 20
1, C, 30
1, D, 40
1, E, 50
1, A, 30
1, C, 50
1, D, 50
1, E, 10
J'aimerai avoir au final
1, 120
2, 130
a savoir
pour le client 1 la somme 30 + 40 + 50 correspondant aux 3 produits
qu'il a le plus commandé (C, D, E)
pour le client 2 la somme 30 + 50 + 50 correspondant aux 3 produits
qu'il a le plus commandé (A, C, D)
Peut on faire ca en une seule requête ?
par ce que pour un client bien précis j'y arrive
SELECT idclient, sum(ca) FROM (SELECT idclient, ca FROM matable WHERE
idclient = 1 ORDER BY ca DESC LIMIT 3) as foo;
me donne ce que je veux pour le client 1 !!!
merci
Etienne.
j'ai une table du style
CREATE TABLE "matable" (
"idclient" integer,
"refproduit" text,
"ca" integer,
PRIMARY key (idclient, idproduit)
);
Comment puis faire pour avoir pour chaque client le ca généré par les 3
principaux produit.
supponsons que la table soit remplie ainsi
idclient, refproduit, ca
1, A, 10
1, B, 20
1, C, 30
1, D, 40
1, E, 50
1, A, 30
1, C, 50
1, D, 50
1, E, 10
J'aimerai avoir au final
1, 120
2, 130
a savoir
pour le client 1 la somme 30 + 40 + 50 correspondant aux 3 produits
qu'il a le plus commandé (C, D, E)
pour le client 2 la somme 30 + 50 + 50 correspondant aux 3 produits
qu'il a le plus commandé (A, C, D)
Peut on faire ca en une seule requête ?
par ce que pour un client bien précis j'y arrive
SELECT idclient, sum(ca) FROM (SELECT idclient, ca FROM matable WHERE
idclient = 1 ORDER BY ca DESC LIMIT 3) as foo;
me donne ce que je veux pour le client 1 !!!
merci
Etienne.
Poser une question
Bonjour,
Votre jeu d'essai a une petite erreur sur idclient des 4 dernières
lignes... ;-)
En une seule requête, à première vue, je ne vois pas... :-(
Par contre, en introduisant dans la table une colonne calculé par
trigger définissant un ordre, on aurait comme jeu d'essai et en
considérant que l'on ne traite pas le cas des ex-æquo :
idclient, refproduit, ca, ordre
1, A, 10, 5
1, B, 20, 4
1, C, 30, 3
1, D, 40, 2
1, E, 50, 1
2, A, 30, 3
2, C, 50, 1
2, D, 50, 2
2, E, 10, 4
on pourrait alors écrire :
select idclient, sum(ca) from
(
select idclient, refproduit, CA
from client
where ordre <= 3
)
group by idclient
Reste à affiner la règle de classement pour les ex-æquo et à implémenter
le trigger pour alimenter la colonne "ordre"...
On pourrait aussi passer par une table temporaire (ou non) ne retenant
que les 3 principaux ca par produit et client...
--
Philippe.
C'est vrai que c'est pas mal.
il doit bien y avoir un moyen de calculer cet ordre !!!
Les ex-aequo n'ont pas d'impact sur le résultat (tant qu'on prend les 3
premiers).
Ben non justement, on ne peut pas faire ça !
Si on pouvait fabriquer cette table temporaire, l'affaire serait jouée...
Etienne.
Postgres (que je connais très imparfaitement) ne gère pas les tables
temporaires ?
Je me suis peut-être aussi mal exprimé :-(
Je voulais dire une table alimentée par un trigger placé sur la table
"matable" citée initialement et qui ne contiendrait que les 3 plus
grands "ca" par client ; cela donnerait à partir du jeu d'essai initial:
matable2(idclient, refproduit, ca)
1, C, 30
1, D, 40
1, E, 50
2, A, 30
2, C, 50
2, D, 50
A partir de la, cela devient trivial :
select idclient, sum(ca) from matable2 group by idclient
Le plus compliqué à implémenter, c'est le trigger qui alimente et met à
jour cette table...
--
Philippe.
En quick and dirty, sous reserve que la jeu de test soit avec un
idclient à 2 sur les 4 dernieres lignes,
select ca.idclient, sum(ca) from
(select row_number() OVER (order by idclient, ca desc) as index, t1.*
from matable as t1) as ca,
(select idclient, min(i)+2 as index from (select row_number() OVER
(order by idclient, ca desc) as i, t2.* from matable as t2) as r1 group
by idclient) as indexes
where
ca.idclient = indexes.idclient and
ca.index <= indexes.index
group by ca.idclient order by ca.idclient
;
select
ca.idclient, sum(ca)
from
(
select
row_number() OVER (order by idclient, ca desc) as index,
t1.*
from matable as t1
) as ca,
(
select
idclient, min(i)+2 as index
from
(
select
row_number() OVER (order by idclient, ca desc) as i,
t2.*
from
matable as t2
) as r1
group by idclient
) as indexes
where
ca.idclient = indexes.idclient and
ca.index <= indexes.index
group by ca.idclient
order by ca.idclient;
L'idée générale est simplement de numéroter les lignes avec row_number
et de ne garder que les 3 premières pour chaque idclient
Oui mais la on a a aucun moment géré les 3 principaux ca !
non ?
Etienne
Bravo. Ca marche très bien.
J'avais bien trouvé row_number() mais j'arrivai pas a m'en servir :)
Super !
Merci beaucoup.
Etienne.
De rien. J'aime bien ce genre de petit quizz qui montre que SQL est
quand même puissant. Ceci dit ma requete n'est ni garantie ni très
optimale. A utiliser avec beaucoup de précautions donc
Ben si, "matable2" ne contient que les 3 principaux CA par client s'ils
existent... Le tout, c'est d'alimenter correctement cette table.
Cela étant, AMHA, Alain Montfranc a donné une bien meilleure solution
que la mienne. Bravo à lui ! ;-)
--
Philippe.
WITH T AS
(SELECT idclient, refproduit, SUM(ca) AS CA,
RANK() OVER(ORDER BY SUM(ca) DESC) AS N
FROM matable
GROUP BY idClient, refproduit)
SELECT idclient, refproduit, CA
FROM T
WHERE N <= 3;
A +
Le 02/05/2012 15:51, Etienne a écrit :
--
Frédéric BROUARD - expert SGBDR et SQL - MVP SQL Server - 06 11 86 40 66
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Enseignant Arts & Métiers PACA, ISEN Toulon et CESI/EXIA Aix en Provence
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
*********************** http://www.sqlspot.com *************************