Postgresql get the most common value in array

user_11387016 0
Viewed 1072

I have a table with column with array values (after group by and array_agg function)

COLUMN_VALUE          | other_columns...
-----------:          | -------:
 {0.45,0.45,0.97,0.99}|        ..
 {0.45,0.45,0.85,0.99}|        ..
 {0.45,0.45,0.77,0.99}|        ..
 {0.45,0.45,0.10,0.99}|        ..

How do I get the most frequent value? (0.45 for each row for this case)

My guess goes to unnest and groupby again but I am trying to find something more robust and faster.

Query I am using to build the table


select column1, column2, column3, array_agg(column4) as prices
from tb
where some conditions
group by 1, 2, 3
aggregate-functions postgresql sql
2 Answers

You can get the most frequent value during aggregation using the mode() aggregate:

select column1, column2, column3, 
       array_agg(column4) as prices
       mode() within group (order by column4 desc) as most_frequent_price
from tb
where ...
group by 1, 2, 3

Online example

user_3303150

You can use unnest() and some aggregation logic:

select t.*, m.mode
from t cross join lateral
     (select el as mode
      from unnest(t.column_value) el
      group by el
      order by count(*) desc
      limit 1
     ) m;

I've called this mode because that is the statistical term for the most common value.

user_11440350
Related