在Databricks中实现5级治疗层级体系的层级查询逻辑

业务背景 #

现有层级体系共5级，从高到低排序如下：

1. MasterBrand
2. Brand
3. DoseForm
4. DoseStrength
5. 最低级节点：Drug、MSA、Device
   常规关联路径为Drug→DoseStrength→DoseForm→Brand→MasterBrand，原始表已存在TherapyId、TherapyType两个字段，需按规则计算剩余字段。

核心规则实现 #

1. TherapyParentid计算规则 #

• MasterBrand无上级节点，TherapyParentid赋值为null
• Brand的TherapyParentid为对应所属MasterBrand的TherapyId
• DoseForm的TherapyParentid为对应所属Brand的TherapyId
• DoseStrength的TherapyParentid为对应所属DoseForm的TherapyId
• Drug、MSA的TherapyParentid为对应所属DoseStrength的TherapyId
• 特殊场景：Device属于6-8级独立体系，跳过DoseStrength、DoseForm两个层级，TherapyParentid直接赋值为对应所属Brand的TherapyId

2. 层级列赋值规则 #

层级序号对应规则：MasterBrand=1、Brand=2、DoseForm=3、DoseStrength=4、最低级节点=5。仅当前节点的父级对应层级列赋值为父级的层级序号，其余层级列统一赋值为null，具体规则如下：

• TherapyType为MasterBrand：所有层级列均为null
• TherapyType为Brand：仅MasterBrand列赋值为1，其余列均为null
• TherapyType为DoseForm：仅Brand列赋值为2，其余列均为null
• TherapyType为DoseStrength：仅DoseForm列赋值为3，其余列均为null
• TherapyType为Drug、MSA：仅DoseStrength列赋值为4，其余列均为null
• TherapyType为Device：仅Brand列赋值为2，其余列均为null

代码实现示例（Databricks Spark SQL） #

假设已提前清洗好各级节点的关联映射表therapy_hierarchy_mapping，表中包含每个节点对应所有上级节点的TherapyId字段（如master_brand_id、brand_id等）：

WITH base_hierarchy AS (
  SELECT
    TherapyId,
    TherapyType,
    -- 计算父级ID
    CASE TherapyType
      WHEN 'MasterBrand' THEN null
      WHEN 'Brand' THEN master_brand_id
      WHEN 'DoseForm' THEN brand_id
      WHEN 'DoseStrength' THEN dose_form_id
      WHEN 'Drug' THEN dose_strength_id
      WHEN 'MSA' THEN dose_strength_id
      WHEN 'Device' THEN brand_id
    END AS TherapyParentid,
    -- 层级列赋值
    CASE WHEN TherapyType = 'Brand' THEN 1 ELSE null END AS MasterBrand,
    CASE WHEN TherapyType IN ('DoseForm', 'Device') THEN 2 ELSE null END AS Brand,
    CASE WHEN TherapyType = 'DoseStrength' THEN 3 ELSE null END AS DoseForm,
    CASE WHEN TherapyType IN ('Drug', 'MSA') THEN 4 ELSE null END AS DoseStrength
  FROM therapy_hierarchy_mapping
)
-- 若需要获取全链路层级路径，可使用递归CTE实现
RECURSIVE full_hierarchy AS (
  -- 锚点：最高层级MasterBrand
  SELECT
    TherapyId,
    TherapyType,
    TherapyParentid,
    MasterBrand,
    Brand,
    DoseForm,
    DoseStrength,
    ARRAY(TherapyId) AS full_path,
    1 AS level
  FROM base_hierarchy
  WHERE TherapyType = 'MasterBrand'
  UNION ALL
  -- 递归关联子节点
  SELECT
    c.TherapyId,
    c.TherapyType,
    c.TherapyParentid,
    c.MasterBrand,
    c.Brand,
    c.DoseForm,
    c.DoseStrength,
    CONCAT(p.full_path, ARRAY(c.TherapyId)) AS full_path,
    p.level + 1 AS level
  FROM base_hierarchy c
  JOIN full_hierarchy p ON c.TherapyParentid = p.TherapyId
)
SELECT * FROM full_hierarchy;

注意事项 #

• 需提前校验therapy_hierarchy_mapping表中各级关联ID的准确性，避免出现断链或错链
• Device类节点需单独做关联校验，确保直接关联到所属Brand的TherapyId，不要误关联到DoseForm层级

内容的提问来源于stack exchange，提问作者BIKASH