如何使用SQL获取每行运算组合数组中的最大值

• 给定三个整数a、b、c，插入运算符+、*和括号()后，返回能得到的最大数值。
• 即：使用[*+()]对a、b、c做所有可能的合法组合运算，返回运算结果的最大值。

示例 #

以数字1、2、3为例，部分符号和括号的放置方式如下：

• 1 * (2 + 3) = 5
• 1 * 2 * 3 = 6
• 1 + 2 * 3 = 7
• (1 + 2) * 3 = 9

因此可得到的最大值为9。

注意事项 #

• 所有数字均为正数。
• 同一运算符可重复使用。
• 不能交换操作数顺序，如示例中不能构造出(1 + 3) * 2 = 8的表达式。

问题描述 #

我已枚举所有运算组合，将结果存入数组，编写SQL如下：

SELECT a,b,c, ARRAY[
  a * b * c, 
  a + b + c,
  a * b + c,
  a + b * c,
  (a + b) * c,
  a * (b + c)
  ] AS res
FROM expression_matter

查询结果示例如下：

a   b   c   res
2   1   2   {4,5,4,4,6,6}
2   1   1   {2,4,3,3,3,4}
2   2   4   {16,8,8,10,16,12}
3   3   3   {27,9,12,12,18,18}
1   1   1   {1,3,2,2,2,2}

现在我需要获取每个数组中的最大值，但MAX和GREATEST函数的运行结果不符合预期：SELECT GREATEST(ARRAY[...])返回结果和原数组一致，SELECT MAX(ARRAY[...])返回的是全表所有数组的全局最大值。我期望实现每行单独计算该行res数组内的最大值，请问该如何用SQL实现？

解决方案 #

首先说明两个函数不符合预期的原因：

• GREATEST函数的入参要求是多个独立的数值参数，而非单个数组，直接传数组会被识别为单个参数，自然返回原数组。
• MAX是聚合函数，作用范围是跨行的分组数据集，所以会返回全表的全局最大值。

方案一（通用最优解，适配绝大多数数据库） #

不需要先把结果存为数组，直接把6种运算结果作为参数传入GREATEST即可，写法简单高效：

SELECT a,b,c,
  GREATEST(
    a * b * c, 
    a + b + c,
    a * b + c,
    a + b * c,
    (a + b) * c,
    a * (b + c)
  ) AS max_res
FROM expression_matter

方案二（必须从已生成数组中取值的场景，以PostgreSQL为例） #

如果业务逻辑要求必须先构造数组再取最大值，可以用UNNEST函数将数组拆分为单行数值，再按原行分组求最大值：

SELECT a,b,c, (SELECT MAX(val) FROM UNNEST(res) val) AS max_res
FROM (
  SELECT a,b,c, ARRAY[
    a * b * c, 
    a + b + c,
    a * b + c,
    a + b * c,
    (a + b) * c,
    a * (b + c)
    ] AS res
  FROM expression_matter
) t

其他数据库如果需要从数组取值，都可以参考「拆分数组为单行→按原行分组求最大值」的逻辑实现，不过综合来看方案一的兼容性和性能都是最优的。

内容的提问来源于stack exchange，提问作者Péter Szilvási