如何在蜂巢中找到hh:mm:ss的平均值 [英] How to Find the average of hh:mm:ss in hive

问题描述

考虑一下，我的蜂巢表的列为script_name，start_time，end_time，duration.开始时间，结束时间和持续时间的格式为hh:mm:ss.我的要求是找到最近7天这些列的平均时间并放入文件中.

Consider i have hive table with columns script_name, start_time, end_time, duration. Start time, end time and duration are in the format of hh:mm:ss. My requirement is to find the average time of these columns for last 7 days and put into a file.

推荐答案

转换为unix_timestamp，求和，除以3，转换为bigint，然后转换回HH:mm:ss:

Convert to unix_timestamp, sum, divide by 3, convert to bigint and convert back to HH:mm:ss:

with data as --Data example. Use your table instead
(select '12:10:30' start_time,'01:10:00' end_time, '02:10:00' duration)

select from_unixtime(cast((unix_timestamp(start_time,'HH:mm:ss')+ unix_timestamp(end_time,'HH:mm:ss')+unix_timestamp(duration,'HH:mm:ss'))/3 as bigint),'HH:mm:ss') from data;

结果:

05:10:10

在此处查看测试: http://demo.gethue.com /hue/editor?editor = 285484& type = hive

对于单列:

转换为unix时间戳，以秒为单位计算平均值，转换为bigint(平均值为double，会有几分之一秒的精度损失)，最后将其转换回字符串时间格式:

Convert to unix timestamp, calculate average in seconds, convert to bigint (average is double, there will be some fraction of second precision loss), and finally convert it back to the string time format:

with data as --Data example. Use your table instead
(select stack(2,'12:10:30','01:10:00') as timeStr)

select from_unixtime(cast(avg(unix_timestamp(timeStr,'HH:mm:ss'))as bigint),'HH:mm:ss') from data;

结果:

06:40:15

在此处查看测试: http://demo.gethue.com /hue/editor?editor = 285464& type = hive

这篇关于如何在蜂巢中找到hh:mm:ss的平均值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！