CODEWITHSUNDEEP
sqlpostgresql
sandy
sandy

Reputation: 123

Get max on comma separated values in column

How to get max on comma separated values in Original_Ids column and get max value in one column and remaining ids in different column.

|Original_Ids   | Max_Id| Remaining_Ids |
|123,534,243,345| 534   | 123,234,345 |

Upadte - If I already have Max_id and just need below equation?

  Remaining_Ids = Original_Ids - Max_id

Thanks

Upvotes: 0

Views: 1919

Answers (3)

user330315
user330315

Reputation:

If you install the extension intarray this is quite easy.

First you need to create the extension (you have to be superuser to do that):

create extension intarray;

Then you can do the following:

select original_ids, 
       original_ids[1] as max_id,
       sort(original_ids - original_ids[1]) as remaining_ids
from (       
  select sort_desc(string_to_array(original_ids,',')::int[]) as original_ids
  from bad_design
) t

But you shouldn't be storing comma separated values to begin with

Upvotes: 1

S-Man
S-Man

Reputation: 23676

SQL Fiddle

You can use a window function (https://www.postgresql.org/docs/current/static/tutorial-window.html) to get the max element per unnested array. After that you can reaggregate the elements and remove the calculated max value from the array.

Result:

a                     max_elem     remaining
123,534,243,345       534          123,243,345
3,23,1                23           3,17
42                    42           
56,123,234,345,345    345          56,123,234

This query needs only one split/unnest as well as only one max calculation.

SELECT 
    a,
    max_elem,
    array_remove(array_agg(elements), max_elem) as remaining      -- C
FROM (
    SELECT 
        *, 
        MAX(elements) OVER (PARTITION BY a) as max_elem           -- B
    FROM (
        SELECT 
            a, 
            unnest((string_to_array(a, ','))::int[]) as elements  -- A
        FROM arrays
    )s
)s
GROUP BY a, max_elem

A: string_to_array converts the string list into an array. Because the arrays are treated as string arrays you need the cast them into integer arrays by adding ::int[]. The unnest() expands all array elements into own rows.

B: window function MAX gives the maximum value of the single arrays as max_elem

C: array_agg reaggregates the elements through the GROUP BY id. After that array_remove removes the max_elem value from the array.

If you do not like to store them as pure arrays but as string list again you could add array_to_string. But I wouldn't recommend this because your data are integer arrays and not strings. For every further calculation you would need this string cast. A even better way (as already stated by @stickybit) is not to store the elements as arrays but as unnested data. As you can see in nearly every operation should would do the unnest before.

Note:

It would be better to use an ID to adress the columns/arrays instead of the origin string as in SQL Fiddle with IDs

Upvotes: 1

sticky bit
sticky bit

Reputation: 37472

Thanks to the excellent possibilities of array manipulation in Postgres, this could be done relatively easy by converting the string to an array and from there to a set.

Then regular queries on that set are possible. With max() the maximum can be selected and with EXCEPT ALL the maximum can be removed from the set.

A set can then be converted to an array and with array_to_string() and the array can be converted to a delimited string again.

SELECT ids original_ids,
       (SELECT max(un.id::integer)
               FROM unnest(string_to_array(ids,
                                           ',')) un(id)) max_id,
       array_to_string(ARRAY((SELECT un.id::integer
                                     FROM unnest(string_to_array(ids,
                                                                 ',')) un(id)
                              EXCEPT ALL
                              SELECT max(un.id::integer)
                                     FROM unnest(string_to_array(ids,
                                                                 ',')) un(id))),
                       ',') remaining_ids
       FROM elbat;

Another option would have been regexp_split_to_table() which directly produces a set (or regexp_split_to_array() but than we'd had the possible regular expression overhead and still had to convert the array to a set).

But nevertheless you just should (almost) never use delimited lists (nor arrays). Use a table, that's (almost) always the best option.

Upvotes: 1

Related Questions