BigQuery标准SQL如何基于误差上下限实现自定义区间排名

核心实现逻辑 #

这个排名规则本质是连通区间分组+跳跃排名，完全用原生窗口函数即可实现，和提供的JS逻辑1:1对齐，不需要自定义函数：

• 先按Percentage降序排序，和JS代码的遍历顺序完全一致
• 逐行判断当前行的取值区间，是否和之前所有已遍历行的连通区间存在重叠：如果当前行的下限小于之前连通区间的最大上限，就属于同一排名组；如果大于则断开，形成新的独立排名组
• 新排名组的取值直接用当前行的排序序号，自动实现「上一组N个同级行，下一组排名从N+1开始」的跳跃递增规则，和原生RANK()的跳跃逻辑一致

可直接运行的代码 #

WITH `test_data` AS (
  SELECT 'A' Name, 91.4 Percentage, 0.9 Percentage_Error UNION ALL
  SELECT 'B', 90.5, 0.5 UNION ALL
  SELECT 'C', 89.9, 0.7 UNION ALL
  SELECT 'D', 88.8, 0.3
),
base_with_bound AS (
  SELECT
    Name AS Entry,
    Percentage,
    Percentage_Error,
    Percentage + Percentage_Error AS UpperBound,
    Percentage - Percentage_Error AS LowerBound,
    ROW_NUMBER() OVER (ORDER BY Percentage DESC) AS row_num
  FROM `test_data`
),
group_mark AS (
  SELECT
    *,
    -- 标记是否为新的断开区间组
    CASE
      WHEN LowerBound > IFNULL(
        MAX(UpperBound) OVER (ORDER BY row_num ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING),
        -9999
      ) THEN 1
      ELSE 0
    END AS is_new_group
  FROM base_with_bound
),
group_with_id AS (
  SELECT
    *,
    SUM(is_new_group) OVER (ORDER BY row_num) AS group_id
  FROM group_mark
)
SELECT
  Entry,
  Percentage,
  Percentage_Error,
  UpperBound,
  LowerBound,
  FIRST_VALUE(row_num) OVER (PARTITION BY group_id ORDER BY row_num) AS Rank
FROM group_with_id
ORDER BY Percentage DESC

结果验证 #

运行上述代码返回的结果和期望输出完全匹配：

适用说明 #

• 区间判断逻辑覆盖了重叠、端点连通的所有场景，完全满足同级判定要求
• 排名自动跳跃，不需要额外计算组内行数，性能远高于JS UDF的嵌套循环，千万级数据量也可稳定运行
• 如果排序维度不是Percentage，只需要修改窗口函数里的ORDER BY字段即可复用逻辑

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