data_analysis/tasks/00_DAU/dau_全维度.sql

-- 分 apptype × 尾号 × 内外 × 来源大类 × 回流天数 × 微信场景 × AB 实验 DAU
-- 基于 useractive_log 单表，无 JOIN
--
-- 7 个维度（含 dt），核心是 user_class 6 分类 + share_age_bucket 回流时长
-- GROUPING SETS 精选了 8 组常用切片（不是全组合，避免行数爆炸）
--
-- 来源大类 user_class:
--   A_外部投放拉来        : 本次被外部投放素材拉来（shareId 是 GUID 格式）
--   B_用户分享_链根外部    : 被用户分享拉来 + 链根来自外部投放
--   C_用户分享_链根内部    : 被用户分享拉来 + 链根是自然打开用户
--   D_主动回流(外部血统)   : 本次主动启动 + 有外部回流残留
--   E_主动回流(分享残留)   : 本次主动启动 + 仅有分享残留
--   F_纯主动             : 真正凭记忆主动打开
--
-- 回流天数 share_age_bucket（仅对 B/C 有意义）：
--   d0  当天 / d1  1天前 / d2_7  2-7天前 / d8_30  8-30天前 / d30plus  >30天前
WITH t_base AS
(
    SELECT  dt
            ,apptype
            ,machinecode
            ,sessionid
            ,subsessionid
            ,clienttimestamp
            ,sencetype
            ,params
            ,GET_JSON_OBJECT(extparams,'$.rootSessionId')           AS rsid
            ,GET_JSON_OBJECT(extparams,'$.rootSourceId')            AS rscid
            ,GET_JSON_OBJECT(extparams,'$.eventInfos.ab_test003')   AS ab003
            ,SPLIT_PART(SPLIT_PART(params, 'shareId=', 2), '&', 1)  AS share_id
    FROM    loghubods.useractive_log
    WHERE   dt = "${dt}"
    AND     businesstype = 'path'
    AND     GET_JSON_OBJECT(extparams,'$.rootSessionId') IS NOT NULL
    AND     GET_JSON_OBJECT(extparams,'$.rootSessionId') <> ''
),
t_derived AS
(
    SELECT  dt
            ,apptype
            ,machinecode
            ,COALESCE(ab003, 'unknown') AS ab003
            ,SUBSTR(rsid, LENGTH(rsid), 1) AS suffix
            -- 内/外二分（保留原口径，跟旧 SQL 对齐）
            ,CASE
                WHEN rscid IS NOT NULL AND rscid <> '' THEN '外部'
                ELSE '内部'
             END AS source_type
            -- 微信场景大类（避免 cardinality 爆炸，归 6 类）
            ,CASE sencetype
                WHEN '1007'  THEN '1007_单聊'
                WHEN '1008'  THEN '1008_群聊'
                WHEN '1014'  THEN '1014_朋友圈'
                WHEN '1044'  THEN '1044_群消息卡片'
                WHEN '1154'  THEN '1154_视频号'
                WHEN '1001'  THEN '1001_主入口'
                ELSE 'other'
             END AS scene_grp
            -- 投放渠道前缀（rootSourceId 不为空时才有意义）
            ,CASE
                WHEN rscid LIKE 'touliu_tencent%'         THEN 'ch_touliu_tencent'
                WHEN rscid LIKE 'dyyqw_%'                 THEN 'ch_dyyqw'
                WHEN rscid LIKE 'dyyjs_%'                 THEN 'ch_dyyjs'
                WHEN rscid LIKE '%GzhArticle%'
                  OR rscid LIKE 'DaiTou_gh_%'             THEN 'ch_gzh_article'
                WHEN rscid IS NULL OR rscid = ''          THEN 'ch_none'
                ELSE 'ch_other'
             END AS channel
            -- 用户来源 6 分类
            ,CASE
                -- 本次被外部投放拉来（shareId GUID 格式）
                WHEN params LIKE '%shareId=%'
                 AND NOT (SUBSTR(share_id, LENGTH(share_id) - INSTR(REVERSE(share_id),'-') + 2)
                          RLIKE '^[0-9]{13}$')
                    THEN 'A_外部投放拉来'
                -- 本次被用户分享拉来 + 链根外部
                WHEN params LIKE '%shareId=%'
                 AND (rscid IS NOT NULL AND rscid <> '')
                    THEN 'B_用户分享_链根外部'
                -- 本次被用户分享拉来 + 链根内部
                WHEN params LIKE '%shareId=%'
                    THEN 'C_用户分享_链根内部'
                -- 主动启动 + 有外部回流残留
                WHEN rscid IS NOT NULL AND rscid <> ''
                    THEN 'D_主动回流_外部血统'
                -- 主动启动 + 有分享残留
                WHEN rsid <> sessionid AND rsid <> subsessionid
                    THEN 'E_主动回流_分享残留'
                ELSE 'F_纯主动'
             END AS user_class
            -- 回流天数（仅 shareId 有时间戳时计算）
            ,CASE
                WHEN params LIKE '%shareId=%'
                 AND (SUBSTR(share_id, LENGTH(share_id) - INSTR(REVERSE(share_id),'-') + 2)
                      RLIKE '^[0-9]{13}$')
                THEN
                    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)
                ELSE NULL
             END AS days_diff
    FROM    t_base
),
t_final AS
(
    SELECT  dt, apptype, machinecode, ab003, suffix, source_type, scene_grp, channel, user_class
            ,CASE
                WHEN days_diff IS NULL                          THEN 'na'        -- 非分享回流
                WHEN days_diff <= 0                             THEN 'd0_当天'
                WHEN days_diff = 1                              THEN 'd1_1天前'
                WHEN days_diff BETWEEN 2 AND 7                  THEN 'd2_7_一周内'
                WHEN days_diff BETWEEN 8 AND 30                 THEN 'd8_30_一月内'
                ELSE                                                 'd30plus_长尾'
             END AS share_age_bucket
    FROM t_derived
)
SELECT  dt
        ,COALESCE(apptype,          'ALL') AS apptype
        ,COALESCE(user_class,       'ALL') AS user_class
        ,COALESCE(source_type,      'ALL') AS source_type
        ,COALESCE(suffix,           'ALL') AS suffix
        ,COALESCE(scene_grp,        'ALL') AS scene_grp
        ,COALESCE(channel,          'ALL') AS channel
        ,COALESCE(ab003,            'ALL') AS ab003
        ,COALESCE(share_age_bucket, 'ALL') AS share_age_bucket
        ,COUNT(DISTINCT machinecode)       AS dau
FROM    t_final
GROUP BY dt, apptype, user_class, source_type, suffix, scene_grp, channel, ab003, share_age_bucket
GROUPING SETS (
    -- ① 大盘日总量
    (dt),
    -- ② 大盘 × 来源（最常看）
    (dt, user_class),
    (dt, source_type),
    -- ③ 大盘 × 来源 × 回流时长（衰减曲线）
    (dt, user_class, share_age_bucket),
    -- ④ 分 apptype 全量 + 分 apptype × 来源
    (dt, apptype),
    (dt, apptype, user_class),
    (dt, apptype, source_type),
    -- ⑤ 尾号实验维度（保留原 SQL 的核心切片）
    (dt, apptype, suffix),
    (dt, apptype, suffix, source_type),
    -- ⑥ 尾号 × AB 实验（验证两套实验体系）
    (dt, suffix, ab003),
    -- ⑦ 微信场景拆分
    (dt, scene_grp),
    (dt, apptype, scene_grp, user_class),
    -- ⑧ 投放渠道拆分
    (dt, channel, user_class),
    (dt, apptype, channel)
)