Числовые функции в Oracle. Часть 1.

Описание числовых функций

ABS абсолютное значение числа. Пример:

select ABS(-4.25) from dual -- вернет 4.25
select ABS(3.75) from dual -- вернет 3.75

AVG среднее значение по полю. Пример:

select AVG(n) from (
select 4 as n from dual union 
select 6 as n from dual) -- вернет 5

BITAND логическое И. 2 входных параметры – NUMBER. Выходной параметр – NUMBER. Если один из входных параметров – NULL, на выходе NULL. Входные параметры преобразовываются к двоичной системе исчисления, складываются через логическое И. Результат преобразовывается к 10-чному виду.Например, bitand(6,4).
6 - 1 1 0
4 - 1 0 0
И - 1 0 0 - результат операции И. Т.е. 4 в десятичной системе.

select BITAND(6,4) from dual -- вернет 4

CEIL округление вверх. Возвращает минимальное целое число, которое больше или равно заданному.Например:

select CEIL(-4.25) from dual -- вернет -4
select CEIL(3.75) from dual -- вернет 4
select CEIL(3.05) from dual -- вернет 4
select CEIL(3) from dual -- вернет 3

COALESCE возвращает первое не нулевое значение. Так же работает с текстовыми значениями. Например:

select COALESCE (c_1, c_2, c_3) as COALESCE from (
select ''       as C_1, 'text_1' as C_2, ''       as C_3 from dual union 
select 'text_2' as C_1, ''       as C_2, 'text_3' as C_3 from dual union
select ''       as C_1, ''       as C_2, 'text_4' as C_3 from dual)

Результат:

COALESCE

text_1

text_2

text_4

CORR линейный коэффициент корреляции Пирсона. Значения от -1 до 1, где -1 высокая обратная корреляция, 1 – высокая корреляций, 0 – отсутствие корреляции. Применим к данным, которые могут быть преобразованы к числовому виду. Может быть использована как агрегирующая или аналитическая функция. Если хотя бы один параметр – NULL, возвращает NULL.
Рассчитывается как: COVAR_POP(expr1, expr2) / (STDDEV_POP(expr1) * STDDEV_POP(expr2))
Пример показывает взаимосвязь стажа сотрудника и заработной платы.

SELECT employee_id, job_id, TO_CHAR((SYSDATE - hire_date) YEAR TO MONTH ) Yrs_Mns, salary, 
CORR(SYSDATE-hire_date, salary) OVER(PARTITION BY job_id) correlation
FROM hr.employees
WHERE department_id in (50, 80)
ORDER BY job_id, employee_id

CORR_K коэффициент ранговой корреляции Кендалла. Применяется для выявления взаимосвязи между количественными или качественными показателями. Применим к данным, которые могут быть преобразованы к числу.
В примере оценивается взаимосвязь между заработной платой и процентом комиссии (commission percent)

SELECT 
CORR_K(salary, commission_pct, 'COEFFICIENT') coefficient,
CORR_K(salary, commission_pct, 'TWO_SIDED_SIG') two_sided_p_value
FROM hr.employees

CORR_S коэффициент ранговой корреляции Спирмена.
Пример:

SELECT COUNT(*) count,
CORR_S(salary, commission_pct) commission,
CORR_S(salary, employee_id) empid
FROM hr.employees

Функции CORR_K и CORR_S имеют необязательный параметр, которыйринимает одно из значений:
COEFFICIENT – коэффициент корреляции (по умолчанию)
ONE_SIDED_SIG – положительное одностороннее значение корреляции
ONE_SIDED_SIG_NEG – отрицательное одностороннее значение корреляции
TWO_SIDED_SIG – полное значение корреляции

COVAR_POP ковариация генеральной совокупности (population covariance) пары выражений с числовыми значениями.
Эквивалент (SUM(expr1 * expr2) - SUM(expr2) * SUM(expr1) / n) / n
COVAR_SAMP выборочная ковариация (sample covariance) пары выражений с числовыми значениями.
Эквивалент (SUM(expr1 * expr2) - SUM(expr1) * SUM(expr2) / n) / (n-1)
Где n – количество пар значений expr1, expr2, где ни один из них не NULL.
Пример:

SELECT job_id, 
COVAR_POP(SYSDATE-hire_date, salary) AS covar_pop,
COVAR_SAMP(SYSDATE-hire_date, salary) AS covar_samp
FROM hr.employees
WHERE department_id in (50, 80)
GROUP BY job_id
ORDER BY job_id, covar_pop, covar_samp

CUME_DIST функция распределения случайной величины (в теории вероятности). Может быть использована как агрегирующая или аналитическая функция.
Пример использования как агрегирующей функции:

SELECT CUME_DIST(15500, .05) WITHIN GROUP (ORDER BY salary, commission_pct) Cume_Dist
FROM hr.employees

Пример использования как аналитической функции:

SELECT job_id, last_name, salary, 
CUME_DIST() OVER (PARTITION BY job_id ORDER BY salary) AS cume_dist
FROM hr.employees
ORDER BY job_id, last_name, salary, cume_dist