蜂巢sql中发现的最新记录(hive sql find the latest record)

该表是：

create table test (
id string,
name string,
age string,
modified string)

这样的数据：

id    name   age  modifed
1     a      10   2011-11-11 11:11:11
1     a      11   2012-11-11 12:00:00
2     b      20   2012-12-10 10:11:12
2     b      20   2012-12-10 10:11:12
2     b      20   2012-12-12 10:11:12
2     b      20   2012-12-15 10:11:12

我想获得最新的记录（包括每个colums ID，姓名，年龄，体改）由ID组，如上面的数据，正确的结果是：

1     a      11   2012-11-11 12:00:00
2     b      20   2012-12-15 10:11:12

我这样做：

insert overwrite table t 
select b.id, b.name, b.age, b.modified 
from (
        select id,max(modified) as modified 
        from test 
        group by id
) a 
left outer join test b on (a.id=b.id  and a.modified=b.modified);

这个SQL可以得到正确的结果，但在海量数据，它运行缓慢。

**有没有办法做到这一点不左外连接？ **

Answer 1:

有蜂巢的SQL（我发现它在他们的吉拉bug报告之一）（一无证差不多的功能，使用结构可以让你这样做argmax（））秒。例如，如果你有这样一个表：

test_argmax
id,val,key
1,1,A
1,2,B
1,3,C
1,2,D
2,1,E
2,1,U
2,2,V
2,3,W
2,2,X
2,1,Y

你可以这样做：

select 
  max(struct(val, key, id)).col1 as max_val,
  max(struct(val, key, id)).col2 as max_key,
  max(struct(val, key, id)).col3 as max_id
from test_argmax
group by id

而得到的结果：

max_val,max_key,max_id
3,C,1
3,W,2

我认为，在VAL（第一结构元素）的关系的情况下，它会回落到第二列的比较。我还没有想出是否有用于获取各个列背出所产生的结构，也许使用named_struct总得简洁的语法？

Answer 2:

有一个相对较新的蜂巢SQL的功能解析函数和OVER子句。这应该做的工作，而不加入

select id, name, age, last_modified 
from ( select id, name, age, modified, 
              max( modified) over (partition by id) as last_modified 
       from test ) as sub
where   modified = last_modified

这是怎么回事的是，子查询生成一个新行LAST_MODIFIED一个额外的列，其具有相应的人的身份证最新修改的时间戳。（类似于哪个组会做），这里的关键是，子查询再次让你每行一个行的原始表，然后从该过滤器。

有机会的话，即使是简单的解决方案的工作原理：

select  id, name, age,  
        max( modified) over (partition by id) last_modified 
from test 
where   modified = last_modified

顺便说一句，同样的代码将在帕拉工作，太。

Answer 3:

试试这个：

select t1.* from test t1
join (
  select id, max(modifed) maxModified from test
  group by id
) s
on t1.id = s.id and t1.modifed = s.maxModified

小提琴这里。

左外连接解决方案在这里。

让我们知道哪一个跑得快:)

Answer 4:

只要稍微不同的方式比在以前的答案被回答。

下面的示例使用蜂巢窗函数找出最新记录，阅读更多在这里

SELECT t.id
    ,t.name
    ,t.age
    ,t.modified
FROM (
    SELECT id
        ,name
        ,age
        ,modified
        ,ROW_NUMBER() OVER (
            PARTITION BY id ORDER BY unix_timestamp(modified,'yyyy-MM-dd hh:mm:ss') DESC
            ) AS ROW_NUMBER   
    FROM test
    ) t
WHERE t.ROW_NUMBER <= 1;

修饰的是字符串，以便将其转换到使用时间戳unix_timestamp(modified,'yyyy-MM-dd hh:mm:ss')然后通过在时间戳施加顺序。

Answer 5:

试试这个

select id,name,age,modified from test
 where modified=max(modified)
 group by id,name

Answer 6:

如果妳可以确保该行已修改最大也有相同的ID行集中的最大年龄。

尝试

select id, name, max(age), max(modified) 
from test
group by id, name

Answer 7:

假设数据是这样的：

    id      name    age     modifed
    1       a       10      2011-11-11 11:11:11
    1       a       11      2012-11-11 12:00:00
    2       b       23      2012-12-10 10:11:12
    2       b       21      2012-12-10 10:11:12
    2       b       22      2012-12-15 10:11:12
    2       b       20      2012-12-15 10:11:12

然后上述查询的结果会给你 - （注意重复2，B具有相同的日期时间）

    1       a       11      2012-11-11 12:00:00
    2       b       22      2012-12-15 10:11:12
    2       b       20      2012-12-15 10:11:12

此查询运行其他组，是效率较低，但给出正确的结果 -

    select collect_set(b.id)[0], collect_set(b.name)[0], collect_set(b.age)[0], b.modified
    from
        (select id, max(modified) as modified from test group by id) a
      left outer join
        test b
      on
        (a.id=b.id and a.modified=b.modified)
    group by
      b.modified;

那么上述查询的结果会给你

    1       a       11      2012-11-11 12:00:00
    2       b       20      2012-12-15 10:11:12

现在，如果我们提高了查询一点 - 然后代替3个抄表它只能运行一个柯坪结果相同 -

    select id, collect_set(name)[0], collect_set(age)[0], max(modified)
    from test 
    group by id;

请注意 - 这将在情况下，你按领域产生较大的结果放缓。

Answer 8:

你可以得到所需要的结果，而无需使用左外连接是这样的：

SELECT * FROM（由ID从测试组中选择的ID，最大值（经修饰））测试，其中在（ID，修改）

http://sqlfiddle.com/#!2/bfbd5/42

文章来源: hive sql find the latest record