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

本文深入探讨了在BigQuery中实现自定义排序的多种策略，包括高效的CASE表达式映射、BigQuery排序规则（Collations）的应用，以及针对特定场景下利用JavaScript UDF进行复杂比较函数排序的实现方法。文章详细阐述了每种方法的适用性、性能考量及具体代码示例，旨在帮助用户根据数据规模和排序逻辑选择最合适的解决方案。

理解自定义排序需求

在数据分析和处理中，我们经常需要根据非字母顺序或数值大小的自定义规则对数据进行排序。例如，将字符串”number”、”time”、”date”按照”number” -> “time” -> “date”的特定优先级进行排列，而不是默认的字典序。虽然许多编程语言（如javascript）提供了直接传入比较函数（comparisonfunction(val1, val2)）来处理这类需求，但在bigquery的标准sql中，order by子句通常直接作用于列值或表达式的结果，并不直接支持这种“比较函数”的签名。

BigQuery中的标准自定义排序方法

尽管BigQuery不直接支持将比较函数作为ORDER BY参数，但可以通过以下标准方法高效实现自定义排序。

使用CASE表达式进行映射排序

对于大多数自定义排序场景，将需要排序的列值映射到一个可排序的数值或字符串，然后依据这个映射值进行排序，是最推荐且性能最优的方法。这种方法利用了BigQuery的并行处理能力，能够很好地扩展到大规模数据集。

示例：

假设我们有如下数据：

我们希望按照 “number” (1) -> “time” (2) -> “date” (3) 的顺序进行排序。

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     ELSE 999 -- 处理未定义的其他值   END AS custom_rank FROM tbl ORDER BY custom_rank;

输出：

优点：

• 高性能和可扩展性： CASE表达式在BigQuery中是高度优化的，可以并行处理大量数据。
• 简洁明了： 排序逻辑直接体现在SQL查询中，易于理解和维护。
• 灵活性： 可以处理复杂的映射规则。

BigQuery排序规则（Collations）

BigQuery引入了排序规则（collations）来支持对字符串进行更精细的排序，例如区分大小写、重音或特定语言的排序规则。虽然这允许在一定程度上自定义字符串排序行为，但它主要针对语言和区域设置相关的排序，目前尚不支持完全任意的、基于用户自定义逻辑的比较函数。

示例（概念性）：

Keeva AI

AI一键生成数字人营销视频

68

查看详情

SELECT my_string_column FROM my_table ORDER BY my_string_column COLLATE 'en_US:ci'; -- 忽略大小写进行排序

注意事项：

• 排序规则主要用于标准字符串排序的变体，不适用于将字符串映射到特定数值顺序的场景。
• 截至目前，BigQuery尚未提供直接通过排序规则传入自定义比较函数的功能。

利用JavaScript UDF实现高级自定义排序

对于非常特定且复杂的排序逻辑，尤其是在数据量相对较小（数百行以内）的情况下，可以考虑使用JavaScript用户定义函数（UDF）。这种方法能够模拟JavaScript中sort(comparisonFunction)的行为。

核心思想： 由于ORDER BY不能直接接受比较函数，我们不能直接在ORDER BY子句中使用UDF来比较两个值。相反，这种方法的工作原理是：

1. 将需要排序的所有不重复值收集到一个数组中。
2. 在JavaScript UDF内部，使用自定义的比较函数对这个数组进行排序。
3. UDF返回排序后的数组。
4. 将原始表与UDF返回的排序数组进行连接，通过数组中元素的偏移量（索引）来确定原始数据的排序顺序。

示例：

沿用之前的需求，我们希望通过JavaScript UDF实现 “number” -> “time” -> “date” 的排序。

-- 定义一个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 customComparisonFunction(val1, val2) {     // 如果值不在RANK_MAP中，可以给一个默认的优先级，例如：     const rank1 = RANK_MAP[val1] !== undefined ? RANK_MAP[val1] : 999;     const rank2 = RANK_MAP[val2] !== undefined ? RANK_MAP[val2] : 999;     return rank1 - rank2; }  // 使用自定义比较函数对数组进行排序 MyValues.sort(customComparisonFunction); return MyValues; """;  -- 原始数据 WITH tbl AS (   SELECT "date" AS val UNION ALL   SELECT "time" UNION ALL   SELECT "number" UNION ALL   SELECT "unknown_value" -- 包含一个未在RANK_MAP中定义的值 ), -- 使用UDF对所有不重复的val进行排序 helper AS (   SELECT sortme(ARRAY_AGG(DISTINCT val)) AS sorted_values_array FROM tbl ) -- 将原始表与排序后的数组连接，并根据偏移量排序 SELECT   t.val FROM tbl AS t LEFT JOIN (   SELECT     val,     sort_by -- 排序后的索引   FROM helper,   UNNEST(helper.sorted_values_array) AS val WITH OFFSET sort_by ) AS sorted_map ON t.val = sorted_map.val ORDER BY sorted_map.sort_by;

输出：

性能考量与注意事项：

• 适用场景限制： 这种方法仅适用于数据集较小（通常为数百行）的情况。JavaScript UDF需要将所有待排序的字符串收集到一个数组中并在UDF内部进行处理。
• 性能瓶颈： 对于大规模数据集，将所有数据聚合到一个数组并在UDF中进行排序会严重影响性能，因为这打破了BigQuery的并行处理优势。UDF的执行开销也相对较高。
• 内存限制： UDF内部处理的数组大小受限于BigQuery UDF的内存限制。
• 复杂性： 相比CASE表达式，这种方法在SQL结构上更为复杂，需要聚合、UDF调用和连接操作。
• 错误处理： 如果UDF中出现JavaScript错误，可能会导致查询失败。

总结与最佳实践

在BigQuery中实现自定义排序时，应根据具体需求和数据规模选择最合适的方法：

1. 首选CASE表达式： 对于大多数自定义排序需求，尤其是涉及将特定字符串映射到固定顺序的场景，CASE表达式是最高效、最可扩展且最推荐的方法。它利用了BigQuery的并行处理能力，适用于任何规模的数据集。
2. 考虑BigQuery排序规则： 如果你的自定义排序仅涉及字符串的语言、大小写或重音敏感性，可以探索BigQuery的排序规则（Collations）。
3. 谨慎使用JavaScript UDF： 仅当你的排序逻辑非常复杂，且无法通过CASE表达式或现有SQL函数实现，并且你的数据集规模非常小（例如，需要排序的唯一值只有几百个）时，才考虑使用JavaScript UDF。务必充分评估其性能影响。

通过理解这些不同的策略及其优缺点，开发者可以在BigQuery中有效地实现各种自定义排序需求。