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

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

DENSE_RANK может использоваться как агрегирующая или аналитическая функция.
Как агрегирующая функция определяет ранг следующей (не существующей, гипотетической) строки по отношению к заданному параметру.

with tbl as (
select 1 as cnt from dual
union all
select 2 as cnt from dual
union all
select 3 as cnt from dual
union all
select 4 as cnt from dual
union all
select 5 as cnt from dual)
SELECT DENSE_RANK(0) WITHIN GROUP (ORDER BY CNT DESC) FROM TBL -- результат 6

with tbl as (
select 1 as cnt from dual
union all
select 2 as cnt from dual
union all
select 3 as cnt from dual
union all
select 4 as cnt from dual
union all
select 5 as cnt from dual)
SELECT DENSE_RANK(5) WITHIN GROUP (ORDER BY CNT DESC) FROM TBl -- результат 1

Как аналитическая функция определяет относительный ранг строки в группе строк. Например, ранжирование по заработной плате, где D_RANK = 1 будет у сотрудника с наибольшей заработной платой

SELECT LAST_NAME, SALARY, DENSE_RANK () OVER (PARTITION BY DEPARTMENT_ID ORDER BY SALARY DESC) D_RANK
FROM HR.EMPLOYEES WHERE DEPARTMENT_ID = 100

Результат:

last_name	salary	d_rank
Greenberg	12008	1
Faviet	9000	2
Chen	8200	3
Urman	7800	4
Sciarra	7700	5
Popp	6900	6

FIRST, LAST для DENSE_RANK. Вывод минимальной и максимальной заработной платы по департаменту по сотрудникам.

SELECT last_name, department_id, salary,
MIN(salary) KEEP (DENSE_RANK FIRST ORDER BY commission_pct)
OVER (PARTITION BY department_id) ceil,
MAX(salary) KEEP (DENSE_RANK LAST ORDER BY commission_pct)
OVER (PARTITION BY department_id) fl
FROM hr.employees
WHERE department_id IN (100)
ORDER BY department_id, salary

Результат:

last_name	department_id	salary	ceil	fl
Popp	100	6900	6900	12008
Sciarra	100	7700	6900	12008
Urman	100	7800	6900	12008
Chen	100	8200	6900	12008
Faviet	100	9000	6900	12008
Greenberg	100	12008	6900	12008

Или номер строки, следует учесть что номеруются все уникальные значение строки. Все повторяющиеся выводятся, но с одним номером.

with tbl as (
select 1 as cnt from dual
union all
select 2 as cnt from dual
union all
select 2 as cnt from dual
union all
select 3 as cnt from dual
l)
select cnt, DENSE_RANK() over (order by cnt) from tbl

DUMP возвращает строку типа VARCHAR в которой содержится: код типа данных (см. таблицу встроенных типов данных) длину в байтах и внутреннее представление значения. Функция не поддерживает тип данных CLOB. Эта функция предусматривает 3 необязательных параметра – формат, начальная позиция и длина. Например:

select DUMP('abcd') from dual
union all
select DUMP('абвг') from dual
union all
select DUMP('12345') from dual
union all
select DUMP(12345) from dual
SELECT last_name, DUMP(last_name, 8, 3, 2) 
FROM hr.employees

EXP Число Эйлера (е = 2,71828 ) возведенное в указанную степень

SELECT EXP(2) FROM DUAL -- 7,389

FLOOR возвращает максимальное целое число которое меньше заданного. Аргументом может быть любой числовой тип данных, или такой который может быть преобразован к числовому.

SELECT FLOOR(123.1) FROM DUAL -- вернет123
union all
SELECT FLOOR(123.9) FROM DUAL -- вернет 123
union all
SELECT FLOOR(-123.9) FROM DUAL -- вурнет - 124

GREATEST вернет наибольшее из предложенных значений.

select GREATEST(1, 5,6.5 ,8, -7) from dual

LEAST возвращает наименьшее из предложенных значений

select LEAST(1, 5,6.5 ,8, -7) from dual

LENGTH возвращает длину числа или строки в символах.

select LENGTH(3) from dual -- вернет 1
union all
select LENGTH(12) from dual -- вернет 2
union all
select LENGTH(-1) from dual -- вернет 2
union all
select LENGTH(1023) from dual -- вернет 4
union all
select LENGTH('ЖОРА') from dual -- вернет 4

LENGTHB возвращает длину числа или строки в байтах

select LENGTHB(3) from dual -- вернет 1
union all
select LENGTHB(12) from dual -- вернет 2
union all
select LENGTHB(-1) from dual -- вернет 2
union all
select LENGTHB(1023) from dual -- вернет 4
union all
select LENGTHB('ЖОРА') from dual -- вернет 8

LN натуральный логарифм числа

select LN (2) from dual -- вернет  0,693147180559945

LOG логарифм за основанием m от n

select LOG (10, 100) from dual -- вернет  2
union all
select LOG (100, 10) from dual -- вернет  0,5

MAX, MIN возвращает максимальное, минимальное значение. Может использоваться как групповая или аналитическая функция
Например:

select MAX(salary) from HR.EMPLOYEES where department_id = 100
--или
select MIN(salary) from HR.EMPLOYEES where department_id = 100
--или
select salary, 
MAX(salary) over (partition by department_id), 
MIN(salary) over (partition by department_id) 
from HR.EMPLOYEES where department_id = 100

MEDIAN возвращает серединное значение. Может использоваться как функция аграгирования или аналитическая функция. Значения NULL игнорируются. БД использует номер строки в группе (N) для расчета веса (RN) этой строки по формуле RN = (1 + (0.5*(N-1)).
Итоговый результат рассчитывается как:
if (CRN = FRN = RN)
then (value of expression from row at RN)
else (CRN - RN) * (value of expression for row at FRN) + (RN - FRN) * (value of expression for row at CRN)
Где:
CRN = CEILING(RN)
FRN = FLOOR(RN)
Как функция агрегации:

with tbl as (
select 3 as cnt from dual
union all
select 4 as cnt from dual
union all
select 5 as cnt from dual
union all
select 5 as cnt from dual
union all
select 150 as cnt from dual)
select MEDIAN(cnt) from tbl -- вернет 5

with tbl as (
select 3 as cnt from dual
union all
select 4 as cnt from dual
union all
select 5 as cnt from dual
union all
select 150 as cnt from dual)
select MEDIAN(cnt) from tbl -- вернет 4,5

Как аналитическая функция

SELECT department_id, manager_id, employee_id, salary,
   MEDIAN(salary) OVER (PARTITION BY manager_id) "Median by Mgr"
   FROM hr.employees
   WHERE department_id in (110, 100)

MOD остаток от деления

select MOD(12,4) from dual -- результат 0

select MOD(11,4) from dual -- результат 3

select MOD(11,-4) from dual -- результат 3

select MOD(-11,4) from dual -- результат -3

select MOD(-11,-4) from dual -- результат -3