Big Query如何计算分组滑动12个月窗口内最大3个值的平均值

你原有方案的优化思路如下：

1. 无需单独定义临时UDF，可直接将数组计算逻辑嵌入主查询中实现
2. 你原窗口逻辑使用ROWS行数限制，并非严格的12个月日期滑动窗口，若某id存在月份断档的情况，会出现窗口覆盖时间超出12个月的问题，可调整为日期范围窗口保证逻辑准确性。

对应的实现代码如下（兼容BigQuery语法，与你原有逻辑的结果完全对齐）：

SELECT
  id,
  date,
  ROUND(
    (
      SELECT AVG(val) 
      FROM UNNEST(val_arr) val 
      ORDER BY val DESC 
      LIMIT 3
    ), 2
  ) AS value
FROM (
  SELECT
    id,
    date,
    ARRAY_AGG(value) OVER (
      PARTITION BY id
      ORDER BY UNIX_DATE(date)
      -- 按12个月日期范围滑动，若引擎支持INTERVAL可替换为更直观的写法
      RANGE BETWEEN 365 PRECEDING AND CURRENT ROW
    ) AS val_arr
  FROM `your_table_name`
)

若你使用的SQL引擎支持INTERVAL类型的RANGE窗口，可将窗口部分调整为：

ARRAY_AGG(value) OVER (
  PARTITION BY id
  ORDER BY date
  RANGE BETWEEN INTERVAL 12 MONTH PRECEDING AND CURRENT ROW
) AS val_arr

如果不需要滑动窗口，仅按id分组取全量数据中最大的三个值计算平均值，可以通过排名过滤实现：

SELECT
  id,
  ROUND(AVG(value), 2) AS avg_top3_value
FROM (
  SELECT
    id,
    value,
    -- 若需保留并列值可替换为RANK/DENSE_RANK
    ROW_NUMBER() OVER (PARTITION BY id ORDER BY value DESC) AS rank_num
  FROM `your_table_name`
)
WHERE rank_num <= 3
GROUP BY id

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