如何用SQL扁平化带索引的父子层次表以构建star schema的fact table

原表结构 #

规则说明：非叶子节点（有子节点）的DataValue存储索引值，叶子节点（无后代）的DataValue存储实际测量数据。

目标扁平化结构 #

解决方案：递归CTE实现扁平化 #

以下是适配该场景的SQL代码，通过递归CTE跟踪每一层级的信息，最终关联叶子节点提取实际数据：

WITH RECURSIVE hierarchy AS (
    -- 锚点成员：从根节点初始化层级信息
    SELECT
        Id AS NodeId,
        Description AS Lvl1Description,
        DataValue AS Lvl1Index,
        CAST(NULL AS VARCHAR) AS Lvl2Description,
        CAST(NULL AS DECIMAL) AS Lvl2Index,
        CAST(NULL AS VARCHAR) AS Lvl3Description,
        CAST(NULL AS DECIMAL) AS Lvl3Index,
        Description AS CurrentDesc,
        DataValue AS CurrentIndex,
        1 AS Level
    FROM your_table_name
    WHERE ParentId = 0
    UNION ALL
    -- 递归成员：逐层遍历非叶子节点，填充对应层级字段
    SELECT
        child.Id AS NodeId,
        parent.Lvl1Description,
        parent.Lvl1Index,
        CASE WHEN parent.Level = 1 THEN child.Description ELSE parent.Lvl2Description END AS Lvl2Description,
        CASE WHEN parent.Level = 1 THEN child.DataValue ELSE parent.Lvl2Index END AS Lvl2Index,
        CASE WHEN parent.Level = 2 THEN child.Description ELSE parent.Lvl3Description END AS Lvl3Description,
        CASE WHEN parent.Level = 2 THEN child.DataValue ELSE parent.Lvl3Index END AS Lvl3Index,
        child.Description AS CurrentDesc,
        child.DataValue AS CurrentIndex,
        parent.Level + 1 AS Level
    FROM your_table_name child
    JOIN hierarchy parent ON child.ParentId = parent.NodeId
    WHERE EXISTS (
        SELECT 1 FROM your_table_name t WHERE t.ParentId = child.Id
    )
),
-- 关联层级信息与叶子节点数据
leaf_data AS (
    SELECT
        h.Lvl1Description,
        h.Lvl1Index,
        h.Lvl2Description,
        h.Lvl2Index,
        h.Lvl3Description,
        h.Lvl3Index,
        t.Description AS LeafDescription,
        t.DataValue
    FROM hierarchy h
    JOIN your_table_name t ON t.ParentId = h.NodeId
    WHERE NOT EXISTS (
        SELECT 1 FROM your_table_name t2 WHERE t2.ParentId = t.Id
    )
)
-- 输出最终扁平化结果
SELECT * FROM leaf_data
WHERE Lvl1Description IS NOT NULL
ORDER BY Lvl1Index, Lvl2Index, Lvl3Index;

代码逻辑说明 #

1. 锚点成员：定位根节点，初始化第一层的层级字段，记录当前节点的核心信息和层级数。
2. 递归成员：逐层关联子节点，根据当前层级数将子节点的描述、索引填充到对应层级的字段中；仅递归有子节点的非叶子节点，避免无效遍历。
3. 叶子节点提取：将递归得到的完整层级路径与叶子节点关联，提取实际测量数据，得到目标结构。

适配星型模型的额外建议 #

• 维度表设计：将扁平化后的层级字段（Lvl1~Lvl3的描述和索引）整合为维度表，为每个层级分配代理键，简化事实表的关联逻辑。
• 事实表构建：以DataValue作为核心度量字段，关联维度表的代理键，形成标准星型模型结构。
• 扩展性优化：若未来层级可能增加，可修改递归CTE中的层级字段定义，或在递归过程中存储完整路径字符串，后续按需拆分；也可考虑使用动态SQL适配动态层级。

内容的提问来源于stack exchange，提问作者M.Kruisselbrink