Tento článek je momentálně v ruštině — anglický překlad se připravuje.
SQRT достаёт квадратный корень из числа: SQRT(144) даёт 12, SQRT(2) — приближённое 1.41.... Вызывают её там, где нужен «корень из суммы квадратов» — геометрия расстояний между точками, дисперсия и стандартное отклонение, нормировка векторов. В PostgreSQL это рядовая функция, но у неё есть два острых угла, на которых регулярно спотыкаются в продакшене: отрицательный аргумент роняет весь запрос с ошибкой, а от выбора между numeric и double precision зависит и точность, и скорость.
Дальше разберём оба угла на боевых данных, а потом соберём из корня два рабочих инструмента — евклидово расстояние и стандартное отклонение руками — и сверим ручную формулу со встроенными функциями PostgreSQL. По ходу отметим, где MySQL и ClickHouse ведут себя иначе, чтобы перенос запроса не превратился в тихую порчу агрегатов.
Базовый синтаксис и типы
Начнём с самого скучного — корня из константы:
SELECT SQRT(144);
SELECT SQRT(2);
SQRT принимает double precision и его же возвращает. Но стоит передать numeric, и PostgreSQL подхватывает перегрузку для numeric: результат становится точным, зато считается медленнее.
SELECT SQRT(2::numeric);
SELECT pg_typeof(SQRT(2::numeric));
SELECT pg_typeof(SQRT(2::double precision));
Отсюда два практических следствия, о которых легко забыть:
- Корень из денег в
orders.amount (а это обычно numeric) сам останется numeric — приводить ничего не нужно.
- А вот в тяжёлой аналитике на миллионах строк осознанное приведение к
double precision экономит заметную долю времени: точность лишних знаков вам там не нужна, а скорость — очень.
Ошибка на отрицательном входе
Корень из отрицательного числа в области вещественных не определён, и PostgreSQL не пытается это замолчать. Он не возвращает NULL и не подсовывает nan — он бросает ошибку и останавливает запрос:
SELECT SQRT(-1);
Звучит безобидно, пока не вспомнишь масштаб: одна-единственная «плохая» строка валит весь SELECT целиком. Поэтому, если на входе теоретически может оказаться минус — скажем, вы считаете разность величин, — знак нужно убирать явно, до корня:
SELECT SQRT(ABS(balance)) AS magnitude,
SIGN(balance) AS direction
FROM (SELECT amount - 100 AS balance FROM orders) t;
- Гарантируйте знак заранее:
ABS(x) снимает минус, power(x, 2) делает выражение неотрицательным по построению.
- А вот
NULL на входе паники не вызывает: он спокойно даёт NULL на выходе. Это штатное поведение, не ошибка, — в отличие от отрицательного аргумента.
Подводный камень: тот же SQRT(-1) в MySQL вернёт NULL, а ClickHouse — nan, и ни один из них не споткнётся. Звучит «удобнее», но цена этому удобству — тихая порча данных: молчаливый NULL или nan просочится в SUM и AVG и перекосит агрегаты, и вы об этом узнаете гораздо позже, чем хотелось бы. Жёсткая ошибка PostgreSQL в этом смысле — друг, а не враг.
Евклидово расстояние
Самое классическое применение корня — расстояние между двумя точками по теореме Пифагора: SQRT(dx^2 + dy^2). Предположим, у пользователей есть координаты lat/lon, и мы хотим найти тех, кто ближе всего к офису:
SELECT id,
name,
SQRT(power(lat - 40.0, 2) + power(lon - (-3.7), 2)) AS dist
FROM users
ORDER BY dist
LIMIT 10;
И вот тут — приятный фокус. Если вам нужно лишь упорядочить точки по близости, корень можно вообще не брать: квадрат расстояния монотонен, то есть ближе по квадрату — ближе и по самому расстоянию.
SELECT id, name
FROM users
ORDER BY power(lat - 40.0, 2) + power(lon - (-3.7), 2)
LIMIT 10;
- Опускайте
SQRT, когда нужен порядок, а не конкретное число: корень — недешёвая операция, и на каждой строке она лишняя.
- Возводить в степень в PostgreSQL умеет и оператор
^ (x ^ 2), но power(x, 2) читается однозначнее и не путается с побитовым XOR из других языков.
Стандартное отклонение руками
В PostgreSQL уже есть stddev_pop и stddev_samp, и в бою стоит звать именно их. Но собрать дисперсию вручную полезно хотя бы раз — чтобы понимать, что у неё под капотом: это SQRT(AVG(x^2) - AVG(x)^2). Посчитаем разброс зарплат по отделам и тут же сверимся со встроенной функцией:
SELECT dept,
SQRT(AVG(power(salary, 2)) - power(AVG(salary), 2)) AS std_pop,
stddev_pop(salary) AS std_check
FROM employees
GROUP BY dept;
Колонки std_pop и std_check сойдутся до последнего знака. Ручная формула наглядно показывает, откуда вообще берётся корень в стандартном отклонении, и без переделок переезжает в оконную функцию:
SELECT id,
salary,
SQRT(
AVG(power(salary, 2)) OVER w
- power(AVG(salary) OVER w, 2)
) AS dept_std
FROM employees
WINDOW w AS (PARTITION BY dept);
- На
double precision выражение AVG(x^2) - AVG(x)^2 иногда даёт крошечный минус из-за погрешности округления — и тогда корень внезапно падает на математически нулевой дисперсии. Подстелите соломки: SQRT(GREATEST(variance, 0)).
- Для финансовой отчётности считайте в
numeric: да, медленнее, зато без сюрпризов с последними знаками после запятой.
Итог
SQRT прост ровно до того момента, пока данные ведут себя прилично. Дальше нужна дисциплина: закрывайтесь от отрицательного входа через ABS и GREATEST, выбирайте numeric против double precision не по привычке, а по задаче, и не зовите корень там, где важен только порядок. Соблюдайте эти три правила — и геометрия со статистикой в SQL останутся и быстрыми, и предсказуемыми, без тихих сюрпризов в агрегатах.
SQRTдостаёт квадратный корень из числа:SQRT(144)даёт12,SQRT(2)— приближённое1.41.... Вызывают её там, где нужен «корень из суммы квадратов» — геометрия расстояний между точками, дисперсия и стандартное отклонение, нормировка векторов. В PostgreSQL это рядовая функция, но у неё есть два острых угла, на которых регулярно спотыкаются в продакшене: отрицательный аргумент роняет весь запрос с ошибкой, а от выбора междуnumericиdouble precisionзависит и точность, и скорость.Дальше разберём оба угла на боевых данных, а потом соберём из корня два рабочих инструмента — евклидово расстояние и стандартное отклонение руками — и сверим ручную формулу со встроенными функциями PostgreSQL. По ходу отметим, где MySQL и ClickHouse ведут себя иначе, чтобы перенос запроса не превратился в тихую порчу агрегатов.
Базовый синтаксис и типы
Начнём с самого скучного — корня из константы:
SELECT SQRT(144); -- 12 SELECT SQRT(2); -- 1.4142135623730951SQRTпринимаетdouble precisionи его же возвращает. Но стоит передатьnumeric, и PostgreSQL подхватывает перегрузку дляnumeric: результат становится точным, зато считается медленнее.SELECT SQRT(2::numeric); -- 1.4142135623730950488... SELECT pg_typeof(SQRT(2::numeric)); -- numeric SELECT pg_typeof(SQRT(2::double precision)); -- double precisionОтсюда два практических следствия, о которых легко забыть:
orders.amount(а это обычноnumeric) сам останетсяnumeric— приводить ничего не нужно.double precisionэкономит заметную долю времени: точность лишних знаков вам там не нужна, а скорость — очень.Ошибка на отрицательном входе
Корень из отрицательного числа в области вещественных не определён, и PostgreSQL не пытается это замолчать. Он не возвращает
NULLи не подсовываетnan— он бросает ошибку и останавливает запрос:SELECT SQRT(-1); -- ERROR: cannot take square root of a negative numberЗвучит безобидно, пока не вспомнишь масштаб: одна-единственная «плохая» строка валит весь
SELECTцеликом. Поэтому, если на входе теоретически может оказаться минус — скажем, вы считаете разность величин, — знак нужно убирать явно, до корня:SELECT SQRT(ABS(balance)) AS magnitude, SIGN(balance) AS direction FROM (SELECT amount - 100 AS balance FROM orders) t;ABS(x)снимает минус,power(x, 2)делает выражение неотрицательным по построению.NULLна входе паники не вызывает: он спокойно даётNULLна выходе. Это штатное поведение, не ошибка, — в отличие от отрицательного аргумента.Евклидово расстояние
Самое классическое применение корня — расстояние между двумя точками по теореме Пифагора:
SQRT(dx^2 + dy^2). Предположим, у пользователей есть координатыlat/lon, и мы хотим найти тех, кто ближе всего к офису:SELECT id, name, SQRT(power(lat - 40.0, 2) + power(lon - (-3.7), 2)) AS dist FROM users ORDER BY dist LIMIT 10;И вот тут — приятный фокус. Если вам нужно лишь упорядочить точки по близости, корень можно вообще не брать: квадрат расстояния монотонен, то есть ближе по квадрату — ближе и по самому расстоянию.
SELECT id, name FROM users ORDER BY power(lat - 40.0, 2) + power(lon - (-3.7), 2) LIMIT 10;SQRT, когда нужен порядок, а не конкретное число: корень — недешёвая операция, и на каждой строке она лишняя.^(x ^ 2), ноpower(x, 2)читается однозначнее и не путается с побитовым XOR из других языков.Стандартное отклонение руками
В PostgreSQL уже есть
stddev_popиstddev_samp, и в бою стоит звать именно их. Но собрать дисперсию вручную полезно хотя бы раз — чтобы понимать, что у неё под капотом: этоSQRT(AVG(x^2) - AVG(x)^2). Посчитаем разброс зарплат по отделам и тут же сверимся со встроенной функцией:SELECT dept, SQRT(AVG(power(salary, 2)) - power(AVG(salary), 2)) AS std_pop, stddev_pop(salary) AS std_check FROM employees GROUP BY dept;Колонки
std_popиstd_checkсойдутся до последнего знака. Ручная формула наглядно показывает, откуда вообще берётся корень в стандартном отклонении, и без переделок переезжает в оконную функцию:SELECT id, salary, SQRT( AVG(power(salary, 2)) OVER w - power(AVG(salary) OVER w, 2) ) AS dept_std FROM employees WINDOW w AS (PARTITION BY dept);double precisionвыражениеAVG(x^2) - AVG(x)^2иногда даёт крошечный минус из-за погрешности округления — и тогда корень внезапно падает на математически нулевой дисперсии. Подстелите соломки:SQRT(GREATEST(variance, 0)).numeric: да, медленнее, зато без сюрпризов с последними знаками после запятой.Итог
SQRTпрост ровно до того момента, пока данные ведут себя прилично. Дальше нужна дисциплина: закрывайтесь от отрицательного входа черезABSиGREATEST, выбирайтеnumericпротивdouble precisionне по привычке, а по задаче, и не зовите корень там, где важен только порядок. Соблюдайте эти три правила — и геометрия со статистикой в SQL останутся и быстрыми, и предсказуемыми, без тихих сюрпризов в агрегатах.