使用空值计算移动平均线

Question

我有一个按年份和子组设置的学校毕业数据，并提供了分子和分母以及一年毕业率，但我还需要计算 3 年移动平均线。 一位不再与我们合作的统计学家告诉我，要做到这一点，我需要获得分子 3 年的运行总计和分母 3 年的运行总计。我了解它背后的数学原理，并通过一些子组手动和通过 excel 检查了我的工作。我也使用 T-SQL 计算了这一点，只要没有空记录，就没有问题，但是当有空或 0 时，我正在努力计算。

我尝试使用 NULLIF 运行查询

null

ID,
Bldg,
GradClass,
Sbgrp ,
TGrads,
TStus,
Rate,
/*Numerator Running total*/
SUM (TGrads) OVER ( partition BY ID, Sbgrp ORDER BY GradClass ROWS BETWEEN 2 preceding AND CURRENT row ) AS NumSum,
/*Denominator Running Total*/
SUM ( TStus) OVER ( partition BY ID, Sbgrp ORDER BY GradClass ROWS BETWEEN 2 preceding AND CURRENT row ) AS DenSum,
/*Moving Year Average*/
(
( SUM ( TGrads) OVER ( partition BY DistrictID, Sbgrp ORDER BY GradClass ROWS BETWEEN 2 preceding AND CURRENT row ) ) / NULLIF ( ( SUM ( TStus) OVER ( partition BY ID, Sbgrp ORDER BY GradClass ROWS BETWEEN 2 preceding AND CURRENT row ) ), 0 ) * 100 
) AS 3yrAvg 
FROM
KResults.DGSRGradBldg

第一个问题，我得到了所有子组的记录，即使他们在子组中没有学生。我想保留记录，以便在学区内考虑所有子组，并且由于我知道他们没有数据，我可以用 0 替换 Tgrads， TStus 中的 Null 值吗？如果我确实将这些值替换为 0，我如何将速率显示为空？

第二个问题如何计算零分母或 0 分母的比率？我知道你不能除以 0，但我想保留记录，这样就很容易清楚地看到他们没有数据。我该怎么做？当我尝试在不考虑 Null 的情况下计算它时，我得到错误，1.)除以零遇到的错误。(8134) 和 2.)空值由聚合或其他 SET 操作消除。(8153).

知道我不能除以 0 或 Null，我修改了我的查询以包含 NULLIF，当我这样做时，查询运行没有错误，但我没有得到低于 100% 的准确百分比。我所有的汇率现在要么是 100% 要么是 0 - 请注意最后一行，2/3 的移动平均线不是 0。

如果我尝试将我的移动三年平均值显示为 0 的零值，数据如下所示。请注意，"移动三年平均"列显示所有 0。

ID   Bldg    Class   Sbggrp  TGrads  TStus      Rate    NumSum DenSum  3yrAvg  
A    1      2014       A1    46      49         93.9    46     49       0
A    1      2015       A1    41      46         89.1    87     95       0
A    1      2016       A1    47      49         95.9    134    144      0
A    1      2017       A1    38      40         95.0    126    135      0
A    1      2018       A1    59      59         98.3    143    148      0
A    1      2014       A2    1       1          100     1      1        100
A    1      2015       A2                               1      1        100
A    1      2016       A2                               1      1        100
A    1      2017       A2    2       3          66.7    2      3        0
A    1      2018       A2    2       2          100     4      5        0

任何建议将不胜感激，但请向这个新手提供建议。 感谢您的时间和帮助。

Answer 1

回答问题 1：输入选择条件

ISNULL(TGrads,0) AS TGRADS,
ISNULL(TStus,0) AS TSTUS,

回答问题2：我会这样做

(CASE WHEN SUM(TStus) OVER ( partition BY ID, Sbgrp ORDER BY GradClass ROWS BETWEEN 2 preceding AND CURRENT row ) IS NOT NULL
AND SUM(TStus) OVER ( partition BY ID, Sbgrp ORDER BY GradClass ROWS BETWEEN 2 preceding AND CURRENT row ) <>0
THEN (SUM(TGrads) OVER ( partition BY DistrictID, Sbgrp ORDER BY GradClass ROWS BETWEEN 2 preceding AND CURRENT row )  / (SUM(TStus) OVER ( partition BY ID, Sbgrp ORDER BY GradClass ROWS BETWEEN 2 preceding AND CURRENT row ) ) ) * 100
ELSE NULL END
) AS 3yrAvg 

我在"ELSE"之后输入空...您可以选择默认值。