sql - Postgresql获取数组中最常见的值

Question

我有一个包含数组值列的表(在 group by 和 array_agg 函数之后)

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

如何获取最频繁的值？ (对于这种情况，每行 0.45)

我的猜测又是 unnest 和 groupby，但我正试图找到更强大、更快的东西。

我用来建表的查询

select column1, column2, column3, array_agg(column4) as prices
from tb
where some conditions
group by 1, 2, 3

Answer 1

您可以使用 mode() 聚合在聚合期间获取最频繁的值:

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