Teradata按日、周统计用户系统驻留时长实现方法咨询

本方案完全匹配需求逻辑：按用户分别统计每日首条操作记录到末条操作记录的差值作为当日驻留时长，再统计该日期所属周内，用户首条操作到末条操作的差值作为周度驻留时长，最终输出要求的三个核心字段。 #

实现代码 #

WITH user_daily_agg AS (
    SELECT
        ASSIGNEDTO AS user_id,
        CAST(op_ts AS DATE) AS stat_date,
        EXTRACT(YEAR FROM op_ts) AS stat_year,
        -- 如需调整周首日规则可替换此处周数获取函数
        EXTRACT(WEEK FROM op_ts) AS stat_week,
        MIN(op_ts) AS day_first_op_ts,
        MAX(op_ts) AS day_last_op_ts,
        -- 当日驻留时长按小时计算，保留2位小数
        ROUND((MAX(op_ts) - MIN(op_ts)) SECOND(4) / 3600, 2) AS daily_duration_hour
    FROM (
        -- 转换原始字符型时间为Timestamp格式
        SELECT 
            ASSIGNEDTO,
            TO_TIMESTAMP(StartDate, 'MM/DD/YYYY HH24:MI') AS op_ts
        FROM your_operation_table -- 替换为你的原始数据表名
    ) t
    GROUP BY 1,2,3,4
)
SELECT
    user_id AS 用户标识,
    daily_duration_hour AS 当日驻留时长_小时,
    -- 周度驻留时长：当周最早操作到最晚操作的时间差，按小时计算保留2位小数
    ROUND((MAX(day_last_op_ts) OVER(PARTITION BY user_id, stat_year, stat_week) 
            - MIN(day_first_op_ts) OVER(PARTITION BY user_id, stat_year, stat_week)) SECOND(4) / 3600, 2) AS 对应周度驻留时长_小时
FROM user_daily_agg
ORDER BY user_id, stat_date;

关键调整说明 #

• 时间格式适配：如果原始时间字段为12小时制带AM/PM标识，可将TO_TIMESTAMP的格式参数替换为'MM/DD/YYYY HH:MI AM'即可完成正确转换。
• 周规则调整：Teradata默认EXTRACT(WEEK FROM xxx)返回的周数以周一为周首日，若需要周日为周首日的统计规则，可替换为TD_SUNDAY_WEEK_OF_YEAR(op_ts)获取周数，避免周划分偏差。
• 时长格式自定义：当前代码返回小时数值（保留2位小数），若需要输出时分格式（如08:27代表8小时27分），可将时长计算逻辑替换为(MAX(op_ts) - MIN(op_ts)) HOUR TO MINUTE。
• 周度逻辑可选调整：如果周度驻留时长需求为当周每日驻留时长的累加值，可将周度时长计算部分替换为SUM(daily_duration_hour) OVER(PARTITION BY user_id, stat_year, stat_week)即可。

内容的提问来源于stack exchange，提问作者Tekno Joe