note/知识图谱/教科书-数学/all/query_examples.sql

-- ===========================================
-- 高中数学知识图谱常用查询视图和函数
-- ===========================================

-- ===========================================
-- 1. 知识图谱查询视图
-- ===========================================

-- 知识点详细信息视图（已创建，这里重新定义以包含更多信息）
CREATE OR REPLACE VIEW knowledge_detail_view AS
SELECT
    kp.id,
    kp.code,
    kp.name,
    kp.level,
    kp.type,
    kp.importance,
    kp.core_content,
    kp.principle_explanation,
    kp.examination_methods,
    kp.textbook_location,
    s.section_name,
    c.chapter_name,
    t.name as textbook_name,
    t.volume_type,
    t.volume_number,
    -- 前置知识点数量
    (SELECT COUNT(*) FROM knowledge_relations kr
     WHERE kr.target_knowledge_id = kp.id AND kr.relation_type = '前置知识') as prerequisite_count,
    -- 支撑的方法数量
    (SELECT COUNT(*) FROM method_support_knowledge msk
     WHERE msk.knowledge_id = kp.id) as supported_methods_count,
    -- 考查的题目数量
    (SELECT COUNT(*) FROM problem_knowledge_tags pkt
     WHERE pkt.knowledge_id = kp.id AND pkt.tag_type = '主要考查') as problem_count,
    kp.created_at,
    kp.updated_at
FROM knowledge_points kp
LEFT JOIN sections s ON kp.section_id = s.id
LEFT JOIN chapters c ON s.chapter_id = c.id
LEFT JOIN textbooks t ON c.textbook_id = t.id;

-- 方法详细信息视图
CREATE OR REPLACE VIEW method_detail_view AS
SELECT
    m.id,
    m.code,
    m.name,
    m.type,
    m.difficulty_level,
    m.mathematical_thoughts,
    m.problem_solving_strategy,
    m.textbook_location,
    m.description,
    s.section_name,
    c.chapter_name,
    t.name as textbook_name,
    t.volume_type,
    -- 支撑的知识点数量
    (SELECT COUNT(*) FROM method_support_knowledge msk WHERE msk.method_id = m.id) as support_knowledge_count,
    -- 步骤数量
    (SELECT COUNT(*) FROM method_steps ms WHERE ms.method_id = m.id) as step_count,
    -- 常见错误数量
    (SELECT COUNT(*) FROM method_common_errors mce WHERE mce.method_id = m.id) as error_count,
    -- 使用的题目数量
    (SELECT COUNT(*) FROM problem_method_tags pmt WHERE pmt.method_id = m.id) as usage_count,
    m.created_at,
    m.updated_at
FROM methods m
LEFT JOIN sections s ON m.section_id = s.id
LEFT JOIN chapters c ON s.chapter_id = c.id
LEFT JOIN textbooks t ON c.textbook_id = t.id;

-- 题目详细信息视图
CREATE OR REPLACE VIEW problem_detail_view AS
SELECT
    p.id,
    p.code,
    p.problem_type,
    p.title,
    p.stem,
    p.full_content,
    p.subquestion_count,
    p.difficulty_info,
    p.textbook_location,
    s.section_name,
    c.chapter_name,
    t.name as textbook_name,
    t.volume_type,
    -- 涉及的知识点数量
    (SELECT COUNT(DISTINCT pkt.knowledge_id) FROM problem_knowledge_tags pkt
     WHERE pkt.problem_id = p.id) as knowledge_count,
    -- 使用的方法数量
    (SELECT COUNT(DISTINCT pmt.method_id) FROM problem_method_tags pmt
     WHERE pmt.problem_id = p.id) as method_count,
    p.created_at,
    p.updated_at
FROM problems p
LEFT JOIN knowledge_points kp ON p.section_id = kp.section_id
LEFT JOIN sections s ON kp.section_id = s.id
LEFT JOIN chapters c ON s.chapter_id = c.id
LEFT JOIN textbooks t ON c.textbook_id = t.id;

-- 知识点关系网络视图
CREATE OR REPLACE VIEW knowledge_relation_network AS
SELECT
    kp1.code as source_code,
    kp1.name as source_name,
    kp2.code as target_code,
    kp2.name as target_name,
    kr.relation_type,
    kr.strength,
    kr.description
FROM knowledge_relations kr
JOIN knowledge_points kp1 ON kr.source_knowledge_id = kp1.id
JOIN knowledge_points kp2 ON kr.target_knowledge_id = kp2.id;

-- ===========================================
-- 2. 统计分析视图
-- ===========================================

-- 章节知识密度统计视图
CREATE OR REPLACE VIEW chapter_knowledge_density AS
SELECT
    c.id,
    c.chapter_name,
    c.chapter_number,
    t.name as textbook_name,
    COUNT(DISTINCT kp.id) as knowledge_count,
    COUNT(DISTINCT m.id) as method_count,
    COUNT(DISTINCT p.id) as problem_count,
    -- 核心知识点数量
    COUNT(DISTINCT CASE WHEN kp.importance = '核心' THEN kp.id END) as core_knowledge_count,
    -- 平均难度
    COALESCE(AVG(p.difficulty_info->>'整体难度')::float, 0) as avg_problem_difficulty,
    -- 知识点密度（知识点数量/题目数量）
    CASE WHEN COUNT(DISTINCT p.id) > 0
         THEN ROUND(COUNT(DISTINCT kp.id)::float / COUNT(DISTINCT p.id), 2)
         ELSE 0 END as knowledge_density
FROM chapters c
LEFT JOIN sections s ON c.id = s.chapter_id
LEFT JOIN knowledge_points kp ON s.id = kp.section_id
LEFT JOIN methods m ON s.id = m.section_id
LEFT JOIN problem_knowledge_tags pkt ON kp.id = pkt.knowledge_id
LEFT JOIN problems p ON pkt.problem_id = p.id
LEFT JOIN textbooks t ON c.textbook_id = t.id
GROUP BY c.id, c.chapter_name, c.chapter_number, t.name
ORDER BY c.chapter_number;

-- 知识点考查频次统计视图
CREATE OR REPLACE VIEW knowledge_examination_frequency AS
SELECT
    kp.code,
    kp.name,
    kp.level,
    kp.importance,
    COUNT(pkt.id) as total_references,
    COUNT(DISTINCT CASE WHEN pkt.tag_type = '主要考查' THEN pkt.problem_id END) as main_problems,
    COUNT(DISTINCT CASE WHEN pkt.tag_type = '辅助涉及' THEN pkt.problem_id END) as auxiliary_problems,
    -- 平均题目难度
    COALESCE(AVG(p.difficulty_info->>'整体难度')::float, 0) as avg_problem_difficulty,
    -- 考查类型分布
    STRING_AGG(DISTINCT p.problem_type, ', ') as problem_types
FROM knowledge_points kp
LEFT JOIN problem_knowledge_tags pkt ON kp.id = pkt.knowledge_id
LEFT JOIN problems p ON pkt.problem_id = p.id
GROUP BY kp.id, kp.code, kp.name, kp.level, kp.importance
HAVING COUNT(pkt.id) > 0
ORDER BY total_references DESC;

-- 方法使用频次统计视图
CREATE OR REPLACE VIEW method_usage_frequency AS
SELECT
    m.code,
    m.name,
    m.type,
    m.difficulty_level,
    COUNT(pmt.id) as total_usage,
    COUNT(DISTINCT pmt.problem_id) as used_in_problems,
    -- 方法类型分布
    STRING_AGG(DISTINCT p.problem_type, ', ') as problem_types,
    -- 平均题目难度
    COALESCE(AVG(p.difficulty_info->>'整体难度')::float, 0) as avg_problem_difficulty
FROM methods m
LEFT JOIN problem_method_tags pmt ON m.id = pmt.method_id
LEFT JOIN problems p ON pmt.problem_id = p.id
GROUP BY m.id, m.code, m.name, m.type, m.difficulty_level
HAVING COUNT(pmt.id) > 0
ORDER BY total_usage DESC;

-- ===========================================
-- 3. 学习路径和推荐视图
-- ===========================================

-- 核心知识点学习路径视图
CREATE OR REPLACE VIEW core_learning_path AS
WITH RECURSIVE learning_sequence AS (
    -- 基础层：无前置知识的核心知识点
    SELECT
        kp.id,
        kp.code,
        kp.name,
        kp.level,
        kp.importance,
        1 as sequence_level,
        ARRAY[kp.id] as prerequisite_path,
        kp.textbook_location
    FROM knowledge_points kp
    WHERE kp.importance = '核心'
    AND NOT EXISTS (
        SELECT 1 FROM knowledge_relations kr
        WHERE kr.target_knowledge_id = kp.id
        AND kr.relation_type = '前置知识'
    )

    UNION ALL

    -- 递归：基于前置知识查找下一层知识点
    SELECT
        kp.id,
        kp.code,
        kp.name,
        kp.level,
        kp.importance,
        ls.sequence_level + 1 as sequence_level,
        ls.prerequisite_path || kp.id as prerequisite_path,
        kp.textbook_location
    FROM knowledge_points kp
    JOIN knowledge_relations kr ON kp.id = kr.source_knowledge_id
    JOIN learning_sequence ls ON kr.target_knowledge_id = ls.id
    WHERE kr.relation_type = '前置知识'
    AND kp.importance IN ('核心', '重要')
    AND kp.id != ALL(ls.prerequisite_path)
    AND ls.sequence_level < 5
)
SELECT
    code,
    name,
    level,
    importance,
    sequence_level,
    textbook_location,
    -- 前置知识点数量
    sequence_level - 1 as prerequisite_count
FROM learning_sequence
ORDER BY sequence_level, code;

-- 易错点分析视图
CREATE OR REPLACE VIEW error_prone_analysis AS
SELECT
    m.code as method_code,
    m.name as method_name,
    m.difficulty_level,
    COUNT(mce.id) as error_count,
    -- 错误类型汇总
    STRING_AGG(DISTINCT LEFT(mce.error_description, 30), '; ') as error_types,
    -- 使用该方法的题目平均难度
    COALESCE(AVG(p.difficulty_info->>'整体难度')::float, m.difficulty_level::float) as avg_usage_difficulty
FROM methods m
LEFT JOIN method_common_errors mce ON m.id = mce.method_id
LEFT JOIN problem_method_tags pmt ON m.id = pmt.method_id
LEFT JOIN problems p ON pmt.problem_id = p.id
GROUP BY m.id, m.code, m.name, m.difficulty_level
HAVING COUNT(mce.id) > 0
ORDER BY error_count DESC, avg_usage_difficulty DESC;

-- ===========================================
-- 4. 实用查询函数
-- ===========================================

-- 获取知识点的完整前置链
CREATE OR REPLACE FUNCTION get_knowledge_prerequisite_chain(knowledge_code VARCHAR)
RETURNS TABLE (
    level INTEGER,
    code VARCHAR,
    name VARCHAR,
    prerequisite_for VARCHAR
) AS $$
WITH RECURSIVE prerequisite_chain AS (
    -- 起始知识点
    SELECT
        0 as level,
        kp.code,
        kp.name,
        NULL::VARCHAR as prerequisite_for
    FROM knowledge_points kp
    WHERE kp.code = knowledge_code

    UNION ALL

    -- 递归查找前置知识点
    SELECT
        pc.level + 1,
        kp.code,
        kp.name,
        pc.code as prerequisite_for
    FROM prerequisite_chain pc
    JOIN knowledge_relations kr ON kr.source_knowledge_id = (
        SELECT id FROM knowledge_points WHERE code = pc.code
    )
    JOIN knowledge_points kp ON kr.target_knowledge_id = kp.id
    WHERE kr.relation_type = '前置知识'
    AND pc.level < 10  -- 防止无限递归
)
SELECT level, code, name, prerequisite_for
FROM prerequisite_chain
WHERE level > 0  -- 排除起始知识点
ORDER BY level;
$$ LANGUAGE SQL;

-- 获取知识点相关的题目推荐
CREATE OR REPLACE FUNCTION get_problem_recommendations(knowledge_code VARCHAR, limit_count INTEGER DEFAULT 5)
RETURNS TABLE (
    problem_code VARCHAR,
    problem_type VARCHAR,
    difficulty_level FLOAT,
    title TEXT,
    relevance_score FLOAT
) AS $$
SELECT
    p.code,
    p.problem_type,
    (p.difficulty_info->>'整体难度')::float as difficulty_level,
    p.title,
    -- 相关性评分：基于知识点标注类型
    CASE
        WHEN pkt.tag_type = '主要考查' THEN 1.0
        WHEN pkt.tag_type = '辅助涉及' THEN 0.5
        ELSE 0.1
    END as relevance_score
FROM problems p
JOIN problem_knowledge_tags pkt ON p.id = pkt.problem_id
JOIN knowledge_points kp ON pkt.knowledge_id = kp.id
WHERE kp.code = knowledge_code
ORDER BY relevance_score DESC, difficulty_level ASC
LIMIT limit_count;
$$ LANGUAGE SQL;

-- 搜索相关内容（全文搜索）
CREATE OR REPLACE FUNCTION search_knowledge_content(search_term TEXT, content_type VARCHAR DEFAULT 'all')
RETURNS TABLE (
    type VARCHAR,
    code VARCHAR,
    name VARCHAR,
    snippet TEXT,
    relevance_score FLOAT
) AS $$
BEGIN
    RETURN QUERY
    -- 搜索知识点
    SELECT
        'knowledge' as type,
        kp.code,
        kp.name,
        ts_headline('chinese', kp.name, to_tsquery('chinese', search_term)) as snippet,
        ts_rank(kp.search_vector, to_tsquery('chinese', search_term)) as relevance_score
    FROM knowledge_points kp
    WHERE kp.search_vector @@ to_tsquery('chinese', search_term)

    UNION ALL

    -- 搜索方法
    SELECT
        'method' as type,
        m.code,
        m.name,
        ts_headline('chinese', m.name, to_tsquery('chinese', search_term)) as snippet,
        ts_rank(m.search_vector, to_tsquery('chinese', search_term)) as relevance_score
    FROM methods m
    WHERE m.search_vector @@ to_tsquery('chinese', search_term)

    UNION ALL

    -- 搜索题目
    SELECT
        'problem' as type,
        p.code,
        COALESCE(p.title, '无标题') as name,
        ts_headline('chinese', p.stem, to_tsquery('chinese', search_term)) as snippet,
        ts_rank(p.search_vector, to_tsquery('chinese', search_term)) as relevance_score
    FROM problems p
    WHERE p.search_vector @@ to_tsquery('chinese', search_term)

    ORDER BY relevance_score DESC;
END;
$$ LANGUAGE plpgsql;

-- 计算知识点掌握度评估
CREATE OR REPLACE FUNCTION evaluate_knowledge_mastery(student_id INTEGER, knowledge_code VARCHAR)
RETURNS TABLE (
    knowledge_code VARCHAR,
    mastery_level NUMERIC,
    recommended_actions TEXT[]
) AS $$
BEGIN
    RETURN QUERY
    WITH problem_performance AS (
        SELECT
            kp.code,
            -- 这里需要实际的答题记录表来计算掌握度
            -- 暂时使用题目难度作为反向指标
            CASE
                WHEN COUNT(pkt.id) = 0 THEN 0.0
                ELSE LEAST(1.0, 1.0 - AVG((p.difficulty_info->>'整体难度')::float) / 5.0)
            END as mastery_level
        FROM knowledge_points kp
        LEFT JOIN problem_knowledge_tags pkt ON kp.id = pkt.knowledge_id
        LEFT JOIN problems p ON pkt.problem_id = p.id
        WHERE kp.code = knowledge_code
        GROUP BY kp.code
    )
    SELECT
        pp.code,
        pp.mastery_level,
        CASE
            WHEN pp.mastery_level >= 0.8 THEN ARRAY['巩固练习', '挑战难题']
            WHEN pp.mastery_level >= 0.6 THEN ARRAY['加强基础练习', '重点复习']
            WHEN pp.mastery_level >= 0.4 THEN ARRAY['返回基础', '系统学习']
            ELSE ARRAY['从基础开始', '寻求辅导']
        END as recommended_actions
    FROM problem_performance pp;
END;
$$ LANGUAGE plpgsql;

-- ===========================================
-- 5. 数据质量检查视图
-- ===========================================

-- 孤立知识点检查（无前置关系和后继关系）
CREATE OR REPLACE VIEW orphaned_knowledge_points AS
SELECT
    kp.code,
    kp.name,
    kp.level,
    kp.importance,
    '无关联关系' as issue_type
FROM knowledge_points kp
LEFT JOIN knowledge_relations kr_out ON kp.id = kr_out.source_knowledge_id
LEFT JOIN knowledge_relations kr_in ON kp.id = kr_in.target_knowledge_id
WHERE kr_out.source_knowledge_id IS NULL
  AND kr_in.target_knowledge_id IS NULL
  AND kp.importance = '核心';

-- 缺失标注检查
CREATE OR REPLACE VIEW missing_tags_check AS
SELECT
    'problems_without_knowledge_tags' as check_type,
    COUNT(*) as count,
    '缺少知识点标注的题目' as description
FROM problems p
WHERE NOT EXISTS (
    SELECT 1 FROM problem_knowledge_tags pkt WHERE pkt.problem_id = p.id
)

UNION ALL

SELECT
    'problems_without_method_tags' as check_type,
    COUNT(*) as count,
    '缺少方法标注的题目' as description
FROM problems p
WHERE NOT EXISTS (
    SELECT 1 FROM problem_method_tags pmt WHERE pmt.problem_id = p.id
)

UNION ALL

SELECT
    'methods_without_knowledge_support' as check_type,
    COUNT(*) as count,
    '缺少支撑知识点的方法' as description
FROM methods m
WHERE NOT EXISTS (
    SELECT 1 FROM method_support_knowledge msk WHERE msk.method_id = m.id
);

-- 编号格式一致性检查
CREATE OR REPLACE VIEW code_format_consistency AS
SELECT
    'knowledge_code_format' as check_type,
    COUNT(*) as invalid_count,
    '知识点编号格式不正确' as description
FROM knowledge_points kp
WHERE kp.code !~ '^K\d+-\d+-\d+-\d+$'

UNION ALL

SELECT
    'method_code_format' as check_type,
    COUNT(*) as invalid_count,
    '方法编号格式不正确' as description
FROM methods m
WHERE m.code !~ '^M\d+-\d+-\d+-\d+$'

UNION ALL

SELECT
    'problem_code_format' as check_type,
    COUNT(*) as invalid_count,
    '题目编号格式不正确' as description
FROM problems p
WHERE p.code !~ '^T\d+-\d+-\d+-[A-Z]\d+$';

-- ===========================================
-- 6. 使用示例
-- ===========================================

/*
-- 基础查询示例
SELECT * FROM knowledge_detail_view WHERE importance = '核心' LIMIT 10;
SELECT * FROM method_detail_view ORDER BY difficulty_level;
SELECT * FROM problem_detail_view WHERE problem_type = '例题';

-- 统计分析示例
SELECT * FROM chapter_knowledge_density ORDER BY knowledge_density DESC;
SELECT * FROM knowledge_examination_frequency WHERE total_references > 5;

-- 关系网络查询
SELECT * FROM knowledge_relation_network WHERE relation_type = '前置知识';

-- 学习路径查询
SELECT * FROM core_learning_path WHERE sequence_level <= 3;

-- 函数调用示例
SELECT * FROM get_knowledge_prerequisite_chain('K1-1-3-01');
SELECT * FROM get_problem_recommendations('K1-1-1-01', 3);
SELECT * FROM search_knowledge_content('函数定义', 'knowledge');
SELECT * FROM evaluate_knowledge_mastery(1, 'K1-1-1-01');

-- 数据质量检查
SELECT * FROM orphaned_knowledge_points;
SELECT * FROM missing_tags_check;
SELECT * FROM code_format_consistency;
*/