65 lines
3.8 KiB
Markdown
65 lines
3.8 KiB
Markdown
# Session: 2026-05-22 16:13:10 GMT+8
|
||
|
||
- **Session Key**: agent:main:feishu:direct:ou_e63ce6b760ad39382852472f28fbe2a2
|
||
- **Session ID**: dbdc6c92-8180-47bf-9e02-b7672f5586d2
|
||
- **Source**: feishu
|
||
|
||
## Conversation Summary
|
||
|
||
user: 帮我跑一个数据，我想看每一个角色每一节课的完课时间。帮我输出成excel，用户ID、角色ID、课程ID、课程名称、完课耗时、是否首通。有什么问题可以问我。
|
||
assistant: 李承龙你好！在开始之前，我需要确认几个关键问题：
|
||
|
||
1. **数据范围**：是所有历史数据，还是限定某个时间段？（比如最近一个月、某个具体日期范围？）数据量可能比较大，明确范围可以避免Excel过大。
|
||
|
||
2. **"完课耗时"的定义**：是指**每次完成这节课的实际耗时**（同一角色同一课时多次完成会有多条记录），还是只取**首次完成的耗时**？
|
||
|
||
3. **"是否首通"的定义**：是指"该角色第一次完成这个课时"即为首通，后面的完成为非首通？这个理解对吗？
|
||
|
||
4. **课程名称**：用 `L1 S0 U00 L01` 这种格式展示可以吗？
|
||
|
||
你确认后我马上开始跑数据。
|
||
user: 1、所有历史数据，剔除测试账号和删除掉的账号。
|
||
2、每次完成都单独生成一个完成耗时，分别列出。
|
||
3、对的。
|
||
4、可以，然后不需要课程ID这一列了。
|
||
assistant: 好的，明确了。我先确认数据库和表结构，然后开始跑数据。
|
||
assistant: 数据量合理：~22万条完成记录、~2.9M条组件记录。我现在写Python脚本跑数据并导出Excel。
|
||
assistant: 数据已导出完成。现在把文件发给你。
|
||
assistant: 文件已发送。再同步发一条说明：
|
||
assistant: 数据已导出并发送给你了，李承龙。汇总一下：
|
||
|
||
| 指标 | 数值 |
|
||
|------|------|
|
||
| 总记录数 | **117,945 条** |
|
||
| 首通记录 | 112,505 条 |
|
||
| 重复完成 | 5,440 条 |
|
||
|
||
文件包含 5 列：用户ID、角色ID、课程名称（如 `L1 S0 U00 L01`）、完课耗时（分钟，保留1位小数）、是否首通。所有历史数据，已剔除测试账号和删除账号，每次完成记录都单独列出，按用户→角色→课程排列。
|
||
user: 给我看看你统计完课耗时的逻辑和代码
|
||
assistant: 核心逻辑如下：
|
||
|
||
## 完课耗时统计逻辑
|
||
|
||
### 数据来源
|
||
- **课时完成记录**：`bi_user_chapter_play_record_0~7`（8张分表，UNION ALL），筛选 `play_status = 1`（正常完成）
|
||
- **组件耗时**：`bi_user_component_play_record_0~7`（8张分表），`interval_time` 字段，单位毫秒
|
||
- **关联**：通过 `chapter_unique_id` 将课时记录与组件耗时关联
|
||
|
||
### 计算步骤
|
||
1. **组件耗时聚合**：对 8 张 component 分表按 `chapter_unique_id` 分组，`SUM(interval_time)` 得到该次课时完成的总耗时（毫秒）
|
||
2. **有效记录筛选**：chapter 表 JOIN `bi_vala_app_character` → `bi_vala_app_account`，过滤 `status=1 AND deleted_at IS NULL`（剔除测试/删除账号）
|
||
3. **课程名称**：JOIN `bi_level_unit_lesson`，拼接 `course_level + course_season + course_unit + course_lesson`
|
||
4. **耗时转换**：`总毫秒数 / 60000`，保留 1 位小数 = 分钟
|
||
5. **首通标记**：窗口函数 `MIN(created_at) OVER (PARTITION BY character_id, chapter_id)`，每条记录的 `created_at` 等于该角色+课时的最早时间则为"首通"
|
||
|
||
---
|
||
|
||
完整代码：
|
||
assistant: 整个脚本就是上面的内容。核心要点：
|
||
|
||
- **耗时源头**：`bi_user_component_play_record.interval_time`（毫秒），按 `chapter_unique_id` 聚合求和，除以 60000 转分钟
|
||
- **首通判定**：同一角色同一 `chapter_id` 下，`created_at` 最早的那条 = 首通，其余 = 非首通
|
||
- **数据过滤**：只保留 `play_status=1`（课时正常完成），且账号 `status=1` + `deleted_at IS NULL`（剔除测试/删除账号）
|
||
|
||
有什么需要调整的地方随时说。
|