Вопрос или проблема
Теперь для расчета стандартного отклонения я использую следующий запрос в Oracle
SELECT rating,
STDDEV(duration) AS StD
FROM ratings
GROUP BY rating
и следующий для SQL Server
SELECT rating,
STDEV(duration) AS StD
FROM ratings
GROUP BY rating
Но они различаются только по используемой функции стандартного отклонения — STDDEV в Oracle и STDEVP в SQL Server.
Как унифицировать этот запрос, не используя представления, в обеих СУБД? Невозможно создать пользовательскую агрегатную функцию или использовать stdev в пользовательской функции в SQL Server. Я мог бы использовать пакет в Oracle, если бы это позволило мне достичь этого.
Их расчеты различаются, поэтому они дают разные результаты.
- Oracle’s
STDDEVвычисляет выборочное стандартное отклонение (делит на N – 1). - SQL Server’s
STDEVPвычисляет популяционное стандартное отклонение (делит на N)
Чтобы рассчитывать вручную, используя SQL, общий для обоих:
Выборочное стандартное отклонение
SELECT
rating,
CASE WHEN COUNT(*) > 1 THEN
SQRT(
(
SUM(duration * duration) - POWER(SUM(duration), 2) / COUNT(*)
) / (COUNT(*) - 1)
)
ELSE
NULL
END AS StD
FROM ratings
GROUP BY rating;
Популяционное стандартное отклонение
SELECT
rating,
SQRT(
(
SUM(duration * duration) - POWER(SUM(duration), 2) / COUNT(*)
) / COUNT(*)
) AS StD
FROM ratings
GROUP BY rating;
.
Ответ или решение
Для расчета стандартного отклонения в системах управления базами данных (СУБД) Oracle и SQL Server часто используются разные функции: Oracle применяет STDDEV, а SQL Server — STDEV, что может приводить к различиям в результатах вычислений. Важно понимать эти различия для корректного объединения запросов, так как одна функция рассчитывает выборочное стандартное отклонение, а другая — генеральное.
Различия в расчетах стандартного отклонения
-
Oracle (
STDDEV): Рассчитывает выборочное стандартное отклонение, используя формулу:
[
s = \sqrt{\frac{\sum (x_i – \bar{x})^2}{n – 1}}
]
где ( n ) — размер выборки. -
SQL Server (
STDEV): Также рассчитывает выборочное стандартное отклонение, аналогично Oracle. В то же время, функцияSTDEVPв SQL Server используется для вычисления генерального стандартного отклонения, основываясь на формуле:
[
\sigma = \sqrt{\frac{\sum (x_i – \mu)^2}{N}}
]
где ( N ) — размер генеральной совокупности.
Из-за различий в использовании этих функций в двух СУБД вы можете столкнуться с проблемами при необходимости единого синтаксиса.
Унификация синтаксиса для обоих СУБД
Для того чтобы написать единый запрос, который будет работать как в Oracle, так и в SQL Server, можно использовать математические формулы вместо встроенных функций.
Выборочное стандартное отклонение
SELECT
rating,
CASE
WHEN COUNT(*) > 1 THEN
SQRT(
(SUM(duration * duration) - POWER(SUM(duration), 2) / COUNT(*)) / (COUNT(*) - 1)
)
ELSE
NULL
END AS StD
FROM ratings
GROUP BY rating;В этом запросе используется стандартная формула для выборочного стандартного отклонения. Условное выражение CASE гарантирует, что стандартное отклонение не будет рассчитано, если выборка содержит менее двух значений.
Генеральное стандартное отклонение
Если необходимо получить общее стандартное отклонение, следуя аналогичному подходу, то запрос будет выглядеть так:
SELECT
rating,
CASE
WHEN COUNT(*) > 0 THEN
SQRT(
(SUM(duration * duration) - POWER(SUM(duration), 2) / COUNT(*)) / COUNT(*)
)
ELSE
NULL
END AS StD
FROM ratings
GROUP BY rating;Заключение
Таким образом, используя предложенные выше SQL-запросы, вы сможете унифицировать подход к расчету стандартного отклонения для обеих СУБД без создания представлений или пользовательских агрегатных функций. Оба подхода обеспечивают согласованность результатов и позволяют учитывать различия в методах расчета.