如何用Google BigQuery标准SQL按组计算加权汇总结果？

structure(list(Year = c(1999, 1999, 1999, 2000, 2000, 2000), 
    Country = c("a", "b", "b", "a", "a", "b"), number = c(2, 
    3, 4, 5, 3, 6), result = c(2, 4, 5, 6, 2, 2)), class = c("tbl_df", 
"tbl", "data.frame"), row.names = c(NA, -6L))

需要计算加权结果weightresult，公式为：weightresult = result * (number / sum(number_year,country))，其中sum(number_year,country)是按Year、Country分组后number字段的总和。

structure(list(Year = c(1999, 1999, 1999, 2000, 2000, 2000), 
    Country = c("a", "b", "b", "a", "a", "b"), number = c(2, 
    3, 4, 5, 3, 6), result = c(2, 4, 5, 6, 2, 2), weight = c(2, 
    7, 7, 8, 8, 6), wre = c(2, 1.71428571428571, 2.85714285714286, 
    3.75, 0.75, 2)), row.names = c(NA, -6L), class = c("tbl_df", 
"tbl", "data.frame"))

structure(list(Country = c("a", "a", "b", "b"), Year = c(1999, 
2000, 1999, 2000), wre = c(2, 4.5, 4.57142857142857, 2)), class = c("tbl_df", 
"tbl", "data.frame"), row.names = c(NA, -4L))

尝试用以下Google BigQuery标准SQL获取结果时出现错误：

SELECT
Year,
Country,
(number/(SUM(number) OVER (PARTITION BY Year, Country))) * result AS wre,
Count(*),
FROM `table`
Where 
Year<=2020
GROUP BY Year,Country
ORDER BY Year,Country

错误提示：

SELECT list expression references column number which is neither grouped nor aggregated at ...

错误根源是混淆了窗口函数与聚合分组的逻辑：窗口函数SUM(number) OVER (...)会保留原表的每一行数据，但后续GROUP BY Year,Country做聚合时，number、result这类非分组、非聚合字段无法直接出现在SELECT列表中。

提供两种可实现目标结果的修正写法：

写法一：先计算每行加权值，再分组求和 #

SELECT
  Year,
  Country,
  SUM((number / total_number) * result) AS wre
FROM (
  SELECT
    Year,
    Country,
    number,
    result,
    SUM(number) OVER (PARTITION BY Year, Country) AS total_number
  FROM `table`
  WHERE Year <= 2020
)
GROUP BY Year, Country
ORDER BY Year, Country

写法二：简化聚合逻辑（更高效） #

利用数学等价转换：SUM(result*(number/S)) = SUM(result*number)/S（S为分组内number总和），直接用聚合函数计算：

SELECT
  Year,
  Country,
  SUM(result * number) / SUM(number) AS wre
FROM `table`
WHERE Year <= 2020
GROUP BY Year, Country
ORDER BY Year, Country

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