MySQL中单列均值、标准差及上下区间的SQL查询实现

需求说明 #

需要在MySQL环境中针对BANKNIFTY_cal_spread表的difference列完成三类计算：

• 单列数据均值（Mean）
• 单列数据标准差（Std dev）
• 基于上述两个值计算1倍标准差的上下区间值

执行SELECT * FROM BANKNIFTY_cal_spread;即可查看表内样例数据：

Date                 Current  Next       difference
2021-09-03 00:00:00 36914.8  37043.95    129.14999999999418
2021-09-06 00:00:00 36734    36869.15    135.15000000000146
2021-09-07 00:00:00 36572.9  36710.65    137.75
2021-09-08 00:00:00 36945    37065       120
2021-09-09 00:00:00 36770    36895.1     125.09999999999854

参考Python实现 #

该逻辑通过Python pandas可快速实现，代码如下：

nf_fut_mean = round(df['difference'].mean())
print(f"NF Future Mean: {nf_fut_mean}")
nf_fut_std = round(df['difference'].std())
print(f"NF Future Standard Deviation: {nf_fut_std}")

upper_range = round((nf_fut_mean + nf_fut_std))
lower_range = round((nf_fut_mean - nf_fut_std))

问题排查 #

初期编写SQL在Grafana告警查询构建器中执行时，始终无法返回正确结果，只能先通过Python DataFrame计算出均值、标准差、upper_range、lower_range字段后回写到MySQL表中使用。
经排查，问题出在初始查询语句中错误带入了Date这类非聚合字段，导致聚合计算逻辑异常。将Date字段从查询逻辑中移除后，即可得到符合预期的计算结果。

正确SQL实现 #

select average + std_deviation, average - std_deviation from (
    select avg(difference) as average, stddev_pop(difference) as std_deviation from BANKNIFTY_cal_spread
) sq

该语句通过子查询先计算全表difference列的均值和总体标准差，外层直接计算得到上下区间值，执行返回结果和Python计算结果完全一致。

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