Примеры аналитических функций Oracle

Статьи про Oracle -> Программирование

Примеры аналитических функций Oracle

v:1.0 29.08.2011
Петрелевич Сергей

В этой статье показаны различные практические примеры применения аналитических функций Oracle.

Таблица с нарастающим итогом

Подготовка тестовых данных.
Допустим, у нас есть таблица проводок по счетам и мы хотим получить баланс после совершения каждой проводки, т.е. нам надо к стартовой сумме прибавить или вычесть сумму первой проводки, к полученной сумме добавить или вычесть сумму второй и т.д. Другими словами, накапливать результат.
В стандартном SQL эта задача не решается, т.к. надо в какой-то переменной хранить сумму, но это можно сделать аналитической функцией Oracle.
Тестовые данные:

create table tBalance
(
 id    number,
 account varchar2(20),
 value  number
);

insert into tBalance values (1,'01',100);
insert into tBalance values (2,'01',200);
insert into tBalance values (3,'01',-100);
insert into tBalance values (4,'01',200);
insert into tBalance values (5,'01',100);
insert into tBalance values (6,'01',-100);
insert into tBalance values (7,'01',100);

insert into tBalance values (8, '02',10);
insert into tBalance values (9, '02',20);
insert into tBalance values (10,'02',-10);
insert into tBalance values (11,'02',-20);
insert into tBalance values (12,'02',10);
insert into tBalance values (13,'02',-10);
insert into tBalance values (14,'02',10);


И сам запрос:
select t.*, sum(t.value) OVER (PARTITION BY t.account order by t.id) as itogo
 from tBalance t


Все более чем просто.
Разделяем проводки по счетам, сортируем по Id и считаем сумму.

Вот еще пример.
Запрос извлекает из таблицы данные: id, time.
Для каждой строки запрос выводит значение поля time из предыдущей строки (строки отсортированы по времени).
Таким образом можно определить, например, сколько времени прошло между вставками этих строк.


 select t.id, t.time, 
       lag(t.time) OVER (PARTITION BY trunc(t.time) ORDER BY t.time) as prev
   from tTranzaction t

P.S. Продолжение следует...

Метки: SQL   аналитика  

Комментарии.

Внимание.
Комментировать могут только зарегистрированные пользователи.
Возможно использование следующих HTML тегов: <a>, <b>, <i>, <br>.