时间戳

首页 > 解决方案 > 计算出一个滚动平均值，您并不总是除以总数

sql - 计算出一个滚动平均值，您并不总是除以总数

问题描述

我正在尝试计算板球击球手的滚动平均值。任何了解这项运动的人都会知道，平均值是按得分/局数计算的，除非击球手没有出局。如果击球手打了 2 局，并且其中 1 局“未出局”，则他们的平均数将计算为

runs scored innings 1 + runs scored innings 2 / 1

如果他们在两局中都出局，则计算为

runs scored innings 1 + runs scored innings 2 / 2

这很容易用于总体平均值，但是我想将其计算为运行平均值。在使用循环并单独计算每行的平均值之前，我已经这样做了，但是任何人都可以建议一种使用任何内置函数的方法来做到这一点吗？

当前代码示例：

with cte as (
select 
Innings_Player, 
Innings_Runs_Scored, 
Innings_Date, 
CASE WHEN Innings_Runs_Scored = "DNB" THEN null WHEN Innings_Runs_Scored LIKE "%*%" THEN REPLACE(Innings_Runs_Scored,"*","") ELSE Innings_Runs_Scored END AS RunsNum,
CASE WHEN Innings_Runs_Scored LIKE "%*%" THEN 1 ELSE 0 END AS NotOutFlag,
ROW_NUMBER() OVER (PARTITION BY Innings_Player ORDER BY Innings_Date) as RN
from TABLE
where Innings_Player = "JE Root"
AND Innings_Runs_Scored IS NOT NULL
ORDER BY Innings_Date
)
,cte2 as
(
select
 *,
   SUM(CAST(RunsNum AS INT64)) OVER (PARTITION BY Innings_Player ORDER BY RN) AS RunningTotal,
   AVG(CAST(RunsNum AS INT64)) OVER (PARTITION BY Innings_Player ORDER BY RN) AS RunningAvg,
 from cte
 where runsNum IS NOT NULL AND runsNum <> "TDNB"
)

select * from cte2

结果数据集：

数据集

所以，平均值是不正确的。对于滚动平均值，第三行的计算应该是前三行的 innings_run_scored，除以 2 而不是 3，正如您从 NotOutFlag 中看到的那样，列表中的 3 局未出局。

类似地，第 4 行应该除以 3，第 5 行除以 4，然后由于第 6 行也没有出来，第 6 行应该除以 4，第 7 行除以 5 等等。我认为等式是

Innings_Run_Scored / Innings - Not Out Count

标签： sqlgoogle-bigqueryaverage

解决方案

AVG基本上是SUM / COUNT因为你想改变COUNT我建议放弃使用 AVG 功能的部分。您可以使用SUMwithCASE仅计算 is 的NotOutFlag情况0

所以这条线

   AVG(CAST(RunsNum AS INT64)) OVER (PARTITION BY Innings_Player ORDER BY RN) AS RunningAvg,

会成为

   SUM(CAST(RunsNum AS INT64)) OVER (PARTITION BY Innings_Player ORDER BY RN) 
/  SUM(CASE WHEN NotOutFlag = 0 THEN 1 ELSE 0 END) OVER (PARTITION BY Innings_Player ORDER BY RN)
AS RunningAvg,

当然，您需要添加更多逻辑以避免除以 0。

CASE WHEN SUM(CASE WHEN NotOutFlag = 0 THEN 1 ELSE 0 END) OVER (PARTITION BY Innings_Player ORDER BY RN) = 0 
THEN 0 
ELSE 
SUM(CAST(RunsNum AS INT64)) OVER (PARTITION BY Innings_Player ORDER BY RN) 
/  
SUM(CASE WHEN NotOutFlag = 0 THEN 1 ELSE 0 END) OVER (PARTITION BY Innings_Player ORDER BY RN) 
END  AS RunningAvg,

推荐阅读