Spark SQL关联标量子查询报AnalysisException 求正确语法

以下是消除错误的正确语法，使用窗口函数替代关联子查询来计算分母总和：

SELECT
  adr.ProductionDate,
  adr.CostCenterKey,
  adr.AEMainCategoryKey,
  (SUM(adr.DurationDayFrac * adr.FixedCashCostAE) * 100) / 
  SUM(SUM(adr.DurationDayFrac * adr.FixedCashCostAE)) OVER (PARTITION BY adr.ProductionDate, adr.CostCenterKey) AS AELossMagnitude
FROM hive_metastore.oth_opsdbconf.operations_db___asset_effectiveness_daily_reporting adr
WHERE
  adr.DurationDayFrac != 0
  AND adr.ProductionDate <= CURRENT_DATE()
  AND adr.ProductionDate >= '2017-01-01'
  AND adr.SiteName IN ('Ludwigshafen', 'Schwarzheide', 'Antwerpen')
  AND AreaIsCurrent = 'true'
GROUP BY adr.ProductionDate, adr.CostCenterKey, adr.AEMainCategoryKey
ORDER BY adr.ProductionDate, adr.CostCenterKey, adr.AEMainCategoryKey;

错误原因及修正说明 #

原查询报错是因为Spark SQL无法正确处理SELECT子句中的关联标量子查询与GROUP BY的组合逻辑。尽管子查询用到的CostCenterKey和ProductionDate已在GROUP BY中，但Spark仍将整个子查询视为未被聚合或分组的独立表达式。

使用窗口函数的优势：

• SUM(SUM(...)) OVER (PARTITION BY adr.ProductionDate, adr.CostCenterKey) 先计算每个(ProductionDate, CostCenterKey, AEMainCategoryKey)分组的聚合值，再通过窗口函数对(ProductionDate, CostCenterKey)分区求和，直接得到分母的总合。
• 这种方式规避了关联子查询的语法限制，同时执行效率更高，无需重复扫描表数据。
• 移除了冗余的SELECT DISTINCT，因为GROUP BY已经保证了结果行的唯一性。

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