计算异常值-嵌套聚合错误

Question

我目前在SQL Workbench/J和Amazon Redshift工作。

我正在进行一个查询，目的是识别数据集中异常值的数量。

我的源数据每天包含一条多个符号的记录。我正在使用30天的跟踪数据。简而言之，在30天的时间里，有10个符号，每个符号有30条记录。

然后，我使用以下查询来计算基于30天数据集的每个唯一符号的平均值、标准偏差和控制上限/下限。

select
symbol,
avg(high) as MEAN,
cast(stddev_samp(high) as dec(14,2)) STDV,
(MEAN+STDV*3) as UCL,
(MEAN-STDV*3) as LCL
from historical
group by symbol
;

我的下一步将计算"高"列中有多少个单独的值超过了控制上限计算值。我尝试添加以下count(case…(语句，但失败了：

select
symbol,
avg(high) as MEAN,
cast(stddev_samp(high) as dec(14,2)) STDV,
(MEAN+STDV*3) as UCL,
(MEAN-STDV*3) as LCL,
count(case when high>avg(high) then 1 else 0 end) as outlier
from historical
group by symbol
;

具体错误为

Amazon无效操作：聚合函数调用可能没有嵌套的聚合或窗口函数

这里使用count(case..)语句是正确的方法吗？或者推荐的方法或示例是什么？

Answer 1

有很多方法可以做到这一点，但我认为所有方法都涉及子查询。这是因为您将聚合(avg(与每行值(high(进行比较，然后对比较进行求和。

我将使用一个子查询，在该子查询中执行按符号划分的avg((窗口函数。这将给你每一行的组的平均值，然后按照你的要求进行查询

我目前在SQL Workbench/J和Amazon Redshift工作。

我正在进行一个查询，目的是识别数据集中异常值的数量。

我的源数据每天包含一条多个符号的记录。我正在使用30天的跟踪数据。简而言之，在30天的时间里，有10个符号，每个符号有30条记录。

然后，我使用以下查询来计算基于30天数据集的每个唯一符号的平均值、标准偏差和控制上限/下限。

按符号从历史分组中选择符号，avg(高(为MEAN，cast(stddev_samp(高((为dec(14,2((STDV，(MEAN+STDV3(为UCL，(MEAN-STDV 3(为LCL；

我的下一步将计算"高"列中有多少个单独的值超过了控制上限计算值。我尝试添加以下count(case…(语句，但失败了：

select symbol, avg(high) as MEAN, cast(stddev_samp(high) as dec(14,2)) STDV, (MEAN+STDV3) as UCL, 
(MEAN-STDV3) as LCL, count(case when high>group_avg then 1 else 0 end) as outlier
from (
select *, avg(high) over (partition by symbol) as group_avg
from historical ) 
group by symbol ;

(也可以将"avg(high("替换为MEAN；用"；min(group_avg(作为MEAN"；因为您已经在窗口函数中计算了平均值。只是一个可能的轻微优化。(

Answer 2

使用窗口函数计算标准偏差和平均值。然后聚合：

select symbol, mean, STDV,
(MEAN+STDV*3) as UCL, (MEAN-STDV*3) as LCL,
sum( (high > mean)::int) ) as outlier
from (select h.*,
avg(high) over (partition by symbol) as mean,
cast(stddev_samp(high) over (partition by symbol) as dec(14,2)) as STDV
from historical h
) h
group by symbol, mean, STDV;

你对"；"异常值"；这相当奇怪——仅仅高于平均水平的情况(非常粗略地(大约有一半的时间会发生。我看到的更典型的定义是在2个标准偏差的范围之外。

作为与SQL没有直接关系的注释。对我来说，使用未来数据来确定异常值似乎很不寻常。我预计会有30天的时间用于此目的。然而，这不是你在这里问的问题。