Примеры аналитических функций 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. Продолжение следует...
Внимание.
Комментировать могут только зарегистрированные пользователи.
Возможно использование следующих HTML тегов: <a>, <b>, <i>, <br>.
