六、核心计算逻辑
6.1 订单/收入类
-- GMV（总交易额，单位：元）
SELECT SUM(o.pay_amount_int) / 100.0 AS gmv_yuan
FROM bi_vala_order o
JOIN bi_vala_app_account a ON o.account_id = a.id AND a.status = 1
WHERE o.deleted_at IS NULL;
-- 退费金额（双条件校验）
SELECT SUM(CASE WHEN r.status = 3 AND o.order_status = 4
THEN o.pay_amount_int ELSE 0 END) / 100.0 AS refund_yuan
FROM bi_vala_order o
JOIN bi_vala_app_account a ON o.account_id = a.id AND a.status = 1
LEFT JOIN bi_refund_order r
ON o.trade_no = r.trade_no
AND o.out_trade_no = r.out_trade_no
AND r.deleted_at IS NULL
WHERE o.deleted_at IS NULL;
-- GSV = GMV - 退费金额（实际收入）
SELECT (SUM(o.pay_amount_int) - SUM(CASE WHEN r.status = 3 AND o.order_status = 4 THEN o.pay_amount_int ELSE 0 END)) / 100.0 AS gsv_yuan
FROM bi_vala_order o
JOIN bi_vala_app_account a ON o.account_id = a.id AND a.status = 1
LEFT JOIN bi_refund_order r ON o.trade_no = r.trade_no AND o.out_trade_no = r.out_trade_no AND r.deleted_at IS NULL
WHERE o.deleted_at IS NULL;
-- 退费率 = 退费订单数 / 总订单数 × 100%
SELECT
COUNT(DISTINCT CASE WHEN r.status = 3 AND o.order_status = 4 THEN o.id END) * 100.0
/ COUNT(DISTINCT o.id) AS refund_rate
FROM bi_vala_order o
JOIN bi_vala_app_account a ON o.account_id = a.id AND a.status = 1
LEFT JOIN bi_refund_order r ON o.trade_no = r.trade_no AND o.out_trade_no = r.out_trade_no AND r.deleted_at IS NULL
WHERE o.deleted_at IS NULL;
6.2 用户增长类
-- 新增注册用户（免费注册）
-- 按 download_channel 分渠道
SELECT DATE(a.created_at) AS stat_date,
a.download_channel,
COUNT(DISTINCT a.id) AS new_users
FROM bi_vala_app_account a
WHERE a.status = 1
AND a.deleted_at IS NULL
GROUP BY stat_date, a.download_channel;
-- 新增付费用户
-- 端内（key_from = 'app-active-h5-0-0'）按 sale_channel
-- 端外 按 key_from
SELECT DATE(o.pay_success_date) AS stat_date,
CASE
WHEN o.key_from = 'app-active-h5-0-0' THEN CAST(o.sale_channel AS text)
ELSE o.key_from
END AS channel,
COUNT(DISTINCT o.account_id) AS new_pay_users
FROM bi_vala_order o
JOIN bi_vala_app_account a ON o.account_id = a.id AND a.status = 1
WHERE o.deleted_at IS NULL
AND o.order_status IN (2, 3, 4) -- 已支付/已完成/已退款
GROUP BY stat_date, channel;
6.3 学习行为类
-- 课时首次完成时间（8表 UNION ALL）
SELECT user_id, chapter_id,
MIN(created_at) AS first_finish_time
FROM (
SELECT user_id, chapter_id, created_at FROM bi_user_chapter_play_record_0 WHERE play_status = 1 AND deleted_at IS NULL
UNION ALL SELECT user_id, chapter_id, created_at FROM bi_user_chapter_play_record_1 WHERE play_status = 1 AND deleted_at IS NULL
UNION ALL SELECT user_id, chapter_id, created_at FROM bi_user_chapter_play_record_2 WHERE play_status = 1 AND deleted_at IS NULL
UNION ALL SELECT user_id, chapter_id, created_at FROM bi_user_chapter_play_record_3 WHERE play_status = 1 AND deleted_at IS NULL
UNION ALL SELECT user_id, chapter_id, created_at FROM bi_user_chapter_play_record_4 WHERE play_status = 1 AND deleted_at IS NULL
UNION ALL SELECT user_id, chapter_id, created_at FROM bi_user_chapter_play_record_5 WHERE play_status = 1 AND deleted_at IS NULL
UNION ALL SELECT user_id, chapter_id, created_at FROM bi_user_chapter_play_record_6 WHERE play_status = 1 AND deleted_at IS NULL
UNION ALL SELECT user_id, chapter_id, created_at FROM bi_user_chapter_play_record_7 WHERE play_status = 1 AND deleted_at IS NULL
) AS all_records
GROUP BY user_id, chapter_id;
-- 课时总耗时（分钟）
SELECT chapter_unique_id,
SUM(interval_time) / 60000.0 AS total_minutes -- 毫秒→分钟
FROM (
SELECT chapter_unique_id, interval_time FROM bi_user_component_play_record_0 WHERE deleted_at IS NULL
UNION ALL SELECT chapter_unique_id, interval_time FROM bi_user_component_play_record_1 WHERE deleted_at IS NULL
UNION ALL SELECT chapter_unique_id, interval_time FROM bi_user_component_play_record_2 WHERE deleted_at IS NULL
UNION ALL SELECT chapter_unique_id, interval_time FROM bi_user_component_play_record_3 WHERE deleted_at IS NULL
UNION ALL SELECT chapter_unique_id, interval_time FROM bi_user_component_play_record_4 WHERE deleted_at IS NULL
UNION ALL SELECT chapter_unique_id, interval_time FROM bi_user_component_play_record_5 WHERE deleted_at IS NULL
UNION ALL SELECT chapter_unique_id, interval_time FROM bi_user_component_play_record_6 WHERE deleted_at IS NULL
UNION ALL SELECT chapter_unique_id, interval_time FROM bi_user_component_play_record_7 WHERE deleted_at IS NULL
) AS all_comp
GROUP BY chapter_unique_id;
-- 组件 Oops率（8表 UNION ALL）
SELECT component_unique_code,
COUNT(CASE WHEN play_result = 'Oops' THEN 1 END) * 100.0 / COUNT(*) AS oops_rate
FROM (
SELECT component_unique_code, play_result FROM bi_user_component_play_record_0 WHERE play_status = 1 AND deleted_at IS NULL
UNION ALL SELECT component_unique_code, play_result FROM bi_user_component_play_record_1 WHERE play_status = 1 AND deleted_at IS NULL
UNION ALL SELECT component_unique_code, play_result FROM bi_user_component_play_record_2 WHERE play_status = 1 AND deleted_at IS NULL
UNION ALL SELECT component_unique_code, play_result FROM bi_user_component_play_record_3 WHERE play_status = 1 AND deleted_at IS NULL
UNION ALL SELECT component_unique_code, play_result FROM bi_user_component_play_record_4 WHERE play_status = 1 AND deleted_at IS NULL
UNION ALL SELECT component_unique_code, play_result FROM bi_user_component_play_record_5 WHERE play_status = 1 AND deleted_at IS NULL
UNION ALL SELECT component_unique_code, play_result FROM bi_user_component_play_record_6 WHERE play_status = 1 AND deleted_at IS NULL
UNION ALL SELECT component_unique_code, play_result FROM bi_user_component_play_record_7 WHERE play_status = 1 AND deleted_at IS NULL
) AS all_comp
GROUP BY component_unique_code;
-- 组件退出率
SELECT component_unique_code,
COUNT(CASE WHEN play_status = 2 THEN 1 END) * 100.0 / COUNT(*) AS exit_rate
FROM (
SELECT component_unique_code, play_status FROM bi_user_component_play_record_0 WHERE play_status IN (1, 2) AND deleted_at IS NULL
UNION ALL SELECT component_unique_code, play_status FROM bi_user_component_play_record_1 WHERE play_status IN (1, 2) AND deleted_at IS NULL
UNION ALL SELECT component_unique_code, play_status FROM bi_user_component_play_record_2 WHERE play_status IN (1, 2) AND deleted_at IS NULL
UNION ALL SELECT component_unique_code, play_status FROM bi_user_component_play_record_3 WHERE play_status IN (1, 2) AND deleted_at IS NULL
UNION ALL SELECT component_unique_code, play_status FROM bi_user_component_play_record_4 WHERE play_status IN (1, 2) AND deleted_at IS NULL
UNION ALL SELECT component_unique_code, play_status FROM bi_user_component_play_record_5 WHERE play_status IN (1, 2) AND deleted_at IS NULL
UNION ALL SELECT component_unique_code, play_status FROM bi_user_component_play_record_6 WHERE play_status IN (1, 2) AND deleted_at IS NULL
UNION ALL SELECT component_unique_code, play_status FROM bi_user_component_play_record_7 WHERE play_status IN (1, 2) AND deleted_at IS NULL
) AS all_comp
GROUP BY component_unique_code;
6.4 课程巩固 (Review)
-- 巩固用时（秒）
SELECT user_id, play_time / 1000 AS review_seconds
FROM bi_user_unit_review_question_result
WHERE deleted_at IS NULL;
-- 巩固正确率（万分比）= 正确数/总题数 × 10000
-- 需解析 question_list JSON，统计 isRight=true 的题目数
6.5 单元强化 (Summary)
-- 强化首次进入时间
SELECT user_id, MIN(created_at) AS first_summary_time
FROM bi_user_unit_summary_km_result
WHERE deleted_at IS NULL
GROUP BY user_id;
6.6 单元挑战 (Challenge)
-- 挑战首次进入时间
SELECT user_id, MIN(created_at) AS first_challenge_time
FROM bi_user_unit_challenge_question_result
WHERE deleted_at IS NULL
GROUP BY user_id;
-- 四维度评分：从 question_list JSON 解析 listening/speaking/reading/writing
七、测试账号剔除（全局规则）
🔔 所有涉及用户的统计，必须通过 bi_vala_app_account 过滤 status = 1。这是最高优先级的前置条件。
-- 方式1：JOIN 方式（推荐，用于订单类统计）
FROM bi_vala_order o
JOIN bi_vala_app_account a ON o.account_id = a.id AND a.status = 1
-- 方式2：子查询过滤（用于学习行为类统计）
WHERE user_id IN (SELECT id FROM bi_vala_app_character WHERE account_id IN
(SELECT id FROM bi_vala_app_account WHERE status = 1))
-- 方式3：NOT EXISTS（有时效率更高）
WHERE NOT EXISTS (
SELECT 1 FROM bi_vala_app_account a
JOIN bi_vala_app_character c ON c.account_id = a.id
WHERE c.id = rec.user_id AND a.status != 1
)
八、关联关系总图
8.1 账户 → 角色 → 播放记录（学习数据主链路）
bi_vala_app_account (id)
│ 1:N
▼
bi_vala_app_character (id, account_id)
│ 1:N (user_id)
├──► bi_user_chapter_play_record_0~7 (user_id → chapter_id → bi_level_unit_lesson.id)
│ │ chapter_unique_id
│ ▼
│ bi_user_component_play_record_0~7 (user_id, chapter_unique_id, component_unique_code)
│
├──► bi_user_unit_review_question_result (user_id) -- 巩固
├──► bi_user_unit_summary_km_result (user_id) -- 强化
└──► bi_user_unit_challenge_question_result (user_id) -- 挑战
课程结构：
bi_level_unit_lesson (id=chapter_id, course_level, course_season, course_unit, course_lesson)
↕ 映射关系
MySQL: vala_game_season_package → vala_game_info → vala_game_chapter
8.2 订单 → 退费 关联链路
bi_vala_order (id, account_id, trade_no, out_trade_no, order_status, pay_amount_int)
│ JOIN ON trade_no + out_trade_no
▼
bi_refund_order (id, account_id, trade_no, out_trade_no, status, refund_amount_int)
退费校验双条件:
bi_refund_order.status = 3 AND bi_vala_order.order_status = 4
8.3 订单 → 账户 → 角色 关联链路
bi_vala_order (account_id)
│ JOIN
▼
bi_vala_app_account (id, status=1 过滤测试账号)
│ JOIN
▼
bi_vala_app_character (account_id, id → user_id)
│
▼
学习记录表 (user_id)
九、渠道映射表
9.1 sale_channel 映射（端内渠道）
🔔 仅当 key_from = 'app-active-h5-0-0'（端内购买）时，sale_channel 字段才有效。
| sale_channel |
渠道名称 |
| 11 |
苹果 |
| 12 |
华为 |
| 13 |
小米 |
| 14 |
荣耀 |
| 15 |
应用宝 |
| 17 |
魅族 |
| 18 |
VIVO |
| 19 |
OPPO |
| 21 |
学而思 |
| 22 |
讯飞 |
| 23 |
步步高 |
| 24 |
作业帮 |
| 25 |
小度 |
| 26 |
希沃 |
| 27 |
京东方 |
| 41 |
官网 |
| 71 |
小程序 |
| 其他 |
站外 |
9.2 key_from 分类（销售渠道来源）
| key_from 值 |
渠道类别 |
说明 |
app-active-h5-0-0 |
端内购买 |
需结合 sale_channel 细分 |
sales-adp*（前缀匹配） |
销售渠道 |
端外中的销售渠道 |
newmedia-dianpu-xhs-0-0 |
小红书店铺 |
|
newmedia-daren%（前缀匹配） |
达人直播 |
如 newmedia-daren-douyin-学霸老王讲真话-0 |
newmedia-dianpu-wwxx-0-0 |
万物 |
|
| 以上之外 |
端外其他 |
|
9.3 download_channel（新增注册用户分渠道）
| 匹配方式 |
说明 |
LIKE '%学而思%' |
汉字格式，关键字包含匹配 |
LIKE '%华为%' |
同上 |
| ... |
按需匹配 |
9.4 渠道统计规则
-- 付费用户渠道分类（完整逻辑）
SELECT
CASE
WHEN o.key_from = 'app-active-h5-0-0' THEN
CASE o.sale_channel
WHEN 11 THEN '苹果' WHEN 12 THEN '华为' WHEN 13 THEN '小米'
WHEN 14 THEN '荣耀' WHEN 15 THEN '应用宝' WHEN 17 THEN '魅族'
WHEN 18 THEN 'VIVO' WHEN 19 THEN 'OPPO' WHEN 21 THEN '学而思'
WHEN 22 THEN '讯飞' WHEN 23 THEN '步步高' WHEN 24 THEN '作业帮'
WHEN 25 THEN '小度' WHEN 26 THEN '希沃' WHEN 27 THEN '京东方'
WHEN 41 THEN '官网' WHEN 71 THEN '小程序'
ELSE '站外'
END
ELSE o.key_from -- 端外直接用 key_from
END AS channel
FROM bi_vala_order o;
十、关键时间节点
| 时间节点 |
说明 |
2025-10-01 |
核心版本上线时间 |
