T-SQL中DENSE_RANK()处理分组内NULL值的问题与需求

针对你遇到的嵌套层级（3-8层）对象用DENSE_RANK()生成层级ID的问题，核心问题是默认的DENSE_RANK()会将NULL视为有效分组值，且分区逻辑没有严格匹配层级嵌套关系。下面是具体的解决方案：

核心思路 #

1. 对每个层级单独计算排名：仅当当前层级的字段非NULL时，才在上层所有非NULL字段的分组内重置排名。
2. 拼接排名时自动忽略NULL值，只保留有实际层级的编号。

具体实现（以SQL Server为例） #

假设你的数据集字段为level1到level8，对应3-8层嵌套对象：

SELECT 
  -- 用CONCAT_WS自动忽略NULL的排名值，生成层级ID
  CONCAT_WS('.', rank1, rank2, rank3, rank4, rank5, rank6, rank7, rank8) AS hierarchy_id,
  level1, level2, level3, level4, level5, level6, level7, level8
FROM (
  SELECT 
    level1, level2, level3, level4, level5, level6, level7, level8,
    -- 第一层：全局按level1排序生成排名
    DENSE_RANK() OVER (ORDER BY level1) AS rank1,
    -- 第二层：仅当level2非NULL时，在同一个level1分组内生成排名
    CASE WHEN level2 IS NOT NULL THEN DENSE_RANK() OVER (PARTITION BY level1 ORDER BY level2) END AS rank2,
    -- 第三层：仅当level3非NULL时，在同一个level1+level2分组内生成排名
    CASE WHEN level3 IS NOT NULL THEN DENSE_RANK() OVER (PARTITION BY level1, level2 ORDER BY level3) END AS rank3,
    -- 后续层级以此类推，严格基于上层所有非NULL字段分区
    CASE WHEN level4 IS NOT NULL THEN DENSE_RANK() OVER (PARTITION BY level1, level2, level3 ORDER BY level4) END AS rank4,
    CASE WHEN level5 IS NOT NULL THEN DENSE_RANK() OVER (PARTITION BY level1, level2, level3, level4 ORDER BY level5) END AS rank5,
    CASE WHEN level6 IS NOT NULL THEN DENSE_RANK() OVER (PARTITION BY level1, level2, level3, level4, level5 ORDER BY level6) END AS rank6,
    CASE WHEN level7 IS NOT NULL THEN DENSE_RANK() OVER (PARTITION BY level1, level2, level3, level4, level5, level6 ORDER BY level7) END AS rank7,
    CASE WHEN level8 IS NOT NULL THEN DENSE_RANK() OVER (PARTITION BY level1, level2, level3, level4, level5, level6, level7 ORDER BY level8) END AS rank8
  FROM your_dataset
) ranked_data

其他数据库适配（以PostgreSQL为例） #

PostgreSQL可以用STRING_AGG收集非NULL的排名值并拼接：

SELECT 
  STRING_AGG(rank_val, '.' ORDER BY level_order) AS hierarchy_id,
  level1, level2, level3, level4, level5, level6, level7, level8
FROM (
  SELECT 
    level1, level2, level3, level4, level5, level6, level7, level8,
    UNNEST(ARRAY[
      CAST(DENSE_RANK() OVER (ORDER BY level1) AS TEXT),
      CASE WHEN level2 IS NOT NULL THEN CAST(DENSE_RANK() OVER (PARTITION BY level1 ORDER BY level2) AS TEXT) END,
      CASE WHEN level3 IS NOT NULL THEN CAST(DENSE_RANK() OVER (PARTITION BY level1, level2 ORDER BY level3) AS TEXT) END,
      CASE WHEN level4 IS NOT NULL THEN CAST(DENSE_RANK() OVER (PARTITION BY level1, level2, level3 ORDER BY level4) AS TEXT) END,
      CASE WHEN level5 IS NOT NULL THEN CAST(DENSE_RANK() OVER (PARTITION BY level1, level2, level3, level4 ORDER BY level5) AS TEXT) END,
      CASE WHEN level6 IS NOT NULL THEN CAST(DENSE_RANK() OVER (PARTITION BY level1, level2, level3, level4, level5 ORDER BY level6) AS TEXT) END,
      CASE WHEN level7 IS NOT NULL THEN CAST(DENSE_RANK() OVER (PARTITION BY level1, level2, level3, level4, level5, level6 ORDER BY level7) AS TEXT) END,
      CASE WHEN level8 IS NOT NULL THEN CAST(DENSE_RANK() OVER (PARTITION BY level1, level2, level3, level4, level5, level6, level7 ORDER BY level8) AS TEXT) END
    ]) WITH ORDINALITY AS r(rank_val, level_order)
  FROM your_dataset
) ranked_data
WHERE rank_val IS NOT NULL
GROUP BY level1, level2, level3, level4, level5, level6, level7, level8

关键说明 #

• 为什么之前的写法有问题：默认DENSE_RANK()会把NULL当作可排序的分组值，导致即使当前层级没有值，也会生成无效排名；同时分区未严格绑定上层所有父层级字段，导致排名无法在正确的分组内重置。
• 排序逻辑：每个层级的ORDER BY可以根据你的实际需求调整（比如按对象名称、创建时间等），确保排名符合业务上的层级顺序。

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