专题 › Hive › hive时间差计算

zhuqitian 发表于 2018-2-7 17:47:09

hive时间差计算

有个小需求，计算商品发货时间和下单时间差，然后算出48小时内发货的占比

select sum(case when ((unix_timestamp(deliveredtime) - unix_timestamp(paytime)) / 3600) > 48
then 1 else 0
end) as deviled_countgt48hour,
sum(case when ((unix_timestamp(deliveredtime) - unix_timestamp(paytime)) / 3600) <= 48
then 1 else 0
end) as deviled_countlte48hour,
sum(case when ((unix_timestamp(deliveredtime) - unix_timestamp(paytime)) / 3600) is null
then 1 else 0
end) as deviledtimeisnull,
(sum(case when ((unix_timestamp(deliveredtime) - unix_timestamp(paytime)) / 3600) <= 48
then 1 else 0
end) / count(ordercodeofsys)) as rate_deviledlte48hour from table

desehawk 发表于 2018-2-7 19:00:17

hive sql已结很灵活了。(unix_timestamp(deliveredtime)这是转换为Linux时间吧，deliveredtime是hive time类型？

zhuqitian 发表于 2018-2-8 10:11:05

desehawk 发表于 2018-2-7 19:00
hive sql已结很灵活了。(unix_timestamp(deliveredtime)这是转换为Linux时间吧，deliveredtime是hive time ...

是的，time类型

查看完整版本: hive时间差计算