行级多列最大值提取的SQL Server与Azure Synapse适配方案

原语法失效原因 #

此前使用的行级多列取最大值写法select max(A) from (values( A,B,C,D,E,F,G, I)) as A) as Z，属于Azure Synapse专用SQL池不支持的受限语法：该语法将外部表列直接传入行级VALUES构造函数做相关子查询计算，无法被Synapse引擎解析。

兼容SQL Server与Azure Synapse的实现语句 #

根据不同环境版本提供三类可直接运行的方案：

最优性能方案（适配高版本环境） #

适用于SQL Server 2022+、Azure Synapse最新版无服务器/专用SQL池、Azure SQL DB环境，直接调用内置GREATEST函数：

SELECT
  ID,
  Date,
  GREATEST(A, B, C, D, E, F, G, I) AS row_level_max
FROM 你的目标表名;

注：GREATEST自动忽略空值，若所有列均为NULL则返回NULL。

全版本兼容方案（无语法限制） #

若需兼容SQL Server 2019及以下等无GREATEST函数的老旧环境，使用UNION ALL拆分行后聚合的写法，全平台通用：

SELECT
  ID,
  Date,
  MAX(col_val) AS row_level_max
FROM (
  SELECT ID, Date, A AS col_val FROM 你的目标表名
  UNION ALL
  SELECT ID, Date, B AS col_val FROM 你的目标表名
  UNION ALL
  SELECT ID, Date, C AS col_val FROM 你的目标表名
  UNION ALL
  SELECT ID, Date, D AS col_val FROM 你的目标表名
  UNION ALL
  SELECT ID, Date, E AS col_val FROM 你的目标表名
  UNION ALL
  SELECT ID, Date, F AS col_val FROM 你的目标表名
  UNION ALL
  SELECT ID, Date, G AS col_val FROM 你的目标表名
  UNION ALL
  SELECT ID, Date, I AS col_val FROM 你的目标表名
) AS unpivot_source
GROUP BY ID, Date;

折中性能方案（减少表扫描次数） #

若Azure Synapse环境支持CROSS APPLY语法（多数最新版专用SQL池已支持），使用该写法可避免全版本兼容方案中的多次表扫描，性能更优：

SELECT
  t.ID,
  t.Date,
  MAX(v.col_val) AS row_level_max
FROM 你的目标表名 t
CROSS APPLY (
  VALUES (A), (B), (C), (D), (E), (F), (G), (I)
) AS v(col_val)
GROUP BY t.ID, t.Date;

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