hadoop - 根据列的子集过滤掉重复的行

Question

我有一些看起来像这样的数据:

ID,DateTime,Category,SubCategory
X01,2014-02-13T12:36:14,Clothes,Tshirts
X01,2014-02-13T12:37:16,Clothes,Tshirts
X01,2014-02-13T12:38:33,Shoes,Running
X02,2014-02-13T12:39:23,Shoes,Running
X02,2014-02-13T12:40:42,Books,Fiction
X02,2014-02-13T12:41:04,Books,Fiction

我想做的是像这样及时保留每个数据点的一个实例(我不关心哪个实例及时):

ID,DateTime,Category,SubCategory
X01,2014-02-13T12:36:14,Clothes,Tshirts
X02,2014-02-13T12:39:23,Shoes,Running
X02,2014-02-13T12:40:42,Books,Fiction

不幸的是，根据Hive Language Manual , Hive 的 DISTINCT 表达式适用于整个表，所以做这样的事情不是一个选项:

SELECT DISTINCT(ID, SubCategory),
       DateTime,
       Category
FROM sometable

如何获取上面的第二个表格？提前致谢!

Answer 1

SQL 中这种事情的常用方法是分组依据:

select ID, category, subcategory, min(datetime) datetime
from sometable
group by ID, category, subcategory