PostgreSQL Crosstab交叉表查询多NPI时记录丢失问题

• 核心原因：行标识重复导致crosstab合并行
  PostgreSQL的crosstab函数会按照传入查询结果的第一列作为行分组标识，你当前给crosstab传入的查询第一列是p.id（practitioner_id），如果两个NPI其中任意一个在practitioner表中没有匹配记录，LEFT JOIN会返回p.id = NULL，crosstab会把所有行标识相同的记录合并为同一行，就会出现两个NPI的数据合并成一条、少一行的问题。单独查询单个NPI时，每个查询只有一个行标识，所以都能正常返回结果。
• 其他潜在错误点
  1. CTE列定义不匹配：你定义CTE sc (npi, sg)时只指定了2个列，但SELECT实际输出了npi、分组计算的pay_code、counter三个列，会导致逻辑异常。
  2. 两处CASE逻辑不一致：SELECT中的CASE分组逻辑和GROUP BY里的CASE逻辑不一致（比如第一个WHEN分支的判断条件，前者只匹配fed类model，后者多匹配了state、work comp等），会导致计算的sg值和分组键不匹配，出现数据错误。
  3. JOIN过滤规则不一致：total CTE的WHERE条件多了plan_name <> ''限制，但sc CTE没有该限制，如果某个NPI存在plan_name为空的记录，会导致counter统计的分母和分子口径不一致，甚至出现total中无对应记录的情况。
  4. 输出列名重复：crosstab的输出列定义里有两个medi NUMERIC，会导致查询报错或者结果取值错误。

修复建议 #

1. 调整crosstab输入查询的行标识顺序，将全局唯一非空的npi放在第一列，practitioner_id放在第二列，确保行标识唯一不会被合并：把crosstab内的SELECT语句改成SELECT t.npi, p.id, sg, ROUND(s.counter::DECIMAL / tot_count, 4) FROM ...，对应的crosstab输出列定义调整为AS ct (npi TEXT, practitioner_id INT, other NUMERIC, medi NUMERIC, commer NUMERIC, med_c NUMERIC, med_adv NUMERIC)（同时修改重复的medi列名）。
2. 统一两处CASE的判断逻辑，确保分组键和赋值的sg值完全对应。
3. 统一sc和total的WHERE过滤条件，确保分子分母统计口径一致。

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