常用代码20220602

最新推荐文章于 2024-07-24 14:28:41 发布

lzmaybe

最新推荐文章于 2024-07-24 14:28:41 发布

阅读量90

点赞数

分类专栏：数据分析文章标签： sql

本文链接：https://blog.csdn.net/lzmaybe/article/details/125104310

版权

数据分析专栏收录该内容

4 篇文章 0 订阅

订阅专栏

里面有常用的一些sql逻辑：

数字格式变化函数

排序函数

时间格式变化函数：

数字格式变化函数

coalesce 函数：

cast 函数：

排序函数

row_number 函数：

时间格式变化函数：

date_format(expo_dt, 'yyyy-MM-dd HH:mm:ss') expo_dt_str
unix_timestamp(date_format(expo_dt, 'yyyy-MM-dd HH:mm:ss')) expo_dt_sec

--合并场景曝光下载整理口径
with 
exp_base as  --曝光部分口径
(
select 
     s1.exp_plat ,s1.imei,s1.app_id,s1.sence,s1.is_tencent
    ,s1.req_id,s1.ctr,s1.pay_will,s1.ltv,s1.score,s1.algoid
    ,s1.expo_pos, s1.intervene   
    ,s1.expo_dur,s1.expo_dt, s1.expo_dt_str, s1.expo_dt_sec
from 
    (
    select
         day
        ,exp_plat
        ,imei
        ,app_id
        ,game_type
        ,game_name
        ,cast(is_tencent as string) is_tencent
        ,coalesce(split(expo_gameps, ',')[2],'') sence
        ,coalesce(split(expo_gameps, ',')[5], '') req_id   --请求id
        ,COALESCE(split(expo_gameps, ',')[7], '') ctr
        ,COALESCE(split(expo_gameps, ',')[8], '') pay_will
        ,COALESCE(split(expo_gameps, ',')[10], '') ltv
        ,COALESCE(split(expo_gameps, ',')[11], '') score
        ,coalesce(split(expo_gameps, ',')[6], '')  algoid   --算法id
        ,coalesce(split(expo_gameps, ',')[15], '')  intervene    --干预情况（0,1,2）
        ,expo_dt
        ,date_format(expo_dt, 'yyyy-MM-dd HH:mm:ss') expo_dt_str
        ,unix_timestamp(date_format(expo_dt, 'yyyy-MM-dd HH:mm:ss')) expo_dt_sec
        ,IF(expo_dur IS NULL OR expo_dur = '', '0', expo_dur) expo_dur
        ,cast(expo_pos as int) as expo_pos    --曝光位置
        ,row_number() over(partition by exp_plat, imei, app_id, coalesce(split(expo_gameps, ',')[5], ''),expo_pos order by expo_dt) as exp_order
    from
        bi_game_recommend.dm_rec_exposure_di
    where 
        day = '${dt}'
        and game_type in (1,2)
        --and expo_page <> ''
        --and exp_plat in ('as')
        AND imei is not null 
        AND imei <> ''
    group by 
         day
        ,exp_plat
        ,imei
        ,app_id
        ,game_type
        ,game_name
        ,cast(is_tencent as string) 
        ,coalesce(split(expo_gameps, ',')[2], '')
        ,coalesce(split(expo_gameps, ',')[5], '') 
        ,COALESCE(split(expo_gameps, ',')[7], '')
        ,COALESCE(split(expo_gameps, ',')[8], '') 
        ,COALESCE(split(expo_gameps, ',')[10], '')
        ,COALESCE(split(expo_gameps, ',')[11], '') 
        ,coalesce(split(expo_gameps, ',')[6], '') 
        ,coalesce(split(expo_gameps, ',')[15], '') 
        ,date_format(expo_dt, 'yyyy-MM-dd HH:mm:ss')
        ,unix_timestamp(date_format(expo_dt, 'yyyy-MM-dd HH:mm:ss')) 
        ,IF(expo_dur IS NULL OR expo_dur = '', '0', expo_dur)
        ,expo_dt
        ,cast(expo_pos as int)
        ,row_number() over(partition by exp_plat, imei, app_id, coalesce(split(expo_gameps, ',')[5], ''), expo_pos order by expo_dt)
    ) s1
    JOIN
    (
    select
        title, code, scene_code,  day
    FROM
        bi_game_recommend.dim_rec_page_code_ds
    WHERE
        day = '${dt}'
        and application_direct_type = 'combineEvaluation'
        and code = 'appstore.recommend.combine'   --as合并场景
    group by 
        title, code, scene_code, day
    ) s2 
    on s1.sence = s2.scene_code
    and s1.day = s2.day    --需要吗？？？
where 
    s1.exp_order = 1
group by 
     s1.exp_plat ,s1.imei,s1.app_id,s1.sence,s1.is_tencent
    ,s1.req_id,s1.ctr,s1.pay_will,s1.ltv,s1.score,s1.algoid
    ,s1.expo_pos ,s1.intervene   
    ,s1.expo_dur,s1.expo_dt, s1.expo_dt_str, s1.expo_dt_sec
)
,

dl_raw as --下载部分口径
(
select 
     s1.dl_plat ,s1.imei ,s1.app_id ,s1.sence ,s1.is_tencent
    ,s1.req_id ,s1.ctr ,s1.pay_will ,s1.ltv ,s1.score ,s1.algoid
    ,s1.dl_pos ,'0' as dl_dur ,s1.intervene   
    ,s1.dl_dt, s1.dl_dt_str, s1.dl_dt_sec
from 
    (
    select
         day
        ,dl_plat
        ,imei
        ,app_id
        ,game_type
        ,game_name
        ,split(dl_gameps,',')[2] sence
        ,cast(is_tencent as string) is_tencent
        ,coalesce(split(dl_gameps, ',')[5], '') req_id   --请求id
        ,COALESCE(split(dl_gameps, ',')[7], '') ctr
        ,COALESCE(split(dl_gameps, ',')[8], '') pay_will
        ,COALESCE(split(dl_gameps, ',')[10], '') ltv
        ,COALESCE(split(dl_gameps, ',')[11], '') score
        ,coalesce(split(dl_gameps, ',')[6], '') as algoid   --算法id
        ,coalesce(split(dl_gameps, ',')[15], '') as intervene    --干预情况（0,1,2）
        ,cast(dl_pos as int) as dl_pos    
        ,dl_dt    
        ,date_format(dl_dt, 'yyyy-MM-dd HH:mm:ss') dl_dt_str
        ,unix_timestamp(date_format(dl_dt, 'yyyy-MM-dd HH:mm:ss')) dl_dt_sec
        ,row_number() over(partition by dl_plat, imei, app_id, coalesce(split(dl_gameps, ',')[5], ''), dl_pos order by dl_dt) as exp_order
    from
        bi_game_recommend.dm_rec_download_di
    where 
        day = '${dt}'
        and game_type in (1,2)
        --and expo_page <> ''
        --and exp_plat in ('as')
        AND imei is not null 
        AND imei <> ''
    group by 
         day
        ,dl_plat
        ,imei
        ,app_id
        ,game_type
        ,game_name
        ,split(dl_gameps,',')[2] 
        ,cast(is_tencent as string) 
        ,coalesce(split(dl_gameps, ',')[5], '') 
        ,COALESCE(split(dl_gameps, ',')[7], '')
        ,COALESCE(split(dl_gameps, ',')[8], '') 
        ,COALESCE(split(dl_gameps, ',')[10], '')
        ,COALESCE(split(dl_gameps, ',')[11], '') 
        ,coalesce(split(dl_gameps, ',')[6], '') 
        ,coalesce(split(dl_gameps, ',')[15], '') 
        ,cast(dl_pos as int) 
        ,dl_dt   
        ,date_format(dl_dt, 'yyyy-MM-dd HH:mm:ss') 
        ,unix_timestamp(date_format(dl_dt, 'yyyy-MM-dd HH:mm:ss'))
        ,row_number() over(partition by dl_plat, imei, app_id, coalesce(split(dl_gameps, ',')[5], ''), dl_pos order by dl_dt)
    ) s1
    JOIN
    (
    select
        title, code, scene_code,  day
    FROM
        bi_game_recommend.dim_rec_page_code_ds
    WHERE
        day = '${dt}'
        and application_direct_type = 'combineEvaluation'
        and code = 'appstore.recommend.combine'   --as合并场景
    group by 
        title, code, scene_code, day
    ) s2 
    on s1.sence = s2.scene_code
    and s1.day = s2.day    --需要吗？？？
where 
    s1.exp_order = 1
group by 
     s1.dl_plat ,s1.imei ,s1.app_id ,s1.sence ,s1.is_tencent
    ,s1.req_id ,s1.ctr ,s1.pay_will ,s1.ltv ,s1.score ,s1.algoid
    ,s1.dl_pos ,'0' ,s1.intervene   
    ,s1.dl_dt, s1.dl_dt_str, s1.dl_dt_sec
)

--数据组合
SELECT
    IF(s.exp_plat IS NOT NULL,
            CONCAT_WS('#',
                    s.expo_dt_str,
                    s.exp_plat,
                    s.ctr,
                    s.pay_will,
                    s.ltv,
                    s.score,
                    s.algoid,
                    s.imei,
                    s.app_id,
                    s.req_id,
                    s.expo_pos,   --不需要了
                    s.intervene,
                    s.is_tencent
                    ),
            CONCAT_WS('#',
                    s.dl_dt_str,
                    s.dl_plat,
                    s.dl_ctr,
                    s.dl_pay_will,
                    s.dl_ltv,
                    s.dl_score,
                    s.dl_algoid,
                    s.dl_imei,
                    s.dl_app_id,
                    s.dl_req_id,
                    s.dl_pos,     --不需要了
                    s.dl_intervene,
                    s.dl_is_tencent)
    ) log_key,
    IF(s.dl_imei IS NOT NULL, '1', '0') label,
    IF(s.expo_dur IS NOT NULL, s.expo_dur, s.dl_dur) dur
FROM
    (
    SELECT 
        eb.exp_plat, eb.imei, eb.app_id,
        eb.req_id, eb.expo_pos, eb.expo_dt_str, eb.expo_dur,
        IF(CAST(eb.ctr as FLOAT) IS NOT NULL, eb.ctr, "0.0") ctr, 
        IF(CAST(eb.pay_will as FLOAT) IS NOT NULL, eb.pay_will, "0.0") pay_will, 
        IF(CAST(eb.ltv as FLOAT) IS NOT NULL, eb.ltv, "0.0") ltv, 
        IF(CAST(eb.score as FLOAT) IS NOT NULL, eb.score, "0.0") score, 
        eb.algoid, 
        eb.intervene, eb.is_tencent,
        dl.dl_plat, dl.imei as dl_imei, dl.app_id as dl_app_id,
        dl.req_id as dl_req_id, dl.dl_pos, dl.dl_dt_str, dl.dl_dur,
        IF(CAST(dl.ctr as FLOAT) IS NOT NULL, dl.ctr, "0.0") dl_ctr,
        IF(CAST(dl.pay_will as FLOAT) IS NOT NULL, dl.pay_will, "0.0") dl_pay_will,
        IF(CAST(dl.ltv as FLOAT) IS NOT NULL, dl.ltv, "0.0") dl_ltv,
        IF(CAST(dl.score as FLOAT) IS NOT NULL, dl.score, "0.0") dl_score,
        dl.algoid as dl_algoid,
        dl.intervene as dl_intervene, dl.is_tencent as dl_is_tencent
    FROM exp_base eb  
    FULL OUTER JOIN dl_raw dl
    ON eb.exp_plat = dl.dl_plat
    AND eb.imei = dl.imei
    AND eb.app_id = dl.app_id
    AND eb.req_id = dl.req_id
    AND eb.expo_pos = dl.dl_pos
    ) s