如何在BigQuery中计算满足y处于窗口前5%的x滚动平均值？

可以实现这个需求，但无法直接在AVG()的OVER子句中添加筛选条件，需要借助窗口函数先标记目标数据，再通过条件聚合计算平均值。下面提供两种常用的实现方式：

方法一：用NTILE划分百分位组 #

NTILE(n)会将窗口内的行均分为n个等级，前5%对应n=20时的第1组（100行窗口刚好每组5行，完美匹配5%比例）。

SELECT
    some_id,
    some_timestamp,
    x,
    y,
    -- 仅计算窗口内y处于前5%的x的滚动平均值
    AVG(CASE WHEN y_percentile = 1 THEN x END) OVER (
        PARTITION BY some_id 
        ORDER BY some_timestamp 
        ROWS BETWEEN 99 PRECEDING AND CURRENT ROW
    ) AS avg_x_top5p_y
FROM (
    SELECT
        some_id,
        some_timestamp,
        x,
        y,
        -- 将每个滚动窗口内的y划分为20个等级，前5%对应等级1
        NTILE(20) OVER (
            PARTITION BY some_id 
            ORDER BY y DESC  -- 若前5%指y最小的部分，改为ASC
            ROWS BETWEEN 99 PRECEDING AND CURRENT ROW
        ) AS y_percentile
    FROM some_table
) sub_query

方法二：用PERCENT_RANK计算百分位排名 #

PERCENT_RANK()返回当前行在窗口内的相对排名（范围0-1），筛选排名≤0.05的行即可对应前5%。

SELECT
    some_id,
    some_timestamp,
    x,
    y,
    AVG(CASE WHEN y_rank <= 0.05 THEN x END) OVER (
        PARTITION BY some_id 
        ORDER BY some_timestamp 
        ROWS BETWEEN 99 PRECEDING AND CURRENT ROW
    ) AS avg_x_top5p_y
FROM (
    SELECT
        some_id,
        some_timestamp,
        x,
        y,
        -- 计算y在窗口内的百分位排名，值越小排名越靠前
        PERCENT_RANK() OVER (
            PARTITION BY some_id 
            ORDER BY y DESC  -- 若前5%指y最小的部分，改为ASC
            ROWS BETWEEN 99 PRECEDING AND CURRENT ROW
        ) AS y_rank
    FROM some_table
) sub_query

注意事项 #

• 排序方向：如果"前5百分位"指y值最大的5%，用ORDER BY y DESC；如果是y值最小的5%，改为ORDER BY y ASC。
• 窗口行数不足100时：两种方法都会自动适配比例，比如窗口有50行时，前2-3行会被判定为前5%。

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