SQL按客户订单聚合产品价格创建新列的高效写法咨询

原实现的核心问题 #

你当前写的多CTE左连接逻辑存在多个会影响结果准确性和执行效率的问题：

• 语法执行顺序错误：SQL的执行顺序中WHERE过滤子句位于GROUP BY分组之前，原代码将产品过滤条件写在分组之后，不符合SQL语法规范，无法正常执行
• 关联逻辑存在缺陷：仅通过Customer_ID做表连接，会触发跨订单的笛卡尔积匹配，同一客户下不同订单的产品价格会被错误拼接，最终结果完全失真
• 数据覆盖不全：以N产品的聚合结果作为主表做左连接，所有客户未购买N产品的订单（哪怕只购买了K、E类产品）都会被直接过滤，无法覆盖全量订单数据
• 性能开销过高：3个产品就需要对订单表做3次全量扫描，当产品类别增加到20个时，扫描次数会线性增长为20次，随表数据量扩大查询延迟会显著升高

通用高效实现方案：条件聚合（单次扫描完成全量计算） #

这是适配从3个产品到20个产品场景的最优落地实现，核心是采用*条件聚合（也叫透视聚合）*逻辑，通过带判断条件的聚合函数，在一次表扫描中同时完成所有产品类别的价格求和，不需要多次表扫描、不需要多表连接。
参考实现代码：

SELECT
  Customer_ID,
  Order_ID,
  Sales_Date,
  -- 每个产品对应独立列：不写ELSE时，未匹配到对应产品的行默认返回NULL，符合"未购买该产品"的业务语义
  SUM(CASE WHEN Product = 'N' THEN Price END) AS N_Price,
  SUM(CASE WHEN Product = 'K' THEN Price END) AS K_Price,
  SUM(CASE WHEN Product = 'E' THEN Price END) AS E_Price
FROM orders
-- 所有公共过滤条件（比如时间范围、有效订单标记）只需要写一次，不用在每个CTE里重复维护
GROUP BY 1, 2, 3

这个方案的核心优势：

• 性能层面：全表仅扫描1次，不管产品类别从3个增长到20个，查询的扫描开销基本不会变化，比多CTE左连接的实现效率高一个量级
• 准确性层面：完全去掉了表连接逻辑，从根源上避免了关联键遗漏、笛卡尔积匹配带来的结果错误，也不会漏掉任何未购买某类产品的订单
• 维护层面：后续新增产品类别时，只需要在SELECT列表中新增一行对应的SUM(CASE ...)计算逻辑即可，不需要新增CTE、修改连接条件，维护成本极低

多产品动态扩展的适配方式 #

当产品类别持续增长、手动写CASE WHEN的维护成本过高时，可以结合数据库自带的透视能力实现动态列生成：

• 对支持PIVOT语法的数据库（如SQL Server、BigQuery），可以直接基于产品字段做动态透视，自动生成对应产品的价格列
• 对PostgreSQL、MySQL等数据库，可以通过提前查询全量在售产品列表，拼接生成动态执行的SQL，避免手动维护大量重复的聚合判断逻辑

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