问题的答案。

问题的历史。

层次数据模型传统上使用邻接列表或嵌套集合来表示树结构。虽然邻接列表最小化存储并简化插入，但分析报告通常需要“物化路径”（例如，“根/类别/子类别”），以便使用前缀模式进行高效过滤。在 SQL:1999 之前，扁平化这些层次结构需要过程游标或应用程序侧递归。ANSI SQL 标准中递归公共表表达式 (CTE) 的引入使得声明式遍历成为可能，但构建确定性、顺序路径与深度限制相关的复杂性涉及到递归终止和排序稳定性。

问题。

您必须将一个递归邻接列表转换为去规范化格式，其中每一行包含完整的祖先血统作为分隔字符串（例如，“/A/B/C”）。解决方案必须强制执行三个约束：（1）每个级别的兄弟节点必须在路径中按字典顺序出现，（2）遍历不得超过指定的最大深度，以防止在格式错误的数据上导致无休止查询，以及（3）实现必须使用严格的 ANSI SQL 语法，而不使用专有的层次扩展。

解决方案。

该解决方案采用两阶段 CTE 方法。首先，一个非递归 CTE 使用窗口函数计算每个节点在其兄弟节点中的顺序位置。由于 ANSI SQL 禁止在 CTE 的递归成员中使用窗口函数以确保单调终止，因此此预计算是必要的。其次，递归 CTE 遍历树，连接节点名称和预计算的排序键，同时在 WHERE 子句中应用深度限制和可选的循环检测。

WITH RECURSIVE 
OrderedNodes AS (
  -- 阶段 1：给兄弟节点分配确定顺序
  SELECT 
    node_id, 
    parent_id, 
    node_name,
    ROW_NUMBER() OVER (PARTITION BY parent_id ORDER BY node_name, node_id) AS sibling_order
  FROM taxonomy
),
PathBuilder AS (
  -- 锚定成员：初始化根节点
  SELECT 
    node_id,
    node_name,
    1 AS depth,
    CAST(node_name AS VARCHAR(1000)) AS materialized_path,
    CAST(sibling_order AS VARCHAR(100)) AS sort_key
  FROM OrderedNodes
  WHERE parent_id IS NULL
  
  UNION ALL
  
  -- 递归成员：附加子节点
  SELECT 
    c.node_id,
    c.node_name,
    p.depth + 1,
    p.materialized_path || '/' || c.node_name,
    p.sort_key || '.' || CAST(c.sibling_order AS VARCHAR(100))
  FROM OrderedNodes c
  INNER JOIN PathBuilder p ON c.parent_id = p.node_id
  WHERE p.depth < 5  -- 严格的深度约束
    AND p.materialized_path NOT LIKE '%/' || c.node_name || '%'  -- 循环保护
)
SELECT 
  node_id, 
  materialized_path,
  depth
FROM PathBuilder
ORDER BY sort_key;

生活中的情况

问题描述。

一个全球电子商务平台在 PostgreSQL 数据库（符合 ANSI SQL 的模式）中维护着超过 100,000 个类别的产品分类。市场团队需要每日将 CSV 导出到一个外部广告平台，该平台要求完全合格的类别路径（例如，“电子产品/计算机/笔记本电脑”），并要求每一层严格按字母顺序排列，以确保目标受众的一致性。现有解决方案使用 Python 脚本执行 N+1 查询，导致 20 分钟的运行时间和频繁的超时。

考虑的不同解决方案。

解决方案 A：应用程序侧缓存与定期重建。 团队考虑通过后台作业每 6 小时将路径物化到 Redis 缓存中。优点包括简单的 Python 实现和在高峰时段减少对数据库的负担。然而，缺点是显著的数据陈旧（最多 6 小时），当类别被重新归类时，缓存失效的复杂性，以及为整个树消耗过多的内存。

解决方案 B：使用递归 CTE 的数据库视图。 这种方法创建一个视图，按需使用 ANSI SQL 递归 CTE 模式计算路径。优点包括确保数据的新鲜度（实时结果）、单一的事实来源，以及利用数据库的查询优化器进行连接。缺点包括对数据库服务器的 CPU 强度和如果数据包含循环引用则面临无限递归的风险（例如，类别意外链接到其后代）。

解决方案 C：触发器维护的物化列。 这将向表中添加一个 materialized_path 列，并在插入、更新或删除时通过触发器更新。优点包括极快的读取性能（简单的索引扫描）。缺点包括显著的写入开销、处理子树移动或重命名的复杂触发器逻辑，以及当兄弟名称更改时保持字典顺序约束的难度。

选择的解决方案和结果。

团队选择了解决方案 B（递归 CTE 视图），因为读取重的工作负载（100:1 读写比）受益于按需计算，而不需触发器的维护负担。为减少循环风险，他们在 WHERE 子句中实现了 LIKE 模式检查，并根据业务规则添加了 5 级的深度限制。他们还在 (parent_id, node_name) 上创建了组合索引，以优化锚定 CTE 中窗口函数的排序。结果，将导出时间从 20 分钟减少到 8 秒，同时在推出阶段检测和隔离了 3 个遗留数据中的循环引用。

候选人通常忽视的内容

为什么聚合或窗口函数不能出现在 CTE 的递归成员中，这如何影响兄弟节点的排序？

ANSI SQL 标准限制递归成员中包含聚合函数（如 SUM）或窗口函数（如 ROW_NUMBER），以确保递归查询是单调的，并在达到固定点时得到保证。窗口函数需要物化和分区整个工作集，这可能会违反递归终止所需的流式语义，并可能引入非确定性。因此，不能在递归过程中动态计算兄弟节点排序。正确的方法是在单独的非递归 CTE 中预先计算顺序位置（如解决方案所示），然后在递归连接中引用该计算列。候选人经常尝试将 ROW_NUMBER() 直接放入递归 SELECT 列表中，导致语法错误或不可预测的执行计划。

您如何在没有专有循环检测语法（如 PostgreSQL 的 CYCLE 子句）的情况下处理层次结构中的循环引用？

纯 ANSI SQL 不提供内置的 CYCLE 检测机制（尽管 SQL:2023 引入了 CYCLE 和 SEARCH 子句，但尚未广泛实施）。为了防止无限递归，您必须手动跟踪已访问的节点。标准的可移植技术涉及累积已访问节点 ID 的分隔字符串（或如果支持则为数组），并在继续之前检查当前 node_id 是否已经存在于该累积器中。使用谓词 WHERE p.materialized_path NOT LIKE '%/' || c.node_id || '%' 可以有效地修剪循环，尽管此方法假设合理的深度限制，以防止字符串长度溢出。或者，可以为较大的数据集使用位字符串或哈希路径。

什么确保当两个兄弟节点共享相同名称时的确定性排序，为什么完全排序对物化路径至关重要？

确定性依赖于在兄弟节点之间建立完全排序。如果窗口函数仅使用 ORDER BY node_name 并且两个兄弟节点共享相同名称，则数据库可自由以任何顺序返回它们（由实现定义），导致不同查询执行或数据库版本之间的路径字符串出现非确定性。这种非确定性破坏了查询结果缓存，复杂化了测试，并可能导致下游系统中的数据“波动”。解决方案是在 ORDER BY 子句中添加一个唯一的决胜者，通常是主键 node_id：ORDER BY node_name, node_id。这确保每个兄弟节点都有一个唯一的顺序位置，确保物化路径和辅助 sort_key 是可复现和稳定的。

假设您必须将层次祖先公开为可排序的分隔字符串，用于报告层；您将如何构建这些血统路径，以确保兄弟节点按字典顺序排列并限制深度，使用严格的 ANSI SQL 递归 CTE？

问题的答案。

生活中的情况

候选人通常忽视的内容