Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id D7E29633180 for ; Fri, 3 Jul 2009 04:14:47 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 71185-04 for ; Fri, 3 Jul 2009 04:14:42 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from fg-out-1718.google.com (fg-out-1718.google.com [72.14.220.156]) by mail.postgresql.org (Postfix) with ESMTP id A4556633135 for ; Fri, 3 Jul 2009 04:14:45 -0300 (ADT) Received: by fg-out-1718.google.com with SMTP id e12so650036fga.0 for ; Fri, 03 Jul 2009 00:14:44 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:in-reply-to:references :date:message-id:subject:from:to:content-type; bh=fZ+ZBePZ5FmjGCvc67dTmjoPiNyMgksyNGuSjEMETiQ=; b=HvWc2V2ENtakvaqmjdJPqdQ0rKfyyWF9XzT2laj8M5bdvprmUOHWNasZ6RnYeZI/8k /wGbcjZSykHJfXJ04Fuix261QspHPIwTlvmO4PfWZax9gQAXJbyyYJCmOTPDK7R3OW2V C+XGOvtFeW3i+RrvWXb6JSU8ksLKv8I7PxEpI= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :content-type; b=rMF664wnlv509RwX22XotreU/zy//LvRD2B4belaPWUctjZwGjFMvTVhmIJo37reDh b3/18G+K8QvtTV4RxNu2aho9HZsnWgAySVHbUdptYqnIqZ8liPf3UtrU02XGhJ/ZFpDf neZbKQ69WUzquM+BQBq8s1dmEwB3FU8NeoJ+4= MIME-Version: 1.0 Received: by 10.86.76.10 with SMTP id y10mr887963fga.63.1246605284048; Fri, 03 Jul 2009 00:14:44 -0700 (PDT) In-Reply-To: <4A4D7F9B.2020107@free.fr> References: <407d949e0907021421qb1df8b1o9468f343298f08dd@mail.gmail.com> <4A4D7F9B.2020107@free.fr> Date: Fri, 3 Jul 2009 09:14:44 +0200 Message-ID: Subject: Re: Create custom aggregate function and custom sfunc From: Jasmin Dizdarevic To: nha , pgsql-sql@postgresql.org Content-Type: multipart/alternative; boundary=000e0cd2511e2181e1046dc7e91b X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200907/14 X-Sequence-Number: 32755 --000e0cd2511e2181e1046dc7e91b Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Hi, thank you everybody for your help. The classy solution posted by nha works great! Regards Jasmin 2009/7/3 nha > Hello, > > Le 2/07/09 23:21, Greg Stark a =E9crit : > > On Thu, Jul 2, 2009 at 3:48 PM, Jasmin >> Dizdarevic wrote: >> >>> customer ; seg >>> 111 ; L1 >>> 111 ; L2 >>> 111 ; L1 >>> 222 ; L3 >>> 222 ; L3 >>> 222 ; L2 >>> >>> the result should look like this: >>> >>> 111: L1 - because L1 is higher than L2 and ratio of L1 : L2 is 2 : 1 >>> 222: L3 - because L3 is higher than L2 and ratio of L3 : L2 is 2 : 1 >>> >> >> Are you just looking for the most frequent seg for each customer? >> >> select distinct on (customer) customer,seg >> from (select customer, seg, count(*) as n from tab group by seg) >> order by customer, n desc >> >> That doesn't give the ratios though. >> >> > Some errors would occur with the above query from within the subquery > because of a projection on customer column that is not a grouped column > (neither an uniquely identified column by seg--seg is assumed not to be a > primary key here according to the given examples of value). By the way, t= he > ordered column n is not a member of the projected columns. > > Nevertheless, in the same direction as Greg Stark, the following query > would approach the target result: > > SELECT T5A.customer, T5A.seg > FROM ( > SELECT T2.customer, T2.seg, COUNT(*) AS nb > FROM cst T2 > GROUP BY T2.customer, T2.seg > ) T5A INNER JOIN ( > SELECT T4.customer, MAX(T4.nb) AS maxNb > FROM ( > SELECT T2B.customer, T2B.seg, COUNT(*) AS nb > FROM cst T2B > GROUP BY T2B.customer, T2B.seg > ) T4 > GROUP BY T4.customer > ) T5B > ON T5A.customer =3D T5B.customer > AND T5A.nb =3D T5B.maxNb > > where 'cst' is the reference table including 'customer' and 'seg' columns= . > This query considers a join between two tables: > - the first table, aliased T5A, counts the multiplicity for each couple o= f > 'customer' and 'seg'; > - the secund table, aliased T5B, reveals the highest multiplicity for eac= h > 'customer' with regard to each 'seg' attached to customer. > Then the join only retains the couples of 'customer' and 'seg' whom > multiplicity equals the higher for the 'customer' of the current couple. > > Each multiplicity may be easily added to the resulting records by spannin= g > the projected columns with T5A.nb column. A sort of ratio may also be add= ed; > one aggregated column may first be added to table T4 as SUM(T4.nb) -- tha= t > is, sum of multiplicity for each customer; then the resulting join may > compute the ratio between the highest multiplicity and the sum of > multiplicity for each customer, in addition to the seg (or list of seg) f= or > which the multiplicity is the highest. > > Here is the modified query: > > SELECT T5A.customer, T5A.seg, T5A.nb/T5B.sumNb > FROM ( > SELECT T2.customer, T2.seg, COUNT(*) AS nb > FROM cst T2 > GROUP BY T2.customer, T2.seg > ) T5A INNER JOIN ( > SELECT T4.customer, MAX(T4.nb) AS maxNb, SUM(T4.nb) AS sumNb > FROM ( > SELECT T2B.customer, T2B.seg, COUNT(*) AS nb > FROM cst T2B > GROUP BY T2B.customer, T2B.seg > ) T4 > GROUP BY T4.customer > ) T5B > ON T5A.customer =3D T5B.customer > AND T5A.nb =3D T5B.maxNb > > As is, this query may result to multiple couples of customer and seg with > the same customer value when many seg relie as many times as many others = for > the current customer. This "edge effect" may be avoided in many ways > depending on the original purpose. > > Regards. > > -- > nha / Lyon / France. > --000e0cd2511e2181e1046dc7e91b Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable
Hi,
=A0
thank you everybody for your help. The classy solution posted by nha w= orks great!
=A0
Regards
Jasmin

2009/7/3 nha <lyondif02@free.fr>
Hello,

Le 2/07/09 23:21, = Greg Stark a =E9crit :=20

On Thu, Jul 2, 2009 at 3:48 PM, = Jasmin
Dizdarevic<jasmin.dizdarevic@gmail.com> =A0wrote:
customer ; seg
111 ; L1
11= 1 ; L2
111 ; L1
222 ; L3
222 ; L3
222 ; L2

the result sh= ould look like this:

111: L1 - because L1 is higher than L2 and ratio of L1 : L2 is 2 : 1222: L3 - because L3 is higher than L2 and ratio of L3 : L2 is 2 : 1

Are you just looking for the most frequent seg for each cust= omer?

select distinct on (customer) customer,seg
=A0 =A0from (select custo= mer, seg, count(*) as n from tab group by seg)
=A0 order by customer, n = desc

That doesn't give the ratios though.

Some errors would occur with the above query from within the su= bquery because of a projection on customer column that is not a grouped col= umn (neither an uniquely identified column by seg--seg is assumed not to be= a primary key here according to the given examples of value). By the way, = the ordered column n is not a member of the projected columns.

Nevertheless, in the same direction as Greg Stark, the following query = would approach the target result:

SELECT T5A.customer, T5A.seg
FR= OM (
=A0 =A0 =A0 =A0SELECT T2.customer, T2.seg, COUNT(*) AS nb
=A0 = =A0 =A0 =A0FROM cst T2
=A0 =A0 =A0 =A0GROUP BY T2.customer, T2.seg
) T5A INNER JOIN (
=A0 = =A0 =A0 =A0SELECT T4.customer, MAX(T4.nb) AS maxNb
=A0 =A0 =A0 =A0FROM (=
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0SELECT T2B.customer, T2B.seg, COUNT(*) A= S nb
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0FROM cst T2B
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0GROUP BY T2B.customer, T2B.seg
=A0 =A0 = =A0 =A0) T4
=A0 =A0 =A0 =A0GROUP BY T4.customer
) T5B
ON T5A.custo= mer =3D T5B.customer
AND T5A.nb =3D T5B.maxNb

where 'cst'= is the reference table including 'customer' and 'seg' colu= mns. This query considers a join between two tables:
- the first table, aliased T5A, counts the multiplicity for each couple of = 'customer' and 'seg';
- the secund table, aliased T5B, r= eveals the highest multiplicity for each 'customer' with regard to = each 'seg' attached to customer.
Then the join only retains the couples of 'customer' and 'seg&#= 39; whom multiplicity equals the higher for the 'customer' of the c= urrent couple.

Each multiplicity may be easily added to the resultin= g records by spanning the projected columns with T5A.nb column. A sort of r= atio may also be added; one aggregated column may first be added to table T= 4 as SUM(T4.nb) -- that is, sum of multiplicity for each customer; then the= resulting join may compute the ratio between the highest multiplicity and = the sum of multiplicity for each customer, in addition to the seg (or list = of seg) for which the multiplicity is the highest.

Here is the modified query:

SELECT T5A.customer, T5A.seg, T5A.nb= /T5B.sumNb
FROM (
=A0 =A0 =A0 =A0SELECT T2.customer, T2.seg, COUNT(*)= AS nb
=A0 =A0 =A0 =A0FROM cst T2
=A0 =A0 =A0 =A0GROUP BY T2.customer= , T2.seg
) T5A INNER JOIN (
=A0 =A0 =A0 =A0SELECT T4.customer, MAX(T4.nb) AS maxNb, SUM(T4.nb) AS sumNb=
=A0 =A0 =A0 =A0FROM (
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0SELECT T2B.cust= omer, T2B.seg, COUNT(*) AS nb
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0FROM cst T2= B
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0GROUP BY T2B.customer, T2B.seg
=A0 =A0 =A0 =A0) T4
=A0 =A0 =A0 =A0GROUP BY T4.customer
) T5B
ON T= 5A.customer =3D T5B.customer
AND T5A.nb =3D T5B.maxNb

As is, this= query may result to multiple couples of customer and seg with the same cus= tomer value when many seg relie as many times as many others for the curren= t customer. This "edge effect" may be avoided in many ways depend= ing on the original purpose.

Regards.

--
nha / Lyon / France.
<= /font>





--000e0cd2511e2181e1046dc7e91b--