目录

• 递归计算树形结构

递归计算树

-- DDL
DROP TABLE IF EXISTS "model_structure";
CREATE TABLE model_structure(
id VARCHAR(50),
model_id VARCHAR(50),
node_id VARCHAR(50),
node_name VARCHAR(50),
weight DECIMAL(22,2),
node_level TINYINT,
node_is_leaf TINYINT,
parent_node_id VARCHAR(50),
PRIMARY KEY(id)
);
DROP TABLE IF EXISTS "node_data";
CREATE TABLE node_data(
id VARCHAR(50),
node_id VARCHAR(50),
node_value DECIMAL(22,2),
period VARCHAR(50),
PRIMARY KEY(id)
);
DROP TABLE IF EXISTS "node_normalization";
CREATE TABLE node_normalization(
id VARCHAR(50),
node_id VARCHAR(50),
max DECIMAL(22,2),
min DECIMAL(22,2),
PRIMARY KEY(id)
);
DROP TABLE IF EXISTS "node_score";
CREATE TABLE node_score(
id VARCHAR(50),
node_id VARCHAR(50),
node_score DECIMAL(22,2),
period VARCHAR(50),
PRIMARY KEY(id)
);

-- 测试数据
INSERT INTO model_structure (id, model_id, node_id, node_name, weight, node_level, node_is_leaf, parent_node_id) VALUES
('MS0',  'TEST_M1', 'N0',  'ROOT', 0,    0, 0, NULL),
('MS1',  'TEST_M1', 'N1',  'A',    3,    1, 0, 'N0'),
('MS2',  'TEST_M1', 'N2',  'B',    2,    1, 0, 'N0'),
('MS11', 'TEST_M1', 'N11', 'A1',   1,    2, 1, 'N1'),
('MS12', 'TEST_M1', 'N12', 'A2',   4,    2, 1, 'N1'),
('MS21', 'TEST_M1', 'N21', 'B1',   2,    2, 1, 'N2'),
('MS22', 'TEST_M1', 'N22', 'B2',   3,    2, 1, 'N2');
INSERT INTO node_normalization (id, node_id, max, min) VALUES
('NN11', 'N11', 100, 0),
('NN12', 'N12', 50, 10),
('NN21', 'N21', 20, 0),
('NN22', 'N22', 80, 20);
-- 2025Q1 数据
INSERT INTO node_data (id, node_id, node_value, period) VALUES
('ND11_Q1', 'N11', 80, '2025Q1'),
('ND12_Q1', 'N12', 30, '2025Q1'),
('ND21_Q1', 'N21', 15, '2025Q1'),
('ND22_Q1', 'N22', 50, '2025Q1');

-- 2025Q2 数据
INSERT INTO node_data (id, node_id, node_value, period) VALUES
('ND11_Q2', 'N11', 60, '2025Q2'),
('ND12_Q2', 'N12', 40, '2025Q2'),
('ND21_Q2', 'N21', 10, '2025Q2'),
('ND22_Q2', 'N22', 70, '2025Q2');
COMMIT;


-- 计算过程：先得到节点list：v_nodes，从叶至根计算，遍历周期、节点，非叶子节点获取所有子节点，计算好的先存入表
CREATE OR REPLACE PROCEDURE CALCULATE_MODEL_SCORE(
    p_model_id IN VARCHAR(50)
)
AS
    -- 游标类型
    TYPE t_period_list IS TABLE OF VARCHAR(50);
    TYPE t_node_record IS RECORD (
        v_node_id VARCHAR(50),
        v_weight DECIMAL,
        v_is_leaf TINYINT,
        v_level TINYINT
    );
    TYPE t_node_table IS TABLE OF t_node_record;

    v_periods t_period_list;
    v_nodes t_node_table;
    v_child_weights DECIMAL;
    v_total_weight DECIMAL;
    v_normalized_value DECIMAL;
    v_child_score DECIMAL;
    v_final_score DECIMAL;
    v_min_val DECIMAL;
    v_max_val DECIMAL;
    v_node_value DECIMAL;
BEGIN
    -- 1. 获取该模型涉及的所有 period
    SELECT DISTINCT period BULK COLLECT INTO v_periods
    FROM node_data nd
    JOIN model_structure ms ON nd.node_id = ms.node_id
    WHERE ms.model_id = p_model_id;

    -- 2. 删除已存在的该模型的 node_score 数据（避免重复）
    DELETE FROM node_score ns
    WHERE EXISTS (
        SELECT 1 FROM model_structure ms
        WHERE ms.node_id = ns.node_id AND ms.model_id = p_model_id
    );

    -- 3. 按 node_level 从高到低（叶子 → 根）获取节点（假设 level 越大越深）
    SELECT 
        node_id, weight, node_is_leaf, node_level
    BULK COLLECT INTO v_nodes
    FROM model_structure
    WHERE model_id = p_model_id
    ORDER BY node_level DESC;  -- 先处理叶子，再处理父节点

    -- 4. 遍历每个 period
    FOR i IN 1 .. v_periods.COUNT LOOP
        DECLARE
            v_period VARCHAR(50) := v_periods(i);
        BEGIN
            -- 5. 遍历每个节点（从叶子到根）
            FOR j IN 1 .. v_nodes.COUNT LOOP
                DECLARE
                    v_node_id VARCHAR(50) := v_nodes(j).v_node_id;
                    v_weight DECIMAL := v_nodes(j).v_weight;
                    v_is_leaf TINYINT := v_nodes(j).v_is_leaf;
                    v_score DECIMAL := NULL;
                BEGIN
                    IF v_is_leaf = 1 THEN
                        -- 叶子节点：归一化 (x - min) / (max - min)
                        BEGIN
                            SELECT nd.node_value, nn.min, nn.max
                            INTO v_node_value, v_min_val, v_max_val
                            FROM node_data nd
                            JOIN node_normalization nn ON nd.node_id = nn.node_id
                            WHERE nd.node_id = v_node_id AND nd.period = v_period;

                            IF v_max_val > v_min_val THEN
                                v_score := (v_node_value - v_min_val) / (v_max_val - v_min_val);
                            ELSE
                                v_score := 0; -- 避免除零，或可设为 1
                            END IF;
                        EXCEPTION
                            WHEN NO_DATA_FOUND THEN
                                v_score := 0; -- 数据缺失时默认 0
                        END;
                    ELSE
                        -- 非叶子节点：获取所有子节点的 (权重, 得分)
                        v_total_weight := 0;
                        v_final_score := 0;

                        FOR child_rec IN (
                            SELECT 
                                ms_child.weight AS child_weight,
                                ns_child.node_score AS child_score
                            FROM model_structure ms_child
                            JOIN node_score ns_child ON ms_child.node_id = ns_child.node_id
                            WHERE ms_child.parent_node_id = v_node_id
                              AND ms_child.model_id = p_model_id
                              AND ns_child.period = v_period
                        ) LOOP
                            v_total_weight := v_total_weight + child_rec.child_weight;
                            v_final_score := v_final_score + child_rec.child_weight * child_rec.child_score;
                        END LOOP;

                        IF v_total_weight > 0 THEN
                            v_score := v_final_score / v_total_weight;
                        ELSE
                            v_score := 0;
                        END IF;
                    END IF;

                    -- 插入结果到 node_score
                    INSERT INTO node_score (id, node_id, node_score, period)
                    VALUES (SYS_GUID(), v_node_id, v_score, v_period);

                END;
            END LOOP;
        END;
    END LOOP;

    COMMIT;
END;

-- 更高效的计算
CREATE OR REPLACE PROCEDURE CALCULATE_MODEL_SCORE(
    p_model_id IN VARCHAR(50)
)
AS
    v_sql VARCHAR(4000);
BEGIN
    -- 清除旧结果（仅当前模型）
    DELETE FROM node_score ns
    WHERE EXISTS (
        SELECT 1 FROM model_structure ms
        WHERE ms.node_id = ns.node_id AND ms.model_id = p_model_id
    );

    -- Step 1: 为叶子节点计算归一化得分（批量）
    INSERT INTO node_score (id, node_id, node_score, period)
    SELECT 
        SYS_GUID(),
        nd.node_id,
        CASE 
            WHEN nn.max > nn.min THEN (nd.node_value - nn.min) / (nn.max - nn.min)
            ELSE 0  -- 或 1，根据业务定义
        END AS node_score,
        nd.period
    FROM node_data nd
    JOIN model_structure ms ON nd.node_id = ms.node_id
    JOIN node_normalization nn ON nd.node_id = nn.node_id
    WHERE ms.model_id = p_model_id
      AND ms.node_is_leaf = 1;

    -- Step 2: 递归向上计算非叶子节点得分（按 level 从高到低迭代）
    DECLARE
        v_max_level TINYINT;
        v_current_level TINYINT;
        v_rows_affected INT;
    BEGIN
        -- 获取最大层级（叶子层）
        SELECT MAX(node_level) INTO v_max_level
        FROM model_structure
        WHERE model_id = p_model_id;

        -- 从叶子上一层开始，逐层向上计算（level = max-1, max-2, ..., 0）
        v_current_level := v_max_level - 1;
        WHILE v_current_level >= 0 LOOP
            -- 插入当前层所有非叶子节点的得分
            v_sql := '
            INSERT INTO node_score (id, node_id, node_score, period)
            SELECT 
                SYS_GUID(),
                parent.node_id,
                COALESCE(
                    SUM(child_w.weight * ns.node_score) / NULLIF(SUM(child_w.weight), 0),
                    0
                ) AS node_score,
                ns.period
            FROM model_structure parent
            JOIN model_structure child_w ON child_w.parent_node_id = parent.node_id
            JOIN node_score ns ON child_w.node_id = ns.node_id
            WHERE parent.model_id = ''' || p_model_id || '''
              AND parent.node_level = ' || v_current_level || '
              AND parent.node_is_leaf = 0
            GROUP BY parent.node_id, ns.period
            ';
            EXECUTE IMMEDIATE v_sql;

            v_current_level := v_current_level - 1;
        END LOOP;
    END;

    COMMIT;
END;

• 调用存储过程

<!-- ModelScoreMapper.xml -->
<mapper namespace="com.yourpackage.mapper.ModelScoreMapper">

    <!-- 调用存储过程：计算模型得分 -->
    <update id="calculateModelScore" statementType="CALLABLE">
        { CALL CALCULATE_MODEL_SCORE(#{modelId, jdbcType=VARCHAR}) }
    </update>

</mapper>

在应用层实现

<!-- 读取模型结构 -->
<select id="selectModelStructure" resultType="ModelStructure">
    SELECT * FROM model_structure WHERE model_id = #{modelId} ORDER BY node_level DESC
</select>

<!-- 读取归一化参数 -->
<select id="selectNormalizationMap" resultType="map">
    SELECT node_id, max, min FROM node_normalization
</select>

<!-- 读取原始数据（按 period 分组） -->
<select id="selectNodeDataByPeriod" resultType="NodeData">
    SELECT node_id, node_value, period FROM node_data
    WHERE node_id IN
    <foreach collection="nodeIds" item="id" open="(" separator="," close=")">
        #{id}
    </foreach>
</select>

<!-- 批量插入得分 -->
<insert id="batchInsertNodeScores">
    INSERT INTO node_score (id, node_id, node_score, period) VALUES
    <foreach collection="scores" item="s" separator=",">
        (SYS_GUID(), #{s.nodeId}, #{s.score}, #{s.period})
    </foreach>
</insert>

@Transactional
public void calculateModelScore(String modelId) {
    // 1. 清空旧结果
    nodeScoreMapper.deleteByModelId(modelId);

    // 2. 加载模型结构（按 level 降序）
    List<ModelStructure> nodes = modelMapper.selectModelStructure(modelId);
    Map<String, ModelStructure> nodeMap = nodes.stream()
        .collect(Collectors.toMap(n -> n.getNodeId(), n -> n));

    // 3. 加载归一化参数
    Map<String, Normalization> normMap = normalizationMapper.selectAll()
        .stream().collect(Collectors.toMap(n -> n.getNodeId(), n -> n));

    // 4. 获取所有叶子节点 ID
    Set<String> leafIds = nodes.stream()
        .filter(n -> n.getNodeIsLeaf() == 1)
        .map(ModelStructure::getNodeId)
        .collect(Collectors.toSet());

    // 5. 加载所有原始数据（按 period 分组）
    List<NodeData> allData = nodeDataMapper.selectByNodeIds(leafIds);
    Map<String, List<NodeData>> dataByPeriod = allData.stream()
        .collect(Collectors.groupingBy(NodeData::getPeriod));

    // 6. 按 period 计算
    for (String period : dataByPeriod.keySet()) {
        Map<String, BigDecimal> scoreMap = new HashMap<>();

        // 先计算叶子节点
        for (NodeData data : dataByPeriod.get(period)) {
            Normalization norm = normMap.get(data.getNodeId());
            BigDecimal score = normalize(data.getValue(), norm.getMin(), norm.getMax());
            scoreMap.put(data.getNodeId(), score);
        }

        // 再按 level 从高到低计算非叶子节点
        for (ModelStructure node : nodes) {
            if (node.getNodeIsLeaf() == 1) continue;

            List<ModelStructure> children = nodes.stream()
                .filter(n -> node.getNodeId().equals(n.getParentNodeId()))
                .collect(Collectors.toList());

            BigDecimal totalWeight = BigDecimal.ZERO;
            BigDecimal weightedSum = BigDecimal.ZERO;

            for (ModelStructure child : children) {
                BigDecimal childScore = scoreMap.get(child.getNodeId());
                if (childScore == null) childScore = BigDecimal.ZERO;
                BigDecimal w = BigDecimal.valueOf(child.getWeight());
                totalWeight = totalWeight.add(w);
                weightedSum = weightedSum.add(w.multiply(childScore));
            }

            BigDecimal finalScore = totalWeight.compareTo(BigDecimal.ZERO) > 0
                ? weightedSum.divide(totalWeight, 6, RoundingMode.HALF_UP)
                : BigDecimal.ZERO;

            scoreMap.put(node.getNodeId(), finalScore);
        }

        // 批量插入当前 period 的所有得分
        List<NodeScore> scores = scoreMap.entrySet().stream()
            .map(e -> new NodeScore(e.getKey(), e.getValue(), period))
            .collect(Collectors.toList());
        nodeScoreMapper.batchInsertNodeScores(scores);
    }
}

回到目录