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
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
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.