postgresql array comparison using weight function or something similar

Question

I want to sort arrays. Postgresql array operators like < or > compare them by first, then second member and so on. It's not what I want. I want above were arrays whose members are bigger and equal as much as possible. For example: we have

[1,2,3]
[2,2,1]
[2,2,2]

sorted result

[2,2,2]
[2,2,1]
[1,2,3]

To achieve this I imagined a function 1. Sort each array in ascending order. 2. Divide each member in this order to fetch float. For example

1/2/2 = 0.25.
1/2/3 = 0.16. 
2/2/2 = 1

3. Multiply result of the 2-nd action to the sum of the array members.

   • 1 * 6 = 6
   • 0.25 * 5 = 1.25
   • 0.16 * 6 = 0.96

How to do this using reqular sql and not using loop which is rather slow? Or may be someone know better way to weigh arrays in postgresql? Any help will be appreciated.

To clarify the question, I should write the table structure

 CREATE TABLE array_example (
     int_array  integer[]
 );

and requests:

insert into array_example values ('{1,2,3}'), ('{2,2,2}'), ('{2,2,1}');
select * from array_example order by int_array desc

It will give me the sorted result that I described above. But if I add extra row

insert into array_example values ('{3,2,1}');

it will be the first, which is not what I want, so order by uses another math... The last inserted row must be on the third-fourth position.

Answer 1

If you install the intarray extension, this can be done quite easily I think:

with sorted as (
  select int_array, sort(int_array) as sarray
  from array_example 
)
select int_array
from sorted
order by (sarray[1]::decimal / sarray[2]::decimal / sarray[3]::decimal) * (sarray[1] + sarray[2] + sarray[3]);

Note that the above lacks error checking: namely that no value equals zero (would cause a division by zero error) and it will also yield null if the array does not always contain (at least) three elements.

Of course you could wrap the formula (and the sorting) into a function, which would make the query a bit shorter:

create or replace function array_weight(p_array int[])
returns decimal
as
$$
declare
  l_sorted int[];
begin
  l_sorted := sort(p_array);
  return (l_sorted[1]::decimal / l_sorted[2]::decimal / l_sorted[3]::decimal) * (l_sorted[1] + l_sorted[2] + l_sorted[3]);
end;
$$
language plpgsql
immutable;

and then use:

select *
from array_example
order by array_weight(int_array) desc;

---

The sorting of the array can be done without the intarray module, but I would not expect a good performance:

create or replace function array_weight(p_array int[])
returns decimal
as
$$
declare
  l_sorted  int[];
begin
  select array_agg(i order by i)
    into l_sorted
  from (select unnest(p_array) i) t;
  return (l_sorted[1]::decimal / l_sorted[2]::decimal / l_sorted[3]::decimal) * (l_sorted[1] + l_sorted[2] + l_sorted[3]);
end;
$$
language plpgsql
immutable;