BigQuery中实现复杂自定义排序：JavaScript UDF与性能考量

在数据分析和处理中，排序是基础且常见的操作。bigquery提供了强大的sql order by 子句来支持各种排序需求。然而，当我们需要实现非标准、基于复杂逻辑或外部比较函数（例如javascript中的 localecompare 或自定义的排名函数）的排序时，传统的sql方法可能会显得力不从心。本文旨在提供一种利用javascript udf在bigquery中实现此类高级自定义排序的方法，并对其适用性及性能考量进行深入探讨。

传统SQL自定义排序的局限性

对于固定且预定义的自定义排序顺序，BigQuery中最常见且高效的方法是结合 CASE 表达式与 ORDER BY 子句。例如，如果您需要按照“number”、“time”、“date”的特定顺序进行排序，可以这样实现：

with tbl as (
  select "date" val union all 
  select "time" union all 
  select "number"
)
select
  tbl.val,
  case tbl.val
    when 'number' then 1
    when 'time' then 2
    when 'date' then 3
  end as custom_rank
from tbl
order by custom_rank

这种方法将每个值映射到一个数字排名，然后根据这些排名进行排序。它在BigQuery中表现出色，尤其适用于大规模数据集，因为BigQuery能够高效地并行处理 CASE 表达式。然而，这种方法的局限性在于：

• 缺乏动态性： 排序逻辑必须在SQL查询中硬编码，不适用于需要外部函数或更复杂、动态比较逻辑的场景。
• 不适用于复杂比较： 对于需要比较两个值（val1, val2）并返回负数、零或正数的函数式比较，如语言敏感的字符串比较（localeCompare）或基于复杂算法的自定义排序，CASE 语句难以直接实现。

基于JavaScript UDF的自定义排序方案

为了克服传统SQL的局限性，BigQuery允许我们创建JavaScript用户定义函数（UDF），从而在SQL查询中嵌入JavaScript逻辑。这为实现复杂的自定义排序提供了可能。核心思想是：将需要排序的字段值集合传递给JavaScript UDF，在UDF内部利用JavaScript的强大功能进行排序，然后将排序结果（通常是排好序的值及其对应的顺序）返回给SQL查询，最终通过连接操作将排序结果应用到原始数据上。

示例：使用JavaScript UDF实现自定义排名排序

假设我们希望实现一个基于自定义排名映射的排序，如将“number”排第一，“time”排第二，“date”排第三，并且我们希望以函数式比较的方式实现，而不是通过 CASE 语句。

立即学习“Java免费学习笔记（深入）”；

首先，我们需要定义一个JavaScript UDF，它接收一个字符串数组，并在内部使用自定义的比较函数对其进行排序：

硅基智能

基于Web3.0的元宇宙，去中心化的互联网，高质量、沉浸式元宇宙直播平台，用数字化重新定义直播

62

查看详情

create temp function sortme(MyValues array<string>)
returns array<string>
language js as
"""
// 示例1: 使用localeCompare进行语言敏感排序
// fct1 = (a,b) => a.localeCompare(b, 'en', { sensitivity: 'base' }) ;

// 示例2: 使用自定义排名映射进行排序
const RANK_MAP = {"number": 1, "time": 2, "date": 3};
function fct2(val1, val2) {
    return RANK_MAP[val1] - RANK_MAP[val2];
}

// 对传入的数组进行排序
MyValues.sort(fct2); 
return MyValues;
""";

在这个UDF中，fct2 是我们的自定义比较函数，它根据 RANK_MAP 定义的优先级对两个值进行比较。MyValues.sort(fct2) 则利用这个比较函数对传入的数组进行原地排序。

接下来，我们将这个UDF整合到SQL查询中，以实现对表格数据的自定义排序：

with tbl as (
  select "date" val union all 
  select "time" union all 
  select "number" union all
  select "time" -- 添加重复值以测试
),
-- 步骤1: 聚合所有不重复的待排序值到一个数组
helper as (
  Select sortme(array_agg(distinct val)) as sorted_values from tbl
)
-- 步骤2: 将排序后的数组展开，并获取每个值在排序数组中的索引（即其排序位置）
select 
  t.val,
  sorted_order.sort_by
from tbl as t
left join (
  select 
    val, 
    sort_by 
  from helper, 
  unnest(helper.sorted_values) as val with offset sort_by
) as sorted_order
on t.val = sorted_order.val
-- 步骤3: 根据获取到的排序位置进行最终排序
order by sorted_order.sort_by;

代码解析：

1. tbl CTE： 包含我们需要排序的原始数据。
2. helper CTE：
   • array_agg(distinct val)：首先从 tbl 中收集所有不重复的 val 值，并将它们聚合到一个数组中。使用 distinct 是为了避免UDF处理重复值，因为我们只需要确定每种值的排序位置。
   • sortme(...)：调用我们之前定义的JavaScript UDF，将聚合后的数组传递给它进行自定义排序。UDF返回一个已经排好序的字符串数组。
3. 主查询的 LEFT JOIN 部分：
   • unnest(helper.sorted_values) as val with offset sort_by：将 helper CTE中返回的已排序数组 sorted_values 展开。unnest 会将数组中的每个元素作为一行返回，with offset sort_by 则会为每个元素生成一个从0开始的整数索引，这个索引就是该元素在排序数组中的位置，也即其排序优先级。
   • 这个子查询创建了一个临时表，包含每个值及其在自定义排序中的位置 (sort_by)。
4. 最终 SELECT 和 ORDER BY：
   • LEFT JOIN 将原始 tbl 表与包含排序位置的临时表连接起来。
   • order by sorted_order.sort_by：根据连接后得到的 sort_by 字段进行最终排序，从而实现自定义的排序逻辑。

注意事项与性能考量

尽管JavaScript UDF提供了极大的灵活性，但在BigQuery中使用它进行自定义排序时，务必注意以下几点：

1. 数据集大小限制： 这种方法不适用于处理包含大量不同值的列。如原答案所述，如果数据集的去重行数超过几百行，性能可能会急剧下降。
   • 原因： BigQuery的优势在于其大规模并行处理能力。然而，将所有不同值聚合到一个数组中，然后在一个JavaScript UDF中进行排序，实际上是将一个并行任务转换为一个串行任务。UDF的执行开销相对较高，并且JavaScript引擎在处理非常大的数组时效率会降低。
2. UDF开销： 每次调用UDF都会产生一定的性能开销。虽然BigQuery会尝试优化UDF的执行，但在大规模数据上频繁或复杂地使用UDF仍可能导致查询变慢。
3. 替代方案的适用性： 对于固定且已知的自定义排序顺序，CASE 语句结合 ORDER BY 仍然是首选方案，因为它更符合BigQuery的并行处理模型，性能远优于JS UDF方案。
4. BigQuery内置排序功能： BigQuery正在不断增强其内置的排序功能，例如引入了 Collation（排序规则）概念，以支持更复杂的语言敏感排序。虽然目前尚未完全支持自定义比较函数，但未来可能会有更原生的解决方案出现。

总结

JavaScript UDF为BigQuery用户在实现复杂、动态或函数式自定义排序时提供了强大的工具。通过将值聚合、在UDF中排序，然后将排序结果重新连接回原始数据，我们可以模拟类似JavaScript sort 方法的行为。然而，这种方法的性能开销不容忽视，尤其是在处理具有大量不同值的列时。因此，在选择这种方案时，务必权衡其灵活性与潜在的性能成本。对于大多数固定顺序的自定义排序需求，传统的 CASE 语句仍然是更高效、更推荐的选择。在实际应用中，应根据数据规模、排序逻辑的复杂度和性能要求，选择最合适的实现策略。

以上就是BigQuery中实现复杂自定义排序：JavaScript UDF与性能考量的详细内容，更多请关注php中文网其它相关文章！