如何在BigQuery中结合NTILE函数实现滚动窗口滚动分箱/滚动分位数

实现方案 #

核心思路 #

BigQuery的编号类函数（包括NTILE）确实不支持自定义窗口帧，无法直接和滚动窗口搭配使用。可以通过滑动窗口值聚合+百分位映射的方式实现完全等效的滚动分箱效果，逻辑和NTILE输出一致。

避免魔术数字的方法 #

不用硬编码86400秒，可以直接用INTERVAL语法定义时间窗口，可读性更高：

-- 窗口定义示例，和原秒数逻辑完全等效
OVER (ORDER BY Time ASC RANGE BETWEEN INTERVAL 24 HOUR PRECEDING AND INTERVAL 24 HOUR FOLLOWING)

具体实现代码 #

方案1：窗口数组聚合（性能更优，适合大数据量）

先把每个滚动窗口内的所有A值聚合为数组，再计算当前值在数组中的分桶位置：

WITH base AS (
  SELECT
    Time,
    A,
    -- 聚合当前窗口内所有A值
    ARRAY_AGG(A) OVER (
      ORDER BY Time ASC
      RANGE BETWEEN INTERVAL 24 HOUR PRECEDING AND INTERVAL 24 HOUR FOLLOWING
    ) AS window_values
  FROM `my_project.my_table`
)
SELECT
  Time,
  A,
  -- 映射为1-5的分桶编号，和NTILE(5)逻辑一致
  CEIL(5 * PERCENT_RANK() OVER (PARTITION BY Time ORDER BY val)) AS rolling_ntile_5
FROM base,
UNNEST(window_values) AS val
QUALIFY val = A

方案2：自关联实现（逻辑更直观）

通过自关联匹配每个行对应的滑动窗口范围，再计算分桶：

SELECT
  t1.Time,
  t1.A,
  CEIL(5 * PERCENT_RANK() OVER (PARTITION BY t1.Time ORDER BY t2.A)) AS rolling_ntile_5
FROM `my_project.my_table` t1
JOIN `my_project.my_table` t2
  ON t2.Time BETWEEN TIMESTAMP_SUB(t1.Time, INTERVAL 24 HOUR) AND TIMESTAMP_ADD(t1.Time, INTERVAL 24 HOUR)
-- 只保留当前行对应的分桶结果
QUALIFY t2.A = t1.A

注意事项 #

如果窗口内存在重复的A值，可以在ORDER BY后追加时间字段作为次排序规则，避免PERCENT_RANK计算出现歧义，比如改成ORDER BY t2.A, t2.Time。

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