祖先樹統計

溫柔の風發表於2024-08-15

DDL

CREATE TABLE organization_ancestor_id_tree (
    id               BIGINT       NOT NULL COMMENT '對應：smarthse_supervise.organization.id' PRIMARY KEY,
    ancestor_id_tree VARCHAR(100) NULL COMMENT 'id的祖先id樹（最近祖先在最左，最遠祖先在最右）'
) COMMENT '存放每一個：smarthse_supervise.organization.id，的祖先id樹，每日03:00更新。';

PROCEDURE

DELIMITER $$
CREATE PROCEDURE generate_all_organization_ancestor_id_tree()
BEGIN
    /* 定義變數用於遍歷所有監管ID */
    DECLARE done BIGINT DEFAULT FALSE;
    DECLARE sid BIGINT;
    DECLARE current_id BIGINT;
    DECLARE prev_id BIGINT;
    DECLARE cur CURSOR FOR SELECT id FROM organization;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    /* 重複執行此儲存過程等價於：以最新的資料重新追溯監管id的祖先id樹。 */
    TRUNCATE organization_ancestor_id_tree;
    /* 建立一個臨時表來儲存祖先ID */
    CREATE TEMPORARY TABLE temp_organization_ancestor_id_list (
        id BIGINT
    );
    /* 開啟遊標 */
    OPEN cur;
    /* 遍歷遊標 */
    read_loop:
    LOOP
        FETCH cur INTO sid;
        IF done THEN LEAVE read_loop; END IF;
        SET current_id = sid;
        SET prev_id = -1;
        /* 迴圈檢索，獲取每個ID的所有祖先 */
        WHILE current_id IS NOT NULL AND current_id != prev_id
            DO
                INSERT INTO temp_organization_ancestor_id_list (id) VALUES (current_id);
                SET prev_id = current_id;
                SELECT pid
                INTO current_id
                FROM organization
                WHERE id = current_id AND pid IS NOT NULL AND pid != 999999999999;
            END WHILE;
        /* 輸出所有祖先ID，以逗號分隔 */
        INSERT INTO organization_ancestor_id_tree
        SELECT sid, GROUP_CONCAT(id ORDER BY id DESC SEPARATOR ',') AS ancestor
        FROM temp_organization_ancestor_id_list;
        TRUNCATE temp_organization_ancestor_id_list;
        /* 重置done值 */
        SET done = FALSE;
    END LOOP;
    /* 關閉遊標 */
    CLOSE cur;
    /* 刪除臨時表 */
    DROP TEMPORARY TABLE IF EXISTS temp_organization_ancestor_id_list;
END$$
DELIMITER ;

CALL

CALL generate_all_organization_ancestor_id_tree();

相關文章