sql - Hive - 创建用最常见的值替换所有值的数据集

Question

我需要创建一个数据集，其中包含与源表相同的行，但将出生日期替换为为该人找到的最常见的出生日期值。如果有平局，则应使用最近的日期。

输入

id  first_name  last_name  dob       date
---------------------------------------------
1   john        doe        06/11/85  01/01/17
2   john        doe        06/11/86  01/01/17
3   john        doe        06/11/86  01/01/17
4   jane        doh        01/06/87  01/01/17
5   jane        doh        01/01/80  01/02/17

输出

1 john doe 06/11/86 01/01/17
2 john doe 06/11/86 01/01/17
3 john doe 06/11/86 01/01/17
4 jane doh 01/01/80 01/01/17
5 jane doh 01/01/80 01/02/17

John Doe 于 86 年 6 月 11 日更新(最常见)。 jane doh 更新到 01/01/80(打破了决胜局)。

我最近的尝试基于一个类似的例子:

SELECT a.id, a.first_name, a.last_name, a.date, b.id  FROM 
(SELECT first_name, last_name,dob,count(*) FROM table group by first_name, last_name,dob having count(*) in 
(SELECT max(total) AS freq FROM 
(SELECT first_name, last_name, dob, count(*) AS total FROM table group by first_name, last_name, dob) 
AS test_temp group by first_name, last_name)
) a   join (select * FROM table) b on (a.id = b.id)

我不仅想要一个解决方案，还想要一个我可以从中学习的解释。

Answer 1

SELECT a.id, a.first_name, a.last_name, b.dob, a.date
FROM table a
JOIN (SELECT DISTINCT id, first_name, last_name, dob, count(dob) AS cnt
FROM table ORDER BY cnt DESC LIMIT 1) b
ON (a.first_name=b.first_name) AND (a.last_name=b.last_name)
我会试试这个。我用一个子选择加入了基表，以获得最常见的 dob。与 ORDER BY cnt DESC LIMIT 1我让舒尔得到最常见的出生日期而不会遇到麻烦 max(count(dob))不是独一无二的。然后我就用相同的 firt_name 将该 dob 加入到每条记录中和 last_name .我希望这对你有帮助。