我有一个表格,里面装满了浮点值。我需要计算按平均值周围的分布分组的结果数量(高斯分布)。基本上,它是这样计算的:
SELECT COUNT(*), FloatColumn - AVG(FloatColumn) - STDEV(FloatColumn)
FROM Data
GROUP BY FloatColumn - AVG(FloatColumn) - STDEV(FloatColumn)
但出于显而易见的原因,SQL Server给出了此错误:Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause.
我的问题是,我可以以某种方式将此计算留给SQL Server吗?还是我必须以老式的方式做?检索所有数据,并自己进行计算?
要获取整个集合的聚合,您可以使用空的 OVER
子句
WITH T(Result)
AS (SELECT FloatColumn - Avg(FloatColumn) OVER() - Stdev(FloatColumn) OVER ()
FROM Data)
SELECT Count(*),
Result
FROM T
GROUP BY Result
SQL Fiddle
您可以执行数据的预聚合,并联接回表。
架构设置:
create table data(floatcolumn float);
insert data values
(1234.56),
(134.56),
(134.56),
(234.56),
(1349),
(900);
查询 1:
SELECT COUNT(*) C, D.FloatColumn - A
FROM
(
SELECT AVG(FloatColumn) + STDEV(FloatColumn) A
FROM Data
) preagg
CROSS JOIN Data D
GROUP BY FloatColumn - A;
结果:
| C | COLUMN_1 |
--------------------------
| 2 | -1196.876067819572 |
| 1 | -1096.876067819572 |
| 1 | -431.436067819572 |
| 1 | -96.876067819572 |
| 1 | 17.563932180428 |