如何修改Google Sheet QUERY公式计算加权平均时长？

原始数据 #

需求说明 #

需要按article_id分组，计算两个指标：

1. 每篇文章的总hit数
2. 以hit为权重的加权平均时长（例如article_id=1的加权平均时长为(180×2 + 20×4)/(2+4)=73.33）

修改后的公式 #

原公式中的AVG(C)是普通算术平均，无法实现加权计算，替换为加权总和除以总hit数的逻辑即可：

=QUERY(A:C,"SELECT A,SUM(B),SUM(B*C)/SUM(B) GROUP BY A LABEL SUM(B)'总hit数', SUM(B*C)/SUM(B)'加权平均时长'")

公式解释 #

• SUM(B)：直接计算每个分组的总hit数，逻辑和原公式一致
• SUM(B*C)/SUM(B)：
  • 分子SUM(B*C)：计算每一行hit与average_time的乘积之和，即加权总时长
  • 分母SUM(B)：该分组的总hit数
  • 两者相除得到加权平均时长
• LABEL子句：为计算结果列设置中文表头，提升可读性

可选优化：保留小数位数 #

如果需要固定小数位数（比如两位），可以用ROUND函数包裹加权平均的计算：

=QUERY(A:C,"SELECT A,SUM(B),ROUND(SUM(B*C)/SUM(B),2) GROUP BY A LABEL SUM(B)'总hit数', ROUND(SUM(B*C)/SUM(B),2)'加权平均时长'")

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