HIVE中相同acct_id多条记录时取create_date最新记录的方法

现有Hive表包含acct_id、name、role、create_date四个字段，需求为相同acct_id存在多条记录时，仅返回create_date最新的条目，样例数据如下：

推荐实现：窗口函数法 #

Hive原生支持ROW_NUMBER()窗口函数，写法简洁、大数据量下性能更优，是这类分组取TopN场景的首选方案，代码如下：

SELECT
  acct_id,
  name,
  role,
  create_date
FROM (
  SELECT
    *,
    ROW_NUMBER() OVER (PARTITION BY acct_id ORDER BY create_date DESC) AS rn
  FROM 替换为你的实际表名
) tmp
WHERE rn = 1;

逻辑说明：

• 子查询中通过PARTITION BY acct_id按账号ID切分分片，每个分片内按create_date倒序排列，ROW_NUMBER()会为分片内每条记录生成从1开始的连续序号，时间最新的记录序号必然为1
• 外层查询过滤rn=1的记录，即可得到每个账号对应的最新条目

注意：如果同一acct_id下存在多条create_date完全一致的记录，ROW_NUMBER()会随机返回其中一条；如果需要保留同时间的所有记录，可将ROW_NUMBER()替换为RANK()。

备选实现：聚合关联法 #

如果使用的Hive版本过低不支持窗口函数，可以用分组聚合后关联原表的方式实现：

SELECT
  t1.acct_id,
  t1.name,
  t1.role,
  t1.create_date
FROM 替换为你的实际表名 t1
JOIN (
  SELECT
    acct_id,
    MAX(create_date) AS latest_date
  FROM 替换为你的实际表名
  GROUP BY acct_id
) t2
ON t1.acct_id = t2.acct_id AND t1.create_date = t2.latest_date;

该方案的问题是如果同一账号存在多条相同最大时间的记录，会全部返回不会去重，且大表场景下join性能弱于窗口函数方案，优先推荐窗口函数写法。

针对给出的样例数据，执行上述语句后返回结果如下：

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