From: justin <justin@emproshunts.com>
To: Jasmin Dizdarevic <jasmin.dizdarevic@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Create custom aggregate function and custom sfunc
Date: Thu, 02 Jul 2009 13:37:28 -0400
Message-ID: <4A4CF058.6000704@emproshunts.com> (raw)
In-Reply-To: <a0eee4d40907020748p7cb9bad7mad8817e60e9ea89@mail.gmail.com>
References: <a0eee4d40907020748p7cb9bad7mad8817e60e9ea89@mail.gmail.com>
Jasmin Dizdarevic wrote:
> hi,
>
> i have to create a aggregate function which evaluates a maximum text
> value but with some conditions i have to take care of.
> is there a way to access a value set of each group?
> e.g.:
>
> 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
>
> i hope you know what i mean.
>
> ty
>
You don't have to create an aggregate function. I have similar problem
where the part numbers have to order based on the contents and the first
thing you have to do is split it apart then set the ordering you want.
This gives you an idea of what you can do and what the results look like.
If the data in the table is laid out as you describe with where 111 and
L1 are different fields its very easy and you don't have to create an
aggregate function
Select '111', 'L1',
regexp_replace( 'L1', '[1-9 `]+', ''),
regexp_replace( 'L1', '[a-zA-Z `]+', '')::integer
union
Select '111', 'L3',
regexp_replace( 'L3', '[1-9 `]+', ''),
regexp_replace( 'L3', '[a-zA-Z `]+', '')::integer
union
Select'111', 'L2',
regexp_replace( 'L2', '[1-9 `]+', ''),
regexp_replace( 'L2', '[a-zA-Z `]+', '')::integer
order by 3, 4
if the data is 111;L1 in a single field its still very easy. Example
like so
Select split_part('111;L1', ';',1),
split_part('111;L1', ';',2),
regexp_replace( split_part('111;L1', ';',2), '[1-9 `]+', ''),
regexp_replace( split_part('111;L1', ';',2), '[a-zA-Z `]+', '')::integer
union
Select split_part('111;L3', ';',1),
split_part('111;L3', ';',2),
regexp_replace( split_part('111;L3', ';',2), '[1-9 `]+', ''),
regexp_replace( split_part('111;L3', ';',2), '[a-zA-Z `]+', '')::integer
union
Select split_part('111;L2', ';',1),
split_part('111;L2', ';',2),
regexp_replace( split_part('111;L2', ';',2), '[1-9 `]+', ''),
regexp_replace( split_part('111;L2', ';',2), '[a-zA-Z `]+',
'')::integer
order by 3, 4 desc
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: justin@emproshunts.com, jasmin.dizdarevic@gmail.com
Subject: Re: Create custom aggregate function and custom sfunc
In-Reply-To: <4A4CF058.6000704@emproshunts.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox