避免使用 coalesce() 的嵌套聚合错误

Question

我目前有一个使用在 SQL Server 中工作的 coalesce 的查询，但是，它在 Amazon Redshift 中不起作用。有没有办法更合适地编写它以在 Redshift 中使用：

coalesce(sum(Score)/nullif(sum(ScorePrem),0),0) as percent

Answer 1

请考虑将聚合查询作为子查询或 CTE 运行，然后在外部主查询中处理转换或辅助计算。

WITH agg AS (
SELECT calendar_month_id
,day_of_month
,month_name
,DaysRemaining
,RPTBRANCH
,0 AS TotalGrp
,SUM(Score) AS Score
,SUM(ScorePrem) AS ScorePrem
FROM #temp_Score
GROUP BY calendar_month_id
, day_of_month
, month_name
, DaysRemaining
, RPTBranch
)
SELECT calendar_month_id
,day_of_month
,month_name
,DaysRemaining
,RPTBRANCH
,TotalGrp
,Score
,ScorePrem
,COALESCE(Score/NULLIF(ScorePrem,0),0) AS percent
FROM agg