STDEV: примеры (SQL)

Простые примеры использования

Пример 1: Расчет стандартного отклонения для столбца.

-- Создаем временную таблицу с тестовыми данными
CREATE TABLE #Sales (Amount DECIMAL(10,2));
INSERT INTO #Sales VALUES (100.50), (150.75), (90.25), (200.00), (125.40);

SELECT STDEV(Amount) AS StDevAmount FROM #Sales;

StDevAmount
--------------------
40.93492884

Пример 2: Использование с аргументом DISTINCT.

INSERT INTO #Sales VALUES (100.50), (100.50); -- Добавляем дубликаты

SELECT 
  STDEV(ALL Amount) AS StDevAll,   -- Учитывает все строки
  STDEV(DISTINCT Amount) AS StDevDistinct -- Учитывает только уникальные значения
FROM #Sales;

StDevAll           StDevDistinct
------------------ ------------------
39.01371792        40.93492884

Пример 3: Функция возвращает NULL для набора из одной строки.

SELECT STDEV(Amount) AS StDevSingle FROM #Sales WHERE Amount = 1000;

StDevSingle
-----------
NULL

Похожие функции в MS SQL

• STDEVP: Вычисляет стандартное отклонение для всей генеральной совокупности. Формула использует знаменатель N, в отличие от STDEV, где знаменатель N-1 (коррекция Бесселя). Используется, когда данные представляют всю совокупность, а не выборку из нее.
• VAR: Вычисляет дисперсию выборки (квадрат стандартного отклонения). Мера разброса в квадратных единицах.
• VARP: Вычисляет дисперсию для всей генеральной совокупности.
• AVG: Вычисляет среднее арифметическое, которое является ключевым компонентом в формуле стандартного отклонения.

Выбор функции зависит от цели анализа: STDEV для выборки, STDEVP для полной совокупности. VAR удобна, когда нужна дисперсия для дальнейших расчетов.

Типичные ошибки

Ошибка 1: Использование с нечисловым типом данных, который не может быть неявно преобразован.

SELECT STDEV('text_value');

Сообщение 8117, Level 16, State 1
Operand data type varchar is invalid for stddev operator.

Ошибка 2: Непонимание контекста выполнения. STDEV - агрегатная функция, поэтому ее нельзя использовать без GROUP BY в одном запросе с обычными столбцами без их агрегации.

-- Ошибка: столбец Region не входит в агрегатную функцию и не указан в GROUP BY
SELECT Region, STDEV(SalesAmount) FROM Sales;

Сообщение 8120, Level 16, State 1
Column 'Sales.Region' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Ошибка 3: Ожидание результата от набора с одной строкой. Функция возвращает NULL, что может нарушить логику последующих расчетов.

DECLARE @SingleValue TABLE (Val INT);
INSERT INTO @SingleValue VALUES (10);
SELECT STDEV(Val) * 100 AS Result FROM @SingleValue; -- Результат NULL, а не 0

Result
-----------
NULL

Изменения в последних версиях

Функция STDEV присутствует в SQL Server многие годы, и ее основная функциональность не претерпела значительных изменений. Однако, начиная с SQL Server 2012, появилась возможность использования агрегатных функций, включая STDEV, в качестве оконных функций с предложением OVER. Это расширило сферу применения, позволив вычислять скользящее стандартное отклонение и другие аналитические показатели без свертки набора данных в одну строку.

-- Пример оконного использования (SQL Server 2012 и выше)
SELECT 
  OrderID, 
  SaleAmount,
  STDEV(SaleAmount) OVER(PARTITION BY Year(OrderDate)) AS StDevPerYear
FROM Orders;

Расширенные примеры

Пример 1: Расчет стандартного отклонения по группам.

SELECT 
  CategoryID,
  AVG(Price) AS AvgPrice,
  STDEV(Price) AS PriceStDev,
  -- Коэффициент вариации (относительная мера разброса)
  (STDEV(Price) / NULLIF(AVG(Price), 0)) * 100 AS CoeffOfVariationPercent
FROM Products
GROUP BY CategoryID;

Пример 2: Использование в оконной функции для анализа временного ряда (скользящее стандартное отклонение).

SELECT 
  TradeDate,
  ClosingPrice,
  AVG(ClosingPrice) OVER(ORDER BY TradeDate ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS MovingAvg,
  STDEV(ClosingPrice) OVER(ORDER BY TradeDate ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS MovingStDev
FROM StockPrices
ORDER BY TradeDate;

Пример 3: Комбинирование с другими агрегатными функциями в аналитическом запросе.

WITH SalesStats AS (
  SELECT 
    SalesPersonID,
    COUNT(*) AS OrderCount,
    AVG(TotalDue) AS AvgSale,
    STDEV(TotalDue) AS StDevSale
  FROM Sales.SalesOrderHeader
  GROUP BY SalesPersonID
)
SELECT 
  *,
  -- Выделяем аномальные средние продажи (за пределами одного стандартного отклонения)
  CASE 
    WHEN AvgSale > (SELECT AVG(AvgSale) + AVG(StDevSale) FROM SalesStats) THEN 'Высокое'
    WHEN AvgSale < (SELECT AVG(AvgSale) - AVG(StDevSale) FROM SalesStats) THEN 'Низкое'
    ELSE 'Ожидаемое'
  END AS SaleCategory
FROM SalesStats;

Пример 4: Фильтрация значений с помощью подзапроса на основе стандартного отклонения (выборка за пределами 2-х сигм).

DECLARE @AvgVal FLOAT, @StDevVal FLOAT;
SELECT 
  @AvgVal = AVG(Measurement), 
  @StDevVal = STDEV(Measurement) 
FROM SensorData WHERE SensorID = 1;

SELECT * 
FROM SensorData 
WHERE SensorID = 1 
  AND ABS(Measurement - @AvgVal) > 2 * @StDevVal; -- Выбросы

Аналоги функции в других СУБД и языках

• MySQL: Функция STDDEV_SAMP() (синоним STD()) для выборки и STDDEV_POP() для совокупности.

SELECT STDDEV_SAMP(amount) FROM sales;

• PostgreSQL: STDDEV_SAMP() и STDDEV_POP(). Также есть агрегатная функция stddev(), которая является алиасом для STDDEV_SAMP.

SELECT STDDEV_SAMP(amount) FROM sales;

• Oracle: STDDEV() (для выборки) и STDDEV_POP(). Функция STDDEV игнорирует NULL значения.

SELECT STDDEV(amount) FROM sales;

• SQLite: Встроенной функции нет, но ее можно вычислить через квадратный корень от дисперсии с помощью подзапросов или расширения math.

SELECT sqrt(avg(amount*amount) - avg(amount)*avg(amount)) AS stdev FROM sales;

• Python (NumPy): numpy.std(array, ddof=1) для выборки (где ddof=1) и numpy.std(array, ddof=0) для совокупности.

import numpy as np
stdev_sample = np.std([100.5, 150.75, 90.25], ddof=1)