Аналитический SQL Oracle за 20 минут

добавлено: 15 апр 15
понравилось:0
просмотров: 41230
комментов: 1

теги:

Автор: Myp3_u_K

Аналитический SQL демонстрирует исключительную производительность, запросы с использованием Аналитического SQL потребляют меньше ресурсов и выполняются быстрее.
Авторский курс. SQL от новичка до профессионала. Бесплатное вводное занятие. Сертификат. Записывайся!
Прокачаю до уровня БОГ!

изучаем SQL группа в контакте - присоединяйтесь

Применяется в основном для отчетности , для следующих типов задач
1. Запросы рейтингов, первых N
2. Запросы с накопительным итогом
3. Запросы с конструкцией окна
4. Может применятся и для оптимизации запросов

Какие бывают функции в аналитическом, разберём основные
ROW_NUMBER() – номер строки в группе
LAG(f, n,m) –f имя поля, n предыдущее значение в группе, m – знач по умолчанию
LEAD(f, n,m) -f имя поля, n последующее значение в группе, m – знач по умолчанию
FIRST_VALUE(f) – f имя поля, первое значение в группе ,
LAST_VALUE(f) –f имя поля, последнее значение в группе
STD_DEV(f) – f имя поля, значение стандартного распределения в группе
SUM(f) – f имя поля, накопительная сумма по группе
AVG (f)– f имя поля, среднее по группе заданной групп
RANK(f) – f имя поля, относительный ранг записи в группе


Синтаксис
SELECT аналитическая функция OVER([PARTITION партицирование…] 
ORDER BY (упорядочивание выражение 2 [,…] [{ASC/DESC}] [{NULLS FIRST/NULLS LAST}]) а



Где Партишен - это некоторое количество записей с общими ключами на которую будет распространятся действие Аналитической функции , сортировка внутри партишена осуществляется с помощью order by


Проще всего разобраться с аналитическими функциями на примерах.
Подготовим необходимые данные для демонстрации

Create table PersonA(Tbn number primary key, name varchar2(20), otd number, sal number); 
--Табельный номер , имя, отдел , зарплата

Insert into PersonA(Tbn,name,otd,sal) values(1, 'Аня',10,9000);
Insert into PersonA(Tbn,name,otd,sal) values(2, 'Саша',10,5500);
Insert into PersonA(Tbn,name,otd,sal) values(3, 'Таня',10,7000);
Insert into PersonA(Tbn,name,otd,sal) values(4, 'Ваня',20,2300);
Insert into PersonA(Tbn,name,otd,sal) values(5, 'Олег',20,4300);
Insert into PersonA(Tbn,name,otd,sal) values(6, 'Коля',20,3900);
Insert into PersonA(Tbn,name,otd,sal) values(7, 'Таня',30,7000);
Insert into PersonA(Tbn,name,otd,sal) values(8, 'Макс',30,9000);
Insert into PersonA(Tbn,name,otd,sal) values(9, 'Таня',30,8500);
Insert into PersonA(Tbn,name,otd,sal) values(10,'Макс',30,9900);
Insert into PersonA(Tbn,name,otd,sal) values(11,'Олег',30,9900);
Insert into PersonA(Tbn,name,otd,sal) values(12,'Макс',30,9900);
Insert into PersonA(Tbn,name,otd,sal) values(13,'Макс',30,9900);
Insert into PersonA(Tbn,name,otd,sal) values(14,'Макс',30,9900);
Insert into PersonA(Tbn,name,otd,sal) values(15,'Макс',30,9900);
Insert into PersonA(Tbn,name,otd,sal) values(16,'Макс',30,7000);
Insert into PersonA(Tbn,name,otd,sal) values(17,'Таня',30,3500);


Запросы списка лидеров
Первые три сотрудника с самой высокой зарплатой по отделам (партишен по отделу)
select * from ( select name , otd , sal ,  row_number() over (partition by otd order by sal desc) 
as num from personA) where num<4

Более корректно
select * from ( select name , otd , sal 
,  rank() over (partition by otd order by sal desc) as num from personA) where num<4
Внимание ! Пример демонстрирует отличие rank() от row_number()

По наименованию (партишен по отделу) сортировка по name
select * from ( select name , otd , sal ,  row_number() over (partition by otd order by name) as num 
from personA) where num<4


Накопительный итог по зарплате
select name , otd , sal ,  sum(sal) over (partition by otd order by sal) as num from personA

Среднее по зарплате в рамках отдела
select name , otd , sal ,  avg(sal) over (partition by otd order by sal) as num from personA

Демонстрация работы lag, leed - сотрудник , отдел , зарплата , сотрудник с более большей заплатой (maxsl),
, сотрудник с менее меньшей заплатой чем данный(minsl) в рамках отдела

select name , otd , sal ,  lead(name,1) over (partition by otd order by sal) as maxsl
, lag(name,1) over (partition by otd order by sal) as minsl
 from personA


запрос демонстрирует конструкцию окна в рамках отдела , среднее по зарплате, вычисляется, не только в рамках отдела , но так же и в рамках окна из 3х строк
select name , otd , sal 
,  avg(sal) over (partition by otd order by sal ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) as num from personA

в запросе используется конструкция окна ROWS BETWEEN N PRECEDING AND CURRENT ROW
то есть, для вычисления среднего avg(sal), считаются 3 предыдущие строки перед текущей строкой

select name , otd , sal 
,  first_value(name) over (partition by otd) as maxsl, last_value(name) over (partition by otd) as minsl
 from personA 

этот запрос демонстрирует применение аналитических функций first_value last_value


...

Комментарии




Необходимо войти на сайт, чтобы оставлять комментарии