ai_member_xiaoxi/output/data_map_lite.md

---

一、数据库概览

数据库	类型	连接地址	端口	角色
vala_bi	PostgreSQL	bj-postgres-16pob4sg.sql.tencentcdb.com	28591	BI 数据（订单、用户、学习行为）
vala / vala_user / vala_order	MySQL	bj-cdb-dh2fkqa0.sql.tencentcdb.com	27751	配置数据 + 用户行为源数据

🔔 所有数据库操作均为只读，禁止写入。

---

二、PostgreSQL vala_bi 库

📦 bi_vala_order（订单表）

🔔 金额计算统一使用 pay_amount_int（分），显示为元时除以 100。不要用 pay_amount（varchar 字符串）。

字段	类型	释义	计算说明
id	bigint	订单主键	COUNT(DISTINCT id) = 订单数
account_id	bigint	关联用户账号	JOIN bi_vala_app_account.id
trade_no	varchar	交易流水号	关联退费表 bi_refund_order.trade_no
out_trade_no	varchar	商户订单号	关联退费表 bi_refund_order.out_trade_no
pay_amount_int	integer	支付金额（分）	GMV/GSV 统计字段，元 = 值/100
pay_amount	varchar	支付金额（元）	⚠️ 字符串格式，不推荐使用
order_status	integer	订单状态	1=未支付 2=已支付 3=已完成 4=已退款 5=已取消
pay_success_date	timestamptz	支付成功时间	用于时间维度统计（新增付费用户日期基准）
key_from	text	销售渠道标识	渠道映射见渠道映射表
sale_channel	integer	端内购课渠道编号	仅 key_from=app-active-h5-0-0 时有效
goods_name	text	商品名称	购买的课程/商品名称
pay_channel	integer	支付渠道	微信/支付宝等
is_used	integer	是否已使用	订单是否已被消费
quantity	integer	购买数量	默认 1
expire_days	integer	有效期天数	课程有效期
created_at	timestamptz	创建时间
updated_at	timestamptz	更新时间
deleted_at	timestamptz	删除时间	NULL=未删除

关键 JOIN 条件：

-- 测试账号剔除（所有订单统计必须）
LEFT 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.status = 3          -- 退费成功
  AND r.deleted_at IS NULL

---

📦 bi_vala_app_account（用户账号表）

🔔 所有涉及订单/用户的统计，必须 WHERE status = 1 剔除测试账号。download_channel 为汉字格式，用 LIKE '%关键词%' 匹配。

字段	类型	释义	计算说明
id	bigint	账号主键	被多方关联的关键字段
tel	varchar	手机号	明文
tel_encrypt	varchar	加密手机号
name	varchar	用户姓名
status	smallint	账号状态	1=正常用户，2=测试用户。统计必须 =1
key_from	varchar	注册来源	渠道标识
download_channel	text	下载渠道（汉字）	新增用户分渠道统计字段，匹配用 LIKE
pay_status	integer	付费状态
login_times	integer	登录次数
created_at	timestamptz	注册时间	新增注册用户统计的时间基准
updated_at	timestamptz	更新时间
deleted_at	timestamptz	删除时间	NULL=未删除
id_card	varchar	身份证	⚠️ 敏感信息
remark	text	备注

---

📦 bi_vala_app_character（角色表）

🔔 关联路径：account_id → bi_vala_app_account.id，id（=角色ID）→ 学习记录表的 user_id。

字段	类型	释义	计算说明
id	bigint	角色主键	关联学习记录表的 user_id
account_id	bigint	关联账号	JOIN bi_vala_app_account.id
nickname	varchar	角色昵称
gender	smallint	性别	0=girl 1=boy
birthday	varchar	出生日期	YYYY-MM-DD，split_part(birthday,'-',1) 取年份
pay_status	smallint	付费状态	0=未付费，1=已付费
status	smallint	角色状态
latest_login	timestamptz	最后登录
created_at	timestamptz	创建时间
updated_at	timestamptz	更新时间
deleted_at	timestamptz	删除时间	NULL=未删除
avatar	text	头像 URL
total_sp_point	integer	总 SP 积分
purchase_season_package	text	赛季包购买状态	JSON 数组

---

📦 bi_refund_order（退费订单表）

🔔 退费校验必须同时满足双条件：status = 3 AND 关联订单 order_status = 4。缺一不可。

字段	类型	释义	计算说明
id	bigint	退费主键
account_id	bigint	退费用户
trade_no	varchar	交易流水号	关联 bi_vala_order.trade_no
out_trade_no	varchar	商户订单号	关联 bi_vala_order.out_trade_no
refund_amount_int	integer	退费金额（分）	退费金额计算字段，元 = 值/100
refund_amount	varchar	退费金额（元）	⚠️ 字符串格式
status	integer	退费状态	3 = 退费成功
reason	text	退费原因
sale_channel	integer	销售渠道
is_admin	boolean	是否管理员操作
refund_type	integer	退费类型
refund_method	integer	退费方式
created_at	timestamptz	创建时间
updated_at	timestamptz	更新时间
deleted_at	timestamptz	删除时间	NULL=未删除

---

📦 bi_user_course_detail（用户课程明细表）

🔔 expire_time 不为空 = 正式课，为空 = 体验课。deleted_at 有值 = 已删除课程。

字段	类型	释义	计算说明
id	integer	主键
account_id	integer	用户账号 ID	JOIN bi_vala_app_account.id
user_id	integer	角色 ID	JOIN bi_vala_app_character.id
course_level	text	课程等级	L1 / L2
active_time	timestamptz	激活时间
expire_time	timestamptz	过期时间	不为空=正式课，为空=体验课
latest_unit_index	integer	最新单元索引
latest_lesson_index	integer	最新课时索引
learn_duration	integer	学习时长
last_learn_time	timestamptz	最后学习时间
created_at	timestamptz	创建时间
updated_at	timestamptz	更新时间
deleted_at	timestamptz	删除时间	有值=已删除，统计时过滤

---

📦 bi_level_unit_lesson（课程结构映射表）

🔔 id = chapter_id，直接关联播放记录表。课程匹配统一使用此表。示例：L1 S0 U00 L01 → id=343。

字段	类型	释义	计算说明
id	integer	chapter_id	关联播放记录表的 chapter_id
course_level	varchar	课程等级	L1 / L2（对应 A1/A2）
course_season	varchar	季度	S0 / S1 / S2 ...
course_unit	varchar	单元	U00 / U01 / U02 ...
course_lesson	varchar	课时	L01 / L02 / L03 / L04 / L05

课程匹配 SQL 模板：

-- 查某课程的学习数据，先通过此表拿到 chapter_id
SELECT id FROM bi_level_unit_lesson
WHERE course_level = 'L1' AND course_season = 'S0'
  AND course_unit = 'U00' AND course_lesson = 'L01';
-- 返回 id = 343，然后用 343 去查播放记录

---

📦 bi_user_chapter_play_record_0 ~ bi_user_chapter_play_record_7（课时播放记录，8 张分表）

🔔 统计时必须用 UNION ALL 合并 8 张表。首次完成时间按 user_id + chapter_id 分组取 MIN(created_at)。chapter_unique_id 关联组件播放记录。

字段	类型	释义	计算说明
id	bigint	主键
user_id	bigint	角色 ID	JOIN bi_vala_app_character.id
chapter_id	bigint	课时 ID	JOIN bi_level_unit_lesson.id
chapter_unique_id	varchar	完课唯一标识	关联 bi_user_component_play_record.chapter_unique_id
play_status	smallint	播放状态	1=正常完成，0=未开始，2=中途退出
created_at	timestamptz	创建时间	首次完成时间 = MIN(created_at) 分组
updated_at	timestamptz	更新时间
deleted_at	timestamptz	删除时间	NULL=未删除
story_id	bigint	故事 ID
level	varchar	等级
map_position	text	地图位置
dialog_info	text	对话信息 JSON

分表合并模板：

SELECT user_id, chapter_id, chapter_unique_id, play_status,
       MIN(created_at) AS first_finish_time
FROM (
    SELECT * FROM bi_user_chapter_play_record_0 WHERE deleted_at IS NULL
    UNION ALL
    SELECT * FROM bi_user_chapter_play_record_1 WHERE deleted_at IS NULL
    UNION ALL
    -- ... 2~7 同理
    SELECT * FROM bi_user_chapter_play_record_7 WHERE deleted_at IS NULL
) AS all_records
WHERE play_status = 1  -- 正常完成
GROUP BY user_id, chapter_id, chapter_unique_id, play_status

---

📦 bi_user_component_play_record_0 ~ bi_user_component_play_record_7（组件播放记录，8 张分表）

🔔 interval_time 求和得课时总耗时（毫秒），÷60000 = 分钟。退出率计算：有效参与 = play_status IN (1,2)，退出 = play_status=2。component_unique_code 用于组件维度分组。

字段	类型	释义	计算说明
id	bigint	主键
user_id	bigint	角色 ID
chapter_id	bigint	课时 ID
chapter_unique_id	varchar	完课唯一标识	关联课时播放记录
component_unique_code	varchar	组件唯一编码	如 core_reading_order-0101004，组件维度统计的分组键
component_id	bigint	组件 ID
component_type	smallint	组件类型
comp_type	varchar	组件类型名
play_status	smallint	播放状态	1=完成，2=退出（退出率：IN (1,2) 为有效参与）
play_result	varchar	练习结果	Perfect / Good / Oops
interval_time	integer	播放耗时（毫秒）	课时总耗时 = SUM(interval_time) / 60000 分钟
pass_time	integer	通过时间
read_word_count	integer	阅读词数
speak_count	integer	口语次数
listen_sentence_count	integer	听力句数
write_word_count	integer	写作词数
created_at	timestamptz	创建时间
updated_at	timestamptz	更新时间
deleted_at	timestamptz	删除时间	统计需过滤
session_id	varchar	会话 ID
listen_time	integer	听力时间
skill_points	integer	技能点
finish_question_count	integer	完成题目数

Oops率/退出率计算模板：

-- 组件 Oops率
SELECT component_unique_code,
       COUNT(CASE WHEN play_result = 'Oops' THEN 1 END) * 100.0 / COUNT(*) AS oops_rate
FROM (
    SELECT * FROM bi_user_component_play_record_0 WHERE deleted_at IS NULL
    UNION ALL ... -- 1~7 同理
) AS all_comp
WHERE play_status = 1
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 * FROM bi_user_component_play_record_0 WHERE deleted_at IS NULL
    UNION ALL ... -- 1~7 同理
) AS all_comp
WHERE play_status IN (1, 2)   -- 有效参与 = 完成 + 退出
GROUP BY component_unique_code;

---

📦 bi_user_unit_review_question_result（课程巩固 Review 源表）

🔔 巩固用时 = play_time / 1000 秒。巩固正确率 = 正确数 / 总题数 × 10000（万分比）。question_list 为 JSON 数组，需解析 isRight 和 isSkip。

字段	类型	释义	计算说明
id	bigint	主键
user_id	bigint	角色 ID
story_id	bigint	故事 ID
score	integer	得分
score_text	varchar	评分等级	Perfect / Good / Oops
question_list	text	题目列表 JSON	解析 isRight、isSkip 计算正确率
play_time	bigint	播放时间（毫秒）	巩固用时 = play_time / 1000（秒）
created_at	timestamptz	创建时间
updated_at	timestamptz	更新时间
deleted_at	timestamptz	删除时间
sp_value	integer	SP 值
exp	integer	经验值
level	varchar	等级

正确率计算模板：

-- 巩固正确率（万分比）
-- 先解析 question_list JSON 数组，统计 isRight=true 的数量
-- 正确率 = SUM(isRight=true) / COUNT(*) * 10000

---

📦 bi_user_unit_summary_km_result（单元强化 Summary 源表）

🔔 强化进入时间 = created_at。

字段	类型	释义	计算说明
id	bigint	主键
user_id	bigint	角色 ID
story_id	bigint	故事 ID
km_id	varchar	知识模块 ID
km_type	varchar	知识模块类型
score_text	varchar	评分等级
play_time	bigint	播放时间
created_at	timestamptz	首次进入时间	强化进入时间的基准
updated_at	timestamptz	更新时间
deleted_at	timestamptz	删除时间
sum_explain	text	总结说明
sp_value	integer	SP 值
level	varchar	等级

---

📦 bi_user_unit_challenge_question_result（单元挑战 Challenge 源表）

🔔 question_list JSON 包含四维度评分详情，需解析出 listening/speaking/reading/writing 各自的评分。

字段	类型	释义	计算说明
id	bigint	主键
user_id	bigint	角色 ID
chapter_id	bigint	课时 ID	注意：挑战是单元级功能
story_id	bigint	故事 ID
score	integer	总得分
score_text	varchar	评分等级	Perfect / Good / Oops
question_list	text	题目列表 JSON	包含 listening/speaking/reading/writing 四维度评分
play_time	bigint	播放时间
created_at	timestamptz	首次进入时间	挑战进入时间
updated_at	timestamptz	更新时间
deleted_at	timestamptz	删除时间
unique_id	varchar	唯一标识
sp_value	integer	SP 值
exp	integer	经验值
level	varchar	等级

---

📦 bi_vala_seasonal_ticket（季度券 PG 侧）

字段	类型	释义	计算说明
id	bigint	主键
account_id	bigint	账号 ID
character_id	integer	角色 ID
out_trade_no	varchar	订单号
ticket_type	smallint	券类型	1=Season Pass，2=Season Copy
season_package_id	integer	赛季包 ID
season_package_name	varchar	赛季包名称
season_package_level	varchar	赛季包等级
status	integer	状态	0=未使用，1=已使用，-1=禁用
give_time	bigint	发放时间戳
expire_time	bigint	到期时间戳
used_time	bigint	使用时间戳
redeem_code	varchar	兑换码
character_name	varchar	角色名
created_at	timestamptz	创建时间
updated_at	timestamptz	更新时间
deleted_at	timestamptz	删除时间

---

📦 其他 PG 表（简要）

🔔 以下表在统计分析中使用频率较低，列出字段概要。

account_detail_info（账号详情表）

字段	释义
id	主键
account_id	关联账号
login_time	登录时间
device	设备信息
device_os	iOS/Android
login_address	登录地址，"省份-城市"，split_part(login_address,'-',2) 取城市
login_times	登录次数
phone_login_times	手机登录次数（=0 从未手机登录）
created_time	创建时间

account_login（账号登录表）

字段	释义
id	主键
account_id	关联账号
login_date	登录日期（日期维度分组）
status	状态
created_time	创建时间

account_device（设备表）

字段	释义
id	主键
account_id	关联账号
device	设备标识
created_time	创建时间

account_activity_count（账号活动统计表）

字段	释义
id	主键
time_period	时间周期
counts	计数
stat_date	统计日期
created_time	创建时间

account_paid_activity_count（付费账号活动统计表）

字段	释义
id	主键
time_period	时间周期
counts	计数
stat_date	统计日期
created_time	创建时间

growth_activity_behavior（增长活动行为表）

字段	释义
id	主键
es_id	ES 记录 ID
account_id	账号 ID
user_id	角色 ID
activity	活动
behavior	行为
sub_behavior	子行为
source	来源
info	详细信息
created_at	创建时间

user_behavior_buried_points（埋点数据表）

字段	释义
id	主键
burying_point_id / burying_point_name	埋点信息
burying_point_sub_id / burying_point_sub_name	子埋点
account_id / account_name	账号
character_id / character_name	角色
unit_id / unit_name	单元
lesson_id / lesson_name	课时
component_id / component_name	组件
active_time	活跃时间
finish_status	完成状态
step_duration	步骤耗时
season_package_id / season_package_name	赛季包
c_type / c_id	组件类别

user_detail_info（用户详情表）

字段	释义
id	主键
user_id	角色 ID
latest_login_time	最近登录时间戳
latest_active_time	最近活跃时间戳
created_time	创建时间

user_behavior_0 ~ user_behavior_15（用户行为分表，16 张）

字段	释义
id	主键
user_id	角色 ID
stat_date	统计日期
learning_time	学习时长
created_time	创建时间

data_sync_config（数据同步配置表）

字段	释义
id	主键
job_name	任务名称
database_name	数据库名
origin_table	源表
dest_table	目标表
deal_time	处理时间

feishu_table_record（飞书表格记录表）

字段	释义
id	主键
data_table	数据表名
record_id	记录 ID

chapter_component_index（组件索引表）

组件与课时的索引映射关系。

chapter_config_count（配置计数表）

课时配置的计数统计。

bi_user_task_log（用户任务日志表）

字段	释义
id	主键
user_id	角色 ID
task_type	任务类型
task_id	任务 ID
status	状态
start_time	开始时间（Unix 时间戳）

---

三、MySQL vala 库（配置表）

📦 vala_game_info（游戏/单元信息表）

字段	类型	释义	计算说明
id	bigint	单元 ID	关联 game_id
cn_name	varchar	中文名称
en_name	varchar	英文名称
game_code	varchar	游戏编码
game_category	tinyint	游戏类型	1=改编，2=原创
season_package_id	bigint	季度包 ID	关联 vala_game_season_package.id
index	int	排序	UnitIndex = (SeasonOfQuarter-1)×12 + Index
level_type	tinyint	展开类型	1=顺序展开，2=触发式展开
is_hide	tinyint	是否隐藏	0=不隐藏，1=隐藏
is_ready	tinyint	是否就绪
desc	text	简介
plot_card_config	text	剧情卡配置 JSON
created_at	datetime	创建时间
updated_at	datetime	更新时间
deleted_at	datetime	删除时间

---

📦 vala_game_chapter（游戏关卡/课时表）

字段	类型	释义	计算说明
id	bigint	课时 ID	即 chapter_id
game_id	bigint	所属单元 ID	JOIN vala_game_info.id
season_package_id	bigint	季度包 ID
cn_name	varchar	中文名称
en_name	varchar	英文名称
scene_id	varchar	关联场景 ID
index	smallint	排序	每单元 5 节课：1~5 → L01~L05
lesson_type	tinyint	课程类型	1=主线，2=支线
desc	text	简介
learn_config	text	思路学习内容 JSON
created_at	datetime	创建时间
updated_at	datetime	更新时间
deleted_at	datetime	删除时间

课程结构公式：

ChapterIndex = UnitIndex × 5 + Chapter.Index
UnitIndex = (SeasonOfQuarter - 1) × 12 + GameInfo.Index

---

📦 vala_game_season_package（赛季包表）

字段	类型	释义	计算说明
id	bigint	赛季包 ID
cn_name	varchar	中文名称
en_name	varchar	英文名称
level	char	难度等级	L1 / L2
season_of_year	int	年份
season_of_quarter	int	季度	1/2/3/4
is_hide	tinyint	是否隐藏
is_ready	tinyint	是否就绪
index	int	排序
created_at	datetime	创建时间
updated_at	datetime	更新时间
deleted_at	datetime	删除时间

课程结构层级：

CourseLevel (L1/L2)
  └─ SeasonPackage (S0 / S1 / S2 / S3 / S4)
       └─ GameInfo (Unit, 每季 12 个)
            └─ GameChapter (Lesson, 每单元 5 个)

---

📦 unit_challenge_question（挑战题目配置表）

字段	类型	释义	计算说明
id	bigint	主键
category	varchar	挑战科目	listening/speaking/reading/writing
type	varchar	题目类型
question_set_id	varchar	题目集 ID
question_content	text	题目配置 JSON
material_list	text	材料列表
related_path	text	组件关联路径
created_at	datetime	创建时间
updated_at	datetime	更新时间
deleted_at	datetime	删除时间

---

四、MySQL vala_user 库（行为源表）

📦 vala_user_learn_record（用户学习记录表）

字段	类型	释义
id	bigint	主键
user_id	bigint	角色 ID
game_id	bigint	故事 ID
chapter_id	bigint	关卡/课时 ID
component_type	bigint	组件类型
sub_component_type	bigint	组件子类型
component_id	bigint	组件 ID
card_content	text	知识卡详情 JSON
is_new	tinyint	是否新数据（0/1）
created_at	datetime	创建时间
updated_at	datetime	更新时间
deleted_at	datetime	删除时间

---

📦 vala_user_chapter_finish_record（课时完成记录表）

字段	类型	释义
id	bigint	主键
user_id	bigint	角色 ID
game_id	bigint	游戏 ID
chapter_id	bigint	关卡/课时 ID
created_at	datetime	首次完成时间
updated_at	datetime	更新时间
deleted_at	datetime	删除时间

---

📦 vala_user_interact_log（用户互动日志表）

字段	类型	释义
id	bigint	主键
user_id	bigint	角色 ID
npc_id	bigint	NPC ID
session_id	varchar	会话 ID
component_type	tinyint	互动类型（1=knowledge_QA）
relation_type	tinyint	好友关系类型（1~5）
summary	text	对话总结
dialog_list	text	对话详情 JSON
dialog_score	text	对话得分汇总
tencent_soe	text	腾讯云语音评测
chat_review_resp	text	回顾学习数据
cost_time	int	耗时（秒）
favor_value	int	好感度值
created_at	datetime	创建时间
updated_at	datetime	更新时间
deleted_at	datetime	删除时间

---

📦 vala_user_game_finish_record（游戏完成记录表）

字段	类型	释义
id	int	主键
user_id	bigint	角色 ID
game_id	bigint	故事 ID
chapter_id	bigint	关卡 ID
score	float	总得分
score_level	char	得分等级
unique_code	varchar	唯一校验码
source_type	tinyint	来源类型（1=APP，2=小程序）
created_at	datetime	创建时间
updated_at	datetime	更新时间
deleted_at	timestamp	删除时间

---

📦 vala_user_long_term_memory（用户长期记忆表）

字段	类型	释义
id	bigint	主键
user_id	int	角色 ID
npc_id	int	NPC 角色 ID
robot_id	int	机器人 ID
session_id	varchar	会话 ID
memory_type	varchar	记忆类型
sub_type	varchar	来源子类型
source	varchar	来源
source_type	varchar	来源类型
user_content	text	用户内容
system_content	text	系统内容
created_at	datetime	创建时间
updated_at	datetime	更新时间
deleted_at	datetime	删除时间

---

五、MySQL vala_order 库

📦 vala_seasonal_ticket（季度券 MySQL 源表）

字段	类型	释义
id	bigint	主键
unique_code	varchar	季度券唯一码
account_id	bigint	账户 ID
account_name	varchar	账户名
tel	varchar	手机号
give_time	int	发放时间戳
expire_time	int	到期时间戳
expire_type	tinyint	有效期类型
ticket_type	tinyint	券类型
used_time	int	使用时间戳
season_package_id	bigint	赛季包 ID
season_package_name	varchar	赛季包名称
status	tinyint	状态
created_at	datetime	创建时间
updated_at	datetime	更新时间
deleted_at	datetime	删除时间

---

六、核心计算逻辑

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	核心版本上线时间

---

附录：快速索引

查什么	去哪个表	关键字段
订单/收入	bi_vala_order	pay_amount_int, order_status, key_from, sale_channel
退费	bi_refund_order + bi_vala_order	status=3 + order_status=4 双条件
测试账号剔除	bi_vala_app_account	status = 1
用户注册渠道	bi_vala_app_account	download_channel
角色信息	bi_vala_app_character	account_id → 账号, id → 学习记录
课程结构	bi_level_unit_lesson	id = chapter_id
课时播放记录	bi_user_chapter_play_record_0~7	user_id, chapter_id, play_status
组件播放记录	bi_user_component_play_record_0~7	component_unique_code, play_result, interval_time
巩固练习	bi_user_unit_review_question_result	play_time, question_list
单元强化	bi_user_unit_summary_km_result	created_at
单元挑战	bi_user_unit_challenge_question_result	question_list 四维度
课程明细	bi_user_course_detail	expire_time（空=体验课）, deleted_at
季度券	bi_vala_seasonal_ticket（PG）/ vala_seasonal_ticket（MySQL）	status, ticket_type
游戏/单元配置	vala_game_info（MySQL）	id, season_package_id
关卡/课时配置	vala_game_chapter（MySQL）	id, game_id
赛季包配置	vala_game_season_package（MySQL）	level, season_of_quarter
挑战题目配置	unit_challenge_question（MySQL）	category
用户互动	vala_user_interact_log（MySQL）	favor_value, component_type