Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss
PostgreSQL Somme groupée
8 views
Skip to first unread message
Etienne
May 2, 2012, 9:51:07 AM5/2/12
to
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.
Ph. B.
May 3, 2012, 3:48:56 AM5/3/12
to
Etienne a écrit :
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.
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
May 3, 2012, 4:34:34 AM5/3/12
to
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.
> 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
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"...
premiers).
> On pourrait aussi passer par une table temporaire (ou non) ne retenant
> que les 3 principaux ca par produit et client...
Si on pouvait fabriquer cette table temporaire, l'affaire serait jouée...
Etienne.
Ph. B.
May 3, 2012, 5:01:44 AM5/3/12
to
Etienne a écrit :
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)
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.
temporaires ?
> Si on pouvait fabriquer cette table temporaire, l'affaire serait jouée...
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
1, D, 40
1, E, 50
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
May 3, 2012, 5:09:46 AM5/3/12
to
Le 02/05/2012, Etienne a supposé :
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
;
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
May 3, 2012, 5:17:05 AM5/3/12
to
Un peu mieux indenté :
select
ca.idclient, sum(ca)
from
(
et de ne garder que les 3 premières pour chaque 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
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;
et de ne garder que les 3 premières pour chaque idclient
Etienne
May 3, 2012, 9:00:29 AM5/3/12
to
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 !
> 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 !
> 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...
non ?
Etienne
Etienne
May 3, 2012, 9:26:01 AM5/3/12
to
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.
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
May 3, 2012, 9:30:35 AM5/3/12
to
Etienne vient de nous annoncer :
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
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.
May 3, 2012, 10:10:16 AM5/3/12
to
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
May 28, 2012, 5:32:29 PM5/28/12
to
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 :
>
> Peut on faire ca en une seule requ�te ?
> par ce que pour un client bien pr�cis j'y arrive
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 *************************
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
>
> j'ai une table du style
>
> CREATE TABLE "matable" (
> "idclient" integer,
> "refproduit" text,
> "ca" integer,
> PRIMARY key (idclient, idproduit)
> );
>
>
> 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)
>
> 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
> 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.
>
>
>
--
> 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 *************************
Etienne
May 29, 2012, 8:29:28 AM5/29/12
to
Le 28/05/2012 23:32, SQLpro a écrit :
> 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;
Nan.
> 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;
Ca donne pas le résultat voulu.
Ca trie bien, mais il n'existe pas pour chaque idclient un
enregistrement pour lequel N vaut 1 par exemple.
Etienne
SQLpro
May 31, 2012, 3:35:09 AM5/31/12
to
CREATE TABLE matable (
idclient integer,
refproduit varchar(16),
idclient integer,
ca integer
);
INSERT INTO matable VALUES
(1, 'A', 10),
(1, 'B', 20),
(1, 'C', 30),
(1, 'D', 40),
(1, 'E', 50),
(2, 'A', 30),
(2, 'C', 50),
(2, 'D', 50),
(2, 'E', 10);
WITH T AS
(SELECT idclient, ca,
RANK() OVER(PARTITION BY idclient ORDER BY ca DESC) AS N
FROM matable)
SELECT idclient, SUM(ca) AS CA
FROM T
WHERE N <= 3
GROUP BY idclient;
WHERE N <= 3
idclient CA
----------- -----------
1 120
2 130
A +
--
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 *************************
Alain Montfranc
May 31, 2012, 5:52:22 AM5/31/12
to
Joli (je garde) mais ne marchera pas si on a des produits ex-aequos
(par exemple 4 CA identiques) car ils seront tous 4 classï¿œs en rang 1
(par exemple 4 CA identiques) car ils seront tous 4 classï¿œs en rang 1
Etienne
May 31, 2012, 6:00:11 AM5/31/12
to
c'est a cause du PARTITION sans doute. Faut que je regarde ce que fait
ce mot clé !!
Merci
Etienne
Alain Montfranc
May 31, 2012, 11:27:46 AM5/31/12
to
Dans son message précédent, Etienne a écrit :
Attention : ca bugge si tu as des exaequos
SQLpro
Jun 7, 2012, 10:49:52 AM6/7/12
to
Si vous voulez des exaequo "dense", DENSE_RANK() au lieu de RANK
0 new messages