871 lines
26 KiB
Markdown
871 lines
26 KiB
Markdown
---
|
||
epoch: 1762307396325
|
||
modelKey: "deepseek-chat|3rd party (openai-format)"
|
||
topic: "高中数学知识图谱架构设计"
|
||
|
||
|
||
tags:
|
||
- copilot-conversation
|
||
---
|
||
|
||
**user**: 你好
|
||
[Context: Notes: 知识图谱/知识图谱分层方法.md]
|
||
[Timestamp: 2025/11/05 09:49:56]
|
||
|
||
**ai**: 你好！看到你正在构建高中数学知识图谱，这个三层架构的设计方案非常专业和系统化！
|
||
|
||
从你的笔记 [[知识图谱分层方法]] 可以看出，你已经深入思考了：
|
||
|
||
🎯 **三层架构的优势**
|
||
- 完美对应"概念理解 → 方法掌握 → 实践应用"的认知规律
|
||
- 直接映射到教师讲解、方法指导、练习巩固的教学环节
|
||
- 技术实现上AI可以分步骤、有重点地提取
|
||
|
||
📚 **理论基础扎实**
|
||
- 覆盖布鲁姆教育目标分类的前5个层次（83.3%）
|
||
- 100%覆盖数学知识"四事"理论
|
||
- 全面体现数学六大核心素养
|
||
|
||
💡 **数据结构设计清晰**
|
||
- Knowledge层：概念、原理、性质、公式
|
||
- Method层：解题方法、计算技巧、证明方法
|
||
- Problem层：题目内容、知识点标注、方法标注
|
||
|
||
这个方案在理论完备性、实用导向性、技术可行性方面都考虑得很周到。需要我帮你进一步优化某个部分，或者开始实施某个具体的提取任务吗？
|
||
[Timestamp: 2025/11/05 09:50:09]
|
||
|
||
**user**: 2.2 完整数据结构设计有些复杂，请给出简化方案
|
||
[Context: Notes: 知识图谱/知识图谱分层方法.md]
|
||
[Timestamp: 2025/11/05 10:00:44]
|
||
|
||
**user**: 2.2完整数据结构设计 中的json设置有些复杂，我需要简化它。请给出方案
|
||
[Context: Notes: 知识图谱/知识图谱分层方法.md]
|
||
[Timestamp: 2025/11/05 10:02:16]
|
||
|
||
**ai**: 好的！我来帮你简化 [[知识图谱分层方法]] 中的JSON数据结构设计。
|
||
|
||
## 简化后的三层数据结构方案
|
||
|
||
### 简化原则
|
||
1. **保留核心字段** - 只保留最必要的字段
|
||
2. **合并冗余字段** - 将相似字段合并
|
||
3. **减少嵌套层级** - 扁平化数据结构
|
||
4. **保持实用性** - 不影响后续应用
|
||
|
||
---
|
||
|
||
## 第一层：Knowledge（知识层）- 简化版
|
||
|
||
```json
|
||
{
|
||
"knowledge_list": [
|
||
{
|
||
"编号": "K3-1-1-01",
|
||
"名称": "函数的三要素",
|
||
"类型": "概念",
|
||
"定义": "函数由定义域、对应关系、值域三部分组成",
|
||
"核心特征": ["任意性", "唯一性"],
|
||
"前置知识": ["K1-1-01", "K1-2-01"],
|
||
"重要程度": "核心",
|
||
"教材位置": "必修1 P62"
|
||
},
|
||
{
|
||
"编号": "K3-1-1-02",
|
||
"名称": "定义域",
|
||
"类型": "概念",
|
||
"定义": "自变量x的取值范围",
|
||
"确定方法": ["实际问题意义", "解析式限制"],
|
||
"常见限制": ["分母≠0", "偶次根式≥0", "对数真数>0"],
|
||
"重要程度": "核心",
|
||
"教材位置": "必修1 P62-65"
|
||
}
|
||
]
|
||
}
|
||
```
|
||
|
||
**简化说明：**
|
||
- 合并了"核心内容"、"原理说明"、"适用条件"为直接字段
|
||
- 移除了"关联内容"中的冗余信息
|
||
- 保留了最关键的"前置知识"和"教材位置"
|
||
|
||
---
|
||
|
||
## 第二层：Method（方法层）- 简化版
|
||
|
||
```json
|
||
{
|
||
"method_list": [
|
||
{
|
||
"编号": "M3-1-1-01",
|
||
"名称": "分式型定义域求解法",
|
||
"类型": "解题方法",
|
||
"适用场景": "求含分式的函数定义域",
|
||
"步骤": [
|
||
"识别所有分母",
|
||
"令分母≠0",
|
||
"解不等式",
|
||
"用区间表示结果"
|
||
],
|
||
"支撑知识点": ["K3-1-1-02"],
|
||
"常见错误": "遗漏分母",
|
||
"难度等级": 2
|
||
},
|
||
{
|
||
"编号": "M3-1-1-03",
|
||
"名称": "综合型定义域求解法",
|
||
"类型": "解题方法",
|
||
"适用场景": "求含多种限制条件的定义域",
|
||
"步骤": [
|
||
"列出所有限制条件",
|
||
"分别求解集",
|
||
"求交集",
|
||
"表示结果"
|
||
],
|
||
"支撑知识点": ["K3-1-1-02", "K1-3-01"],
|
||
"常见错误": "求并集而不是交集",
|
||
"难度等级": 3
|
||
}
|
||
]
|
||
}
|
||
```
|
||
|
||
**简化说明：**
|
||
- 移除了复杂的"适用场景"对象结构
|
||
- 简化"方法步骤"为字符串数组
|
||
- 移除了"数学思想"、"解题策略"等理论性字段
|
||
- 保留最实用的"常见错误"和"难度等级"
|
||
|
||
---
|
||
|
||
## 第三层：Problem（题目层）- 简化版
|
||
|
||
```json
|
||
{
|
||
"problem_list": [
|
||
{
|
||
"编号": "T3-1-1-E02",
|
||
"题目类型": "例题",
|
||
"完整题目": "已知函数 f(x)=√(x+3)+1/(x+2)\n(1) 求函数的定义域;\n(2) 求 f(-3), f(2/3) 的值;",
|
||
"考查知识点": {
|
||
"小题1": ["K3-1-1-02"],
|
||
"小题2": ["K3-1-1-06"]
|
||
},
|
||
"使用方法": {
|
||
"小题1": ["M3-1-1-01", "M3-1-1-02", "M3-1-1-03"],
|
||
"小题2": ["M3-1-1-05"]
|
||
},
|
||
"题型": "函数基础",
|
||
"难度": 2,
|
||
"来源": "必修1 P65 例2"
|
||
}
|
||
]
|
||
}
|
||
```
|
||
|
||
**简化说明：**
|
||
- 大幅简化题目基本信息
|
||
- 将"知识点标注"和"方法标注"合并为简单对象
|
||
- 移除了复杂的"题型分类"层级
|
||
- 简化难度评估为单个数字
|
||
|
||
---
|
||
|
||
## 进一步简化方案（极简版）
|
||
|
||
如果还需要更简化，可以考虑：
|
||
|
||
### 极简Knowledge层
|
||
```json
|
||
{
|
||
"编号": "K3-1-1-01",
|
||
"名称": "函数的三要素",
|
||
"定义": "函数由定义域、对应关系、值域组成",
|
||
"前置知识": ["K1-1-01"],
|
||
"重要程度": "核心"
|
||
}
|
||
```
|
||
|
||
### 极简Method层
|
||
```json
|
||
{
|
||
"编号": "M3-1-1-01",
|
||
"名称": "分式型定义域求解法",
|
||
"步骤": ["识别分母", "令≠0", "解不等式"],
|
||
"支撑知识点": ["K3-1-1-02"]
|
||
}
|
||
```
|
||
|
||
### 极简Problem层
|
||
```json
|
||
{
|
||
"编号": "T3-1-1-E02",
|
||
"题目": "求函数定义域...",
|
||
"知识点": ["K3-1-1-02"],
|
||
"方法": ["M3-1-1-01"],
|
||
"难度": 2
|
||
}
|
||
```
|
||
|
||
---
|
||
|
||
## 推荐方案
|
||
|
||
我建议采用**第一版简化方案**，因为它在简洁性和实用性之间取得了很好的平衡：
|
||
|
||
✅ **保留了核心教学价值**
|
||
- 知识点之间的关联关系
|
||
- 方法的可操作性
|
||
- 题目的考查重点
|
||
|
||
✅ **大幅减少了复杂度**
|
||
- 字段数量减少约60%
|
||
- 嵌套层级最多2层
|
||
- 更易于AI提取和人工校验
|
||
|
||
✅ **不影响后续应用**
|
||
- 仍然支持智能推荐
|
||
- 仍然支持学习路径规划
|
||
- 仍然支持能力评估
|
||
|
||
你觉得这个简化方案如何？需要进一步调整哪个部分？
|
||
[Timestamp: 2025/11/05 10:03:18]
|
||
|
||
**user**: 这种json格式，是否可以直接存入数据库？应该如何储存？
|
||
[Context: Notes: 知识图谱/知识图谱分层方法.md]
|
||
[Timestamp: 2025/11/05 10:06:09]
|
||
|
||
**ai**: 从你的简化JSON格式来看，**可以直接存入数据库**，但需要合理设计数据库表结构。让我为你提供几种存储方案：
|
||
|
||
## 方案一：关系型数据库存储（推荐）
|
||
|
||
### 1. Knowledge表
|
||
```sql
|
||
CREATE TABLE knowledge (
|
||
id VARCHAR(20) PRIMARY KEY, -- K3-1-1-01
|
||
name VARCHAR(100) NOT NULL, -- 函数的三要素
|
||
type VARCHAR(20), -- 概念/定理/公式
|
||
definition TEXT, -- 定义内容
|
||
core_features JSON, -- ["任意性", "唯一性"]
|
||
prerequisites JSON, -- ["K1-1-01", "K1-2-01"]
|
||
importance VARCHAR(10), -- 核心/重要/基础
|
||
textbook_location VARCHAR(100), -- 必修1 P62
|
||
created_at TIMESTAMP DEFAULT NOW()
|
||
);
|
||
```
|
||
|
||
### 2. Method表
|
||
```sql
|
||
CREATE TABLE method (
|
||
id VARCHAR(20) PRIMARY KEY, -- M3-1-1-01
|
||
name VARCHAR(100) NOT NULL, -- 分式型定义域求解法
|
||
type VARCHAR(20), -- 解题方法/计算技巧
|
||
scenario VARCHAR(200), -- 适用场景
|
||
steps JSON, -- ["识别分母", "令≠0", ...]
|
||
supported_knowledge JSON, -- ["K3-1-1-02"]
|
||
common_errors JSON, -- ["遗漏分母"]
|
||
difficulty_level INT, -- 1-5
|
||
created_at TIMESTAMP DEFAULT NOW()
|
||
);
|
||
```
|
||
|
||
### 3. Problem表
|
||
```sql
|
||
CREATE TABLE problem (
|
||
id VARCHAR(20) PRIMARY KEY, -- T3-1-1-E02
|
||
problem_type VARCHAR(20), -- 例题/练习题/习题
|
||
content TEXT NOT NULL, -- 完整题目内容
|
||
knowledge_mapping JSON, -- {"小题1": ["K3-1-1-02"], ...}
|
||
method_mapping JSON, -- {"小题1": ["M3-1-1-01"], ...}
|
||
problem_category VARCHAR(50), -- 函数基础
|
||
difficulty INT, -- 1-5
|
||
source VARCHAR(100), -- 必修1 P65 例2
|
||
created_at TIMESTAMP DEFAULT NOW()
|
||
);
|
||
```
|
||
|
||
### 4. 关系表（用于复杂查询）
|
||
```sql
|
||
-- 知识点前置关系表
|
||
CREATE TABLE knowledge_prerequisite (
|
||
knowledge_id VARCHAR(20),
|
||
prerequisite_id VARCHAR(20),
|
||
PRIMARY KEY (knowledge_id, prerequisite_id),
|
||
FOREIGN KEY (knowledge_id) REFERENCES knowledge(id),
|
||
FOREIGN KEY (prerequisite_id) REFERENCES knowledge(id)
|
||
);
|
||
|
||
-- 题目-知识点关联表（用于高效查询）
|
||
CREATE TABLE problem_knowledge (
|
||
problem_id VARCHAR(20),
|
||
knowledge_id VARCHAR(20),
|
||
question_part VARCHAR(10), -- 小题1/小题2/整体
|
||
is_primary BOOLEAN, -- 主要考查/辅助涉及
|
||
PRIMARY KEY (problem_id, knowledge_id, question_part),
|
||
FOREIGN KEY (problem_id) REFERENCES problem(id),
|
||
FOREIGN KEY (knowledge_id) REFERENCES knowledge(id)
|
||
);
|
||
```
|
||
|
||
---
|
||
|
||
## 方案二：文档数据库存储（如MongoDB）
|
||
|
||
如果你的数据结构相对固定且查询模式简单，可以考虑文档数据库：
|
||
|
||
### Knowledge集合
|
||
```json
|
||
{
|
||
"_id": "K3-1-1-01",
|
||
"name": "函数的三要素",
|
||
"type": "概念",
|
||
"definition": "函数由定义域、对应关系、值域三部分组成",
|
||
"core_features": ["任意性", "唯一性"],
|
||
"prerequisites": ["K1-1-01", "K1-2-01"],
|
||
"importance": "核心",
|
||
"textbook_location": "必修1 P62"
|
||
}
|
||
```
|
||
|
||
### Method集合
|
||
```json
|
||
{
|
||
"_id": "M3-1-1-01",
|
||
"name": "分式型定义域求解法",
|
||
"type": "解题方法",
|
||
"scenario": "求含分式的函数定义域",
|
||
"steps": ["识别所有分母", "令分母≠0", "解不等式", "用区间表示结果"],
|
||
"supported_knowledge": ["K3-1-1-02"],
|
||
"common_errors": ["遗漏分母"],
|
||
"difficulty_level": 2
|
||
}
|
||
```
|
||
|
||
### Problem集合
|
||
```json
|
||
{
|
||
"_id": "T3-1-1-E02",
|
||
"problem_type": "例题",
|
||
"content": "已知函数 f(x)=√(x+3)+1/(x+2)...",
|
||
"knowledge_mapping": {
|
||
"小题1": ["K3-1-1-02"],
|
||
"小题2": ["K3-1-1-06"]
|
||
},
|
||
"method_mapping": {
|
||
"小题1": ["M3-1-1-01", "M3-1-1-02", "M3-1-1-03"],
|
||
"小题2": ["M3-1-1-05"]
|
||
},
|
||
"category": "函数基础",
|
||
"difficulty": 2,
|
||
"source": "必修1 P65 例2"
|
||
}
|
||
```
|
||
|
||
---
|
||
|
||
## 方案三：图数据库存储（如Neo4j）
|
||
|
||
如果强调知识点之间的关系网络，图数据库是最佳选择：
|
||
|
||
```cypher
|
||
// 创建知识点节点
|
||
CREATE (k:Knowledge {
|
||
id: 'K3-1-1-01',
|
||
name: '函数的三要素',
|
||
type: '概念',
|
||
importance: '核心'
|
||
})
|
||
|
||
// 创建前置关系
|
||
MATCH (k1:Knowledge {id: 'K3-1-1-02'})
|
||
MATCH (k2:Knowledge {id: 'K3-1-1-01'})
|
||
CREATE (k1)-[:PREREQUISITE]->(k2)
|
||
|
||
// 创建方法节点
|
||
CREATE (m:Method {
|
||
id: 'M3-1-1-01',
|
||
name: '分式型定义域求解法',
|
||
difficulty: 2
|
||
})
|
||
|
||
// 创建支撑关系
|
||
MATCH (m:Method {id: 'M3-1-1-01'})
|
||
MATCH (k:Knowledge {id: 'K3-1-1-02'})
|
||
CREATE (m)-[:SUPPORTS]->(k)
|
||
```
|
||
|
||
---
|
||
|
||
## 推荐实施方案
|
||
|
||
### 阶段1：MVP阶段
|
||
**使用方案一（关系型数据库）**
|
||
- 技术成熟，工具丰富
|
||
- 便于复杂查询和分析
|
||
- 适合现有技术栈
|
||
|
||
### 阶段2：扩展阶段
|
||
**考虑方案三（图数据库）**
|
||
- 当关系网络变得复杂时
|
||
- 需要频繁的路径查询时
|
||
- 如："找到从知识点A到知识点B的所有学习路径"
|
||
|
||
### 具体实施建议
|
||
|
||
1. **先建立核心三表**：knowledge, method, problem
|
||
2. **使用JSON字段存储数组数据**（MySQL 5.7+ / PostgreSQL都支持）
|
||
3. **建立适当的索引**：
|
||
```sql
|
||
CREATE INDEX idx_knowledge_name ON knowledge(name);
|
||
CREATE INDEX idx_problem_difficulty ON problem(difficulty);
|
||
CREATE INDEX idx_method_difficulty ON method(difficulty_level);
|
||
```
|
||
|
||
4. **数据导入示例**：
|
||
```sql
|
||
INSERT INTO knowledge (id, name, type, definition, core_features, prerequisites, importance, textbook_location)
|
||
VALUES (
|
||
'K3-1-1-01',
|
||
'函数的三要素',
|
||
'概念',
|
||
'函数由定义域、对应关系、值域三部分组成',
|
||
'["任意性", "唯一性"]',
|
||
'["K1-1-01", "K1-2-01"]',
|
||
'核心',
|
||
'必修1 P62'
|
||
);
|
||
```
|
||
|
||
你的简化JSON格式**完全适合直接存入数据库**，我建议从关系型数据库开始，它提供了最好的查询灵活性和生态系统支持。需要我帮你设计具体的API接口或查询示例吗？
|
||
[Timestamp: 2025/11/05 10:07:26]
|
||
|
||
**user**: 我还要基于这个json，做每个学生的个性化打分或评分，比如某个知识点，A学生在某次做题中错误了，我需要对这个学生的这个知识点以及所牵涉到的方法进行减分或类似权重的打分，我应该使用什么样的数据库或形式进行储存？
|
||
[Context: Notes: 知识图谱/知识图谱分层方法.md]
|
||
[Timestamp: 2025/11/05 10:10:11]
|
||
|
||
**ai**: 基于你的需求，我建议使用**关系型数据库 + 学习记录表**的方案。这种设计能够很好地支持个性化评分和权重计算。
|
||
|
||
## 数据库设计方案
|
||
|
||
### 1. 学生表
|
||
```sql
|
||
CREATE TABLE student (
|
||
id VARCHAR(20) PRIMARY KEY, -- S001
|
||
name VARCHAR(50) NOT NULL,
|
||
grade VARCHAR(10), -- 高一/高二/高三
|
||
created_at TIMESTAMP DEFAULT NOW()
|
||
);
|
||
```
|
||
|
||
### 2. 学习记录表（核心表）
|
||
```sql
|
||
CREATE TABLE learning_record (
|
||
id BIGINT AUTO_INCREMENT PRIMARY KEY,
|
||
student_id VARCHAR(20) NOT NULL,
|
||
problem_id VARCHAR(20) NOT NULL, -- T3-1-1-E02
|
||
knowledge_id VARCHAR(20) NOT NULL, -- K3-1-1-02
|
||
method_id VARCHAR(20), -- M3-1-1-01 (可选)
|
||
question_part VARCHAR(10), -- 小题1/小题2/整体
|
||
is_correct BOOLEAN NOT NULL, -- 正确/错误
|
||
score DECIMAL(5,2), -- 得分(0-100)
|
||
response_time INT, -- 答题时间(秒)
|
||
attempt_count INT DEFAULT 1, -- 尝试次数
|
||
exam_date DATE, -- 考试/练习日期
|
||
created_at TIMESTAMP DEFAULT NOW(),
|
||
|
||
FOREIGN KEY (student_id) REFERENCES student(id),
|
||
FOREIGN KEY (problem_id) REFERENCES problem(id),
|
||
FOREIGN KEY (knowledge_id) REFERENCES knowledge(id),
|
||
FOREIGN KEY (method_id) REFERENCES method(id)
|
||
);
|
||
```
|
||
|
||
### 3. 知识点掌握度表（汇总表）
|
||
```sql
|
||
CREATE TABLE knowledge_mastery (
|
||
student_id VARCHAR(20),
|
||
knowledge_id VARCHAR(20),
|
||
mastery_score DECIMAL(5,2) DEFAULT 100.0, -- 掌握度分数(0-100)
|
||
total_attempts INT DEFAULT 0, -- 总尝试次数
|
||
correct_attempts INT DEFAULT 0, -- 正确次数
|
||
avg_response_time DECIMAL(8,2), -- 平均答题时间
|
||
last_practiced DATE, -- 最后练习日期
|
||
confidence_level DECIMAL(3,2), -- 置信度(0-1)
|
||
updated_at TIMESTAMP DEFAULT NOW(),
|
||
|
||
PRIMARY KEY (student_id, knowledge_id),
|
||
FOREIGN KEY (student_id) REFERENCES student(id),
|
||
FOREIGN KEY (knowledge_id) REFERENCES knowledge(id)
|
||
);
|
||
```
|
||
|
||
### 4. 方法掌握度表
|
||
```sql
|
||
CREATE TABLE method_mastery (
|
||
student_id VARCHAR(20),
|
||
method_id VARCHAR(20),
|
||
mastery_score DECIMAL(5,2) DEFAULT 100.0,
|
||
total_usage INT DEFAULT 0, -- 总使用次数
|
||
successful_usage INT DEFAULT 0, -- 成功使用次数
|
||
updated_at TIMESTAMP DEFAULT NOW(),
|
||
|
||
PRIMARY KEY (student_id, method_id),
|
||
FOREIGN KEY (student_id) REFERENCES student(id),
|
||
FOREIGN KEY (method_id) REFERENCES method(id)
|
||
);
|
||
```
|
||
|
||
---
|
||
|
||
## 评分算法设计
|
||
|
||
### 1. 基础评分规则
|
||
```sql
|
||
-- 每次答题后更新掌握度的存储过程
|
||
DELIMITER //
|
||
CREATE PROCEDURE UpdateMasteryScore(
|
||
IN p_student_id VARCHAR(20),
|
||
IN p_knowledge_id VARCHAR(20),
|
||
IN p_is_correct BOOLEAN,
|
||
IN p_score DECIMAL(5,2),
|
||
IN p_response_time INT
|
||
)
|
||
BEGIN
|
||
DECLARE current_mastery DECIMAL(5,2);
|
||
DECLARE new_mastery DECIMAL(5,2);
|
||
DECLARE weight_factor DECIMAL(3,2);
|
||
|
||
-- 获取当前掌握度
|
||
SELECT mastery_score INTO current_mastery
|
||
FROM knowledge_mastery
|
||
WHERE student_id = p_student_id AND knowledge_id = p_knowledge_id;
|
||
|
||
-- 如果不存在记录，初始化为100
|
||
IF current_mastery IS NULL THEN
|
||
SET current_mastery = 100.0;
|
||
INSERT INTO knowledge_mastery (student_id, knowledge_id)
|
||
VALUES (p_student_id, p_knowledge_id);
|
||
END IF;
|
||
|
||
-- 计算权重因子（基于答题时间和得分）
|
||
SET weight_factor = 0.7; -- 基础权重
|
||
|
||
IF p_response_time < 30 THEN
|
||
SET weight_factor = weight_factor + 0.1; -- 快速答题加分
|
||
END IF;
|
||
|
||
IF p_score > 80 THEN
|
||
SET weight_factor = weight_factor + 0.1; -- 高分加分
|
||
END IF;
|
||
|
||
-- 计算新掌握度
|
||
IF p_is_correct THEN
|
||
-- 答对：小幅提升
|
||
SET new_mastery = current_mastery + (100 - current_mastery) * 0.1 * weight_factor;
|
||
ELSE
|
||
-- 答错：根据错误程度扣分
|
||
SET new_mastery = current_mastery - current_mastery * 0.2 * weight_factor;
|
||
END IF;
|
||
|
||
-- 确保分数在0-100之间
|
||
SET new_mastery = GREATEST(0, LEAST(100, new_mastery));
|
||
|
||
-- 更新掌握度表
|
||
UPDATE knowledge_mastery
|
||
SET mastery_score = new_mastery,
|
||
total_attempts = total_attempts + 1,
|
||
correct_attempts = correct_attempts + IF(p_is_correct, 1, 0),
|
||
last_practiced = CURDATE(),
|
||
updated_at = NOW()
|
||
WHERE student_id = p_student_id AND knowledge_id = p_knowledge_id;
|
||
|
||
END//
|
||
DELIMITER ;
|
||
```
|
||
|
||
### 2. 高级评分策略
|
||
|
||
#### 时间衰减因子
|
||
```sql
|
||
-- 考虑时间因素的掌握度计算
|
||
CREATE FUNCTION CalculateTimeDecay(last_practiced DATE)
|
||
RETURNS DECIMAL(3,2)
|
||
READS SQL DATA
|
||
DETERMINISTIC
|
||
BEGIN
|
||
DECLARE days_passed INT;
|
||
DECLARE decay_factor DECIMAL(3,2);
|
||
|
||
SET days_passed = DATEDIFF(CURDATE(), last_practiced);
|
||
|
||
IF days_passed <= 7 THEN
|
||
SET decay_factor = 1.0;
|
||
ELSEIF days_passed <= 30 THEN
|
||
SET decay_factor = 0.9;
|
||
ELSEIF days_passed <= 90 THEN
|
||
SET decay_factor = 0.7;
|
||
ELSE
|
||
SET decay_factor = 0.5;
|
||
END IF;
|
||
|
||
RETURN decay_factor;
|
||
END;
|
||
```
|
||
|
||
#### 难度权重调整
|
||
```sql
|
||
-- 根据题目难度调整权重
|
||
CREATE FUNCTION GetDifficultyWeight(problem_id VARCHAR(20))
|
||
RETURNS DECIMAL(3,2)
|
||
READS SQL DATA
|
||
DETERMINISTIC
|
||
BEGIN
|
||
DECLARE difficulty INT;
|
||
|
||
SELECT difficulty INTO difficulty
|
||
FROM problem
|
||
WHERE id = problem_id;
|
||
|
||
RETURN CASE
|
||
WHEN difficulty = 1 THEN 0.5 -- 简单题权重低
|
||
WHEN difficulty = 2 THEN 0.8 -- 中等题
|
||
WHEN difficulty = 3 THEN 1.0 -- 较难题
|
||
WHEN difficulty = 4 THEN 1.2 -- 难题
|
||
WHEN difficulty = 5 THEN 1.5 -- 困难题
|
||
ELSE 1.0
|
||
END;
|
||
END;
|
||
```
|
||
|
||
---
|
||
|
||
## 查询示例
|
||
|
||
### 1. 获取学生知识点薄弱点
|
||
```sql
|
||
SELECT k.name, km.mastery_score, km.total_attempts, km.correct_attempts
|
||
FROM knowledge_mastery km
|
||
JOIN knowledge k ON km.knowledge_id = k.id
|
||
WHERE km.student_id = 'S001'
|
||
AND km.mastery_score < 60 -- 掌握度低于60分
|
||
ORDER BY km.mastery_score ASC;
|
||
```
|
||
|
||
### 2. 推荐练习题（基于薄弱知识点）
|
||
```sql
|
||
SELECT p.id, p.content, p.difficulty, p.source
|
||
FROM problem p
|
||
JOIN problem_knowledge pk ON p.id = pk.problem_id
|
||
WHERE pk.knowledge_id IN (
|
||
SELECT knowledge_id
|
||
FROM knowledge_mastery
|
||
WHERE student_id = 'S001' AND mastery_score < 70
|
||
)
|
||
AND p.difficulty BETWEEN 1 AND 3 -- 控制难度范围
|
||
ORDER BY p.difficulty ASC
|
||
LIMIT 10;
|
||
```
|
||
|
||
### 3. 学习进度统计
|
||
```sql
|
||
SELECT
|
||
COUNT(*) as total_knowledge,
|
||
SUM(CASE WHEN mastery_score >= 80 THEN 1 ELSE 0 END) as mastered_count,
|
||
SUM(CASE WHEN mastery_score < 60 THEN 1 ELSE 0 END) as weak_count,
|
||
AVG(mastery_score) as avg_mastery
|
||
FROM knowledge_mastery
|
||
WHERE student_id = 'S001';
|
||
```
|
||
|
||
---
|
||
|
||
## 实施建议
|
||
|
||
### 阶段1：基础评分
|
||
1. 实现基础的学习记录表
|
||
2. 使用简单的加减分规则
|
||
3. 重点关注正确/错误记录
|
||
|
||
### 阶段2：智能评分
|
||
1. 加入时间衰减因子
|
||
2. 考虑题目难度权重
|
||
3. 实现置信度计算
|
||
|
||
### 阶段3：预测分析
|
||
1. 基于历史数据预测考试表现
|
||
2. 个性化学习路径推荐
|
||
3. 知识点关联性分析
|
||
|
||
这种设计能够很好地支持你的个性化评分需求，同时保持了系统的扩展性。需要我帮你设计具体的API接口或数据导入脚本吗？
|
||
[Timestamp: 2025/11/05 10:11:50]
|
||
|
||
**user**: 重新梳理一下：我现在需要设计几张表？包括知识图谱的表和学生评分的表
|
||
[Context: Notes: 知识图谱/知识图谱分层方法.md]
|
||
[Timestamp: 2025/11/05 10:15:09]
|
||
|
||
**ai**: 基于你的需求，我来重新梳理一下需要设计的表结构。总共需要**7张核心表**：
|
||
|
||
## 知识图谱相关表（3张）
|
||
|
||
### 1. knowledge（知识点表）
|
||
```sql
|
||
CREATE TABLE knowledge (
|
||
id VARCHAR(20) PRIMARY KEY, -- K3-1-1-01
|
||
name VARCHAR(100) NOT NULL, -- 函数的三要素
|
||
type VARCHAR(20), -- 概念/定理/公式
|
||
definition TEXT, -- 定义内容
|
||
core_features JSON, -- ["任意性", "唯一性"]
|
||
prerequisites JSON, -- ["K1-1-01", "K1-2-01"]
|
||
importance VARCHAR(10), -- 核心/重要/基础
|
||
textbook_location VARCHAR(100), -- 必修1 P62
|
||
created_at TIMESTAMP DEFAULT NOW()
|
||
);
|
||
```
|
||
|
||
### 2. method（方法表）
|
||
```sql
|
||
CREATE TABLE method (
|
||
id VARCHAR(20) PRIMARY KEY, -- M3-1-1-01
|
||
name VARCHAR(100) NOT NULL, -- 分式型定义域求解法
|
||
type VARCHAR(20), -- 解题方法/计算技巧
|
||
scenario VARCHAR(200), -- 适用场景
|
||
steps JSON, -- ["识别分母", "令≠0", ...]
|
||
supported_knowledge JSON, -- ["K3-1-1-02"]
|
||
common_errors JSON, -- ["遗漏分母"]
|
||
difficulty_level INT, -- 1-5
|
||
created_at TIMESTAMP DEFAULT NOW()
|
||
);
|
||
```
|
||
|
||
### 3. problem（题目表）
|
||
```sql
|
||
CREATE TABLE problem (
|
||
id VARCHAR(20) PRIMARY KEY, -- T3-1-1-E02
|
||
problem_type VARCHAR(20), -- 例题/练习题/习题
|
||
content TEXT NOT NULL, -- 完整题目内容
|
||
knowledge_mapping JSON, -- {"小题1": ["K3-1-1-02"], ...}
|
||
method_mapping JSON, -- {"小题1": ["M3-1-1-01"], ...}
|
||
problem_category VARCHAR(50), -- 函数基础
|
||
difficulty INT, -- 1-5
|
||
source VARCHAR(100), -- 必修1 P65 例2
|
||
created_at TIMESTAMP DEFAULT NOW()
|
||
);
|
||
```
|
||
|
||
---
|
||
|
||
## 学生评分相关表（4张）
|
||
|
||
### 4. student（学生表）
|
||
```sql
|
||
CREATE TABLE student (
|
||
id VARCHAR(20) PRIMARY KEY, -- S001
|
||
name VARCHAR(50) NOT NULL,
|
||
grade VARCHAR(10), -- 高一/高二/高三
|
||
created_at TIMESTAMP DEFAULT NOW()
|
||
);
|
||
```
|
||
|
||
### 5. learning_record（学习记录表 - 核心流水表）
|
||
```sql
|
||
CREATE TABLE learning_record (
|
||
id BIGINT AUTO_INCREMENT PRIMARY KEY,
|
||
student_id VARCHAR(20) NOT NULL,
|
||
problem_id VARCHAR(20) NOT NULL, -- T3-1-1-E02
|
||
knowledge_id VARCHAR(20) NOT NULL, -- K3-1-1-02
|
||
method_id VARCHAR(20), -- M3-1-1-01 (可选)
|
||
question_part VARCHAR(10), -- 小题1/小题2/整体
|
||
is_correct BOOLEAN NOT NULL, -- 正确/错误
|
||
score DECIMAL(5,2), -- 得分(0-100)
|
||
response_time INT, -- 答题时间(秒)
|
||
attempt_count INT DEFAULT 1, -- 尝试次数
|
||
exam_date DATE, -- 考试/练习日期
|
||
created_at TIMESTAMP DEFAULT NOW(),
|
||
|
||
FOREIGN KEY (student_id) REFERENCES student(id),
|
||
FOREIGN KEY (problem_id) REFERENCES problem(id),
|
||
FOREIGN KEY (knowledge_id) REFERENCES knowledge(id),
|
||
FOREIGN KEY (method_id) REFERENCES method(id)
|
||
);
|
||
```
|
||
|
||
### 6. knowledge_mastery（知识点掌握度表 - 汇总表）
|
||
```sql
|
||
CREATE TABLE knowledge_mastery (
|
||
student_id VARCHAR(20),
|
||
knowledge_id VARCHAR(20),
|
||
mastery_score DECIMAL(5,2) DEFAULT 100.0, -- 掌握度分数(0-100)
|
||
total_attempts INT DEFAULT 0, -- 总尝试次数
|
||
correct_attempts INT DEFAULT 0, -- 正确次数
|
||
avg_response_time DECIMAL(8,2), -- 平均答题时间
|
||
last_practiced DATE, -- 最后练习日期
|
||
confidence_level DECIMAL(3,2), -- 置信度(0-1)
|
||
updated_at TIMESTAMP DEFAULT NOW(),
|
||
|
||
PRIMARY KEY (student_id, knowledge_id),
|
||
FOREIGN KEY (student_id) REFERENCES student(id),
|
||
FOREIGN KEY (knowledge_id) REFERENCES knowledge(id)
|
||
);
|
||
```
|
||
|
||
### 7. method_mastery（方法掌握度表 - 汇总表）
|
||
```sql
|
||
CREATE TABLE method_mastery (
|
||
student_id VARCHAR(20),
|
||
method_id VARCHAR(20),
|
||
mastery_score DECIMAL(5,2) DEFAULT 100.0,
|
||
total_usage INT DEFAULT 0, -- 总使用次数
|
||
successful_usage INT DEFAULT 0, -- 成功使用次数
|
||
updated_at TIMESTAMP DEFAULT NOW(),
|
||
|
||
PRIMARY KEY (student_id, method_id),
|
||
FOREIGN KEY (student_id) REFERENCES student(id),
|
||
FOREIGN KEY (method_id) REFERENCES method(id)
|
||
);
|
||
```
|
||
|
||
---
|
||
|
||
## 表关系说明
|
||
|
||
```
|
||
知识图谱层：
|
||
knowledge ← (一对多) → problem (通过knowledge_mapping关联)
|
||
method ← (一对多) → problem (通过method_mapping关联)
|
||
|
||
学生评分层：
|
||
student ← (一对多) → learning_record (流水记录)
|
||
student ← (一对多) → knowledge_mastery (汇总统计)
|
||
student ← (一对多) → method_mastery (汇总统计)
|
||
|
||
learning_record 同时关联：
|
||
- student_id (哪个学生)
|
||
- problem_id (哪道题)
|
||
- knowledge_id (哪个知识点)
|
||
- method_id (哪个方法)
|
||
```
|
||
|
||
---
|
||
|
||
## 数据流向
|
||
|
||
```
|
||
学生答题 → learning_record (记录原始数据)
|
||
↓
|
||
定时任务 → knowledge_mastery (计算掌握度)
|
||
↓
|
||
查询分析 → 个性化推荐
|
||
```
|
||
|
||
## 核心优势
|
||
|
||
1. **数据完整性** - 记录了每个学生的详细学习轨迹
|
||
2. **实时更新** - learning_record实时记录，mastery表定期汇总
|
||
3. **灵活查询** - 可以按学生、知识点、方法、时间等多个维度分析
|
||
4. **扩展性强** - 支持后续的智能推荐和预测分析
|
||
|
||
这7张表构成了完整的知识图谱+个性化评分系统。需要我帮你设计具体的SQL插入示例或API接口吗？
|
||
[Timestamp: 2025/11/05 10:16:23]
|
||
|
||
**user**: 学习纪录表learning_record 里面，是否要考虑记录题目表的id
|
||
[Context: Notes: 知识图谱/知识图谱数据库设计.md]
|
||
[Timestamp: 2025/11/05 10:28:17] |