某音频内容平台会把播客节目的精彩片段剪成短音频，账号可以继续转发别人的分享链接，形成一棵“传播链”。运营团队想统计某个片段在指定时间范围内发起的每一条首发传播链的扩散质量。 本题使用 MySQL 8.0 语法，核心考点是 WITH RECURSIVE ... AS (...)递归查询。 表结构和字段说明如下。 表podcast_accounts：平台账号主数据表。 字段account_id：BIGINT，账号 ID，主键。 字段account_name：VARCHAR(100)，账号名称。 字段account_role：VARCHAR(20)，账号角色，可能值如official、creator、listener。 字段city：VARCHAR(50)，账号所在城市。 字段joined_at：DATETIME，账号注册时间。 表clip_share_events：播客片段分享事件表。 字段share_id：BIGINT，分享事件 ID，主键。 字段clip_id：BIGINT，播客片段 ID。 字段sharer_account_id：BIGINT，执行本次分享的账号 ID，对应podcast_accounts.account_id。 字段parent_share_id：BIGINT，上游分享事件 ID；若为NULL，表示该条记录是首发分享。 字段share_time：DATETIME，分享发生时间。 字段play_seconds：INT，该分享链接带来的有效播放秒数。 补充业务规则如下。 一条传播链从一条parent_share_id IS NULL的首发分享开始。 某条分享的下游分享，是指clip_share_events.parent_share_id = 当前 share_id的记录。 传播链需要递归向下展开，直到没有新的下游分享为止。 本题只统计clip_id = 9001。 首发分享必须发生在2025-07-01 00:00:00到2025-07-07 23:59:59之间。 只要某条记录属于这些首发分享的递归下游，即使它自己的share_time超出上述时间范围，也应计入对应传播链。 若某个节点在本传播链内没有任何子节点，则它是叶子节点。 如果某条首发分享没有任何下游分享，则这条首发记录本身也算 1 个叶子节点。 3. 问题 请查询clip_id = 9001在2025-07-01 00:00:00到2025-07-07 23:59:59期间发起的每一条首发传播链，返回以下字段： root_share_id：首发分享 ID root_account_name：首发账号名称 total_share_count：该传播链的分享总数，包含首发节点自身 max_depth：该传播链的最大层级深度，首发节点深度记为0 leaf_share_count：该传播链中的叶子节点数量 distinct_account_count：该传播链中参与分享的不同账号数量 total_play_minutes：该传播链内所有节点play_seconds之和换算成分钟，并四舍五入保留 2 位小数 结果按以下规则排序： max_depth降序 distinct_account_count降序 total_share_count降序 root_share_id升序 4. 示例数据表 表podcast_accounts account_id account_name account_role city joined_at 1 澜声播客官号 official 上海 2025-01-05 09:00:00 2 阿越 creator 北京 2025-02-10 10:00:00 3 北屿 creator 杭州 2025-03-01 08:30:00 4 Momo listener 深圳 2025-03-20 12:00:00 5 七喜 listener 成都 2025-04-08 18:00:00 表clip_share_events share_id clip_id sharer_account_id parent_share_id share_time play_seconds 1001 9001 1 NULL 2025-07-02 09:00:00 120 1002 9001 2 1001 2025-07-02 10:00:00 80 1003 9001 3 1002 2025-07-02 11:30:00 55 1004 9001 4 1002 2025-07-02 12:00:00 20 1005 9001 5 1004 2025-07-02 12:30:00 66 1006 9001 3 NULL 2025-07-03 09:30:00 40 1007 9001 4 1006 2025-07-03 10:00:00 35 1008 9002 1 NULL 2025-07-04 08:00:00 90 5. 示例数据查询结果表 root_share_id root_account_name total_share_count max_depth leaf_share_count distinct_account_count total_play_minutes 1001 澜声播客官号 5 3 2 5 5.68 1006 北屿 2 1 1 2 1.25
示例1

输入

CREATE TABLE podcast_accounts (
    account_id BIGINT PRIMARY KEY,
    account_name VARCHAR(100) NOT NULL,
    account_role VARCHAR(20) NOT NULL,
    city VARCHAR(50) NOT NULL,
    joined_at DATETIME NOT NULL
);

CREATE TABLE clip_share_events (
    share_id BIGINT PRIMARY KEY,
    clip_id BIGINT NOT NULL,
    sharer_account_id BIGINT NOT NULL,
    parent_share_id BIGINT NULL,
    share_time DATETIME NOT NULL,
    play_seconds INT NOT NULL
);

INSERT INTO podcast_accounts (account_id, account_name, account_role, city, joined_at) VALUES
(1, '澜声播客官号', 'official', '上海', '2025-01-05 09:00:00'),
(2, '阿越', 'creator', '北京', '2025-02-10 10:00:00'),
(3, '北屿', 'creator', '杭州', '2025-03-01 08:30:00'),
(4, 'Momo', 'listener', '深圳', '2025-03-20 12:00:00'),
(5, '七喜', 'listener', '成都', '2025-04-08 18:00:00');

INSERT INTO clip_share_events (share_id, clip_id, sharer_account_id, parent_share_id, share_time, play_seconds) VALUES
(1001, 9001, 1, NULL, '2025-07-02 09:00:00', 120),
(1002, 9001, 2, 1001, '2025-07-02 10:00:00', 80),
(1003, 9001, 3, 1002, '2025-07-02 11:30:00', 55),
(1004, 9001, 4, 1002, '2025-07-02 12:00:00', 20),
(1005, 9001, 5, 1004, '2025-07-02 12:30:00', 66),
(1006, 9001, 3, NULL, '2025-07-03 09:30:00', 40),
(1007, 9001, 4, 1006, '2025-07-03 10:00:00', 35),
(1008, 9002, 1, NULL, '2025-07-04 08:00:00', 90);

输出

root_share_id|root_account_name|total_share_count|max_depth|leaf_share_count|distinct_account_count|total_play_minutes
1001|澜声播客官号|5|3|2|5|5.68
1006|北屿|2|1|1|2|1.25
加载中...