QuickSight中如何跨7个数据列逐行计算最大值生成新列

QuickSight 内置的max()函数属于聚合函数，仅支持对单列数据做聚合统计，无法直接完成跨列的行级最大值计算，可通过以下两种方案实现需求：

• 方案1：分析层新建计算字段，通过嵌套判断逻辑实现
  直接在QuickSight的分析页面新建计算字段，通过ifelse逐次比较各周成绩列的数值，返回当前行的最大值即可。假设7周成绩对应的字段名分别为week1_score到week7_score，可直接套用以下公式：

  ifelse(
    week1_score >= week2_score AND week1_score >= week3_score AND week1_score >= week4_score AND week1_score >= week5_score AND week1_score >= week6_score AND week1_score >= week7_score, week1_score,
    week2_score >= week3_score AND week2_score >= week4_score AND week2_score >= week5_score AND week2_score >= week6_score AND week2_score >= week7_score, week2_score,
    week3_score >= week4_score AND week3_score >= week5_score AND week3_score >= week6_score AND week3_score >= week7_score, week3_score,
    week4_score >= week5_score AND week4_score >= week6_score AND week4_score >= week7_score, week4_score,
    week5_score >= week6_score AND week5_score >= week7_score, week5_score,
    week6_score >= week7_score, week6_score,
    week7_score
  )
  

  该方案无需修改上游数据集，配置成本低，适配7列的场景完全够用。如果成绩列存在空值，可先通过coalesce(成绩字段, 替换默认值)将空值转换为对应数值（比如缺考按0分计算），避免比较逻辑出错。

• 方案2：数据集层预处理，通过SQL原生函数计算
  如果你的数据集支持自定义SQL查询（比如Athena、Redshift、MySQL等数据源），可以在数据集编辑页选择使用自定义SQL，通过多数SQL引擎原生支持的greatest()函数直接完成行级最大值计算，示例SQL片段如下：

  select 
    *,
    greatest(week1_score, week2_score, week3_score, week4_score, week5_score, week6_score, week7_score) as row_max_score
  from 你的原始表名
  

  该方案逻辑更简洁，适合后续周次列会持续增加的场景，后续新增周成绩列只需要在greatest函数的参数里追加对应字段名即可。

注意：不要尝试将聚合版max()和维度字段搭配做行级计算，该函数的计算逻辑是基于筛选后的数据集聚合，会返回整个分组下的单列最大值，和逐行取跨列最大值的需求逻辑不匹配。

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