PostgreSQL中如何过滤同ID下含空值的冗余分类行？

你需要剔除那些存在更细分层级数据的冗余空值行，只保留最底层的完整层级记录——也就是当同一分类下存在子分类数据时，去掉父分类的空值行；当同一子分类下存在子子分类数据时，去掉子分类的空值行。这种过滤在PostgreSQL中完全可行，以下是两种实现方案：

方法一：使用EXISTS子查询过滤 #

SELECT t.id, t.category, t.sub_category, t.sub_sub_category
FROM your_table t
WHERE
  -- 若同一id+category下存在非空sub_category，则排除当前sub_category为空的行
  (NOT EXISTS (
    SELECT 1
    FROM your_table
    WHERE id = t.id
      AND category = t.category
      AND sub_category IS NOT NULL
  ) OR t.sub_category IS NOT NULL)
  AND
  -- 若同一id+category+sub_category下存在非空sub_sub_category，则排除当前sub_sub_category为空的行
  (NOT EXISTS (
    SELECT 1
    FROM your_table
    WHERE id = t.id
      AND category = t.category
      AND sub_category = t.sub_category
      AND sub_sub_category IS NOT NULL
  ) OR t.sub_sub_category IS NOT NULL);

逻辑说明 #

这个查询通过两个NOT EXISTS条件分别判断：

1. 如果当前行的sub_category为空，但同一id和category下存在非空的sub_category记录，就剔除当前行；
2. 如果当前行的sub_sub_category为空，但同一id、category和sub_category下存在非空的sub_sub_category记录，就剔除当前行。

方法二：使用窗口函数标记过滤 #

如果偏好窗口函数的写法，可以先标记每个分组是否存在子层级数据，再过滤：

WITH category_groups AS (
    SELECT 
        *,
        -- 标记同一id+category下是否有非空的sub_category
        MAX(CASE WHEN sub_category IS NOT NULL THEN 1 ELSE 0 END) OVER (PARTITION BY id, category) AS has_sub_category,
        -- 标记同一id+category+sub_category下是否有非空的sub_sub_category
        MAX(CASE WHEN sub_sub_category IS NOT NULL THEN 1 ELSE 0 END) OVER (PARTITION BY id, category, sub_category) AS has_subsub_category
    FROM your_table
)
SELECT id, category, sub_category, sub_sub_category
FROM category_groups
WHERE
    -- 要么分组里没有子分类，要么当前行有子分类
    (has_sub_category = 0 OR sub_category IS NOT NULL)
    AND
    -- 要么分组里没有子子分类，要么当前行有子子分类
    (has_subsub_category = 0 OR sub_sub_category IS NOT NULL);

逻辑说明 #

先通过窗口函数MAX() OVER (PARTITION BY ...)计算每个分组内是否存在非空的子层级数据，再根据标记值过滤掉冗余的空值行。

两种方法都能精准得到你需要的结果。

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