14 KiB
14 KiB
一、数据库概览
| 数据库 | 类型 | 连接地址 | 端口 | 角色 |
|---|---|---|---|---|
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 = 3AND 关联订单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 | 等级 |