在BigQuery SQL中实现列排序与逐位比较的技术方案咨询

核心思路 #

因为无法直接获取中间值，我们可以通过总和减去最大最小值得到中间值，再分别对A、B组的三个值排序后逐位比较，最终判断是否全部满足A<B的条件。这种行级计算逻辑高效，适配大数据量场景。

具体SQL实现 #

SELECT
  *,
  -- 计算A组排序后的三个值：最小、中间、最大
  LEAST(A_Length, A_breadth, A_Width) AS A_min,
  (A_Length + A_breadth + A_Width) - LEAST(A_Length, A_breadth, A_Width) - GREATEST(A_Length, A_breadth, A_Width) AS A_mid,
  GREATEST(A_Length, A_breadth, A_Width) AS A_max,
  -- 计算B组排序后的三个值：最小、中间、最大
  LEAST(B_length, B_breadth, B_width) AS B_min,
  (B_length + B_breadth + B_width) - LEAST(B_length, B_breadth, B_width) - GREATEST(B_length, B_breadth, B_width) AS B_mid,
  GREATEST(B_length, B_breadth, B_width) AS B_max,
  -- 判断三次比较是否全为True
  (LEAST(A_Length, A_breadth, A_Width) < LEAST(B_length, B_breadth, B_width))
  AND ((A_Length + A_breadth + A_Width) - LEAST(A_Length, A_breadth, A_Width) - GREATEST(A_Length, A_breadth, A_Width) < (B_length + B_breadth + B_width) - LEAST(B_length, B_breadth, B_width) - GREATEST(B_length, B_breadth, B_width))
  AND (GREATEST(A_Length, A_breadth, A_Width) < GREATEST(B_length, B_breadth, B_width)) AS is_all_less
FROM
  `你的项目名.数据集名.表名`

关键说明 #

• 中间值计算：三个数值的总和减去最小值和最大值，剩余结果即为中间值，无需额外聚合或窗口函数，计算成本极低。
• 结果判断：用AND串联三次比较逻辑，只有三次比较均返回True时，is_all_less字段才会输出True，否则为False。
• 性能适配：全程为行级运算，不会触发数据shuffle，在大数据量场景下仍能保持高效执行。

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