sql ODPS常用例子

2020/03/10 19:19
阅读数 34

1.新手漏斗

SELECT 
        a.pt as 日期, 
        count(DISTINCT a.device_id) as 新增激活用户,
        round(sum(g1)/count(DISTINCT a.device_id), 3) as 成功进入游戏曝光,
        round(sum(if(game_level>=1, 1, 0))/count(DISTINCT a.device_id), 3) as 通过第一关,
        round(sum(g2)/count(DISTINCT a.device_id), 3) as 1元弹框红包弹框曝光,
        round(sum(g3)/count(DISTINCT a.device_id), 3) as 游戏页一元红包点击,
        round(sum(g4)/count(DISTINCT a.device_id), 3) as 领取第一关奖励点击,
        round(sum(if(game_level>=2, 1, 0))/count(DISTINCT a.device_id), 3) as 通过第二关,
        round(sum(if(game_level>=3, 1, 0))/count(DISTINCT a.device_id), 3) as 通过第三关,
        round(sum(if(game_level>=4, 1, 0))/count(DISTINCT a.device_id), 3) as 通过第四关,
        round(sum(if(game_level>=5, 1, 0))/count(DISTINCT a.device_id), 3) as 通过第五关,
        round(sum(if(game_level>=6, 1, 0))/count(DISTINCT a.device_id), 3) as 通过第六关,
        round(sum(if(game_level>=9, 1, 0))/count(DISTINCT a.device_id), 3) as 通过第九关,
        round(sum(if(game_level>=10, 1, 0))/count(DISTINCT a.device_id), 3) as 通过第10关,
        round(sum(if(game_level>=15, 1, 0))/count(DISTINCT a.device_id), 3) as 通过第15关,
        round(sum(if(game_level>=20, 1, 0))/count(DISTINCT a.device_id), 3) as 通过第20关,
        round(sum(g5)/count(DISTINCT a.device_id), 3) as 第五关活动机会曝光,
        round(count(DISTINCT wx_open_id)/count(DISTINCT a.device_id), 3) as 微信登录,
        round(count(DISTINCT id_card)/count(DISTINCT a.device_id), 3) as 身份证绑定,
        round(sum(if(COALESCE(money, 0)>0, 1,0))/count(DISTINCT a.device_id), 3) as 提现成功

from (
        SELECT if(Sort_array(guide)[size(guide)-1]  >=1, 1, 0) as g1,   
        if(Sort_array(guide)[size(guide)-1]  >= 2, 1, 0) as g2,
        if(Sort_array(guide)[size(guide)-1]  >=3, 1, 0) as g3,
        if(Sort_array(guide)[size(guide)-1]  >=4, 1, 0) as g4,
        if(Sort_array(guide)[size(guide)-1] >= 5, 1, 0) as g5,
        device_id, wx_open_id, pt, id_card
        from   
    sync_mongo_box.extract_hztt_app_source__userinfo  
    WHERE   pt BETWEEN '${start}' and '${end}'
    AND SUBSTR(create_time,1,10) = pt
) as a 
LEFT JOIN 
(
    select device_id, tg_num as game_level, pt from sync_mongo_box.extract_hztt_app_source__userdaystat  
    WHERE   pt BETWEEN '${start}' and '${end}' and tg_num >= 1 
) as c 
on a.device_id = c.device_id and a.pt=c.pt
LEFT JOIN 
(
    SELECT sum(COALESCE(money, 0)) as money, device_id, pt 
        FROM sync_mongo_box.extract_hztt_app_source__extractcashrecord 
        WHERE pt BETWEEN '${start}' and '${end}'
        AND status = 3 GROUP by device_id, pt
) as b
on a.device_id = b.device_id and a.pt = b.pt
group by a.pt;

 

 

推人游戏启动漏斗

with a as(
    select device_id,pt from sync_mongo_box.extract_trmoney_app_source__userinfo
    WHERE   pt BETWEEN '${start}' and '${end}'
    AND SUBSTR(create_time,1,10) = '${end}' and create_time >="2020-02-26 19:00" GROUP by device_id,pt -- 新增表
),
b as(
    select device_id,ngx_date,act_page,event_name from liquid_log_project.log_trmoney_android 
    where ngx_date BETWEEN '${start}' and '${end}' and ngx_date_time >="2020-02-26 19:00"
)
SELECT  b.ngx_date,
        count( distinct if(event_name="b_entry_page" and act_page = "p_welcome", a.device_id, null)) 进入loading页的用户,
        count( distinct if(event_name="b_click_enter", device_id, null)) 同意隐私协议的用户,
        count( distinct if(event_name="b_game_plugin_install_already", device_id, null)) 插件安装成功的用户, 
        count( distinct if(event_name="b_entry_page" and act_page= "p_home_activity", device_id, null)) 成功进入app首页的用户,
        count( distinct if(event_name="b_click_game_start", device_id, null)) 点击开始游戏的用户,
        count( distinct if((event_name="b_entey_page" and act_page = "b_game_plugin_init") 
        or event_name = "b_game_plugin_init", device_id, null)) 开始启动插件的用户,
        count( distinct if(event_name="b_entry_page" and act_page="p_game_plugin", device_id, null)) 成功进入插件游戏的用户                         

from  a LEFT  join b  on a.device_id = b.device_id and a.pt = b.ngx_date
GROUP by b.ngx_date;

 

 

游戏加载时长是否成功进入app

--odps sql 
--********************************************************************--
--author:odps-game
--create time:2020-02-25 17:50:12
--********************************************************************--
--推人游戏加载时长&&是否成功进入app


with a as(
    select device_id,pt from sync_mongo_box.extract_trmoney_app_source__userinfo
    WHERE   pt BETWEEN '${start}' and '${end}'
    AND SUBSTR(create_time,1,10) = pt GROUP by device_id,pt -- 新增表
),
b as(
    -- p_welcome p_splash
    select * from (select duration,device_id,ngx_date 
    ,row_number()OVER(PARTITION by device_id,ngx_date order by ngx_date_time asc,duration DESC ) rn
    from liquid_log_project.log_trmoney_android 
    where ngx_date BETWEEN '${start}' and '${end}' and event_name ="b_leave_page" 
    and act_page = "p_welcome" ) a where rn=1-- 首次启动时长 duration  -- 一个人有多条只取时间最小的那一条         

), c as (
    select device_id,ngx_date from liquid_log_project.log_trmoney_android 
    where ngx_date BETWEEN '${start}' and '${end}' and event_name ="b_entry_page" 
    and act_page = "p_home_activity" GROUP by device_id,ngx_date -- 成功进入app
)
SELECT  a.pt,a.device_id,b.duration,if(c.device_id is not null ,1,0) isin  -- 日期、device_id、启动时长、是否进入app
from  a LEFT  join b  on a.device_id = b.device_id and a.pt = b.ngx_date 
        LEFT  join c  on a.device_id = c.device_id and a.pt = c.ngx_date;

 

 

LTV 模版

--odps sql 
--********************************************************************--
--author:odps-game
--create time:2019-11-21 10:42:43
--********************************************************************--
with  new as ( --新增
SELECT  user_id
        ,pt
FROM    basic_data_center.odps_base_box_user_day_stat
WHERE   pt BETWEEN '${bdatestart}'
AND     '${bdateend}'
AND     day_age = 0
and     version_name='5.4.9.0'
)
,stat as (--活跃加三天
SELECT  user_id
        ,pt
        ,duration
FROM    basic_data_center.odps_base_box_user_day_stat
WHERE   pt BETWEEN '${bdatestart}'
AND     SUBSTR(DATEADD(TO_DATE('${bdateend}','yyyy-mm-dd'),3,'dd'),1,10)

)
SELECT  new.pt AS 日期
        ,CASE    WHEN basic_data_center.getbucketid(new.user_id,18) BETWEEN 0 AND 49 THEN 'A'
                 WHEN basic_data_center.getbucketid(new.user_id,18) BETWEEN 50 AND 99 THEN 'B' 
         END AS 策略
        ,sum(if(DATEDIFF(to_date(stat.pt,'yyyy-mm-dd'),to_date(new.pt,'yyyy-mm-dd'),'dd')=0,1,0 )) as 新增人数
        ,sum(if(DATEDIFF(to_date(stat.pt,'yyyy-mm-dd'),to_date(new.pt,'yyyy-mm-dd'),'dd')=0,duration,0 )) as 当天新增人数的总时长
        ,sum(if(DATEDIFF(to_date(stat.pt,'yyyy-mm-dd'),to_date(new.pt,'yyyy-mm-dd'),'dd')BETWEEN 0 and 1,duration,0 )) as `当天新增人数2日内时长(当天和第二天)`
        ,sum(if(DATEDIFF(to_date(stat.pt,'yyyy-mm-dd'),to_date(new.pt,'yyyy-mm-dd'),'dd')=2,duration,0 )) as `当天新增人数的+2天总时长`
        ,sum(if(DATEDIFF(to_date(stat.pt,'yyyy-mm-dd'),to_date(new.pt,'yyyy-mm-dd'),'dd')=3,duration,0 )) as `当天新增人数的+3天总时长`
FROM    new
        ,stat
WHERE   new.user_id = stat.user_id
GROUP BY new.pt
         ,CASE    WHEN basic_data_center.getbucketid(new.user_id,18) BETWEEN 0 AND 49 THEN 'A'
                  WHEN basic_data_center.getbucketid(new.user_id,18) BETWEEN 50 AND 99 THEN 'B' 
          END
;

 

宝箱新增LTV

with
    data0 as(
        select pt,device_id
        FROM basic_data_center.extract_ttgame_extra_app_source__userdaystat
        WHERE pt between '${date_start}' AND '${date_end}' and day_age = 0       
        ), 
    data1 as(
        select pt,device_id , SUM(COALESCE(reward_coin, 0)) as total_reward,
         SUM(COALESCE(total_video_count, 0)) as video_count
        ,SUM(COALESCE(cg_game_time, 0)) as game_time   
        FROM basic_data_center.extract_ttgame_extra_app_source__userdaystat
        WHERE pt between '${date_start}' AND SUBSTR(DATEADD(TO_DATE('${date_end}','yyyy-mm-dd'),7,'dd'),1,10)
        GROUP BY pt,device_id
        ),
    data2 as (
        SELECT device_id,sum(COALESCE(money, 0)) as money, pt
        FROM basic_data_center.extract_ttgame_extra_app_source__extractcashrecord
        WHERE pt between '${date_start}' AND SUBSTR(DATEADD(TO_DATE('${date_end}','yyyy-mm-dd'),7,'dd'),1,10) AND status = 3 GROUP by pt,device_id
    ),all_data as (
            SELECT data1.pt, data1.device_id,total_reward,video_count
            ,game_time,money
            from data1 LEFT  join data2 on data1.device_id = data2.device_id  and data1.pt = data2.pt
               
                                                
    )
    SELECT 
    data0.pt
    ,sum(if(DATEDIFF(to_date(all_data.pt,'yyyy-mm-dd'),to_date(data0.pt,'yyyy-mm-dd'),'dd')=0,1,0 )) as 新增总人数

    ,round(
        sum(if(DATEDIFF(to_date(all_data.pt,'yyyy-mm-dd'),to_date(data0.pt,'yyyy-mm-dd'),'dd')=0,game_time,0 ))
        /
        sum(if(DATEDIFF(to_date(all_data.pt,'yyyy-mm-dd'),to_date(data0.pt,'yyyy-mm-dd'),'dd')=0,1,0 ))
        /60
        ,3
    )as 新增人均时长

    ,round(
        sum(if(DATEDIFF(to_date(all_data.pt,'yyyy-mm-dd'),to_date(data0.pt,'yyyy-mm-dd'),'dd')=0,video_count,0 ))
        /
        sum(if(DATEDIFF(to_date(all_data.pt,'yyyy-mm-dd'),to_date(data0.pt,'yyyy-mm-dd'),'dd')=0,1,0 ))
        ,3
    )as 新增人均视频数

    ,round(
        sum(if(DATEDIFF(to_date(all_data.pt,'yyyy-mm-dd'),to_date(data0.pt,'yyyy-mm-dd'),'dd')=0,total_reward,0 ))
        /
        sum(if(DATEDIFF(to_date(all_data.pt,'yyyy-mm-dd'),to_date(data0.pt,'yyyy-mm-dd'),'dd')=0,1,0 ))
        ,3
    )as 新增人均名义补贴

    ,round(
        sum(if(DATEDIFF(to_date(all_data.pt,'yyyy-mm-dd'),to_date(data0.pt,'yyyy-mm-dd'),'dd')=0,money,0 ))
        /
        sum(if(DATEDIFF(to_date(all_data.pt,'yyyy-mm-dd'),to_date(data0.pt,'yyyy-mm-dd'),'dd')=0,1,0 ))
        ,3
    )as 新增人均体现金额


    ,round(
        sum(if(DATEDIFF(to_date(all_data.pt,'yyyy-mm-dd'),to_date(data0.pt,'yyyy-mm-dd'),'dd') BETWEEN 0 and 2 ,game_time,0 ))
        /
        sum(if(DATEDIFF(to_date(all_data.pt,'yyyy-mm-dd'),to_date(data0.pt,'yyyy-mm-dd'),'dd')=0,1,0 ))
        /60
        ,3
    )as 3日人均时长

    ,round(
        sum(if(DATEDIFF(to_date(all_data.pt,'yyyy-mm-dd'),to_date(data0.pt,'yyyy-mm-dd'),'dd') BETWEEN 0 and 2 ,video_count,0 ))
        /
        sum(if(DATEDIFF(to_date(all_data.pt,'yyyy-mm-dd'),to_date(data0.pt,'yyyy-mm-dd'),'dd')=0,1,0 ))
        ,3
    )as 3日人均视频数

    ,round(
        sum(if(DATEDIFF(to_date(all_data.pt,'yyyy-mm-dd'),to_date(data0.pt,'yyyy-mm-dd'),'dd') BETWEEN 0 and 2 ,total_reward,0 ))
        /
        sum(if(DATEDIFF(to_date(all_data.pt,'yyyy-mm-dd'),to_date(data0.pt,'yyyy-mm-dd'),'dd')=0,1,0 ))
        ,3
    )as 3日人均名义补贴

    ,round(
        sum(if(DATEDIFF(to_date(all_data.pt,'yyyy-mm-dd'),to_date(data0.pt,'yyyy-mm-dd'),'dd') BETWEEN 0 and 2 ,money,0 ))
        /
        sum(if(DATEDIFF(to_date(all_data.pt,'yyyy-mm-dd'),to_date(data0.pt,'yyyy-mm-dd'),'dd')=0,1,0 ))
        ,3
    )as 3日人均提现金额


    FROM data0
    ,all_data
    WHERE data0.device_id = all_data.device_id
    GROUP BY data0.pt;


    
    

 

页面渗透统计

--odps sql 
--********************************************************************--
--author:odps-game
--create time:2019-11-14 12:08:37
--********************************************************************--
with 
    a as (
        SELECT  user_id AS day_active_person ,ngx_date AS pt
        FROM    liquid_log_project.log_box_android_master  
        WHERE   ngx_date = '${date}'
        AND     event_name = "h_enter_page" and act_page = "p_index" GROUP BY  ngx_date,user_id
),sign_num as (
        SELECT  user_id AS sign_num_user_id,ngx_date AS pt
        FROM    liquid_log_project.log_box_android_master  
        WHERE   ngx_date = '${date}'
        AND     event_name = "h_enter_page" and act_page = "p_signIn" GROUP BY  ngx_date,user_id
)
,task_list as (
        SELECT  user_id AS task_user_id,ngx_date AS pt
        FROM    liquid_log_project.log_box_android_master  
        WHERE   ngx_date = '${date}'
        AND     event_name = "h_enter_page" and act_page = "p_taskList" GROUP BY  ngx_date,user_id
)
,activity_list as (
        SELECT  user_id AS activity_user_id,ngx_date AS pt
        FROM    liquid_log_project.log_box_android_master  
        WHERE   ngx_date = '${date}'
        AND     event_name = "h_enter_page" and act_page = "p_activityList" GROUP BY  ngx_date,user_id
)
,p_lottery as (
        SELECT  user_id AS p_lottery_user_id,ngx_date AS pt
        FROM    liquid_log_project.log_box_android_master  
        WHERE   ngx_date = '${date}'
        AND     event_name = "h_enter_page" and act_page = "p_lottery" GROUP BY  ngx_date,user_id
),p_carveup as (
        SELECT  user_id AS p_carveup_user_id,ngx_date AS pt
        FROM    liquid_log_project.log_box_android_master  
        WHERE   ngx_date = '${date}'
        AND     event_name = "h_enter_page" and act_page = "p_carveUp" GROUP BY  ngx_date,user_id
),mingame_chooseroom as (
        SELECT  user_id AS chooseroom_user_id,ngx_date AS pt
        FROM    liquid_log_project.log_box_android_master  
        WHERE   ngx_date = '${date}'
        AND     event_name = "h_enter_page" and act_page = "p_chooseRoom" GROUP BY  ngx_date,user_id
),mingame_matchgame as (
        SELECT  user_id AS p_matchgame_user_id,ngx_date AS pt
        FROM    liquid_log_project.log_box_android_master  
        WHERE   ngx_date = '${date}'
        AND     event_name = "h_enter_page" and act_page = "p_matchGame" GROUP BY  ngx_date,user_id
),mingame_p_game_list as (
        SELECT  user_id AS p_game_list_user_id,ngx_date AS pt
        FROM    liquid_log_project.log_box_android_master  
        WHERE   ngx_date = '${date}'
        AND     event_name = "h_enter_page" and act_page = "p_gameList" GROUP BY  ngx_date,user_id
)

select  a.pt as date,count(DISTINCT day_active_person) as all_person, --日活
count(DISTINCT sign_num_user_id) as sign_person_num, --打卡人数
count(DISTINCT task_user_id) as task_person_num,--任务赚币人数 
count(DISTINCT activity_user_id) as activity_person_num, --活动送币人数
count(DISTINCT p_lottery_user_id) as activity_person_jq_num, --活动送币奖券人数
count(DISTINCT p_carveup_user_id) as carveup_person_num, --瓜分百万人数
count(DISTINCT chooseroom_user_id) as mingame_chooseroom, --小游戏选择房间人数
count(DISTINCT p_matchgame_user_id) as mingame_matchgame, --小游戏匹配人数
count(DISTINCT p_game_list_user_id) as mingame__game_list --小游戏游戏列表人数
from  a LEFT  join sign_num  on a.day_active_person = sign_num.sign_num_user_id and a.pt= sign_num.pt
        LEFT  join task_list on a.day_active_person = task_list.task_user_id  and a.pt= task_list.pt 
        LEFT  join activity_list on a.day_active_person = activity_list.activity_user_id  and a.pt= activity_list.pt 
        LEFT  join p_lottery on a.day_active_person = p_lottery.p_lottery_user_id  and a.pt= p_lottery.pt 
        LEFT  join p_carveup on a.day_active_person = p_carveup.p_carveup_user_id  and a.pt= p_carveup.pt
        LEFT  join mingame_chooseroom on a.day_active_person = mingame_chooseroom.chooseroom_user_id  and a.pt= mingame_chooseroom.pt
        LEFT  join mingame_matchgame on a.day_active_person = mingame_matchgame.p_matchgame_user_id  and a.pt= mingame_matchgame.pt
        LEFT  join mingame_p_game_list on a.day_active_person = mingame_p_game_list.p_game_list_user_id  and a.pt= mingame_p_game_list.pt        
GROUP  by a.pt;

 

各项时长统计

--odps sql 
--********************************************************************--
--author:zhaoyingjie
--create time:2019-11-01 10:39:36
--********************************************************************--
with games as (  --人均时长
        select COUNT(a.user_id) AS new_add_nums,sum(game_times) as all_game_times, a.pt
        FROM (
                SELECT  pt,_id as user_id         
                from sync_mongo_box.extract_box_gamezone_source__userdetails 
                WHERE pt='${date}' and substr(create_time, 1, 10)='${date}'
                GROUP BY _id,pt
        ) as a 
        LEFT JOIN 
        (
            SELECT pt,user_id
                    ,SUM(
                        COALESCE(game_duration,0) --小游戏时长 
                        + COALESCE(huangdi_time,0) --皇帝时长  
                        + COALESCE(fishing_time,0) -- 捕鱼时长
                        + COALESCE(ddz_times,0)  --斗地主时长
                        + COALESCE(game_activity_cg_time,0) -- 闯关时长 
                        + COALESCE(majiang_times,0)  -- 麻将时长         
                    ) AS game_times
        
            FROM  sync_mongo_box.extract_box_gamezone_game_source__gamedaystat
            WHERE  pt = '${date}' GROUP BY pt,user_id
        )as b
        on a.user_id = b.user_id and a.pt = b.pt
        GROUP BY a.pt
)
,pingtai as (  --人均平台时长
        select COUNT(a.user_id) AS new_add_nums,sum(total_playtime) as all_pingtai_times, a.pt
        FROM (
                SELECT  pt,_id as user_id         
                from sync_mongo_box.extract_box_gamezone_source__userdetails 
                WHERE pt='${date}' and substr(create_time, 1, 10)='${date}'
                GROUP BY _id,pt
        ) as a 
        LEFT JOIN 
        (
            SELECT user_id,ngx_date,round((SUM(duration)/1000), 2) as total_playtime
            FROM liquid_log_project.log_box_android_master
            WHERE ngx_date = '${date}'
            AND event_name='b_leave_page'
            AND SUBSTR(target_url,1,56)  = 'https://saiyan_game_center.liquidnetwork.com/game_center' 
            AND duration < 3600000 and duration > 0
            and user_id in (
                SELECT user_id FROM sync_mongo_box.extract_box_gamezone_source__userdaystat 
                WHERE pt= '${date}'
            )GROUP by user_id,ngx_date
        )as b
        on a.user_id = b.user_id and a.pt = b.ngx_date
        GROUP BY a.pt
),
game_times as (
    select a.pt, 
        COUNT(if(ddz_times is null,null,a.user_id)) AS ddz_nums,--斗地主人数
        COUNT(if(majiang_times is null,null,a.user_id)) AS mj_nums, --麻将人数
        COUNT(if(game_duration ==0,null,a.user_id)) AS mingame_nums, --小游戏人数
        COUNT(if(huangdi_time is null,null,a.user_id)) AS huangdi_time_nums, --皇帝人数
        COUNT(if(fishing_time is null,null,a.user_id)) AS fishing_time_nums, --捕鱼人数
        COUNT(if(game_activity_cg_time is null,null,a.user_id)) AS game_activity_cg_nums, --闯关人数
        round(sum(if(ddz_times is null,0,ddz_times)) /COUNT(if(ddz_times is null,null,a.user_id))/ 60,2)  as avge_ddz_times, --斗地主人均时长  
        round(sum(if(majiang_times is null,0,majiang_times)) /COUNT(if(majiang_times is null,null,a.user_id))/ 60,2)  as avge_majiang_times, --麻将人均时长 
        round(sum(if(game_duration==0,0,game_duration)) /COUNT(if(game_duration==0,null,a.user_id))/ 60,2)  as avge_mingame_times, --小游戏人均时长 
        round(sum(if(huangdi_time is null,0,huangdi_time)) /COUNT(if(huangdi_time is null,null,a.user_id))/ 60,2)  as avge_huangdi_times, --皇帝人均时长
        round(sum(if(fishing_time is null,0,fishing_time)) /COUNT(if(fishing_time is null,null,a.user_id))/ 60,2)  as avge_fishing_times, --捕鱼人均时长 
        round(sum(if(game_activity_cg_time is null,0,game_activity_cg_time)) /COUNT(if(game_activity_cg_time is null,null,a.user_id))/ 60,2)  as avge_game_activity_times --捕鱼人均时长          
        FROM (
                SELECT  pt,_id as user_id         
                from sync_mongo_box.extract_box_gamezone_source__userdetails 
                WHERE pt='${date}' and substr(create_time, 1, 10)='${date}'
        ) as a 
        LEFT JOIN 
        (
            SELECT pt,user_id, ddz_times ,fishing_time, majiang_times, game_duration, huangdi_time,game_activity_cg_time
            FROM  sync_mongo_box.extract_box_gamezone_game_source__gamedaystat
            WHERE  pt = '${date}' 
        )as b
        on a.user_id = b.user_id 
        GROUP BY a.pt

)

SELECT games.pt,games.new_add_nums,ROUND((all_game_times+all_pingtai_times) / games.new_add_nums,2) as avge_times,ROUND(all_game_times/games.new_add_nums,2) as avge_game_times,  
       avge_ddz_times,avge_majiang_times,avge_fishing_times,avge_huangdi_times
       ,ROUND(mingame_nums/games.new_add_nums,3) as shentou_mingame ,ROUND(ddz_nums/games.new_add_nums,3) as shentou_ddz
       ,ROUND(mj_nums/games.new_add_nums,3) as shentou_mj,ROUND(fishing_time_nums/games.new_add_nums,3) as shentou_fishing
       ,ROUND(huangdi_time_nums/games.new_add_nums,3) as shentou_huangdi
       ,ROUND(game_activity_cg_nums/games.new_add_nums,3) as shentou_game_activity  
 from games,pingtai,game_times where games.pt = pingtai.pt and games.pt = game_times.pt

 

专区ROI

--odps sql 
--********************************************************************--
--author:odps-game
--create time:2019-11-18 20:04:53
--********************************************************************--
with 
a as ( -- 平台奖励
        SELECT  pt,user_id
            ,SUM(
                COALESCE(punchcard_a_flash_coin,0) 
                + COALESCE(patchcard_a_flash_coin,0)  
                + COALESCE(dayactivity_a_flash_coin,0) 
                + COALESCE(newperson_a_flash_coin,0) 
                + COALESCE(dailytask_a_flash_coin,0)
                + COALESCE(gu_a_flash_coin,0) -- 瓜分奖励
                + COALESCE(guc_a_flash_coin,0) -- 瓜分安慰奖励
                + COALESCE(p_a_flash_coin,0)  -- 进步奖励
                + COALESCE(pc_a_flash_coin,0) -- 进步安慰奖励 
                + COALESCE(agc_a_flash_coin,0) -- 奖券奖励
                + COALESCE(88173991_agc_a_flash_coin,0) --消消乐
                + COALESCE(88107542_agc_a_flash_coin,0) --摩登大楼
                + COALESCE(88190422_agc_a_flash_coin,0) --爆爆球球
                + COALESCE(88111422_agc_a_flash_coin,0) --球别掉
                + COALESCE(88165662_agc_a_flash_coin,0) --守护飞飞
                + COALESCE(cg_a_flash_coin,0) -- 闯关奖励奖励
                +  COALESCE(giftpackage_0_a_flash_coin,  0)
                +  COALESCE(giftpackage_1_a_flash_coin,  0)
                +  COALESCE(giftpackage_2_a_flash_coin,  0)
                +  COALESCE(giftpackage_3_a_flash_coin,  0)
                +  COALESCE(giftpackage_4_a_flash_coin,  0)
                +  COALESCE(reward_coin,0)
                +  COALESCE(finish_box,  0)*2500
                                                                
            ) AS userdaystat_fishing, 
            SUM(COALESCE(video_count,0)) as patch_card_video
         
        FROM  sync_mongo_box.extract_box_gamezone_source__userdaystat
        WHERE pt BETWEEN '${t_start}' and '${t_end}' 
        and client_version >= '5.4.7.0' and basic_data_center.getbucketid(user_id, 5) BETWEEN '${t_num}' and '${e_num}'  GROUP BY user_id,pt

)
,b as (  --游戏内奖励、回收、视频数、时长
        SELECT pt,user_id,SUM(
                    COALESCE(huangdi_task,0) 
                    + COALESCE(fishing_game_obtain_flash,0)
                    + COALESCE(reward_coin,0)
                    + COALESCE(a_ddz,0)  -- 斗地主奖励
                    + COALESCE(mj_task_flash_coin, 0) -- 麻将游戏内奖励
                ) AS gameuserdaystat_fishing
                ,SUM( 
                    + ABS(COALESCE(fishing_game_consum_flash,0)) --- 捕鱼回收门票
                    + ABS(COALESCE(minigame_need_coin,0)) --- 小游戏门票
                    + ABS(COALESCE(r_ddz,0)) -- 斗地主回收
                ) AS consum_flash
                ,SUM(
                    COALESCE(if (game_duration < 0,0,game_duration),0) 
                    + COALESCE(if (huangdi_time < 0,0,huangdi_time),0) 
                    + COALESCE(if (fishing_time < 0,0,fishing_time),0) 
                    
                    + COALESCE(if (ddz_times < 0,0,ddz_times),0)  --斗地主时长
                    + COALESCE(if (game_activity_cg_time < 0,0,game_activity_cg_time),0) -- 闯关时长 
                    + COALESCE(if (majiang_times < 0,0,majiang_times),0)  -- 麻将时长         
                ) AS game_times
                ,SUM(
                    COALESCE(total_video_count,0) 
                    + COALESCE(fishing_watch_video_times,0) -- 捕鱼看视频数
                    + COALESCE(ddz_watch_video,0) -- 斗地主看视频数
                    + COALESCE(majiang_watch_video, 0) -- 麻将游戏内视频数 
                    + COALESCE(mj_task_video, 0) -- 麻将任务视频
                ) AS game_video
        FROM  sync_mongo_box.extract_box_gamezone_game_source__gamedaystat
        WHERE  pt BETWEEN '${t_start}' and '${t_end}' GROUP BY pt,user_id

)
,c as(  --皇帝视频数
    SELECT  user_id,ngx_date,COUNT(1) AS huangdi_video_count    
    FROM  liquid_log_project.log_box_android_master
    WHERE  ngx_date BETWEEN '${t_start}' and '${t_end}'
    AND  event_name IN ('task_video_success','speed_video_success','box_video_success','offline_video_success')
    AND  GET_JSON_OBJECT(other_column,'$.game_id') = "42104418"    
    GROUP by user_id,ngx_date


),d as(  -- 游戏平台所占时长

    SELECT a.user_id,a.ngx_date,round((SUM(duration)/1000), 2) as total_playtime from 
        (
        SELECT  user_id
                ,duration
                ,ngx_date
        FROM    liquid_log_project.log_box_android_master
        WHERE   ngx_date BETWEEN '${t_start}' and '${t_end}'
        AND     event_name = 'b_leave_page'
        AND     SUBSTR(target_url,1,56) = 'https://saiyan_game_center.liquidnetwork.com/game_center'
        AND     duration < 3600000
        AND     duration > 0

        )a LEFT SEMI JOIN  (
        SELECT  user_id
                ,pt
        FROM    sync_mongo_box.extract_box_gamezone_source__userdaystat
        WHERE   pt BETWEEN '${t_start}' and '${t_end}'
        GROUP BY pt,user_id
        ) b 
        on a.user_id=b.user_id
        and a.ngx_date=b.pt
        GROUP BY a.ngx_date,a.user_id



),f as ( --转圈奖励
    SELECT user_id,pt,SUM(COALESCE(red_envelopes_game_module_flash,0)) as red_game_flash, --游戏专区转圈
                      SUM(COALESCE(red_envelopes_doudizhu_flash,0)) as doudizhu_flash, -- 首页斗地主转圈
                      SUM(COALESCE(third_order_flash_saiyisai,0)) red_zq,  --红包新游戏中心 转圈
                      SUM(COALESCE(games_reward_flash,0)) games_reward_flash,  --游戏激励视频奖励
                      SUM(COALESCE(games_read_count_reward,0)) games_read_count_reward,  --游戏转圈阶梯奖励
                      SUM(COALESCE(size(games_red_reward_y_list),0)) games_red_video_count  --游戏转圈视频数
                      
    from sync_mongo_box.extract_source__userdaystat 
    where pt BETWEEN '${t_start}' and '${t_end}'
    GROUP BY user_id,pt  
)
select  '${t_num}' "--" '${e_num}' as 桶号,
        a.pt,COUNT(a.user_id) AS 日活, --日活

        SUM(red_game_flash) as 转圈奖励, --转圈奖励
        ROUND((SUM(red_game_flash + doudizhu_flash + games_reward_flash + games_read_count_reward)) / COUNT(a.user_id)) as 人均转圈补贴,  --人均转圈补贴

        SUM(userdaystat_fishing+gameuserdaystat_fishing + red_game_flash + doudizhu_flash +red_zq + games_reward_flash + games_read_count_reward) AS 补贴闪电币, --补贴闪电币

        ROUND(
            SUM(userdaystat_fishing+gameuserdaystat_fishing + red_game_flash + doudizhu_flash +red_zq + games_reward_flash +games_read_count_reward - consum_flash) / COUNT(a.user_id)
        ) as 人均补贴,  --人均补贴

        SUM(consum_flash) AS 回收闪电币, --回收闪电币
        SUM(game_times) +SUM(total_playtime) AS 总时长,  --总时长

        ROUND(
            (
                (SUM(game_times) + SUM(total_playtime)) / COUNT(a.user_id) / 60
            )
        ,2) as 人均总时长,  --人均总时长

        SUM(game_times) AS 游戏时长, --游戏时长

        ROUND(
            (SUM(game_times) / COUNT(a.user_id)) / 60
        ,2) as 人均游戏时长,  --人均游戏时长

        SUM(game_video) + sum(huangdi_video_count) +sum(patch_card_video) + sum(games_red_video_count) AS 视频数, --视频数
        
        ROUND(
            (SUM(game_video) + sum(huangdi_video_count) +sum(patch_card_video) + sum(games_red_video_count)) / COUNT(a.user_id)
        ,2) as 人均视频数  --人均视频数



from  a LEFT  join b  on a.user_id = b.user_id and a.pt = b.pt 
        LEFT  join c  on a.user_id = c.user_id and a.pt = c.ngx_date
        LEFT  JOIN d  on a.user_id = d.user_id and a.pt = d.ngx_date
        LEFT  join f  on a.user_id = f.user_id and a.pt = f.pt


GROUP  by a.pt

 

专区新手任务提现

with  user as (
    SELECT user_id,pt FROM (
        SELECT * from basic_data_center.odps_base_box_user_day_stat
        WHERE pt BETWEEN '${bdatestart}' and '${bdateend}'
        and version_name >='5.4.4.5'
        -- and day_age = 0 --判断新老用户
        and day_age != 0   --老用户
    )a join (
        SELECT pt,user_id FROM  sync_mongo_box.extract_box_gamezone_source__userdaystat where day_age=0
        and pt BETWEEN '${bdatestart}' and '${bdateend}'
    )b on a.user_id = b.user_id and a.pt=b.pt  GROUP by user_id,pt
)
,duration as (  --full join  保证全部用户
SELECT  COALESCE(a.user_id,b.user_id) AS user_id
        ,COALESCE(duration,0)+COALESCE(total_playtime,0) AS allduration  --两部分的时长
        ,COALESCE(a.pt,b.pt) AS pt
FROM    (    --第一部分的时长
            SELECT  user_id
                    ,COALESCE(game_duration,0)+COALESCE(huangdi_time,0)+COALESCE(fishing_time,0)+COALESCE(ddz_times,0)+COALESCE(game_activity_cg_time,0) AS duration
                    ,pt
            FROM    sync_mongo_box.extract_box_gamezone_game_source__gamedaystat
            WHERE   pt BETWEEN '${bdatestart}'
            AND     split_part(DATEADD(CONCAT('${bdateend}',' 00:00:00'), 6,'dd'),' ',1)
        ) a FULL
JOIN    (    --游戏中心的时长
            SELECT  user_id
                    ,ngx_date AS pt
                    ,SUM(duration)/1000 AS total_playtime
            FROM    liquid_log_project.log_box_android_master
            WHERE   ngx_date BETWEEN '${bdatestart}' and split_part(DATEADD(CONCAT('${bdateend}',' 00:00:00'), 6,'dd'),' ',1)
            AND     event_name = 'b_leave_page'
            AND     SUBSTR(target_url,1,56) = 'https://saiyan_game_center.liquidnetwork.com/game_center'
            AND     duration < 3600000
            AND     duration > 0
            AND     user_id IN ( SELECT user_id FROM sync_mongo_box.extract_box_gamezone_source__userdaystat WHERE pt BETWEEN '${bdatestart}' AND '${bdateend}' )
            GROUP BY user_id
                     ,ngx_date
        ) b
ON      a.user_id = b.user_id
AND     a.pt = b.pt
)
,videos as ( --full join  保证全部用户
SELECT  COALESCE(d.user_id,c.user_id) AS user_id
        ,COALESCE(videos,0) +COALESCE(games_red_video_count,0) AS allvideos  --三者相加的视频数
        ,COALESCE(d.pt,c.pt) AS pt
FROM    (
            SELECT  COALESCE(a.user_id,b.user_id) AS user_id
                    ,COALESCE(videos,0) +COALESCE(huangdi_video_count,0) AS videos   --皇帝+其他的视频数
                    ,COALESCE(a.pt,b.pt) AS pt
            FROM    (  --皇帝视频数
                        SELECT  user_id
                                ,ngx_date AS pt
                                ,COUNT(1) AS huangdi_video_count
                        FROM    liquid_log_project.log_box_android_master
                        WHERE   ngx_date BETWEEN '${bdatestart}'
                        AND     split_part(DATEADD(CONCAT('${bdateend}',' 00:00:00'), 1,'dd'),' ',1)
                        AND     event_name IN ('task_video_success','speed_video_success','box_video_success','offline_video_success')
                        AND     GET_JSON_OBJECT(other_column,'$.game_id') = "42104418"
                        GROUP BY user_id
                                 ,ngx_date
                    ) a FULL
            JOIN    ( --其他的视频数
                        SELECT  user_id
                                ,COALESCE(total_video_count,0) +COALESCE(fishing_watch_video_times,0) +COALESCE(ddz_watch_video,0) +COALESCE(majiang_watch_video,0 ) +COALESCE(mj_task_video,0) AS videos
                                ,pt
                        FROM   sync_mongo_box.extract_box_gamezone_game_source__gamedaystat
                        WHERE   pt BETWEEN '${bdatestart}'
                        AND     split_part(DATEADD(CONCAT('${bdateend}',' 00:00:00'), 6,'dd'),' ',1)
                    ) b
            ON      a.user_id = b.user_id
            AND     a.pt = b.pt
        ) d FULL
JOIN    (  --补贴表里的视频数
            SELECT  user_id
                    ,SUM(COALESCE(SIZE(games_red_reward_y_list),0)) games_red_video_count    --游戏转圈视频数
                    ,pt
            FROM    sync_mongo_box.extract_source__userdaystat
            WHERE   pt BETWEEN '${bdatestart}'
            AND     split_part(DATEADD(CONCAT('${bdateend}',' 00:00:00'), 6,'dd'),' ',1)
            group by user_id,pt
        ) c
ON      d.user_id = c.user_id
AND     d.pt = c.pt
)
,subsidy as (
SELECT  COALESCE(a.user_id,b.user_id) AS user_id
        ,COALESCE(a.subsidy,0)+COALESCE(b.subsidy,0) AS allsubsidy --两者的补贴
        ,COALESCE(a.pt,b.pt) AS pt
FROM    (    --第一部分的补贴
            SELECT  user_id
                    ,SUM(red_envelopes_game_module_flash)+SUM(red_envelopes_doudizhu_flash)+SUM(third_order_flash_saiyisai)+SUM(games_reward_flash)+SUM(games_read_count_reward)
                     as subsidy    
                    ,pt
            FROM    sync_mongo_box.extract_source__userdaystat
            WHERE   pt BETWEEN '${bdatestart}'
            AND     split_part(DATEADD(CONCAT('${bdateend}',' 00:00:00'), 6,'dd'),' ',1)
            group by user_id,pt
        ) a FULL
JOIN    (    --第二部分的补贴
            SELECT  COALESCE(a.user_id,b.user_id) AS user_id
                    ,COALESCE(a.subsidy,0) +COALESCE(b.subsidy,0) AS subsidy   --皇帝+其他的视频数
                    ,COALESCE(a.pt,b.pt) AS pt
            FROM    (  
                        SELECT  user_id
                                ,pt
                                ,COALESCE(huangdi_task,0)+COALESCE(fishing_rank_flash,0)+COALESCE(fishing_game_obtain_flash,0)+COALESCE(reward_coin,0)
                                +COALESCE(a_ddz,0) +COALESCE(mj_task_flash_coin,0)-COALESCE(fishing_game_consum_flash,0)-COALESCE(minigame_need_coin,0) 
                                -COALESCE(r_ddz,0) as subsidy
                        FROM    sync_mongo_box.extract_box_gamezone_game_source__gamedaystat
                        WHERE   pt BETWEEN '${bdatestart}'
                        AND     SPLIT_PART(DATEADD(CONCAT('${bdateend}',' 00:00:00'), 6,'dd'),' ',1)
                    ) a FULL
            JOIN    ( --其他的补贴
                        SELECT  user_id
                                ,pt
                                ,COALESCE(punchcard_a_flash_coin,0)+COALESCE(patchcard_a_flash_coin,0)+COALESCE(dayactivity_a_flash_coin,0)+COALESCE(newperson_a_flash_coin,0)
                                +COALESCE(dailytask_a_flash_coin,0) +COALESCE(gu_a_flash_coin,0)+COALESCE(guc_a_flash_coin,0)+COALESCE(p_a_flash_coin,0) 
                                +COALESCE(pc_a_flash_coin,0) +COALESCE(agc_a_flash_coin,0) +COALESCE(88173991_agc_a_flash_coin,0)+COALESCE(88107542_agc_a_flash_coin,0)+COALESCE(88190422_agc_a_flash_coin,0) 
                                +COALESCE(88111422_agc_a_flash_coin,0) +COALESCE(88165662_agc_a_flash_coin,0) +COALESCE(cg_a_flash_coin,0)+COALESCE(giftpackage_0_a_flash_coin,0)+COALESCE(giftpackage_1_a_flash_coin,0) 
                                +COALESCE(giftpackage_2_a_flash_coin,0) +COALESCE(giftpackage_3_a_flash_coin,0) +COALESCE(giftpackage_4_a_flash_coin,0)+COALESCE(reward_coin,0)+COALESCE(finish_box,0)*2500 
                                as subsidy
                        FROM    sync_mongo_box.extract_box_gamezone_source__userdaystat
                        WHERE   pt BETWEEN '${bdatestart}'
                        AND     SPLIT_PART(DATEADD(CONCAT('${bdateend}',' 00:00:00'), 6,'dd'),' ',1)                      
                    ) b
            ON      a.user_id = b.user_id
            AND     a.pt = b.pt
        ) b
ON      a.user_id = b.user_id
AND     a.pt = b.pt
)
,last_duration as (
SELECT  user.pt
        ,CASE    WHEN basic_data_center.getbucketid(user.user_id, 11) BETWEEN 0 AND 84 THEN 'A'
                 WHEN basic_data_center.getbucketid(user.user_id, 11) BETWEEN 85 AND 89 THEN 'B'
                 WHEN basic_data_center.getbucketid(user.user_id, 11) BETWEEN 90 AND 94 THEN 'C'
                 WHEN basic_data_center.getbucketid(user.user_id, 11) BETWEEN 95 AND 99 THEN 'D' 
                 ELSE 'E' 
         END AS bucket_type
          , sum(if(DATEDIFF(to_date(duration.pt,'yyyy-mm-dd'),TO_DATE(user.pt,'yyyy-mm-dd'),'dd' )=0,1,0)) as newuser --新增人数
          , sum(if(DATEDIFF(to_date(duration.pt,'yyyy-mm-dd'),TO_DATE(user.pt,'yyyy-mm-dd'),'dd' )=0,allduration,0)) as duration0 --当日总时长
          , sum(if(DATEDIFF(to_date(duration.pt,'yyyy-mm-dd'),TO_DATE(user.pt,'yyyy-mm-dd'),'dd' )BETWEEN 0 and 2,allduration,0)) as duration3 --三日内时长
          , sum(if(DATEDIFF(to_date(duration.pt,'yyyy-mm-dd'),TO_DATE(user.pt,'yyyy-mm-dd'),'dd' )BETWEEN 0 and 6,allduration,0)) as duration7 --七日内时长
FROM    user LEFT
JOIN    duration
ON      user.user_id = duration.user_id
GROUP BY user.pt
         ,CASE    WHEN basic_data_center.getbucketid(user.user_id, 11) BETWEEN 0 AND 84 THEN 'A'
                  WHEN basic_data_center.getbucketid(user.user_id, 11) BETWEEN 85 AND 89 THEN 'B'
                  WHEN basic_data_center.getbucketid(user.user_id, 11) BETWEEN 90 AND 94 THEN 'C'
                  WHEN basic_data_center.getbucketid(user.user_id, 11) BETWEEN 95 AND 99 THEN 'D' 
                  ELSE 'E' 
          END
)
,last_videos as (
SELECT  user.pt
        ,CASE    WHEN basic_data_center.getbucketid(user.user_id, 11) BETWEEN 0 AND 84 THEN 'A'
                 WHEN basic_data_center.getbucketid(user.user_id, 11) BETWEEN 85 AND 89 THEN 'B'
                 WHEN basic_data_center.getbucketid(user.user_id, 11) BETWEEN 90 AND 94 THEN 'C'
                 WHEN basic_data_center.getbucketid(user.user_id, 11) BETWEEN 95 AND 99 THEN 'D' 
                 ELSE 'E' 
         END AS bucket_type
          , sum(if(DATEDIFF(to_date(videos.pt,'yyyy-mm-dd'),TO_DATE(user.pt,'yyyy-mm-dd'),'dd' )=0,allvideos,0)) as videos0 --当日总时长
          , sum(if(DATEDIFF(to_date(videos.pt,'yyyy-mm-dd'),TO_DATE(user.pt,'yyyy-mm-dd'),'dd' )BETWEEN 0 and 2,allvideos,0)) as videos3 --三日内时长
          , sum(if(DATEDIFF(to_date(videos.pt,'yyyy-mm-dd'),TO_DATE(user.pt,'yyyy-mm-dd'),'dd' )BETWEEN 0 and 6,allvideos,0)) as videos7 --七日内时长
FROM    user LEFT
JOIN    videos
ON      user.user_id = videos.user_id
GROUP BY user.pt
         ,CASE    WHEN basic_data_center.getbucketid(user.user_id, 11) BETWEEN 0 AND 84 THEN 'A'
                  WHEN basic_data_center.getbucketid(user.user_id, 11) BETWEEN 85 AND 89 THEN 'B'
                  WHEN basic_data_center.getbucketid(user.user_id, 11) BETWEEN 90 AND 94 THEN 'C'
                  WHEN basic_data_center.getbucketid(user.user_id, 11) BETWEEN 95 AND 99 THEN 'D' 
                  ELSE 'E' 
          END
)
,last_subsidy as (
SELECT  user.pt
        ,CASE    WHEN basic_data_center.getbucketid(user.user_id, 11) BETWEEN 0 AND 84 THEN 'A'
                 WHEN basic_data_center.getbucketid(user.user_id, 11) BETWEEN 85 AND 89 THEN 'B'
                 WHEN basic_data_center.getbucketid(user.user_id, 11) BETWEEN 90 AND 94 THEN 'C'
                 WHEN basic_data_center.getbucketid(user.user_id, 11) BETWEEN 95 AND 99 THEN 'D' 
                 ELSE 'E' 
         END AS bucket_type
          , sum(if(DATEDIFF(to_date(subsidy.pt,'yyyy-mm-dd'),TO_DATE(user.pt,'yyyy-mm-dd'),'dd' )=0,allsubsidy,0)) as subsidy0 --当日总时长
          , sum(if(DATEDIFF(to_date(subsidy.pt,'yyyy-mm-dd'),TO_DATE(user.pt,'yyyy-mm-dd'),'dd' )BETWEEN 0 and 2,allsubsidy,0)) as subsidy3 --三日内时长
          , sum(if(DATEDIFF(to_date(subsidy.pt,'yyyy-mm-dd'),TO_DATE(user.pt,'yyyy-mm-dd'),'dd' )BETWEEN 0 and 6,allsubsidy,0)) as subsidy7 --七日内时长
FROM    user LEFT
JOIN    subsidy
ON      user.user_id = subsidy.user_id
GROUP BY user.pt
         ,CASE    WHEN basic_data_center.getbucketid(user.user_id, 11) BETWEEN 0 AND 84 THEN 'A'
                  WHEN basic_data_center.getbucketid(user.user_id, 11) BETWEEN 85 AND 89 THEN 'B'
                  WHEN basic_data_center.getbucketid(user.user_id, 11) BETWEEN 90 AND 94 THEN 'C'
                  WHEN basic_data_center.getbucketid(user.user_id, 11) BETWEEN 95 AND 99 THEN 'D' 
                  ELSE 'E' 
          END
)
SELECT 
last_duration.pt as 日期
,last_duration.bucket_type as 分桶情况
,newuser as 新增人数
,ROUND(duration0/newuser,2)/60  as 当日人均时长分
, ROUND(subsidy0/newuser,2)/100000  as 当日人均补贴元
, ROUND(videos0/newuser,2)  as 当日人均视频数
, ROUND(duration3/newuser,2)/60   as 三日人均时长分
, ROUND(subsidy3/newuser,2)/100000  as 三日人均补贴元
, ROUND(videos3/newuser,2)  as 三日人均视频数
, ROUND(duration7/newuser,2)/60   as 七日人均时长分
, ROUND(subsidy7/newuser,2)/100000   as 七日人均补贴元
, ROUND(videos7 /newuser,2)  as 七日人均视频数
from last_duration,last_videos,last_subsidy
WHERE  last_duration.pt =last_videos.pt and last_duration.bucket_type =last_videos.bucket_type
and last_duration.pt =last_subsidy.pt and last_duration.bucket_type =last_subsidy.bucket_type;

 

活跃留存模版

--odps sql 
--********************************************************************--
--author:odps-game
--create time:2019-11-21 16:22:46
--********************************************************************--

with user_t as(
        select pt,user_id from workspace_saiyisai.odps_calc_user_roi 
        where pt between '${date_start}' AND '${date_end}'
    ),
    userdaystat as(
        select pt,user_id from workspace_saiyisai.odps_calc_user_roi 
        where pt between '${date_start}' AND  SUBSTR(DATEADD(TO_DATE('${date_end}','yyyy-mm-dd'),14,'dd'),1,10)
    )
select 
    CASE  
    -- 实验分桶修改 start
    WHEN basic_data_center.getbucketid(user_t.user_id,'8') BETWEEN 0 AND 5 THEN "a"
    WHEN basic_data_center.getbucketid(user_t.user_id,'8') BETWEEN 6 AND 10 THEN "b"
    WHEN basic_data_center.getbucketid(user_t.user_id,'8') BETWEEN 6 AND 10 THEN "b"
    WHEN basic_data_center.getbucketid(user_t.user_id,'8') BETWEEN 6 AND 10 THEN "b"
    -- 实验分桶修改 end
    END AS 策略,
    user_t.pt as 日期,
    count(distinct user_t.user_id) 活跃,
    sum(
        if (DATEDIFF(CONCAT(userdaystat.pt, ' 00:00:00'), CONCAT(user_t.pt, ' 00:00:00'), 'dd') = 1, 1, 0)
    ) 留存1,
    sum(
        if (DATEDIFF(CONCAT(userdaystat.pt, ' 00:00:00'), CONCAT(user_t.pt, ' 00:00:00'), 'dd') = 3, 1, 0)
    ) 留存3,
    sum(
        if (DATEDIFF(CONCAT(userdaystat.pt, ' 00:00:00'), CONCAT(user_t.pt, ' 00:00:00'), 'dd') = 7, 1, 0)
    ) 留存7,
    sum(
        if (DATEDIFF(CONCAT(userdaystat.pt, ' 00:00:00'), CONCAT(user_t.pt, ' 00:00:00'), 'dd') = 10, 1, 0)
    ) 留存10,
    sum(
        if (DATEDIFF(CONCAT(userdaystat.pt, ' 00:00:00'), CONCAT(user_t.pt, ' 00:00:00'), 'dd') = 14, 1, 0)
    ) 留存14,

    ROUND(
        sum(
            if (DATEDIFF(CONCAT(userdaystat.pt, ' 00:00:00'), CONCAT(user_t.pt, ' 00:00:00'), 'dd') = 1, 1, 0)
        ) /
        count(distinct user_t.user_id)
       ,3
    ) as 留存1率,

    ROUND(
        sum(
            if (DATEDIFF(CONCAT(userdaystat.pt, ' 00:00:00'), CONCAT(user_t.pt, ' 00:00:00'), 'dd') = 3, 1, 0)
        ) /
        count(distinct user_t.user_id)
        ,3
    ) as 留存3率,

    ROUND(
        sum(
            if (DATEDIFF(CONCAT(userdaystat.pt, ' 00:00:00'), CONCAT(user_t.pt, ' 00:00:00'), 'dd') = 7, 1, 0)
        ) /
        count(distinct user_t.user_id)
        ,3
    ) as 留存7率,
    ROUND(
        sum(
            if (DATEDIFF(CONCAT(userdaystat.pt, ' 00:00:00'), CONCAT(user_t.pt, ' 00:00:00'), 'dd') = 10, 1, 0)
        ) /
        count(distinct user_t.user_id)
        ,3
    ) as 留存10率,
        ROUND(
        sum(
            if (DATEDIFF(CONCAT(userdaystat.pt, ' 00:00:00'), CONCAT(user_t.pt, ' 00:00:00'), 'dd') = 14, 1, 0)
        ) /
        count(distinct user_t.user_id)
        ,3
    ) as 留存14率

    from user_t LEFT JOIN userdaystat
    on user_t.user_id = userdaystat.user_id
    group by user_t.pt,
    
    -- 实验分桶修改 start
    CASE WHEN basic_data_center.getbucketid(user_t.user_id,'') BETWEEN 0 AND 99 THEN "20-99"
    

    -- 实验分桶修改 end
    END;

 

全量ROI模版

-- --odps sql 
-- --********************************************************************--
-- --author:odps-game
-- --create time:2019-11-21 15:50:26
-- --********************************************************************--



select  pt, 
        CASE 
        -- 实验分桶修改 start
  
        WHEN basic_data_center.getbucketid(user_id,'${bucket_num}') BETWEEN 0 AND 9 THEN '跳一跳' 
        WHEN basic_data_center.getbucketid(user_id,'${bucket_num}') BETWEEN 10 AND 19 THEN '箭头'
        WHEN basic_data_center.getbucketid(user_id,'${bucket_num}') BETWEEN 20 AND 99 THEN '对照组'

        -- 实验分桶修改 end
        END AS 策略,
        count(1) as 日活,round(sum(video_count) / count(1),2) as 人均视频数,
        round(sum(times) / count(1),3) as 人均时长,round(sum(max_flash_coin + red_coin) / count(1),3) as 人均补贴,
        round(sum(income) / count(1),3) as 人均收入,ROUND(sum(possible_reward) / count(1),3) as 人均可提补贴 
        from workspace_saiyisai.odps_calc_user_roi  where pt between "${sdate}"
        and "${edate}"  
        GROUP by pt,CASE 

         -- 实验分桶修改 start
        WHEN basic_data_center.getbucketid(user_id,'${bucket_num}') BETWEEN 0 AND 9 THEN '跳一跳' 
        WHEN basic_data_center.getbucketid(user_id,'${bucket_num}') BETWEEN 10 AND 19 THEN '箭头'
        WHEN basic_data_center.getbucketid(user_id,'${bucket_num}') BETWEEN 20 AND 99 THEN '对照组'
         -- 实验分桶修改 end
    
        END;

 

实验LTV模版

--odps sql 
--********************************************************************--
--author:odps-read
--create time:2019-11-25 14:08:06
--********************************************************************--
with 
userdaystat as (
    select pt,user_id from workspace_saiyisai.odps_calc_user_roi 
    where pt between '${date_start}' AND '${date_end}'
    and box_day_age = 0 and  client_version >= "5.4.4.5" and day_age = 0 
),

odps_calc_user_roi as ( --roi user表
select * from workspace_saiyisai.odps_calc_user_roi 
where pt between '${date_start}' AND SUBSTR(DATEADD(TO_DATE('${date_end}','yyyy-mm-dd'),7,'dd'),1,10)
)

SELECT userdaystat.pt AS 日期
,CASE 

-- 实验分桶修改 start
WHEN basic_data_center.getbucketid(userdaystat.user_id,'${bucket_num}') BETWEEN 0 AND 84 THEN '对照' 
WHEN basic_data_center.getbucketid(userdaystat.user_id,'${bucket_num}') BETWEEN 85 AND 89 THEN '改版'
WHEN basic_data_center.getbucketid(userdaystat.user_id,'${bucket_num}') BETWEEN 90 AND 94 THEN '提现'
WHEN basic_data_center.getbucketid(userdaystat.user_id,'${bucket_num}') BETWEEN 95 AND 99 THEN '改版+提现'
-- 实验分桶修改 end

END AS 策略


,sum(if(DATEDIFF(to_date(odps_calc_user_roi.pt,'yyyy-mm-dd'),to_date(userdaystat.pt,'yyyy-mm-dd'),'dd')=0,1,0 )) as 新增总人数

,round(
sum(if(DATEDIFF(to_date(odps_calc_user_roi.pt,'yyyy-mm-dd'),to_date(userdaystat.pt,'yyyy-mm-dd'),'dd')=0,times,0 ))
/
sum(if(DATEDIFF(to_date(odps_calc_user_roi.pt,'yyyy-mm-dd'),to_date(userdaystat.pt,'yyyy-mm-dd'),'dd')=0,1,0 ))
/60
,3
)as 新增人均时长

,round(
sum(if(DATEDIFF(to_date(odps_calc_user_roi.pt,'yyyy-mm-dd'),to_date(userdaystat.pt,'yyyy-mm-dd'),'dd')=0,max_flash_coin+red_coin,0 ))
/
sum(if(DATEDIFF(to_date(odps_calc_user_roi.pt,'yyyy-mm-dd'),to_date(userdaystat.pt,'yyyy-mm-dd'),'dd')=0,1,0 ))
,3
)
as 新增人均补贴

,round(
sum(if(DATEDIFF(to_date(odps_calc_user_roi.pt,'yyyy-mm-dd'),to_date(userdaystat.pt,'yyyy-mm-dd'),'dd')=0,possible_reward,0 ))
/
sum(if(DATEDIFF(to_date(odps_calc_user_roi.pt,'yyyy-mm-dd'),to_date(userdaystat.pt,'yyyy-mm-dd'),'dd')=0,1,0 ))
,3
)
as 新增人均可提补贴
,round(
sum(if(DATEDIFF(to_date(odps_calc_user_roi.pt,'yyyy-mm-dd'),to_date(userdaystat.pt,'yyyy-mm-dd'),'dd')=0,income,0 ))
/
sum(if(DATEDIFF(to_date(odps_calc_user_roi.pt,'yyyy-mm-dd'),to_date(userdaystat.pt,'yyyy-mm-dd'),'dd')=0,1,0 ))
,3
)
as 新增人均收入


,round(
sum(if(DATEDIFF(to_date(odps_calc_user_roi.pt,'yyyy-mm-dd'),to_date(userdaystat.pt,'yyyy-mm-dd'),'dd')=0,video_count,0 ))
/
sum(if(DATEDIFF(to_date(odps_calc_user_roi.pt,'yyyy-mm-dd'),to_date(userdaystat.pt,'yyyy-mm-dd'),'dd')=0,1,0 ))
,3
)
as 新增人均视频数

,round(
sum(if(DATEDIFF(to_date(odps_calc_user_roi.pt,'yyyy-mm-dd'),to_date(userdaystat.pt,'yyyy-mm-dd'),'dd') BETWEEN 0 and 2 ,times,0 ))
/
sum(if(DATEDIFF(to_date(odps_calc_user_roi.pt,'yyyy-mm-dd'),to_date(userdaystat.pt,'yyyy-mm-dd'),'dd')=0,1,0 ))
/60
,3
)as 3日人均时长

,round(
sum(if(DATEDIFF(to_date(odps_calc_user_roi.pt,'yyyy-mm-dd'),to_date(userdaystat.pt,'yyyy-mm-dd'),'dd') BETWEEN 0 and 2 ,max_flash_coin+red_coin,0 ))
/
sum(if(DATEDIFF(to_date(odps_calc_user_roi.pt,'yyyy-mm-dd'),to_date(userdaystat.pt,'yyyy-mm-dd'),'dd')=0,1,0 ))
,3
)
as 3日人均补贴

,round(
sum(if(DATEDIFF(to_date(odps_calc_user_roi.pt,'yyyy-mm-dd'),to_date(userdaystat.pt,'yyyy-mm-dd'),'dd') BETWEEN 0 and 2 ,video_count,0 ))
/
sum(if(DATEDIFF(to_date(odps_calc_user_roi.pt,'yyyy-mm-dd'),to_date(userdaystat.pt,'yyyy-mm-dd'),'dd')=0,1,0 ))
,3
)
as 3日人均视频数
,round(

sum(if(DATEDIFF(to_date(odps_calc_user_roi.pt,'yyyy-mm-dd'),to_date(userdaystat.pt,'yyyy-mm-dd'),'dd') BETWEEN 0 and 2 ,possible_reward,0 ))
/
sum(if(DATEDIFF(to_date(odps_calc_user_roi.pt,'yyyy-mm-dd'),to_date(userdaystat.pt,'yyyy-mm-dd'),'dd')=0,1,0 ))
,3
)
as 3日人均可提补贴
,round(
sum(if(DATEDIFF(to_date(odps_calc_user_roi.pt,'yyyy-mm-dd'),to_date(userdaystat.pt,'yyyy-mm-dd'),'dd') BETWEEN 0 and 2,income,0 ))
/
sum(if(DATEDIFF(to_date(odps_calc_user_roi.pt,'yyyy-mm-dd'),to_date(userdaystat.pt,'yyyy-mm-dd'),'dd')=0,1,0 ))
,3
)
as 3日人均收入

,round(
sum(if(DATEDIFF(to_date(odps_calc_user_roi.pt,'yyyy-mm-dd'),to_date(userdaystat.pt,'yyyy-mm-dd'),'dd') BETWEEN 0 and 6 ,times,0 ))
/
sum(if(DATEDIFF(to_date(odps_calc_user_roi.pt,'yyyy-mm-dd'),to_date(userdaystat.pt,'yyyy-mm-dd'),'dd')=0,1,0 ))
/60
,3
)as 7日人均时长

,round(
sum(if(DATEDIFF(to_date(odps_calc_user_roi.pt,'yyyy-mm-dd'),to_date(userdaystat.pt,'yyyy-mm-dd'),'dd') BETWEEN 0 and 6 ,max_flash_coin+red_coin,0 ))
/
sum(if(DATEDIFF(to_date(odps_calc_user_roi.pt,'yyyy-mm-dd'),to_date(userdaystat.pt,'yyyy-mm-dd'),'dd')=0,1,0 ))
,3
)
as 7日人均补贴

,round(
sum(if(DATEDIFF(to_date(odps_calc_user_roi.pt,'yyyy-mm-dd'),to_date(userdaystat.pt,'yyyy-mm-dd'),'dd') BETWEEN 0 and 6 ,video_count,0 ))
/
sum(if(DATEDIFF(to_date(odps_calc_user_roi.pt,'yyyy-mm-dd'),to_date(userdaystat.pt,'yyyy-mm-dd'),'dd')=0,1,0 ))
,3
)
as 7日人均视频数
,round(

sum(if(DATEDIFF(to_date(odps_calc_user_roi.pt,'yyyy-mm-dd'),to_date(userdaystat.pt,'yyyy-mm-dd'),'dd') BETWEEN 0 and 6 ,possible_reward,0 ))

/
sum(if(DATEDIFF(to_date(odps_calc_user_roi.pt,'yyyy-mm-dd'),to_date(userdaystat.pt,'yyyy-mm-dd'),'dd')=0,1,0 ))
,3
)
as 7日人均可提补贴
,round(
sum(if(DATEDIFF(to_date(odps_calc_user_roi.pt,'yyyy-mm-dd'),to_date(userdaystat.pt,'yyyy-mm-dd'),'dd') BETWEEN 0 and 6,income,0 ))
/
sum(if(DATEDIFF(to_date(odps_calc_user_roi.pt,'yyyy-mm-dd'),to_date(userdaystat.pt,'yyyy-mm-dd'),'dd')=0,1,0 ))
,3
)
as 7日人均收入

FROM userdaystat
,odps_calc_user_roi
WHERE userdaystat.user_id = odps_calc_user_roi.user_id
GROUP BY userdaystat.pt,
CASE 

-- 实验分桶修改 start

WHEN basic_data_center.getbucketid(userdaystat.user_id,'${bucket_num}') BETWEEN 0 AND 84 THEN '对照' 
WHEN basic_data_center.getbucketid(userdaystat.user_id,'${bucket_num}') BETWEEN 85 AND 89 THEN '改版'
WHEN basic_data_center.getbucketid(userdaystat.user_id,'${bucket_num}') BETWEEN 90 AND 94 THEN '提现'
WHEN basic_data_center.getbucketid(userdaystat.user_id,'${bucket_num}') BETWEEN 95 AND 99 THEN '改版+提现'

-- 实验分桶修改 end
END;

 

新增留存模版

--odps sql 
--********************************************************************--
--author:odps-game
--create time:2019-11-21 16:22:46
--********************************************************************--

with user_t as(
        select pt,user_id from workspace_saiyisai.odps_calc_user_roi 
        where pt between '${date_start}' AND '${date_end}' and day_age = 0
    ),
    userdaystat as(
        select pt,user_id from workspace_saiyisai.odps_calc_user_roi 
        where pt between '${date_start}' AND SUBSTR(DATEADD(TO_DATE('${date_end}','yyyy-mm-dd'),14,'dd'),1,10)
    )
select 
    CASE 
    -- 实验分桶修改 start
    
    WHEN basic_data_center.getbucketid(user_t.user_id,'') BETWEEN 0 AND 99 THEN "20-99"
 
    -- 实验分桶修改 end
    END AS 策略,
    user_t.pt as 日期,
    count(distinct user_t.user_id) 新增,
    sum(
        if (DATEDIFF(CONCAT(userdaystat.pt, ' 00:00:00'), CONCAT(user_t.pt, ' 00:00:00'), 'dd') = 1, 1, 0)
    ) 留存1,
    sum(
        if (DATEDIFF(CONCAT(userdaystat.pt, ' 00:00:00'), CONCAT(user_t.pt, ' 00:00:00'), 'dd') = 3, 1, 0)
    ) 留存3,
    sum(
        if (DATEDIFF(CONCAT(userdaystat.pt, ' 00:00:00'), CONCAT(user_t.pt, ' 00:00:00'), 'dd') = 7, 1, 0)
    ) 留存7,
    sum(
        if (DATEDIFF(CONCAT(userdaystat.pt, ' 00:00:00'), CONCAT(user_t.pt, ' 00:00:00'), 'dd') = 10, 1, 0)
    ) 留存10,
    sum(
        if (DATEDIFF(CONCAT(userdaystat.pt, ' 00:00:00'), CONCAT(user_t.pt, ' 00:00:00'), 'dd') = 14, 1, 0)
    ) 留存14,

    ROUND(
        sum(
            if (DATEDIFF(CONCAT(userdaystat.pt, ' 00:00:00'), CONCAT(user_t.pt, ' 00:00:00'), 'dd') = 1, 1, 0)
        ) /
         count(distinct user_t.user_id)
        ,3
    ) as 留存1率,

    ROUND(
        sum(
            if (DATEDIFF(CONCAT(userdaystat.pt, ' 00:00:00'), CONCAT(user_t.pt, ' 00:00:00'), 'dd') = 3, 1, 0)
        ) /
       count(distinct user_t.user_id
        ),3
    ) as 留存3率,

    ROUND(
        sum(
            if (DATEDIFF(CONCAT(userdaystat.pt, ' 00:00:00'), CONCAT(user_t.pt, ' 00:00:00'), 'dd') = 7, 1, 0)
        ) /
        count(distinct user_t.user_id)
        ,3
    ) as 留存7率,
    ROUND(
        sum(
            if (DATEDIFF(CONCAT(userdaystat.pt, ' 00:00:00'), CONCAT(user_t.pt, ' 00:00:00'), 'dd') = 10, 1, 0)
        ) /
        count(distinct user_t.user_id)
        ,3
    ) as 留存10率,
        ROUND(
        sum(
            if (DATEDIFF(CONCAT(userdaystat.pt, ' 00:00:00'), CONCAT(user_t.pt, ' 00:00:00'), 'dd') = 14, 1, 0)
        ) /
        count(distinct user_t.user_id)
        ,3
    ) as 留存14率


    from user_t LEFT join userdaystat
    on user_t.user_id = userdaystat.user_id
    and userdaystat.pt in (
        SUBSTR(DATEADD(TO_DATE(user_t.pt,'yyyy-mm-dd'),1,'dd'),1,10),
        SUBSTR(DATEADD(TO_DATE(user_t.pt,'yyyy-mm-dd'),3,'dd'),1,10),
        SUBSTR(DATEADD(TO_DATE(user_t.pt,'yyyy-mm-dd'),7,'dd'),1,10),
        SUBSTR(DATEADD(TO_DATE(user_t.pt,'yyyy-mm-dd'),10,'dd'),1,10),
        SUBSTR(DATEADD(TO_DATE(user_t.pt,'yyyy-mm-dd'),14,'dd'),1,10)
    )
    group by user_t.pt,
    CASE  
    -- 实验分桶修改 start
    WHEN basic_data_center.getbucketid(user_t.user_id,'') BETWEEN 0 AND 99 THEN "20-99"
 
    -- 实验分桶修改 end
    END;

 

新增ROI模版

-- --odps sql 
-- --********************************************************************--
-- --author:odps-game
-- --create time:2019-11-21 14:47:44
-- --********************************************************************--


select  pt, CASE 
        -- 实验分桶修改 start 
        WHEN basic_data_center.getbucketid(user_id,'${bucket_num}') BETWEEN 0 AND 9 THEN '跳一跳' 
        WHEN basic_data_center.getbucketid(user_id,'${bucket_num}') BETWEEN 10 AND 19 THEN '箭头'
        WHEN basic_data_center.getbucketid(user_id,'${bucket_num}') BETWEEN 20 AND 99 THEN '对照组'
        -- 实验分桶修改 end
        END AS 策略,
        count(1) as 日活,round(sum(video_count) / count(1),2) as 人均视频数,
        round(sum(times) / count(1),3) as 人均时长,round(sum(max_flash_coin + red_coin) / count(1),3) as 人均补贴,
        round(sum(income) / count(1),3) as 人均收入,ROUND(sum(possible_reward) / count(1),3) as 人均可提补贴 
        from workspace_saiyisai.odps_calc_user_roi  where pt between "${sdate}"
        and "${edate}" and day_age = 0
        GROUP by pt,CASE 
        -- 实验分桶修改 start
        WHEN basic_data_center.getbucketid(user_id,'${bucket_num}') BETWEEN 0 AND 9 THEN '跳一跳' 
        WHEN basic_data_center.getbucketid(user_id,'${bucket_num}') BETWEEN 10 AND 19 THEN '箭头'
        WHEN basic_data_center.getbucketid(user_id,'${bucket_num}') BETWEEN 20 AND 99 THEN '对照组'
        -- 实验分桶修改 end
        END;

 

展开阅读全文
打赏
0
0 收藏
分享
加载中
更多评论
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部