BigQuery中累积分布窗口函数实现问题求助

架构老林

2026-7-3

解决窗口函数嵌套报错的问题

首先，你遇到的错误是因为SQL不允许在一个分析函数的参数中嵌套另一个分析函数，而且你的LAG(num_events, 0)用法完全多余——偏移量为0的LAG函数返回的就是当前行的num_events，直接引用当前行的字段即可。

针对你的需求（计算每一日之前的天数中，事件数少于当日的天数占比），可以用两种方式实现，取决于你是否要包含当日：

1. 包含当日的累积占比（累积分布函数逻辑）

如果需要统计从最早日期到当前日期的所有天数中，事件数少于当日的占比：

SELECT
    ref_date,
    num_events,
    AVG(CASE WHEN num_events < t.num_events THEN 1.0 ELSE 0 END)
        OVER (ORDER BY ref_date ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_distribution
FROM initial_table t;

这里用AVG直接计算符合条件的行数占比，1.0确保结果是浮点型，避免整数除法导致的精度丢失。
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW指定窗口范围为从第一行到当前行。

2. 仅统计当日之前的天数（不包含当日）

如果严格只计算当前日期之前的天数中事件数少于当日的占比：

SELECT
    ref_date,
    num_events,
    COALESCE(
        AVG(CASE WHEN num_events < t.num_events THEN 1.0 ELSE 0 END)
            OVER (ORDER BY ref_date ASC ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING),
        0
    ) AS cumulative_distribution
FROM initial_table t;