-- 原 dau_分apptype.sql + share_age 维度 = 当天 / 24h内 / 24h外
-- 定义（精确按时间差和自然日切，3 桶 disjoint）：
--   当天    = shareId 跟 dt 同一自然日（北京时间）
--             OR 无 shareId 时间戳（主动/投放首层默认当天）
--   24h内   = 跨日 AND (clientTs - shareTs) ≤ 24 小时
--   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_parsed AS
(
    SELECT  dt, apptype, machinecode, suffix, source_type, clienttimestamp
            ,share_id
            ,SUBSTR(share_id, LENGTH(share_id) - INSTR(REVERSE(share_id),'-') + 2) AS sid_last
            ,CASE WHEN params LIKE '%shareId=%' THEN 1 ELSE 0 END AS has_share
    FROM    t_base
),
t_derived AS
(
    SELECT  dt, apptype, machinecode, suffix, source_type
            ,CASE
                -- 没有 shareId 或 shareId 末段不是 13 位时间戳 → 默认当天
                WHEN has_share = 0 OR NOT (sid_last RLIKE '^[0-9]{13}$')
                    THEN '1_当天'
                -- 同一自然日（北京时间）
                WHEN CAST((CAST(clienttimestamp AS BIGINT) + 28800000) / 86400000 AS BIGINT)
                   = CAST((CAST(sid_last AS BIGINT)        + 28800000) / 86400000 AS BIGINT)
                    THEN '1_当天'
                -- 跨日 AND 时间差 ≤ 24 小时
                WHEN (CAST(clienttimestamp AS BIGINT) - CAST(sid_last AS BIGINT)) <= 86400000
                    THEN '2_24h内'
                ELSE '3_24h外'
             END AS share_age
    FROM t_parsed
)
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, 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
