题解 | 播客精彩片段裂变传播链统计

-- 1.1 找到首发账号，确定为当前账号
with RECURSIVE temp as (
    select
        share_id as root_share_id,   -- 根分享id
        sharer_account_id as root_account_id,  -- 根账号id
        share_id as current_share_id,         -- 确定当前分享id
        sharer_account_id as current_account_id,  -- 确定当前账号id
        0 as depth,
        play_seconds
        from clip_share_events
        where clip_id = 9001
        and parent_share_id is null            -- 筛选精髓，当前分享id不能有父类分享id，确定了它是初始分享id
        and share_time >= '2025-07-01 00:00:00'
        and share_time <= '2025-07-07 23:59:59'

union all
    
    select
        tp.root_share_id,
        tp.root_account_id,
        cse.share_id as current_share_id,
        cse.sharer_account_id as current_account_id,
        tp.depth +1 as depth,        -- 每次递归，深度+1
        cse.play_seconds
        from temp tp
        inner join clip_share_events cse
        on cse.parent_share_id = tp.current_share_id     -- 递归精髓，当前的分享id是一个id的父类分享id
        where cse.clip_id = 9001
),

temp1 as (
    select
        root_share_id,
        root_account_id,
        count(current_share_id) as total_share_count,
        max(depth) as max_depth,
        count(distinct current_account_id) as distinct_account_count,
        ROUND(SUM(play_seconds) / 60, 2) AS total_play_minutes,
        sum(
            case
            when not exists(
                select 
                1
                from clip_share_events child
                where child.parent_share_id = tp.current_share_id and  child.clip_id = 9001   -- 计算叶子节点的精髓，当前的分享id不能是任何节点的父类节点，这样它一定是叶子节点
            ) then 1
            else 0
            end
        ) as leaf_share_count
        from temp tp
        group by root_share_id , root_account_id
)

select
    tp1.root_share_id,
    pa.account_name AS root_account_name,
    tp1.total_share_count,
    tp1.max_depth,
    tp1.leaf_share_count,
    tp1.distinct_account_count,
    tp1.total_play_minutes
    from temp1 tp1
    join podcast_accounts pa
    on tp1.root_account_id = pa.account_id
    ORDER BY 
    tp1.max_depth DESC,
    tp1.distinct_account_count DESC,
    tp1.total_share_count DESC,
    tp1.root_share_id ASC;