时间戳

首页 > 解决方案 > 计算异常值 - 嵌套聚合错误

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

问题描述

我目前正在使用 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..)适合在这里使用，或者推荐的方法或示例是什么？

标签： sqlamazon-redshift

解决方案

有很多方法可以做到这一点，但我认为它们都涉及子查询。这是因为您将聚合（平均）与每行值（高）进行比较，然后将比较相加。

我会使用一个子查询，您可以在其中执行按符号分区的 avg() 窗口函数。这将为您提供每一行上组的平均值，然后按照您的要求进行查询。有点像这样：

我目前正在使用 SQL Workbench/J 和 Amazon Redshift。

我正在处理一个查询，旨在识别数据集中异常值的数量。

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

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

选择符号，avg(high) 作为 MEAN，cast(stddev_samp(high) as dec(14,2)) STDV，(MEAN+STDV3) 作为 UCL，(MEAN-STDV3) 作为 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) as MEAN”替换为“min(group_avg) as MEAN”，因为您已经在窗口函数中计算了平均值。只是可能的轻微优化。）

推荐阅读