hadoop - 配置单元 : group column based on max value

Question

我有一个包含字段的表

date       value
10-02-1900 23
09-05-1901 22
10-03-1900 10
10-02-1901 24

....

我必须返回每年的最大值即，

1900 23
1901 24

我尝试了以下查询，但得到了错误的答案。

SELECT YEAR(FROM_UNIXTIME(UNIX_TIMESTAMP(date,'dd-mm-yyyy'))) as date,MAX(value) FROM teb GROUP BY date;

有人可以建议我查询吗？

Answer 1

选项 1

select      year(from_unixtime(unix_timestamp(date,'dd-MM-yyyy'))) as year
           ,max(value)                                             as max_value
from        t
group by    year(from_unixtime(unix_timestamp(date,'dd-MM-yyyy')))
;

选项 2

Hive 2.2.0 之前

set hive.groupby.orderby.position.alias=true;

从 Hive 2.2.0 开始

set hive.groupby.position.alias=true;

---

select      year(from_unixtime(unix_timestamp(date,'dd-MM-yyyy'))) as date
           ,max(value)
from        t
group by    1
;

---

+------+-----------+
| year | max_value |
+------+-----------+
| 1900 |        23 |
| 1901 |        24 |
+------+-----------+

附言

另一种提取年份的方法:

from_unixtime(unix_timestamp(date,'dd-MM-yyyy'),'yyyy')