Postgresql array comparison using weighting function or something similar
I want to sort arrays. Postgresql array operators such as <or> compare them first, then the second member, and so on. This is not what I want. I want the above to be arrays whose members are as large and equal 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 function 1. Sort each array in ascending order. 2. Divide each term in this order to get a float. for example
1/2/2 = 0.25. 1/2/3 = 0.16. 2/2/2 = 1
-
Multiply the result of the 2nd action by the sum of the array elements.
- 1 * 6 = 6
- 0.25 * 5 = 1.25
- 0.16 * 6 = 0.96
How can I do this using reqular sql and not using a loop which is pretty slow? Or maybe someone knows a better way to weight arrays in postgresql? Any help would be appreciated.
To clarify the question, I have to write the structure of the table
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
This will give me the sorted result that I described above. But if you add an extra line
insert into array_example values ('{3,2,1}');
it will be the first one, which I don't want, so the ordering uses different math ... The last inserted row should be at the third-fourth position.
source to share
If you install the intarray extension it 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 there is no error checking in the example above: namely, that no value is zero (will result in a division by a zero error), and it will also give null
if the array does not always contain (at least) three elements.
Of course, you can wrap the formula (and sort) in a function that makes the query a little 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;
Sorting an array can be done without the intarray module, but I wouldn't expect 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;
source to share