埋点-延迟率

一、建表语句

create table target_tab(
  version_flag bigint comment '版本标签  2:web延迟  3ios直播延迟  4安卓直播延迟',
  query_version string comment '查询版本号',
  is_video int comment '直播标签 1直播 0非直播',
  sumcount bigint comment '总数',
  day_delay bigint comment '天延迟数',
  day_delay_rate string comment '天延迟率',
  span_day_delay bigint comment '跨天延迟数',
  span_day_delay_rate string comment '跨天延迟率')
partitioned by (dt int comment '分区')

二、模型代码

set hive.exec.dynamic.partition.mode = nonstrict;
insert overwrite table bdg_inf.dws_bhv_habo_measure_delay_rate_mb_di partition(dt)

--改造指标层--延迟率app层----web当天延迟60000  androi
d当天延迟60000  ios当天延迟100000
select
 version_flag
,query_version
,is_video
--总数
,case version_flag
    when 2 then sumcount                   --web条件
    when 3 then ios_video_sumcount          --ios条件
    when 4 then android_video_sumcount      --android条件
    else -1
end as sumcount
--天延迟数
,case version_flag
    when 2 then day_delay                   --web条件
    when 3 then ios_video_day_delay         --ios条件
    when 4 then android_video_day_delay     --android条件
    else -1
end as day_delay
--天延迟率
,case version_flag
    when 2 then day_delay * 1.0 / sumcount                              --web条件
    when 3 then ios_video_day_delay * 1.0 / ios_video_sumcount          --ios条件
    when 4 then android_video_day_delay * 1.0 / android_video_sumcount  --android条件
    else -1
end as day_delay_rate
--跨天延迟数
,case version_flag
    when 2 then span_day_delay                  --web条件
    when 3 then ios_video_span_day_delay        --ios条件
    when 4 then android_video_span_day_delay    --android条件
    else -1
end as span_day_delay
--跨天延迟率
,case version_flag
    when 2 then span_day_delay * 1.0 / sumcount                                 --web条件
    when 3 then ios_video_span_day_delay * 1.0 / ios_video_sumcount             --ios条件
    when 4 then android_video_span_day_delay * 1.0 / android_video_sumcount     --android条件
    else -1
end as span_day_delay_rate
,dt
from 
(
    ------直播非直播条件不一样，所以得单独判断
    select
    dt,
    version_flag,
    query_version,
    is_video,
    --web总上报数
    count(1) AS sumcount,
    --web当天延迟数
    count(IF(abs(ngx_timestamp - event_timestamp) >= 60000,true,null)) as day_delay,
    --web跨天延迟数
    count(IF(event_time != dt,true,null)) as span_day_delay,

    --ios直播总上报数
    count(1) AS ios_video_sumcount,
    --ios直播当天延迟数
    count(IF(abs(ngx_timestamp - event_timestamp) >= 100000,true,null)) as ios_video_day_delay,
    --ios直播跨天延迟数
    count(IF(video_time != dt,true,null)) as ios_video_span_day_delay,

    --android直播总上报数
    count(1) AS android_video_sumcount,
    --android直播当天延迟数
    count(IF(abs(ngx_timestamp - event_timestamp) >= 60000,true,null)) as android_video_day_delay,
    --android直播跨天延迟数
    count(IF(video_time != dt,true,null)) as android_video_span_day_delay
    from(
        --对各个客户端条件打标签
        select
        ngx_timestamp
        ,event_timestamp
        ,event_time
        ,video_timestamp
        ,video_time
        ,biz_name
        ,client_type
        ,sdk_version
        ,app_version
        ,biz_type
        ,gif
        ,is_video
        ,label_map
        --统一Web和App的字段
        ,case
            when label_map['label_delay_02']='1' then 2  --web延迟条件
            when label_map['label_delay_03']='1' then 3  --ios直播延迟条件
            when label_map['label_delay_04']='1' then 4  --android直播延迟条件
            else -1
        end as version_flag
        ,IF(label_map['label_delay_02']='1',sdk_version,app_version) query_version
        ,dt
        from base_tab
        where dt = ${dt}
        and client_type not in(8, 9)
        and biz_type = 7
    ) label_tab
    group by 
    dt
    ,query_version
    ,version_flag
    ,is_video
) c1