VAR: примеры (SQL)
VAR(expression numeric): floatФункция VAR в MS SQL Server
Функция VAR является статистической функцией в Microsoft SQL Server, предназначенной для вычисления дисперсии выборки на основе указанных значений. Дисперсия представляет собой меру разброса данных относительно среднего значения.
Использование функции VAR актуально в анализе данных, статистической обработке, финансовых расчетах и задачах бизнес-аналитики для оценки вариативности показателей.
Синтаксис функции:
VAR ( [ ALL | DISTINCT ] expression )
Аргументы функции:
- ALL: применяется ко всем значениям. Используется по умолчанию, если не указан иной модификатор.
- DISTINCT: учитывает только уникальные значения выражения.
- expression: выражение точного или приблизительного числового типа, за исключением типа bit. Агрегатные функции и вложенные запросы не допускаются.
Возвращаемое значение: результат типа float. Если набор данных содержит менее двух строк, функция возвращает NULL. При использовании DISTINCT с единственным значением результат также будет NULL.
Примеры использования VAR
Вычисление дисперсии всех значений в столбце:
SELECT VAR(ListPrice) AS VariancePrice
FROM Production.Product
WHERE ListPrice > 0;VariancePrice ------------- 6868959,8203
Использование с модификатором DISTINCT:
SELECT VAR(DISTINCT Weight) AS VarianceDistinctWeight
FROM Production.Product
WHERE Weight IS NOT NULL;VarianceDistinctWeight ---------------------- 1234,5678
Применение в группировке данных:
SELECT ProductCategoryID,
VAR(ListPrice) AS VarianceByCategory
FROM Production.Product
WHERE ListPrice > 0
GROUP BY ProductCategoryID;ProductCategoryID VarianceByCategory ---------------- ------------------- 1 256789,1234 2 98765,4321 3 456123,7890
Похожие функции в MS SQL
VARP: вычисляет дисперсию для всей генеральной совокупности. В отличие от VAR, которая использует формулу для выборки (n-1 в знаменателе), VARP применяет знаменатель n. Используется, когда данные представляют всю совокупность, а не выборку из нее.
STDEV: возвращает стандартное отклонение выборки, которое является квадратным корнем из дисперсии, вычисляемой функцией VAR. Предоставляет результат в тех же единицах измерения, что и исходные данные.
STDEVP: вычисляет стандартное отклонение для генеральной совокупности, аналогично тому, как VARP относится к VAR.
Выбор между VAR и VARP зависит от того, являются ли данные всей совокупностью или выборкой. STDEV и STDEVP удобнее для интерпретации, так как сохраняют масштаб исходных величин.
Типичные ошибки
Использование с нечисловыми типами данных вызывает ошибку.
SELECT VAR(ProductName) FROM Products;Сообщение 8117, уровень 16: Операнд типа varchar недопустим для функции VAR.
Игнорирование NULL значений может привести к неожиданным результатам, так как они не участвуют в вычислениях.
SELECT VAR(Weight) FROM Production.Product;NULL (если не NULL значений меньше двух)
Некорректное применение в оконных функциях без указания ORDER BY, когда требуется.
SELECT ProductID,
VAR(ListPrice) OVER(PARTITION BY ProductCategoryID) AS Variance
FROM Production.Product;Ошибка 10753, если в версиях до SQL Server 2022
Изменения в последних версиях
Начиная с SQL Server 2012, функция VAR может использоваться как оконная с предложением OVER. В более ранних версиях она была доступна только как агрегатная.
В SQL Server 2022 улучшена обработка оконных функций, включая VAR, в распределенных сценариях и расширена совместимость с стандартом SQL.
Для Azure SQL Database и управляемого экземпляра актуальные обновления оптимизируют производительность вычисления статистических функций на больших наборах данных.
Расширенные примеры
Использование VAR в оконной функции для анализа скользящей дисперсии:
SELECT OrderDate,
TotalDue,
VAR(TotalDue) OVER(ORDER BY OrderDate
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS Variance7Days
FROM Sales.SalesOrderHeader
ORDER BY OrderDate;OrderDate TotalDue Variance7Days ---------- --------- -------------- 2023-01-01 1000,00 NULL 2023-01-02 1200,00 NULL ... 2023-01-07 1100,00 12345,67 2023-01-08 1300,00 13456,78
Комбинирование VAR с другими агрегатными функциями:
SELECT ProductCategoryID,
COUNT(*) AS ProductCount,
AVG(ListPrice) AS AvgPrice,
VAR(ListPrice) AS VariancePrice,
SQRT(VAR(ListPrice)) AS StdDevPrice
FROM Production.Product
WHERE ListPrice > 0
GROUP BY ProductCategoryID
HAVING VAR(ListPrice) > 100000;ProductCategoryID ProductCount AvgPrice VariancePrice StdDevPrice ---------------- ------------ -------- ------------- ----------- 1 15 456,78 1234567,89 1111,11 3 22 789,01 9876543,21 3142,86
Вычисление дисперсии для выражений:
SELECT VAR(UnitPrice * OrderQty) AS VarianceRevenue
FROM Sales.SalesOrderDetail
WHERE UnitPrice > 0;VarianceRevenue --------------- 987654321,0987
Применение в CTE для многоэтапных вычислений:
WITH PriceStats AS (
SELECT ProductCategoryID,
VAR(ListPrice) AS CategoryVariance
FROM Production.Product
WHERE ListPrice IS NOT NULL
GROUP BY ProductCategoryID
)
SELECT AVG(CategoryVariance) AS AvgCategoryVariance
FROM PriceStats
WHERE CategoryVariance IS NOT NULL;AvgCategoryVariance ------------------- 3456789,0123
Аналоги в других СУБД
MySQL: функция VAR_SAMP() для дисперсии выборки и VARIANCE() как алиас VAR_SAMP(). Для генеральной совокупности используется VAR_POP().
SELECT VAR_SAMP(price) FROM products;12345,67
Oracle: функция VARIANCE() вычисляет дисперсию выборки. Для генеральной совокупности можно использовать вычисление через другие агрегаты.
SELECT VARIANCE(salary) FROM employees;9876543,21
PostgreSQL: VAR_SAMP() для выборки и VAR_POP() для генеральной совокупности.
SELECT VAR_SAMP(measurement) FROM sensor_data;456,789
SQLite: отсутствует встроенная функция. Дисперсию можно вычислить через подзапросы или расширения.
SELECT AVG((value - mean) * (value - mean)) AS variance
FROM data, (SELECT AVG(value) AS mean FROM data);12,345