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.
Vidéos High-Tech et Jeu Vidéo
Téléchargements
Vos réponses Page 1 / 2
Gagnez chaque mois un abonnement Premium avec GNT : Inscrivez-vous !
Trier par : date / pertinence
Ph. B.
Le #24448771
Etienne a écrit :
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.



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.
Etienne
Le #24448851
Le 03/05/2012 09:48, Ph. B. a écrit :
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



C'est vrai que c'est pas mal.
il doit bien y avoir un moyen de calculer cet ordre !!!

Reste à affiner la règle de classement pour les ex-æquo et à implémenter
le trigger pour alimenter la colonne "ordre"...



Les ex-aequo n'ont pas d'impact sur le résultat (tant qu'on prend les 3
premiers).

On pourrait aussi passer par une table temporaire (ou non) ne retenant
que les 3 principaux ca par produit et client...



Ben non justement, on ne peut pas faire ça !
Si on pouvait fabriquer cette table temporaire, l'affaire serait jouée...

Etienne.
Ph. B.
Le #24448951
Etienne a écrit :
Le 03/05/2012 09:48, Ph. B. a écrit :
On pourrait aussi passer par une table temporaire (ou non) ne retenant
que les 3 principaux ca par produit et client...



Ben non justement, on ne peut pas faire ça !



Postgres (que je connais très imparfaitement) ne gère pas les tables
temporaires ?

Si on pouvait fabriquer cette table temporaire, l'affaire serait jouée...



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.
Alain Montfranc
Le #24448941
Le 02/05/2012, Etienne a supposé :
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.



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
;
Alain Montfranc
Le #24448961
Un peu mieux indenté :

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
Etienne
Le #24449621
Le 03/05/2012 11:01, Ph. B. a écrit :
Etienne a écrit :
Le 03/05/2012 09:48, Ph. B. a écrit :
On pourrait aussi passer par une table temporaire (ou non) ne retenant
que les 3 principaux ca par produit et client...



Ben non justement, on ne peut pas faire ça !



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...



Oui mais la on a a aucun moment géré les 3 principaux ca !
non ?

Etienne
Etienne
Le #24449711
Le 03/05/2012 11:17, Alain Montfranc a écrit :

Bravo. Ca marche très bien.
J'avais bien trouvé row_number() mais j'arrivai pas a m'en servir :)

Super !
Merci beaucoup.

Etienne.
Alain Montfranc
Le #24449701
Etienne vient de nous annoncer :
Le 03/05/2012 11:17, Alain Montfranc a écrit :

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
Ph. B.
Le #24449881
Etienne a écrit :
Le 03/05/2012 11:01, Ph. B. a écrit :
Etienne a écrit :
Le 03/05/2012 09:48, Ph. B. a écrit :
On pourrait aussi passer par une table temporaire (ou non) ne retenant
que les 3 principaux ca par produit et client...



Ben non justement, on ne peut pas faire ça !





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...



Oui mais la on a a aucun moment géré les 3 principaux ca !
non ?



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.
SQLpro
Le #24515531
Quelque chose comme :

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 :
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.







--
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 *************************
Publicité
Suivre les réponses
Poster une réponse
Anonyme