您可以找到层次结构的叶节点，并使用SYS_CONNECT_BY_PATH找到所采用的路径，然后将其拆分为不同的级别：

SELECT lvl_1,
       COALESCE(lvl_2, lvl_1) AS lvl_2,
       COALESCE(lvl_3, lvl_2, lvl_1) AS lvl_3,
       COALESCE(lvl_4, lvl_3, lvl_2, lvl_1) AS lvl_4
FROM   (
  SELECT REGEXP_SUBSTR(
           SYS_CONNECT_BY_PATH(product_id, ','), ',([^,]+)', 1, 1, NULL, 1
         ) AS lvl_1,
         REGEXP_SUBSTR(
           SYS_CONNECT_BY_PATH(product_id, ','), ',([^,]+)', 1, 2, NULL, 1
         ) AS lvl_2,
         REGEXP_SUBSTR(
           SYS_CONNECT_BY_PATH(product_id, ','), ',([^,]+)', 1, 3, NULL, 1
         ) AS lvl_3,
         REGEXP_SUBSTR(
           SYS_CONNECT_BY_PATH(product_id, ','), ',([^,]+)', 1, 4, NULL, 1
         ) AS lvl_4
  FROM   ztest_product
  WHERE  CONNECT_BY_ISLEAF = 1
  START WITH parent_id IS NULL
  CONNECT BY PRIOR product_id = parent_id
)

对于您的示例数据，它将输出：
| 第1级|第二级|三级|四级|
| - -|- -|- -|- -|
| 第0页|第一页|第1页第1页|第1页第1页|
| 第0页|第一页|第1页第1页|第1_1_2页|
| 第0页|第一页|第1_1_3页|第1_1_3页|
| 第0页|第一页|第1页第2页|第1页第2页|
| 第0页|第二页第一页|第二页第一页|第二页第一页|
| 第0页|第三层|第3_1_1页|第3_1_1页|
| 第0页|第三层|P_3_2页|第三页第一页第二页|
fiddle

下面是一个准备查询，它生成缺少的节点，而不使用regexp_substr，如果数据集很大，regexp_substr可能会导致性能问题：

WITH cnodes AS (
    SELECT LEVEL AS lvl, p.*, connect_by_isleaf AS is_leaf FROM ztest_product p
    START WITH product_id IN (
        SELECT product_id FROM ztest_product WHERE parent_id IS NULL
    )
    CONNECT BY PRIOR product_id = parent_id 
),
inodes AS (
    SELECT cn.* FROM cnodes cn
    WHERE NOT EXISTS(
        SELECT 1 FROM cnodes c1 WHERE cn.product_id = c1.parent_id AND cn.lvl = c1.lvl + 1 AND cn.lvl < 4
    )
    AND is_leaf = 1 AND lvl < 4
)
SELECT * FROM (
    SELECT lvl, product_id, parent_id FROM (
        SELECT lvl+LEVEL AS lvl, product_id, product_id AS parent_id FROM inodes
        CONNECT BY LEVEL < 5 - lvl AND PRIOR product_id = product_id AND PRIOR sys_guid() IS NOT NULL
        UNION ALL 
        SELECT lvl, product_id, parent_id FROM cnodes
    )
)
ORDER BY lvl, parent_id, product_id
;

结果是包含缺失节点的完整树，其中添加的节点是其自身的父节点：

完整查询给出了预期的表：

WITH cnodes AS (
    SELECT LEVEL AS lvl, p.*, connect_by_isleaf AS is_leaf, SYS_CONNECT_BY_PATH(product_id,'/') AS p 
    FROM ztest_product p
    START WITH product_id IN (
        SELECT product_id FROM ztest_product WHERE parent_id IS NULL
    )
    CONNECT BY PRIOR product_id = parent_id 
),
inodes AS (
    SELECT cn.* FROM cnodes cn
    WHERE NOT EXISTS(
        SELECT 1 FROM cnodes c1 WHERE cn.product_id = c1.parent_id AND cn.lvl = c1.lvl + 1 AND cn.lvl < 4
    )
    AND is_leaf = 1 AND lvl < 4
),
fnodes AS (
    SELECT lvl, product_id, parent_id, p
    FROM (
        SELECT lvl+LEVEL AS lvl, product_id, product_id AS parent_id, p || SYS_CONNECT_BY_PATH(product_id,'/') AS p
        FROM inodes
        CONNECT BY LEVEL < 5 - lvl AND PRIOR product_id = product_id AND PRIOR sys_guid() IS NOT NULL
        UNION ALL 
        SELECT lvl, product_id, parent_id, p FROM cnodes
    )
)
SELECT * FROM (
    SELECT f.r AS row_idx, l AS col_idx, pitem FROM (
        SELECT ROW_NUMBER() OVER(ORDER BY p) AS r, f.* 
        FROM fnodes f
        WHERE lvl = 4
    ) f
    CROSS APPLY (
        SELECT LEVEL AS l, regexp_substr( f.p , '[^/]+', 1, LEVEL) AS pitem FROM DUAL
        CONNECT BY regexp_substr( f.p , '[^/]+', 1, LEVEL) IS NOT NULL
    ) t
)
PIVOT (
    MAX(pitem) AS product_id FOR col_idx IN (1,2,3,4)
)
;