postgreSQL：仅选择第二列最常见的值是特定值的 id(按 id 分组)

Question

我在SQL中有一个表(使用postgres(，有两列：videoid ，分类

videoid | classification
1       |20
1       |24
1       |24
1       |24
1       |24
2       |20
2       |20
2       |20
2       |20
2       |24
2       |24
3       |24
3       |24

我正在尝试检索所有最常见的分类为 24 的视频 id。(答案应该只有视频ID 1和3( 当我使用查询时： (可从 如何为每个 id 组选择列中最常用的值？

SELECT DISTINCT ON (videoid) videoid, most_frequent_species FROM (
SELECT videoid, classification AS most_frequent_species, count(*) as _count
FROM userviewed
GROUP BY videoid, classification) a
ORDER BY videoid, _count DESC
;

我检索结果集：

videoid     | most_frequent_species
1       |24
2       |20
3       |24

但是当我尝试添加一个 WHERE 子句时：

WHERE classification = 24

我得到：

videoid| most_frequent_species
1      |24
2      |24
3      |24

如何创建仅检索的查询

videoid | most_frequent_species
1       |24
3       |24

Answer 1

您可以使用having子句(本质上是一个后组 where 子句(和mode函数：

select
videoid
from
userviewed
group by
videoid
having
mode() within group (order by classification) = 24

• mode文档
• having文档

Answer 2

一种方法使用distinct on然后过滤：

select *
from (select distinct on (videoid) videoid, category, count(*)
from userviewed
group by videoid, category
order by videoid, count(*) desc
) vc
where category = 24;