基于UserID和Store分区，按Product筛选生成MAX值新列的优化求助

需求说明 #

基于SourceTable，按UserID和Store分区，分别计算Product为Apples和Grapes的Value字段最大值，生成max_value_by_apples和max_value_by_grapes两个新列。

原方案问题 #

你当前用多个CTE分别过滤产品计算最大值，再左连接回原表的方式，会导致多次扫描SourceTable，且两次左连接会增加查询的IO开销和执行复杂度，数据量较大时性能会明显下降。

优化方案：使用条件聚合的窗口函数 #

仅需一次扫描原表，结合CASE WHEN在窗口函数中过滤指定产品，直接计算分区内的最大值，避免多余的连接操作：

SELECT 
    *,
    MAX(CASE WHEN Product = 'Apples' THEN [Value] END) OVER (PARTITION BY UserID, Store) AS max_value_by_apples,
    MAX(CASE WHEN Product = 'Grapes' THEN [Value] END) OVER (PARTITION BY UserID, Store) AS max_value_by_grapes
FROM SourceTable

方案优势 #

• 仅需一次全表扫描，相比原方案多次扫描+连接，大幅减少IO消耗
• 逻辑更简洁，避免了多CTE和连接带来的执行计划复杂度
• 对于没有对应产品的分区，最大值会返回NULL，和原左连接的结果逻辑完全一致

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