Hive中HH:MM:SS格式字符串时间列的周维度计算需求

我创建了Hive表agent_performance，其中avg_response_time、avg_resolution_time列存储HH:MM:SS格式的时间值，因不属于timestamp类型，故设为string类型。现需完成两项计算：

• 按周统计每位坐席的总贡献时长
• 按周统计每位坐席的平均响应时间

表结构如下：

create table agent_performance
(
    S_No int,
    `Date` string,
    Agent string,
    Total_chats int,
    avg_response_time string,
    avg_resolution_time string,
    avg_rating float,
    Total_feedback int
)
row format delimited
fields terminated by ',';

1. 按周统计每位坐席的总贡献时长 #

总贡献时长指坐席每周所有聊天的总解决时长，计算逻辑为：将每日的avg_resolution_time转换为秒数，乘以当日Total_chats得到当日总解决时长，按坐席和周累加求和后，再将总秒数转回HH:MM:SS格式。

SELECT
    Agent,
    date_format(to_date(`Date`), 'yyyy-WW') AS week,
    concat(
        lpad(floor(total_seconds / 3600), 2, '0'), ':',
        lpad(floor((total_seconds % 3600) / 60), 2, '0'), ':',
        lpad(total_seconds % 60, 2, '0')
    ) AS total_contribution_duration
FROM (
    SELECT
        Agent,
        date_format(to_date(`Date`), 'yyyy-WW') AS week,
        SUM(
            hour(avg_resolution_time) * 3600 + 
            minute(avg_resolution_time) * 60 + 
            second(avg_resolution_time)
        ) * Total_chats AS total_seconds
    FROM agent_performance
    GROUP BY Agent, date_format(to_date(`Date`), 'yyyy-WW')
) t;

2. 按周统计每位坐席的平均响应时间 #

采用加权平均计算，以每日Total_chats为权重，先计算每周总响应时长（每日响应时长秒数*当日聊天数之和），再除以每周总聊天数得到平均秒数，最后转回HH:MM:SS格式。

SELECT
    Agent,
    date_format(to_date(`Date`), 'yyyy-WW') AS week,
    concat(
        lpad(floor(avg_seconds / 3600), 2, '0'), ':',
        lpad(floor((avg_seconds % 3600) / 60), 2, '0'), ':',
        lpad(avg_seconds % 60, 2, '0')
    ) AS avg_weekly_response_time
FROM (
    SELECT
        Agent,
        date_format(to_date(`Date`), 'yyyy-WW') AS week,
        SUM(
            (hour(avg_response_time) * 3600 + 
             minute(avg_response_time) * 60 + 
             second(avg_response_time)) * Total_chats
        ) / SUM(Total_chats) AS avg_seconds
    FROM agent_performance
    GROUP BY Agent, date_format(to_date(`Date`), 'yyyy-WW')
) t;

关键逻辑说明 #

• 用date_format(to_date(Date), 'yyyy-WW')将日期转换为年-周数格式，实现按周分组
• 通过hour()、minute()、second()提取时间字符串的时分秒，转换为总秒数进行数值计算
• 用concat()和lpad()将计算后的秒数转回HH:MM:SS格式，确保时分秒为两位数字

内容的提问来源于stack exchange，提问作者Pranjal Tripathi