-- 一次性 SQL：计算 20260307~20260311 五天的 dau / dau_today / dau_non_today 基线均值
-- 输出格式直接复制粘贴到 base_all_new_v3_分层_当天.sql 的 t_dau2_base5 CTE 里
--
-- 当天/非当天定义（按自然日切，北京时间）：
--   非当天 = shareId 有 13 位时间戳 且 ts 日期 < dt 自然日
--   当天   = 所有其他情况（同自然日 / 主动 / 投放首层 / shareId 异常）
WITH t_suffix_group AS
(
    SELECT "a" AS suffix, "ab" AS suffix_group
    UNION ALL SELECT "b", "ab"
    UNION ALL SELECT "0", "01"
    UNION ALL SELECT "1", "01"
    UNION ALL SELECT "2", "2c"
    UNION ALL SELECT "c", "2c"
    UNION ALL SELECT "3", "34"
    UNION ALL SELECT "4", "34"
    UNION ALL SELECT "5", "5d"
    UNION ALL SELECT "d", "5d"
    UNION ALL SELECT "6", "67"
    UNION ALL SELECT "7", "67"
    UNION ALL SELECT "8", "89"
    UNION ALL SELECT "9", "89"
    UNION ALL SELECT "e", "ef"
    UNION ALL SELECT "f", "ef"
)
,t_src AS
(
    SELECT  dt
            ,apptype
            ,machinecode
            ,clienttimestamp
            ,params
            ,SUBSTR(GET_JSON_OBJECT(extparams,'$.rootSessionId'),
                    LENGTH(GET_JSON_OBJECT(extparams,'$.rootSessionId')), 1) AS suffix
            ,subsessionid
            ,sessionid
            ,GET_JSON_OBJECT(extparams,'$.rootSessionId')                AS rsid
            ,GET_JSON_OBJECT(extparams,'$.rootSourceId')                 AS rscid
            ,SPLIT_PART(SPLIT_PART(params, 'shareId=', 2), '&', 1)       AS share_id
    FROM    loghubods.useractive_log
    WHERE   dt BETWEEN '20260307' AND '20260311'
    AND     apptype IN ("0","4")
    AND     (apptype = "4"
             OR GET_JSON_OBJECT(extparams,'$.eventInfos.ab_test003') IN ("ab0","ab1","ab2","ab3","ab4","ab5","ab6","ab7","ab8","ab9"))
    AND     (apptype = "4"
             OR GET_JSON_OBJECT(extparams,'$.eventInfos.ab_test003') NOT IN ("ab100"))
)
,t_labeled AS
(
    SELECT  dt
            ,apptype
            ,machinecode
            ,suffix
            ,CASE WHEN rscid != '' AND rscid IS NOT NULL THEN '外部' ELSE '内部' END AS source_type
            ,CASE WHEN rsid = subsessionid OR rsid = sessionid THEN '首层' ELSE '裂变层' END AS layer_type
            ,CASE
                -- 有可解析 shareId 时间戳 且 自然日不同
                WHEN params LIKE '%shareId=%'
                 AND SUBSTR(share_id, LENGTH(share_id) - INSTR(REVERSE(share_id),'-') + 2)
                       RLIKE '^[0-9]{13}$'
                 AND CAST((CAST(clienttimestamp AS BIGINT) + 28800000) / 86400000 AS BIGINT)
                   > CAST((CAST(SUBSTR(share_id, LENGTH(share_id) - INSTR(REVERSE(share_id),'-') + 2)
                              AS BIGINT) + 28800000) / 86400000 AS BIGINT)
                    THEN '非当天'
                ELSE '当天'
             END AS share_age
    FROM t_src
)
-- ① 单日 × (apptype, suffix, source_type, layer_type) DAU — 按物理尾号 distinct
,t_per_suffix AS
(
    SELECT  l.dt
            ,l.apptype
            ,sg.suffix_group
            ,l.suffix
            ,COALESCE(source_type,'总体') AS source_type
            ,COALESCE(layer_type,'总体')  AS layer_type
            ,COUNT(DISTINCT machinecode)  AS dau2
            ,COUNT(DISTINCT CASE WHEN share_age = '当天'   THEN machinecode END) AS dau_today
            ,COUNT(DISTINCT CASE WHEN share_age = '非当天' THEN machinecode END) AS dau_non_today
    FROM    t_labeled l
    INNER JOIN t_suffix_group sg
    ON      l.suffix = sg.suffix
    GROUP BY l.dt, l.apptype, sg.suffix_group, l.suffix, source_type, layer_type
    GROUPING SETS (
        (l.dt, l.apptype, sg.suffix_group, l.suffix, source_type, layer_type),
        (l.dt, l.apptype, sg.suffix_group, l.suffix)
    )
)
-- ② 单日 × suffix_group — 按 suffix 取平均
,t_daily AS
(
    SELECT  dt, apptype, suffix_group, source_type, layer_type
            ,AVG(dau2)          AS dau2
            ,AVG(dau_today)     AS dau_today
            ,AVG(dau_non_today) AS dau_non_today
    FROM t_per_suffix
    GROUP BY dt, apptype, suffix_group, source_type, layer_type
)
-- ③ 5 天均值
SELECT
    apptype
    ,suffix_group
    ,source_type
    ,layer_type
    ,ROUND(AVG(dau2),          1) AS dau2_base5
    ,ROUND(AVG(dau_today),     1) AS dau_today_base5
    ,ROUND(AVG(dau_non_today), 1) AS dau_non_today_base5
    ,COUNT(*)                     AS days_observed
FROM t_daily
GROUP BY apptype, suffix_group, source_type, layer_type
ORDER BY apptype, suffix_group, source_type, layer_type
