BigQuery中结合GROUP BY使用PERCENTILE_CONT实现聚合移动窗口的问题

云原生阿岚

2026-4-20

我明白你的需求啦——原来的移动平均容易受异常值影响，想换成移动窗口内的分位数（比如中位数）来做更稳健的统计，然后再取这个分位数序列的最大值对吧？

问题的核心在于：你需要的是基于时间范围的移动窗口内，计算value1的分位数，但BigQuery里直接用PERCENTILE_CONT作为窗口函数时，没办法同时指定时间窗口和分位数的计算字段（因为窗口的ORDER BY和分位数的排序字段会冲突）。不过我们可以用ARRAY_AGG先把窗口内的value1都收集起来，再对数组计算分位数，就能解决这个问题。

下面是修改后的完整代码，我把first_agg子查询替换成了用数组收集窗口值再计算分位数的版本，你可以根据需要调整分位数的数值（比如0.5就是中位数，0.75就是上四分位数）：

-- create table and fill in content
CREATE OR REPLACE TABLE my_project.my_db.my_table (
    timestamp TIMESTAMP,
    id INT64,
    value1 FLOAT64,
    value2 FLOAT64
);

INSERT INTO my_project.my_db.my_table (timestamp, id, value1, value2) VALUES
(TIMESTAMP '2023-01-01 00:00:00 UTC', 1, 0.0001, 20),
(TIMESTAMP '2023-01-01 00:00:01 UTC', 1, 0,      25),
(TIMESTAMP '2023-01-01 00:00:02 UTC', 1, 0.001,  30),
(TIMESTAMP '2023-01-01 00:00:05 UTC', 1, 0.002,  30),
(TIMESTAMP '2023-01-01 00:00:06 UTC', 1, 1,      30),
(TIMESTAMP '2023-01-01 00:00:08 UTC', 1, 0.0001, 30),
(TIMESTAMP '2023-01-01 00:00:09 UTC', 1, 0.0005, 30),
(TIMESTAMP '2023-01-01 00:00:10 UTC', 1, 0.0001, 30),
(TIMESTAMP '2023-01-01 00:00:05 UTC', 2, 0,      80),
(TIMESTAMP '2023-01-01 00:00:06 UTC', 2, 0,      110),
(TIMESTAMP '2023-01-01 00:00:07 UTC', 2, 0,      120),
(TIMESTAMP '2023-01-01 00:00:09 UTC', 2, 0.003,  130),
(TIMESTAMP '2023-01-01 00:00:10 UTC', 2, 0.003,  90),
(TIMESTAMP '2023-01-01 00:00:11 UTC', 2, 0,      80);

-- subqueries
WITH second_agg AS (
    SELECT
        id,
        COUNT(*) AS second_agg_count
    FROM
        my_project.my_db.my_table
    WHERE
        value2 > 100
    GROUP BY
        id
),
first_agg AS (
    SELECT
        id,
        MAX(first_agg_val) AS first_agg_max
    FROM  (
        SELECT
            id,
            -- 用ARRAY_AGG收集3秒移动窗口内的所有value1
            ARRAY_AGG(value1) OVER (
                PARTITION BY id
                ORDER BY UNIX_SECONDS(timestamp)
                RANGE BETWEEN 3 PRECEDING AND CURRENT ROW
            ) AS window_value1s,
            -- 对收集到的数组计算分位数（这里用0.5表示中位数，可按需修改）
            (SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY val) FROM UNNEST(window_value1s) val) AS first_agg_val
        FROM my_project.my_db.my_table
    )
    GROUP BY id
)
-- main query
SELECT
    t.id,
    MIN(t.value1) AS min_v1,
    MIN(t.timestamp) AS min_time,
    ANY_VALUE(IFNULL(sa.second_agg_count, 0)) AS second_agg_count,
    ANY_VALUE(IFNULL(fa.first_agg_max, 0)) AS first_agg_val
FROM
    my_project.my_db.my_table AS t
LEFT JOIN second_agg AS sa ON t.id = sa.id
LEFT JOIN first_agg AS fa ON t.id = fa.id
WHERE t.id < 10
GROUP BY t.id

解释一下这个思路：

先用ARRAY_AGG作为窗口函数，按照id分区、时间排序，把当前行及前3秒内的所有value1收集成一个数组；
然后用子查询UNNEST这个数组，再用PERCENTILE_CONT计算数组内的分位数，得到每一行对应的移动分位数；
最后和原来一样，对每个id取这些移动分位数的最大值。

这个方法完全兼容你原来的SQL结构，也能在SQLAlchemy里正常使用，不用担心适配问题~

备注：内容来源于stack exchange，提问作者Raphael