如何在BigQuery中创建MAX_BY自定义函数（UDF）？

其他数据仓库内置的MAX_BY函数能根据指定的最大化值返回对应列的值，在BigQuery里可以通过原生聚合函数高效实现，无需硬编码表名，也不用依赖JS。

推荐实现：原生聚合函数版本 #

这种方式性能最优，适配所有查询场景，还支持分组聚合：

CREATE OR REPLACE FUNCTION dataset.MAX_BY(col ANY TYPE, val ANY TYPE)
RETURNS ANY TYPE
AS (
  ARRAY_AGG(col ORDER BY val DESC LIMIT 1)[OFFSET(0)]
);

核心逻辑说明：

• 用ARRAY_AGG将目标列col按val降序排列，通过LIMIT 1只保留对应最大val的那一个元素
• [OFFSET(0)]直接提取数组的第一个元素，就是我们要的结果
• 返回类型设为ANY TYPE，比固定STRING更灵活，能适配字符串、数值、结构体等各种列类型
• 调用时无需指定表名，直接传入当前查询中的列即可

使用示例

1. 单表取最大值对应列：

SELECT dataset.MAX_BY(name, score) AS top_name
FROM dataset.my_table;

（假设my_table有name和score列，返回分数最高的名字）

2. 分组取每组最大值对应列：

SELECT category, dataset.MAX_BY(name, score) AS top_name_in_category
FROM dataset.my_table
GROUP BY category;

（按category分组，返回每个组内分数最高的名字）

备选：JS UDF版本（不推荐大数据量场景） #

如果一定要用JS实现，也可以写，但BigQuery的JS UDF在处理大规模数据时性能远不如原生聚合函数：

CREATE OR REPLACE FUNCTION dataset.MAX_BY_JS(col ANY TYPE, val ANY TYPE)
RETURNS ANY TYPE
LANGUAGE js
AS """
let maxVal = -Infinity;
let result = null;
for (let i = 0; i < val.length; i++) {
  if (val[i] > maxVal) {
    maxVal = val[i];
    result = col[i];
  }
}
return result;
""";

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