我目前有一个使用coalesce的查询，它在sqlserver中工作，但是在amazonredshift中不工作。有什么方法可以更恰当地在红移中使用：
coalesce(sum(Score)/nullif(sum(ScorePrem),0),0) as percent
kx7yvsdv1#
考虑将聚合查询作为子查询或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 percentFROM agg
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
)
,TotalGrp
,Score
,ScorePrem
,COALESCE(Score/NULLIF(ScorePrem,0),0) AS percent
FROM agg
1条答案
按热度按时间kx7yvsdv1#
考虑将聚合查询作为子查询或cte运行，然后在外部主查询中处理转换或辅助计算。