PostgreSQL 行式属性表多条件查询无结果及重复统计优化

本质问题来自行存储属性表的设计逻辑：

• 行存储结构下，attribute_table的单条记录仅能对应1个属性ID与取值，不可能出现同一条记录同时满足attribute_id =1000 AND attribute_id=1001 AND attribute_id=1002的情况
• 原查询将三个互斥的属性匹配条件全部写在同一个WHERE筛选层级，相当于要求单条属性行同时属于三个不同的属性类别，逻辑上永远无法命中。这也是为什么单个属性条件可以正常返回结果，叠加三个属性后查询完全无返回值的根本原因

方案1：多分支独立关联（适配小数据量场景） #

通过为每个属性条件创建独立的内连接分支，拆分互斥的属性匹配逻辑，同时保留去重计数逻辑避免关联带来的重复统计问题：

SELECT 
  sa.country_id,
  COUNT(DISTINCT sa.personal_id) AS qualified_personal_count
FROM study_table sa
-- 匹配属性1000：性别为女
INNER JOIN attribute_table a1 
  ON sa.country_id = a1.country_id 
  AND sa.personal_id = a1.personal_id
  AND a1.attribute_id = 1000 
  AND a1.attribute_number = 2
-- 匹配属性1001：年龄组为55-64岁
INNER JOIN attribute_table a2 
  ON sa.country_id = a2.country_id 
  AND sa.personal_id = a2.personal_id
  AND a2.attribute_id = 1001 
  AND a2.attribute_number = 5
-- 匹配属性1002：家庭规模为单人户
INNER JOIN attribute_table a3 
  ON sa.country_id = a3.country_id 
  AND sa.personal_id = a3.personal_id
  AND a3.attribute_id = 1002 
  AND a3.attribute_number = 1
WHERE 
  to_char(sa.mailing_date, 'yyyy-MM') = '2021-01'
  AND sa.study_type = 'Wave'
  AND sa.status NOT IN ('NEW', 'EXCLUDED', 'ERROR')
GROUP BY sa.country_id

该写法的特性：

• 每个属性匹配逻辑独立拆分，不存在单条属性行同时满足三个条件的逻辑矛盾，从根源解决多属性查询无返回的问题
• 内连接的机制天然保证了只有同时命中三个属性条件的personal_id才会被纳入统计范围
• COUNT(DISTINCT personal_id)的去重逻辑，可以完全抵消多表关联可能带来的行数膨胀问题，避免重复计数

方案2：属性预聚合（适配大数据量场景） #

如果attribute_table数据量较大，多次关联的查询开销较高，可以通过CTE先对属性表做聚合过滤，减少关联阶段的计算量：

WITH qualified_person AS (
  SELECT 
    country_id,
    personal_id
  FROM attribute_table
  -- 提前过滤仅需匹配的三个属性，缩小扫描范围
  WHERE attribute_id IN (1000, 1001, 1002)
  GROUP BY country_id, personal_id
  HAVING 
    -- 校验三个属性的取值是否全部匹配
    BOOL_AND(
      CASE 
        WHEN attribute_id = 1000 THEN attribute_number = 2
        WHEN attribute_id = 1001 THEN attribute_number = 5
        WHEN attribute_id = 1002 THEN attribute_number = 1
      END
    )
    -- 校验三个属性记录全部存在，避免属性缺失导致的误判
    AND COUNT(*) = 3
)
SELECT 
  sa.country_id,
  COUNT(DISTINCT sa.personal_id) AS qualified_personal_count
FROM study_table sa
INNER JOIN qualified_person qp
  ON sa.country_id = qp.country_id
  AND sa.personal_id = qp.personal_id
WHERE 
  to_char(sa.mailing_date, 'yyyy-MM') = '2021-01'
  AND sa.study_type = 'Wave'
  AND sa.status NOT IN ('NEW', 'EXCLUDED', 'ERROR')
GROUP BY sa.country_id

该写法的优势：

• 仅对属性表做一次全表扫描完成匹配判断，相比多次关联的写法，在百万级以上的大表场景下性能提升明显
• 预聚合阶段已经把匹配通过的个人过滤为单条记录，和study_table关联时不会产生行数膨胀，统计结果精度更稳定
• HAVING子句中增加的COUNT(*)=3校验，可以避免某个人缺少某条属性记录时，被错误判定为符合条件的边界问题

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