-- 在原 dau_分apptype.sql 基础上加 share_age 维度 = 24h内 / 24h外（纯时间差，2 桶）
-- 定义：
--   24h内 = (clientTs - shareTs) ≤ 24 小时  OR 无可解析 shareId 时间戳（主动/投放首层）
--   24h外 = (clientTs - shareTs) > 24 小时
WITH t_base AS
(
    SELECT  dt
            ,apptype
            ,machinecode
            ,clienttimestamp
            ,params
            ,SUBSTR(
                GET_JSON_OBJECT(extparams,'$.rootSessionId'),
                LENGTH(GET_JSON_OBJECT(extparams,'$.rootSessionId')),
                1
            ) AS suffix
            ,CASE
                WHEN GET_JSON_OBJECT(extparams,'$.rootSourceId') IS NOT NULL
                 AND GET_JSON_OBJECT(extparams,'$.rootSourceId') <> ''
                THEN '外部'
                ELSE '内部'
             END AS source_type
            ,SPLIT_PART(SPLIT_PART(params, 'shareId=', 2), '&', 1) AS share_id
    FROM    loghubods.useractive_log
    WHERE   dt = "${dt}"
    AND     GET_JSON_OBJECT(extparams,'$.rootSessionId') IS NOT NULL
    AND     GET_JSON_OBJECT(extparams,'$.rootSessionId') <> ''
),
t_derived AS
(
    SELECT  dt, apptype, machinecode, suffix, source_type
            ,CASE
                -- ① 有可解析 shareId 时间戳 + 时间差 > 24h
                WHEN params LIKE '%shareId=%'
                 AND SUBSTR(share_id, LENGTH(share_id) - INSTR(REVERSE(share_id),'-') + 2)
                       RLIKE '^[0-9]{13}$'
                 AND (CAST(clienttimestamp AS BIGINT)
                    - CAST(SUBSTR(share_id, LENGTH(share_id) - INSTR(REVERSE(share_id),'-') + 2)
                           AS BIGINT)) > 86400000
                    THEN '2_24h外'
                -- ② 其他全部归 24h 内（≤24h 分享、主动、投放首层、shareId 异常）
                ELSE '1_24h内'
             END AS share_age
    FROM t_base
)
SELECT  dt
        ,COALESCE(apptype,     'ALL') AS apptype
        ,COALESCE(source_type, 'ALL') AS source_type
        ,COALESCE(suffix,      'ALL') AS suffix
        ,COALESCE(share_age,   'ALL') AS share_age
        ,COUNT(DISTINCT machinecode)  AS dau
FROM    t_derived
GROUP BY dt, apptype, suffix, source_type, share_age
GROUPING SETS (
    (dt, apptype, suffix, source_type, share_age),
    (dt, apptype, suffix, share_age),
    (dt, apptype, source_type, share_age),
    (dt, apptype, share_age),
    (dt, suffix, share_age),                       -- ← 补上次漏掉的"大盘 × 尾号 × share_age"
    (dt, suffix, source_type, share_age),
    (dt, source_type, share_age),
    (dt, share_age),
    -- 原 SQL 切片
    (dt, apptype, suffix, source_type),
    (dt, apptype, suffix),
    (dt, apptype, source_type),
    (dt, apptype),
    (dt, source_type),
    (dt)
)
ORDER BY dt, apptype, source_type, suffix, share_age
