数据预处理拆分sql

最新推荐文章于 2024-02-08 16:52:26 发布

MONKEYMOMO

最新推荐文章于 2024-02-08 16:52:26 发布

阅读量1.1k

点赞数

CC 4.0 BY-SA版权

分类专栏：机器学习 sql

本文链接：https://blog.youkuaiyun.com/weixin_35780812/article/details/79461623

此SQL脚本用于华为视频效果监控，处理点击和展示数据，生成报表，包括地域推荐和为您推荐场景的点击、用户转换率等指标。脚本涉及到数据解析、创建临时表、数据聚合以及将结果写入外部表。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

# --------------------------------------------------------------------------------------------------------
# @FileName: test_ads_rcm_movie_effect_monitoring_dm.sql
# @CopyRight: copyright(c)huawei technologies co.,ltd.1998-2017.all rights reserved.
# @Purpose: 华为视频效果监控报表
# @Describe：获取地域推荐及为您推荐各场景下的点击转换率及用户转换率
# @Input: dwd_evt_bisdk_customize_dm,0,0;ads_persona_label_0level_dev_relevant_ds,0,0;
# @Output: test_ads_rcm_movie_effect_monitoring_dm
# @Author: ywx402796
# @Version: Video(2.0.2,2.0.3)
# @Create: 2017-11-25
# ---------------------------------------------------------------------------------------------------------

beeline -e"
use biads;
##点击数据解析
DROP TABLE IF EXISTS temp.tmp_rcm_ywx402796_$date_client_column_video_click_ds;
create table if not exists temp.tmp_rcm_ywx402796_$date_client_column_video_click_ds as
select t.imei,t.oper_id,
        get_json_object(t.non_stru_field,'$.toType') as toType,
        if(t.non_stru_field rlike 'toID',get_json_object(t.non_stru_field,'$.toID'),'') as toID,
        if(t.non_stru_field rlike 'fromType',get_json_object(t.non_stru_field,'$.fromType'),'') as fromType,
        if(t.non_stru_field rlike 'fromID',get_json_object(t.non_stru_field,'$.fromID'),'') as fromID,
        if(t.non_stru_field rlike 'fromAlgId',get_json_object(t.non_stru_field,'$.fromAlgId'),'') as fromAlgId,
        if(t.non_stru_field rlike 'userId',get_json_object(t.non_stru_field,'$.userId'),'') as userId
from
(select imei,oper_id,regexp_replace(non_stru_field,'\\\\^','\\,') as non_stru_field
from bicoredata.dwd_evt_bisdk_customize_dm where pt_d=${start_time,-2,yyyyMMdd} and pt_service='movie' and oper_id='V001')t;
##展示数据解析
DROP TABLE IF EXISTS temp.tmp_rcm_ywx402796_$date_client_column_video_show_ds;
create table if not exists temp.tmp_rcm_ywx402796_$date_client_column_video_show_ds as
select
     t.imei
    ,t.oper_id
    ,t.userId
    ,t.viewID
    ,t.viewType
    ,t.categoryID
    ,split(a.content,'\\\\|')[0] as contentid
    ,split(a.content,'\\\\|')[1] as fromalgid
from
(
    select
         t.imei
        ,t.oper_id
        ,t.userId
        ,t.viewID
        ,t.viewType
        ,if(t.contList rlike 'categoryID',get_json_object(t.contList,'$.categoryID'),'') as categoryID
        ,if(t.contList rlike 'content',get_json_object(t.contList,'$.content'),'') as content
    from
    (
        select
            t.imei
           ,t.oper_id
           ,t.userId
     &