SQL:仅选择第二列的最频繁值是某个值的 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

我正在尝试检索所有视频 ID，其中最常见的分类是 24。(答案应该只有视频 ID 1 和 3)当我使用查询时:(来自 How to select most frequent value in a column per each id group? )

    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 docs
• having docs