如何在BigQuery中使用自定义比较函数实现排序？

问题背景 #

需要在BigQuery中实现类似JavaScript Array.sort(comparisonFunction)的排序逻辑：通过一个返回负数、0、正数的二元比较函数定义排序规则，且不使用inline SQL的case语句实现。

可行方案 #

方案1：封装排序键的JS UDF（适配BigQuery原生排序） #

BigQuery的ORDER BY依赖单值排序键，我们可以将比较逻辑封装成返回排序权重的UDF，同时保留你需要的二元比较函数结构：

-- 创建临时UDF，内部保留目标比较函数结构
CREATE TEMP FUNCTION getSortKey(val STRING)
RETURNS INT64
LANGUAGE js AS """
  const RANK_MAP = {"number": 1, "time": 2, "date": 3};
  
  // 保留要求的comparisonFunction签名和逻辑
  function comparisonFunction(val1, val2) {
      return RANK_MAP[val1] - RANK_MAP[val2];
  }
  
  // 返回当前值的排序权重，用于ORDER BY
  return RANK_MAP[val];
""";

-- 使用UDF排序
WITH tbl AS (
  SELECT "date" val UNION ALL 
  SELECT "time" UNION ALL 
  SELECT "number"
)
SELECT val
FROM tbl
ORDER BY getSortKey(val);

方案2：模拟JS数组sort逻辑（直接传递比较函数） #

如果想完全复刻JS中传递二元比较函数的写法，可以通过将数据转为数组，用JS UDF执行排序后再展开：

-- 创建支持自定义比较函数的排序UDF
CREATE TEMP FUNCTION sortWithCustomComparator(arr ARRAY<STRING>, comparatorCode STRING)
RETURNS ARRAY<STRING>
LANGUAGE js AS """
  // 将传入的比较函数代码转为可执行函数
  const compareFn = new Function('val1', 'val2', comparatorCode);
  return arr.sort(compareFn);
""";

-- 使用自定义比较函数排序
WITH tbl AS (
  SELECT "date" val UNION ALL 
  SELECT "time" UNION ALL 
  SELECT "number"
),
data_array AS (
  SELECT ARRAY_AGG(val) AS vals FROM tbl
)
SELECT val
FROM data_array,
UNNEST(sortWithCustomComparator(vals, `
  const RANK_MAP = {"number": 1, "time": 2, "date": 3};
  return RANK_MAP[val1] - RANK_MAP[val2];
`)) AS val;

说明 #

BigQuery原生ORDER BY不支持直接传入二元比较函数，因为它是基于列值的批量排序而非数组元素的两两比较。方案1性能更优，适合大数据量场景；方案2完全贴合JS sort的语法习惯，适合小批量数据或需要灵活修改比较逻辑的场景。

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