hadoop - 在Impala中按组减去最大，最小日期

Question

我有一个查询，可以在黑斑羚中按用户查找最大和最小日期:

select max(id_date) as last_tran
       ,min(id_date) as first_tran
       ,user
from table_1 a 
join table_2 d
on a.id = d.id
group by 3

然后，我想按用户减去最小和最大日期。

在Impala中，我尝试使用 date_sub函数，但是它不起作用。

select date_sub(last_tran, first_tran) as date_len
, user
from
(select max(id_date) as last_tran
       ,min(id_date) as first_tran
       ,user
from table_1 a 
join table_2 d
on a.id= d.id
group by 3) time
group by 1,2

看来 date_sub函数的第二个参数必须是代表天的整数。

我该如何解决？

Answer 1

为了使这项工作有效，应按以下方式使用datediff函数:

select datediff(last_tran, first_tran) as date_len
, user
from
(select max(id_date) as last_tran
       ,min(id_date) as first_tran
       ,user
from table_1 a 
join table_2 d
on a.id= d.id
group by 3) time
group by 1,2

该函数返回开始日期和结束日期之间的天数。从 documentation看来，天的返回值不能更改。