標準偏差関数と平方偏差関数

平方偏差関数と標準偏差関数について、標本バージョンと母集団バージョンという 2 つのバージョンがサポートされています。どちらのバージョンを選択するかは、その関数が使用される統計上のコンテキストによって変わります。

すべての平方偏差関数と標準偏差関数は、クエリの GROUP BY 句で決定されるローの分割について値を計算できるという点で、真の集合関数であるといえます。MAX や MIN などのその他の基本集合関数と同様に、入力の NULL 値は無視されます。

パフォーマンスを向上させるために、データベースサーバは平均と平均からの偏差を同時に計算します。そのためデータを 1 パスするだけですみます。

また、分析対象の式のドメインに関係なく、すべての平方偏差と標準偏差は IEEE 倍精度浮動小数点を使用して計算されます。平方偏差関数や標準偏差関数の入力が空のセットである場合、各関数は結果で NULL を返します。単一ローに対して VAR_SAMP が計算されると NULL が返されます。VAR_POP の場合は値 0 が返されます。

サポートされる標準偏差関数と平方偏差関数を次に示します。

STDDEV 関数
STDDEV_POP 関数
STDDEV_SAMP 関数
VARIANCE 関数
VAR_POP 関数
VAR_SAMP 関数

例 1

次のクエリは、部門の平均給与に標準偏差を加えたものよりも多い給与を得ている従業員を示す結果セットを返します。標準偏差は、データがどれだけ平均からばらつきがあるかを計るものです。

SELECT *
FROM ( SELECT 
    Surname AS Employee, 
    DepartmentID AS Department, 
    CAST( Salary as DECIMAL( 10, 2 ) ) 
        AS Salary,
    CAST( AVG( Salary ) 
        OVER ( PARTITION BY DepartmentID ) AS DECIMAL ( 10, 2 ) ) 
        AS Average,
    CAST( STDDEV_POP( Salary )
        OVER ( PARTITION BY DepartmentID ) AS DECIMAL ( 10, 2 ) ) 
        AS StandardDeviation
    FROM Employees
    GROUP BY Department, Employee, Salary )
    AS DerivedTable
WHERE Salary > Average + StandardDeviation
ORDER BY Department, Salary, Employee;

次のテーブルは、クエリからの結果セットを示します。すべての部門で、少なくとも 1 人は平均から著しく外れた従業員がいます。

	Employee	Department	Salary	Average	StandardDeviation
1	Lull	100	87900.00	58736.28	16829.60
2	Scheffield	100	87900.00	58736.28	16829.60
3	Scott	100	96300.00	58736.28	16829.60
4	Sterling	200	64900.00	48390.95	13869.60
5	Savarino	200	72300.00	48390.95	13869.60
6	Kelly	200	87500.00	48390.95	13869.60
7	Shea	300	138948.00	59500.00	30752.40
8	Blaikie	400	54900.00	43640.67	11194.02
9	Morris	400	61300.00	43640.67	11194.02
10	Evans	400	68940.00	43640.67	11194.02
11	Martinez	500	55500.00	33752.20	9084.50

従業員 Scott は 96,300.00 ドルを得ていますが、部門の平均給与は 58,736.28 ドルです。この部門の標準偏差は 16,829.00 ドルです。つまり、平均給与以上でかつ 75,565.88 ドル (58736.28 + 16829.60 = 75565.88) に満たない給与は、平均から標準偏差内にあるということになります。従業員 Scott の給与は 96,300.00 ドルで、この値を超えています。

この例では、Surname と Salary が従業員ごとにユニークであることを想定していますが、ユニークである必要はありません。ユニーク性を保証するには、EmployeeID を GROUP BY 句に追加します。

例 2

次の文は、異なる期間における注文ごとの項目数で平均と平方偏差をリストします。

SELECT YEAR( ShipDate ) AS Year,
    QUARTER( ShipDate ) AS Quarter,
    AVG( Quantity ) AS Average,
    STDDEV_POP( Quantity ) AS Variance
FROM SalesOrderItems
GROUP BY Year, Quarter
ORDER BY Year, Quarter;

このクエリは、次の結果を返します。

Year	Quarter	Average	Variance
2000	1	25.775148	14.2794...
2000	2	27.050847	15.0270...
...	...	...	...

STDDEV_SAMP 関数

この関数は、数値式からなるサンプルの標準偏差を DOUBLE として計算します。たとえば、次の文は、異なる四半期における注文ごとの項目数で平均と平方偏差を返します。

SELECT YEAR( ShipDate ) AS Year,
    QUARTER( ShipDate ) AS Quarter,
    AVG( Quantity ) AS Average,
    STDDEV_SAMP( Quantity ) AS Variance
FROM SalesOrderItems
GROUP BY Year, Quarter
ORDER BY Year, Quarter;

このクエリは、次の結果を返します。

Year	Quarter	Average	Variance
2000	1	25.775148	14.3218...
2000	2	27.050847	15.0696...
...	...	...	...

VARIANCE 関数

VAR_POP 関数

この関数は、数値式からなる母集団の統計上の平方偏差を DOUBLE として計算します。たとえば、次の文は、異なる期間における注文ごとの項目数で平均と平方偏差をリストします。

SELECT YEAR( ShipDate ) AS Year,
    QUARTER( ShipDate ) AS Quarter,
    AVG( Quantity ) AS Average,
    VAR_POP( quantity ) AS Variance
FROM SalesOrderItems
GROUP BY Year, Quarter
ORDER BY Year, Quarter;

このクエリは、次の結果を返します。

Year	Quarter	Average	Variance
2000	1	25.775148	203.9021...
2000	2	27.050847	225.8109...
...	...	...	...

単一ローに対して VAR_POP が計算されると値 0 が返されます。

VAR_SAMP 関数

この関数は、数値式からなるサンプルの統計上の平方偏差を DOUBLE として計算します。

たとえば、次の文は、異なる期間における注文ごとの項目数で平均と平方偏差をリストします。

SELECT YEAR( ShipDate ) AS Year,
    QUARTER( ShipDate ) AS Quarter,
    AVG( Quantity ) AS Average,
    VAR_SAMP( Quantity ) AS Variance
FROM SalesOrderItems
GROUP BY Year, Quarter
ORDER BY Year, Quarter;

このクエリは、次の結果を返します。

Year	Quarter	Average	Variance
2000	1	25.775148	205.1158...
2000	2	27.050847	227.0939...
...	...	...	...

単一ローに対して VAR_SAMP が計算されると値 NULL が返されます。

参照

DocCommentXchange で意見交換できます