选择每个用户 PostgreSQL 的最高出现值

Question

>我有一个用户ID和值的表。我想选择每个用户最常出现的值。

这是表格的外观...

UserID  |  Value
--------------------
501     |    1
501     |    1
24      |    2
55      |    1
55      |    1
55      |    3

所以我希望我的查询输出表看起来像...

UserID  |  Value
--------------------
501     |    1
24      |    2
55      |    1

Answer 1

一种方法是使用 count 和 row_number 窗口函数，如下所示：

select UserID, Value  from (
    select t.*, row_number() over(partition by UserID order by cnt desc) as rn from (
        select your_table.*, count(*) over(partition by UserID, Value) as cnt from your_table
    ) t
) tt
where rn = 1

Answer 2

我认为在您的示例中用户 55 的最高出现值是 3，对吗？

select max(Value), UserID
from table
group by UserID

编辑：拉胡尔说的。

Answer 3

你需要group by和count()喜欢

select UserID,  count(`Value`) as `value`
from tbl1
group by UserID  
order by count(`Value`) desc

Answer 4

SELECT DISTINCT ON(id)
  id,
  value
FROM
  (
    SELECT
      id,
      value,
      count(value) AS cnt
    FROM
      tbl
    GROUP BY id, value
  ) mq
ORDER BY id, cnt DESC