BigQuery中如何获取窗口函数over输出最大值对应的其他列值

实现方案 #

你可以通过BigQuery的ARRAY_AGG窗口函数实现需求，核心逻辑是将窗口内每行的数值与对应日期打包为结构，按数值降序排序后取首行的日期即可，具体实现如下：

完整可运行代码

with o as (
    select date from unnest(GENERATE_TIMESTAMP_ARRAY('2021-01-01 00:00:00',current_timestamp(),interval 1 hour)) as date
), p as (
    select *,RAND()*100 as Number from o
), q as (
    select *,
        max(number) over(order by date) as best,
        -- 计算当前窗口内最大值对应的日期
        ARRAY_AGG(STRUCT(number, date) ORDER BY number DESC LIMIT 1)[OFFSET(0)].date as best_date
    from p
    order by date
)
select * from q

逻辑说明

• STRUCT(number, date) 将每行的数值和对应日期打包为结构化数据
• ORDER BY number DESC 让窗口内的所有行按数值从大到小排序
• LIMIT 1 仅保留排序后的第一行（即数值最大的行）
• [OFFSET(0)].date 取出数组中第一个结构的日期字段，即为最大值对应的日期

如果存在多个行数值相同且同为最大值的场景，可以在排序规则中新增日期维度的排序规则，比如：

• 要取最早出现的最大值日期：ORDER BY number DESC, date ASC
• 要取最晚出现的最大值日期：ORDER BY number DESC, date DESC

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