BigQuery中实现自定义排序：策略与实践

引言

在数据分析和处理中，我们经常需要对数据进行排序。bigquery提供了标准的order by子句，支持按列的升序或降序排列。然而，有时我们需要更复杂的自定义排序逻辑，例如根据特定业务规则、字符串的自然语言顺序（忽略大小写和变音符号），或者基于非字母数字的自定义排名。本文将详细介绍在bigquery中实现这类自定义排序的两种主要方法：利用case语句构建排序键和使用javascript用户定义函数（udf）。

1. 使用CASE语句构建排序键（推荐）

当自定义排序规则是预先定义且相对固定时，使用CASE语句创建临时的排序键是 BigQuery 中最推荐且性能最佳的方法。这种方法将每个需要排序的值映射到一个数值或具有明确顺序的字符串，然后根据这个映射值进行排序。

工作原理： 通过CASE表达式，您可以为每个特定的输入值指定一个对应的“排名”或“权重”。BigQuery会根据这些生成的排名值进行标准的数字或字符串排序，从而实现自定义的逻辑顺序。

示例场景： 假设我们有一个包含字符串值"date", "time", "number"的表，我们希望它们按照"number" -> "time" -> "date"的顺序进行排序。

WITH tbl AS (
  SELECT "date" AS val UNION ALL
  SELECT "time" UNION ALL
  SELECT "number"
)
SELECT
  tbl.val,
  -- 使用CASE语句为每个值分配一个排序优先级
  CASE tbl.val
    WHEN 'number' THEN 1
    WHEN 'time' THEN 2
    WHEN 'date' THEN 3
    ELSE 99 -- 处理未匹配的值，给予一个默认优先级
  END AS sort_rank
FROM tbl
ORDER BY sort_rank;

输出：

ViiTor实时翻译

AI实时多语言翻译专家！强大的语音识别、AR翻译功能。

116

查看详情

优点：

• 高性能和可扩展性： CASE语句是标准SQL的一部分，BigQuery对其进行了高度优化，可以在大规模数据集上高效运行。
• 易于理解和维护： 逻辑清晰，可以直接在SQL查询中表达排序规则。
• 并行处理： BigQuery能够并行处理CASE表达式，充分利用其分布式计算能力。

注意事项：

• 此方法适用于排序规则相对固定且可以清晰映射到某个顺序值的场景。
• 如果排序规则非常动态或复杂到难以用CASE语句表达，可能需要考虑其他方法。

2. 使用JavaScript UDF实现复杂排序逻辑（适用于小规模数据）

对于需要更复杂比较逻辑的场景，例如字符串的自然语言比较（如JavaScript的localeCompare）或基于动态规则的自定义排名函数，JavaScript UDF提供了一种解决方案。然而，由于其工作机制，此方法在处理大规模数据集时存在显著的性能限制。

工作原理： BigQuery的JavaScript UDF允许您在SQL查询中执行JavaScript代码。但是，与传统编程语言中直接传递比较函数给sort方法不同，BigQuery UDF不能直接接收两个元素进行比较。相反，它需要接收一个完整的数组，在UDF内部对这个数组进行排序，然后返回排序后的数组。之后，我们需要将原始数据与这个排序结果进行关联，以确定每个元素的最终排序位置。

示例场景： 沿用上面的例子，我们希望实现"number" -> "time" -> "date"的自定义排序，但这次通过一个JavaScript函数来定义排名映射。

-- 定义一个JavaScript UDF，用于对字符串数组进行自定义排序
CREATE TEMP FUNCTION sortme(MyValues ARRAY<STRING>)
RETURNS ARRAY<STRING>
LANGUAGE js AS
"""
// 定义自定义的排名映射
const RANK_MAP = {"number": 1, "time": 2, "date": 3};

// 自定义比较函数
function customCompare(val1, val2) {
    // 如果值不在RANK_MAP中，可以给予一个默认排名，例如Infinity
    const rank1 = RANK_MAP[val1] !== undefined ? RANK_MAP[val1] : Infinity;
    const rank2 = RANK_MAP[val2] !== undefined ? RANK_MAP[val2] : Infinity;
    return rank1 - rank2;
}

// 使用自定义比较函数对数组进行排序
MyValues.sort(customCompare);
return MyValues;
""";

-- 示例数据
WITH tbl AS (
  SELECT "date" AS val UNION ALL
  SELECT "time" UNION ALL
  SELECT "number" UNION ALL
  SELECT "apple" -- 添加一个不在RANK_MAP中的值
),
-- 步骤1: 聚合所有不重复的val值到一个数组中
helper AS (
  SELECT sortme(ARRAY_AGG(DISTINCT val)) AS sorted_values FROM tbl
)
-- 步骤2: 将排序后的数组展开，并与原始表连接
SELECT
  t.val,
  -- 通过连接获取排序后的索引，作为最终的排序键
  s.sort_by
FROM tbl AS t
LEFT JOIN (
  SELECT
    sorted_val,
    sort_by -- sort_by是unnest生成的偏移量，即排序后的索引
  FROM helper,
  UNNEST(helper.sorted_values) AS sorted_val WITH OFFSET AS sort_by
) AS s
ON t.val = s.sorted_val
ORDER BY s.sort_by;

输出：

解释：

1. CREATE TEMP FUNCTION sortme(...): 定义了一个临时UDF，它接受一个字符串数组并返回一个字符串数组。
2. UDF内部逻辑: RANK_MAP定义了自定义的排名。customCompare函数使用这个映射来比较两个值。MyValues.sort(customCompare)对传入的数组进行原地排序。
3. helper CTE: 使用ARRAY_AGG(DISTINCT val)将tbl表中所有不重复的val值收集到一个数组中，然后将这个数组传递给sortme UDF进行排序。
4. 最终查询:
   • UNNEST(helper.sorted_values) AS sorted_val WITH OFFSET AS sort_by：将UDF返回的已排序数组展开，sort_by列会生成每个元素在排序后数组中的索引（从0开始）。
   • LEFT JOIN：通过val列将原始表tbl与展开后的排序结果进行连接。
   • ORDER BY s.sort_by：最终根据sort_by（即排序后的索引）对原始数据进行排序。

优点：

• 极高的灵活性： 可以在JavaScript中实现任何复杂的比较逻辑，包括使用localeCompare进行语言敏感排序。
• 代码复用： 复杂的排序逻辑可以封装在UDF中。

注意事项：

• 性能瓶颈： 这是最主要的限制。将所有需要排序的唯一值聚合到一个数组中，并在UDF中进行处理，意味着：
  • 数据需要从BigQuery传递到JavaScript执行环境。
  • JavaScript引擎对大型数组的排序效率可能不如BigQuery的原生排序。
  • ARRAY_AGG操作本身在处理非常大的基数（distinct values）时可能会消耗大量内存和计算资源。
  • 官方建议此方法适用于数据集不超过数百行的情况，因为数据量过大可能导致查询失败或执行时间过长。
• 复杂性增加： 查询结构变得更复杂，需要聚合、UDF调用、展开和连接多个步骤。

总结

在BigQuery中实现自定义排序时，应优先考虑以下策略：

1. 首选CASE语句： 对于预定义且可映射的自定义排序规则，CASE语句是最高效、最可扩展且最易于维护的方法。它直接利用BigQuery的优化能力，适用于绝大多数场景。
2. 谨慎使用JavaScript UDF： 当排序逻辑极其复杂，无法通过CASE语句表达，且数据集规模较小（例如，只有数百个不同的值需要排序）时，可以考虑使用JavaScript UDF。但请务必意识到其性能限制，并避免在处理大规模数据时采用此方法。

通过选择合适的策略，您可以在BigQuery中有效地管理和实现各种自定义排序需求。

以上就是BigQuery中实现自定义排序：策略与实践的详细内容，更多请关注php中文网其它相关文章！