Re: Create custom aggregate function and custom sfunc

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