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

mmvthczy 于 2021-07-26 发布在 Java

关注(0)|答案(1)|浏览(252)

我目前有一个使用coalesce的查询，它在sqlserver中工作，但是在amazonredshift中不工作。有什么方法可以更恰当地在红移中使用：

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

sql Null coalesce Aggregate amazon-redshift

来源：https://stackoverflow.com/questions/62416808/avoid-nested-aggregate-error-using-coalesce

1条答案

按热度按时间

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 percent
FROM agg

赞(0）回复(0）举报 2021-07-26

我来回答

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

1条答案

相关问题

热门标签

最新问答