google-bigquery - 如何使用 BigQuery 获取每个记录组的最后 3 条记录的平均值？

Question

我有一个 BigQuery 表，其中包含ids和values。我想获得每个 id 的最后 3 条记录的平均值。

WITH t0 as (SELECT 1 as bigid, 1 as id, 10 as value
UNION ALL 
SELECT 2, 1, 14 
UNION ALL 
SELECT 3, 1, 18 
UNION ALL 
SELECT 4, 1, 11 
UNION ALL 
SELECT 5, 1, 13 
UNION ALL 
SELECT 6, 1, 14 
UNION ALL 
SELECT 7, 2, 19 
UNION ALL 
SELECT 8, 2, 19
UNION ALL 
SELECT 9, 2, 11 
UNION ALL 
SELECT 10, 2, 13 
UNION ALL 
SELECT 11, 2, 14 
UNION ALL 
SELECT 12, 2, 19 
UNION ALL 
SELECT 13, 3, 19)
SELECT id, AVG(value) OVER (PARTITION BY id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) FROM t0

此查询返回窗口内每条记录的结果。如何获取每个 id 的最后 3 条记录的平均值？

预期产出

标签： google-bigquery

Answer 1

您当前的查询是正确的，只是它ORDER BY在调用中缺少一个子句AVG：

WITH cte AS (
    SELECT *, AVG(value) OVER (PARTITION BY id ORDER BY bigid
                  ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) avg_last_3
    FROM t0
)

SELECT DISTINCT id, avg_last_3
FROM cte
ORDER BY id;