-- 在原 dau_分apptype.sql 基础上加一个维度：share_age = 当天 / 非当天
-- 定义：
--   "非当天" = 被以前日期产生的分享拉回（shareId 有时间戳 且 ts 日期 < dt）
--   "当天"   = 所有其他情况（主动 / 投放首层 / 当天分享）—— 即"今天产生的流量"
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 末段是 13 位时间戳 且 日期 < dt 时才算"非当天"
                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_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, 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
