notebook/996eco/ads_996eco_index_box_d.sql

CREATE TABLE datasci.ads_996eco_index_box_d (
  index_type text,
  data_day text,
  is_box text,
	dau bigint,
	pay_cnt bigint,
	pay_amount bigint,


)
;

DELETE FROM datasci.ads_996eco_index_box_d WHERE data_day = '${biz-date}';


-- dau
WITH game_apply AS
(
    SELECT  id
            ,name
            ,bind_game_id
    FROM    public.gm_apply
    WHERE   bind_game_type = 1
)
INSERT INTO
    datasci.ads_996eco_index_box_d (index_type,data_day,is_box,dau)
SELECT  'dau' AS  index_type
        ,dt data_day
        ,CASE   WHEN GROUPING(user_flag) = 0 THEN user_flag
                ELSE '总计'
        END AS is_box
        ,uniq(user_id) AS dau
FROM    (
            SELECT  ds::date AS dt
                    ,CASE   WHEN t1.channel LIKE '%996box%' THEN '盒子'
                            ELSE '非盒子'
                    END AS user_flag
                    ,user_id
            FROM    public.ods_active_account t1
            INNER JOIN game_apply t2
            ON      t1.appid = t2.id
            WHERE   t1.ds = '${bizdate}'
        ) t
GROUP BY
GROUPING SETS (dt,user_flag)
         ,(dt)
;


-- reg
WITH legend_apps AS
(
    SELECT  id AS appid
    FROM    gm_apply
    WHERE   bind_game_type = 1
    AND     bind_type = 1 -- 默认应用
)
---传奇游戏
,tmp_ods_newly_account AS
(
    SELECT  *
    FROM    ods_newly_account
    WHERE   ds = '${bizdate}'
)
,box_users AS
(
    SELECT  user_id
    FROM    tmp_ods_newly_account
    WHERE   appid = 1015
    OR      channel ~ '996box'
    OR      channel IN (
                SELECT  value AS channel
                FROM    public.dws_app_props
                WHERE   appid = 1015
                AND     prop = 2
            )
    GROUP BY user_id
)
-- 筛选出有盒子记录以及玩过传奇游戏的玩家
,box_users_with_legend AS
(
    SELECT  acc.user_id
    FROM    tmp_ods_newly_account acc
    WHERE
EXISTS(
                SELECT  1
                FROM    legend_apps app
                WHERE   acc.appid = app.appid
            )
    AND
EXISTS(
                SELECT  1
                FROM    box_users box
                WHERE   box.user_id = acc.user_id
            )
)
,reg_account AS
(
    -- 普通用户的传奇记录
    SELECT  acc.gmid
            ,acc.appid
            ,acc.channel
            ,acc.user_id
            ,acc.device_type
            ,acc.event_time
            ,acc.ds
            ,'非盒子用户' AS user_flag --表示没有玩过盒子的普通传奇用户
            ,'0' AS sub_channel
    FROM    tmp_ods_newly_account acc
    WHERE
EXISTS(
                SELECT  1
                FROM    legend_apps app
                WHERE   app.appid = acc.appid
            )
    AND     NOT
EXISTS(
                SELECT  1
                FROM    box_users box
                WHERE   box.user_id = acc.user_id
            )
    UNION ALL -- 盒子用户玩过传奇的全量记录
    SELECT  acc.gmid
            ,acc.appid
            ,CASE   WHEN bxchl.channel_desc IS NOT NULL THEN bxchl.channel_desc
                    ELSE acc.channel
            END AS channel
            ,acc.user_id
            ,acc.device_type
            ,acc.event_time
            ,acc.ds
            ,'盒子用户' AS user_flag ---表示玩过盒子的用户
            ,bxchl.channel_desc AS sub_channel
    FROM    tmp_ods_newly_account acc
    LEFT JOIN   (
                    SELECT  appid
                            ,value AS channel
                            ,ext AS channel_desc
                    FROM    public.dws_app_props
                    WHERE   appid = 1015
                    AND     prop = 2 -- and length(ext)>0
                ) bxchl
    ON      acc.appid = bxchl.appid
    AND     acc.channel = bxchl.channel
    WHERE
EXISTS(
                SELECT  1
                FROM    box_users_with_legend b
                WHERE   b.user_id = acc.user_id
            )
)
 INSERT INTO
     datasci.ads_996eco_index_box_d (index_type,data_month,is_box,reg_count)
SELECT  'reg' index_type
        ,'${biz-date}'  AS data_day
        ,CASE   WHEN user_flag = '非盒子用户' THEN '非盒子注册用户'
                WHEN user_flag = '盒子用户'
                    AND (appid = 1015
                    OR channel ~ '996box'
                    OR sub_channel IS NOT NULL) THEN '盒子注册用户'
                ELSE '非盒子注册用户'
        END AS is_box
        ,uniq(mi.user_id) AS reg_count
FROM    reg_account acc
INNER JOIN  (
                SELECT  user_id
                        ,MIN(event_time) AS event_time_min
                FROM    reg_account
                GROUP BY user_id
            ) mi ---从玩家的众多游戏记录中选取最早的一条
ON      acc.user_id = mi.user_id
AND     acc.event_time = mi.event_time_min
GROUP BY 1
         ,2
         ,3
;