Redshift长表转宽表性能优化求助——医疗记录上报场景下UNLOAD前的查询效率提升

首先，你的问题核心在于多次内连接导致的查询计划爆炸和重复扫描大表——每个字段都做一次JOIN，相当于把CTE_patient_record_item（9700万行）扫描了200次，这直接导致了30-40分钟的耗时。而解决这个问题的最优方案就是用条件聚合（行转列/Pivot），也就是你更新里提到的MAX(CASE WHEN ...)的方式，这也是Redshift处理这类行转列场景的标准高效做法。

具体优化后的SQL示例 #

结合你的CTE，优化后的查询应该是这样的：

WITH CTE_patient_record AS (
 SELECT record_id
 FROM fact_patient_record
 WHERE update_date = <yesterday>
),
CTE_patient_record_item AS (
 SELECT record_id
 ,record_item_name
 ,record_item_value
 FROM fact_patient_record_item fpri
 INNER JOIN CTE_patient_record cpr ON fpri.record_id = cpr.record_id
)
SELECT 
 record_id,
 MAX(CASE WHEN record_item_name = 'diagnosis_1' THEN record_item_value END) AS diagnosis_1,
 MAX(CASE WHEN record_item_name = 'diagnosis_2' THEN record_item_value END) AS diagnosis_2,
 MAX(CASE WHEN record_item_name = 'medication_1' THEN record_item_value END) AS medication_1,
 -- 继续添加所有需要的字段...
FROM CTE_patient_record_item
GROUP BY record_id;

为什么这个方法快？ #

1. 单次扫描大表：整个查询只需要扫描一次CTE_patient_record_item，而不是原来的200次，直接减少了IO和计算量。
2. 简化查询计划：原来的200次JOIN会让查询计划变得异常复杂（你提到的2700行EXPLAIN），而条件聚合的查询计划非常简洁，Redshift能高效地执行分组和条件判断。
3. 避免笛卡尔积风险：多次JOIN如果处理不当，可能会产生中间结果的笛卡尔积，导致数据膨胀，而条件聚合是基于分组的，不会有这个问题。

额外的补充优化建议 #

除了核心的条件聚合，你还可以考虑这些细节来进一步提升性能：

• 优化CTE_patient_record的过滤：确保fact_patient_record的update_date字段有合适的排序键（Sort Key）或者索引，这样过滤出昨天的数据会更快，减少后续CTE_patient_record_item需要处理的行数。
• 临时编码record_item_name：如果管理层允许临时表，可以先把所有用到的record_item_name映射成整数（比如创建一个临时字典表），然后在CTE_patient_record_item里替换成整数，这样CASE WHEN的判断会比字符串比较更快——不过如果你的字符串是短字符串，这个提升可能不大。
• UNLOAD环节优化：导出时可以指定DELIMITER '|'直接生成管道分隔文件，同时开启PARALLEL ON（默认开启）让Redshift并行导出，加快文件生成速度；如果需要单个文件，可以用PARALLEL OFF，但大文件的话建议并行。

其他方案的评估 #

你提到的几个备选方案，这里也可以做个快速评估：

• 存储过程：其实条件聚合可以直接写到存储过程里，但核心还是查询逻辑的优化，存储过程本身不会提升查询性能，只是方便复用。
• 新建表加整数ID：如果能说服管理层，这是长期的优化方案，但短期来看条件聚合已经足够解决问题。
• 拉到Python处理：虽然可行，但数据量大会增加网络传输的开销，而且Redshift本身就是为这类数据处理设计的，能在库内解决肯定更高效。

很高兴看到你用条件聚合把耗时从30分钟降到了30秒，这确实是这类场景的最优解！

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