--*********************
-- 曝光回流收益计算 (B + C 多跳版)
-- 数学公式:
--   B_i      = 不换视频回流人数（用 rootshareid 计算）
--   C_1(i)   = Σ B_j, j 在 E_i 回流用户的 session 中（1跳）
--   C_2(i)   = Σ B_k, k 在 C_1 涉及曝光的回流用户 session 中（2跳）
--   C_3(i)   = 同理（3跳）
--   V_total  = B + C_1 + C_2 + C_3
--
-- 相比递推版:
--   1. 去掉 D（session内累积），避免指数级膨胀
--   2. 每层 C 独立计算，不依赖上一层结果
--   3. CTE 从 ~18 个减到 ~12 个
--*********************

WITH
--========================================
-- 1. 基础数据准备
--========================================

-- 1.1 回流数据（用户通过分享链接回流）
-- 时间范围：${dt}${hh} 往后 24 小时
t_return AS (
    SELECT  *
            ,CONCAT(dthh,":",shareid,":",vid,":",dthh_id) AS id
    FROM    (
                SELECT  CONCAT(year,month,day,hour) AS dthh
                        ,apptype
                        ,machinecode AS mid
                        ,clickobjectid AS vid
                        ,sessionid
                        ,subsessionid
                        ,shareid
                        ,rootshareid
                        ,CAST(clienttimestamp / 1000 AS BIGINT) AS ts
                        ,ROW_NUMBER() OVER (PARTITION BY CONCAT(year,month,day,hour),apptype,machinecode,clickobjectid,sessionid,subsessionid,shareid,rootshareid ORDER BY clienttimestamp DESC ) AS rn
                        ,ROW_NUMBER() OVER (PARTITION BY CONCAT(year,month,day,hour),shareid,clickobjectid ORDER BY clienttimestamp ) AS dthh_id
                FROM    loghubods.user_share_log_flow
                WHERE   CONCAT(year,month,day,hour) BETWEEN '${dt}${hh}' AND TO_CHAR(FROM_UNIXTIME(UNIX_TIMESTAMP(TO_DATE('${dt}${hh}','YYYYMMDDHH')) + 3600 * 24),'YYYYMMDDHH')
                AND     __topic__ = 'click'
                AND     apptype IS NOT NULL
                AND     apptype NOT IN ('12')
                AND     machinecode IS NOT NULL
                AND     clickobjectid IS NOT NULL
                AND     pagesource REGEXP "-pages/user-videos-share$"
            ) t
    WHERE   rn = 1
)

-- 1.2 分享数据
-- 时间范围：${dt}${hh} 往后 24 小时
,t_share_from_sharelog AS (
    SELECT  *
    FROM    (
                SELECT  CONCAT(year,month,day,hour) AS dthh
                        ,apptype
                        ,machinecode AS mid
                        ,shareobjectid AS vid
                        ,sessionid
                        ,subsessionid
                        ,pagesource
                        ,shareid
                        ,CAST(clienttimestamp / 1000 AS BIGINT) AS ts
                        ,ROW_NUMBER() OVER (PARTITION BY CONCAT(year,month,day,hour),apptype,machinecode,shareobjectid,sessionid,subsessionid,pagesource,shareid ORDER BY clienttimestamp DESC ) AS rn
                FROM    loghubods.user_share_log_flow
                WHERE   CONCAT(year,month,day,hour) BETWEEN '${dt}${hh}' AND TO_CHAR(FROM_UNIXTIME(UNIX_TIMESTAMP(TO_DATE('${dt}${hh}','YYYYMMDDHH')) + 3600 * 24),'YYYYMMDDHH')
                AND     __topic__ = 'share'
                AND     apptype IS NOT NULL
                AND     apptype NOT IN ('12')
                AND     machinecode IS NOT NULL
                AND     shareobjectid IS NOT NULL
            ) t
    WHERE   rn = 1
)

-- 1.3 曝光数据
-- 时间范围：${dt}${hh} 单小时（曝光起点）
,t_exposure AS (
    SELECT  dthh_id
            ,dthh
            ,apptype
            ,uid
            ,mid
            ,vid
            ,sessionid
            ,subsessionid
            ,pagesource
            ,ts
            ,id
            ,dt
            ,hh
    FROM    loghubods.dwd_recsys_alg_exposure_base_view_20250402
    WHERE   CONCAT(dt,hh) = '${dt}${hh}'
)

-- 1.4 详情页曝光（用于非常规分享关联）
,t_exposure_detail AS (
    SELECT  *
    FROM    t_exposure
    WHERE   pagesource REGEXP "-pages/user-videos-detail$|pages/detail-recommend$"
)

--========================================
-- 2. 曝光关联分享（单次 JOIN + 优先级选择）
--========================================

-- 2.1 常规分享关联曝光（一次性匹配所有优先级）
,t_normal_share_exposure AS (
    SELECT  *
    FROM    (
        SELECT  s.dthh
                ,s.apptype
                ,s.mid
                ,s.vid
                ,s.sessionid
                ,s.subsessionid
                ,s.pagesource
                ,s.shareid
                ,s.ts
                ,e.id AS exposure_id
                ,e.ts AS exposure_ts
                -- 优先级：subsession+pagesource+ts > session+pagesource+ts > ...
                ,CASE
                    WHEN s.subsessionid = e.subsessionid AND s.pagesource = e.pagesource AND s.ts >= e.ts THEN 1
                    WHEN s.sessionid = e.sessionid AND s.pagesource = e.pagesource AND s.ts >= e.ts THEN 2
                    WHEN s.subsessionid = e.subsessionid AND s.pagesource = e.pagesource THEN 3
                    WHEN s.sessionid = e.sessionid AND s.pagesource = e.pagesource THEN 4
                    WHEN s.subsessionid = e.subsessionid THEN 5
                    WHEN s.sessionid = e.sessionid THEN 6
                END AS match_priority
                ,ROW_NUMBER() OVER (
                    PARTITION BY s.dthh,s.apptype,s.mid,s.vid,s.sessionid,s.subsessionid,s.pagesource,s.shareid
                    ORDER BY
                        CASE
                            WHEN s.subsessionid = e.subsessionid AND s.pagesource = e.pagesource AND s.ts >= e.ts THEN 1
                            WHEN s.sessionid = e.sessionid AND s.pagesource = e.pagesource AND s.ts >= e.ts THEN 2
                            WHEN s.subsessionid = e.subsessionid AND s.pagesource = e.pagesource THEN 3
                            WHEN s.sessionid = e.sessionid AND s.pagesource = e.pagesource THEN 4
                            WHEN s.subsessionid = e.subsessionid THEN 5
                            WHEN s.sessionid = e.sessionid THEN 6
                        END
                        ,e.ts DESC
                ) AS rn
        FROM    t_share_from_sharelog s
        LEFT JOIN t_exposure e
        ON      s.apptype = e.apptype
        AND     s.mid = e.mid
        AND     s.vid = e.vid
        AND     (s.subsessionid = e.subsessionid OR s.sessionid = e.sessionid)
        WHERE   s.pagesource NOT REGEXP "pages/detail-user-videos-share-recommend$"
    ) t
    WHERE   rn = 1
)

-- 2.2 非常规分享关联曝光（detail页面，一次性匹配）
,t_no_normal_share_exposure AS (
    SELECT  *
    FROM    (
        SELECT  s.dthh
                ,s.apptype
                ,s.mid
                ,s.vid
                ,s.sessionid
                ,s.subsessionid
                ,s.pagesource
                ,s.shareid
                ,s.ts
                ,e.id AS exposure_id
                ,e.ts AS exposure_ts
                ,CASE
                    WHEN s.subsessionid = e.subsessionid AND s.ts >= e.ts THEN 1
                    WHEN s.sessionid = e.sessionid AND s.ts >= e.ts THEN 2
                    WHEN s.subsessionid = e.subsessionid THEN 3
                    WHEN s.sessionid = e.sessionid THEN 4
                END AS match_priority
                ,ROW_NUMBER() OVER (
                    PARTITION BY s.dthh,s.apptype,s.mid,s.vid,s.sessionid,s.subsessionid,s.pagesource,s.shareid
                    ORDER BY
                        CASE
                            WHEN s.subsessionid = e.subsessionid AND s.ts >= e.ts THEN 1
                            WHEN s.sessionid = e.sessionid AND s.ts >= e.ts THEN 2
                            WHEN s.subsessionid = e.subsessionid THEN 3
                            WHEN s.sessionid = e.sessionid THEN 4
                        END
                        ,e.ts DESC
                ) AS rn
        FROM    t_share_from_sharelog s
        LEFT JOIN t_exposure_detail e
        ON      s.apptype = e.apptype
        AND     s.mid = e.mid
        AND     s.vid = e.vid
        AND     (s.subsessionid = e.subsessionid OR s.sessionid = e.sessionid)
        WHERE   s.pagesource REGEXP "pages/detail-user-videos-share-recommend$"
    ) t
    WHERE   rn = 1
)

--========================================
-- 3. 合并所有分享-曝光关联
--========================================
,t_share_exposure AS (
    SELECT  dthh, apptype, mid, vid, sessionid, subsessionid, pagesource, shareid, ts, exposure_id, exposure_ts
    FROM    t_normal_share_exposure
    UNION ALL
    SELECT  dthh, apptype, mid, vid, sessionid, subsessionid, pagesource, shareid, ts, exposure_id, exposure_ts
    FROM    t_no_normal_share_exposure
)

--========================================
-- 4. 分享关联回流 + B 值计算
--========================================

-- 4.1 分享关联回流（exposure_id → return_subsessionid）
,t_share_return AS (
    SELECT  se.exposure_id
            ,se.shareid
            ,se.vid
            ,se.apptype
            ,se.subsessionid
            ,r.subsessionid AS return_subsessionid
            ,r.mid AS return_mid
    FROM    t_share_exposure se
    JOIN    t_return r
    ON      se.shareid = r.rootshareid
    AND     se.vid = r.vid
    AND     se.apptype = r.apptype
)

-- 4.2 每个曝光的 B 值和 bn_subsessions
,t_exposure_bn AS (
    SELECT  e.id AS exposure_id
            ,e.subsessionid
            ,e.ts
            ,e.vid
            ,e.uid
            ,e.mid
            ,COALESCE(bn.B, 0) AS B
            ,bn.bn_subsessions
    FROM    t_exposure e
    LEFT JOIN (
        SELECT  exposure_id
                ,COUNT(DISTINCT return_mid) AS B
                ,COLLECT_SET(return_subsessionid) AS bn_subsessions
        FROM    t_share_return
        GROUP BY exposure_id
    ) bn
    ON      e.id = bn.exposure_id
)

--========================================
-- 5. 多跳计算（直接 JOIN，无递推）
--========================================

-- C_1: 1跳（exposure → 回流session → 那些session中曝光的B之和）
,t_c1 AS (
    SELECT  sr.exposure_id
            ,SUM(eb.B) AS C_1
    FROM    t_share_return sr
    JOIN    t_exposure_bn eb
    ON      sr.return_subsessionid = eb.subsessionid
    GROUP BY sr.exposure_id
)

-- C_2: 2跳
,t_c2 AS (
    SELECT  sr1.exposure_id
            ,SUM(eb2.B) AS C_2
    FROM    t_share_return sr1
    JOIN    t_exposure_bn eb1
    ON      sr1.return_subsessionid = eb1.subsessionid
    JOIN    t_share_return sr2
    ON      eb1.exposure_id = sr2.exposure_id
    JOIN    t_exposure_bn eb2
    ON      sr2.return_subsessionid = eb2.subsessionid
    GROUP BY sr1.exposure_id
)

-- C_3: 3跳
,t_c3 AS (
    SELECT  sr1.exposure_id
            ,SUM(eb3.B) AS C_3
    FROM    t_share_return sr1
    JOIN    t_exposure_bn eb1
    ON      sr1.return_subsessionid = eb1.subsessionid
    JOIN    t_share_return sr2
    ON      eb1.exposure_id = sr2.exposure_id
    JOIN    t_exposure_bn eb2
    ON      sr2.return_subsessionid = eb2.subsessionid
    JOIN    t_share_return sr3
    ON      eb2.exposure_id = sr3.exposure_id
    JOIN    t_exposure_bn eb3
    ON      sr3.return_subsessionid = eb3.subsessionid
    GROUP BY sr1.exposure_id
)

--========================================
-- 6. 最终输出
--========================================
SELECT  e.id AS exposure_id
        ,e.vid
        ,v.title AS video_title
        ,e.uid
        ,e.mid
        ,e.ts
        ,CASE WHEN se.exposure_id IS NOT NULL THEN 1 ELSE 0 END AS is_share
        ,COALESCE(bn.B, 0) AS B
        ,COALESCE(c1.C_1, 0) AS C_1
        ,COALESCE(c2.C_2, 0) AS C_2
        ,COALESCE(c3.C_3, 0) AS C_3
        ,COALESCE(bn.B, 0) + COALESCE(c1.C_1, 0) + COALESCE(c2.C_2, 0) + COALESCE(c3.C_3, 0) AS V_total
FROM    t_exposure e
LEFT JOIN (SELECT DISTINCT exposure_id FROM t_share_exposure) se
ON      e.id = se.exposure_id
LEFT JOIN t_exposure_bn bn
ON      e.id = bn.exposure_id
LEFT JOIN t_c1 c1
ON      e.id = c1.exposure_id
LEFT JOIN t_c2 c2
ON      e.id = c2.exposure_id
LEFT JOIN t_c3 c3
ON      e.id = c3.exposure_id
LEFT JOIN videoods.wx_video v
ON      e.vid = CAST(v.id AS STRING)
;
