要求：计算叶子结点和其上级的距离

表结构：

"Goods_Category"

(

　　"Id" bigint,

　　"ParentId" bigint

);

1.整理叶子结点及其上级的关系，按顺序存储成数组：[叶子结点1， 叶子结点1-上级Id1， 上级Id1的上级Id2...]

WITH T1 AS(WITH RECURSIVE T AS(

    --没有下级的就是叶子节点

    SELECT ARRAY[GC."ParentId"] || GC."Id" AS "ParentId"

    FROM "Goods_Category" AS GC

    JOIN "Goods_Category" AS GC1 ON

        GC."ParentId" = GC1."Id"

    WHERE NOT EXISTS (    

        SELECT 1

        FROM "Goods_Category" AS GC1

        WHERE GC."Id" = GC1."ParentId"

    )

    UNION ALL (

        SELECT GC."ParentId" || T."ParentId" AS "ParentId"

         FROM T

         JOIN "Goods_Category" AS GC ON

            T."ParentId"[1] = GC."Id"

        JOIN "Goods_Category" AS GC1 ON

            GC."ParentId" = GC1."Id"

    )

)

SELECT T1."Id",

    T."ParentId"

FROM T

JOIN(

    WITH T1 AS(

        SELECT T."ParentId"[ARRAY_LENGTH(T."ParentId", 1)] AS "Id",

            ARRAY_LENGTH(T."ParentId", 1) AS LENGTH,

        T."ParentId"

    FROM T

    )

    SELECT T1."Id",

        MAX(T1.LENGTH) AS LENGTH

    FROM T1

    GROUP BY T1."Id"

) AS T1 ON

    T."ParentId"[ARRAY_LENGTH(T."ParentId", 1)] = T1."Id"

    AND ARRAY_LENGTH(T."ParentId", 1) = T1.LENGTH

ORDER BY T1."Id"

)

SELECT *

FROM T1;

 

2.循环T1记录

FOR R IN (SELECT * FROM T1) LOOP

　　ARRAY_LENGTH = (ARRAY_LENGTH(R."ParentId", 1) - 1);

　　FOR B_INDEX IN 1..ARRAY_LENGTH LOOP

　　　　FOR E_INDEX IN (B_INDEX+1)..(ARRAY_LENGTH+1) LOOP

　　　　　　IF NOT EXISTS(SELECT 1 FROM bi_dim_category_closure WHERE parent_id = R."ParentId"[B_INDEX] AND child_id = R."ParentId"[E_INDEX]) THEN

　　　　　　　　INSERT INTO bi_dim_category_closure

　　　　　　　　(

　　　　　　　　　　parent_id,

　　　　　　　　　　child_id,

　　　　　　　　　　distance

　　　　　　　　)

　　　　　　　　VALUES(

　　　　　　　　　　R."ParentId"[B_INDEX],

　　　　　　　　　　R."ParentId"[E_INDEX],

　　　　　　　　　　(E_INDEX-B_INDEX)

　　　　　　　　);

　　　　　　END IF;

　　　　END LOOP;

　　END LOOP;

END LOOP;