时间戳

首页 > 解决方案 > 基于过去 1 年的 SQL 中的 Z 分数

sql - 基于过去 1 年的 SQL 中的 Z 分数

问题描述

我有以下格式的每日数据。请注意，这只是数据的一个子集，我必须进行一些修改才能共享它。

第一列是 [DataValue]，我需要通过 IndexValue、[Qualifier]、[QualifierCode] 和 [QualifierType] 找到 Z 分数。我那里也有 [Date] 列。

我基本上需要通过 IndexValue、[Qualifier]、[QualifierCode] 和 [QualifierType] 找到每个数据点的 Z-score 值。这里的重点是我有过去 3 年的数据，但为了计算 Z 分数，我只想取过去一年的平均值和标准差。

Z 分数 = [DataValue] -（过去 1 年的平均值）/（过去 1 年的标准偏差）

我正在为如何获得过去一年的平均水平而苦苦挣扎。有人能帮我解决这个问题吗？

SELECT [IndexValue]
  ,[Qualifier]
  ,[QualifierCode]
  ,[QualifierType],[Date]
  ,[Month]
  ,[Year]
  ,[Z-Score] = ([DataValue] - ROUND(AVG([DataValue]),3))/ ROUND(STDEV([DataValue]),3)
  FROM [TABLEA] 
   GROUP BY [IndexValue]
  ,[Qualifier]
  ,[QualifierCode]
  ,[QualifierType]
  ,[Date]
  ,[Month]
  ,[Year]
  order by [IndexValue]
  ,[Qualifier]
  ,[QualifierCode]
  ,[QualifierType]
  ,[Date] desc

: https://i.stack.imgur.com/pqhJD.png

标签： sqlstatisticsanalytics

解决方案

为此，您需要窗口函数：

SELECT a.*,
       ( (DataValue - AVG(DataValue) OVER ()) /
         STDEV(DataValue) OVER ()
       ) as z_score
FROM [TABLEA] a;

注意：如果data_value是整数，则需要将其转换为带数字的数字：

SELECT a.*,
       ( (DataValue - AVG(DataValue * 1.0) OVER ()) /
         STDEV(DataValue) OVER ()
       ) as z_score
FROM [TABLEA] a;

计算的四舍五入似乎偏离了基础，除非您的意图是产生一个类似于 z 的分数，而这并不是真正的 z 分数。

推荐阅读