Hive中如何限制name列最多返回3个不同值（保留重复记录）

需求很明确：查询时要让结果里的name列最多保留3个不同的值，但允许同一name下因id、year不同产生的重复记录。DISTINCT因为是对整行去重，显然满足不了这个需求，下面给两种通用的实现方法：

方法一：子查询+关联查询（兼容多数数据库） #

先通过子查询筛选出最多3个不同的name值，再关联原表获取这些name对应的所有记录。如果需要按name的出现频率优先保留，可以加排序逻辑：

按name排序取前3个 #

SELECT t.id, t.name, t.year
FROM your_table t
JOIN (
    -- 先取出3个不同的name
    SELECT DISTINCT name
    FROM your_table
    LIMIT 3
) sub ON t.name = sub.name;

按name出现次数从多到少取前3个 #

如果想优先保留出现次数多的name，可以用GROUP BY统计次数后排序：

SELECT t.id, t.name, t.year
FROM your_table t
JOIN (
    SELECT name
    FROM your_table
    GROUP BY name
    ORDER BY COUNT(*) DESC  -- 按出现次数降序
    LIMIT 3
) sub ON t.name = sub.name;

注意：SQL Server中需要把LIMIT 3换成SELECT TOP 3，Oracle则用FETCH FIRST 3 ROWS ONLY

方法二：窗口函数（适用于支持窗口函数的数据库，如MySQL 8+、PostgreSQL、SQL Server） #

用DENSE_RANK()窗口函数给不同的name排名，然后筛选排名≤3的记录，这样就能保留这些name的所有行：

按name排序排名 #

WITH ranked_names AS (
    SELECT 
        id, name, year,
        -- 给每个不同的name分配排名
        DENSE_RANK() OVER (ORDER BY name) AS name_rank
    FROM your_table
)
SELECT id, name, year
FROM ranked_names
WHERE name_rank <= 3;

按name出现次数排名 #

同样如果要优先保留高频name，可以基于出现次数排序：

WITH ranked_names AS (
    SELECT 
        id, name, year,
        DENSE_RANK() OVER (ORDER BY COUNT(*) OVER (PARTITION BY name) DESC) AS name_rank
    FROM your_table
)
SELECT id, name, year
FROM ranked_names
WHERE name_rank <= 3;

这两种方法都能实现需求：既限制了name的不同值数量，又保留了同一name下的所有重复记录。

内容的提问来源于stack exchange，提问作者Ayushi Jain