Оракл : достать вычисленное поле из предыдущей записи

← →
ANB (2008-03-19 10:43) [0]

Имеем (тестовая таблица, реал чуток сложнее) :
create table Test1
(
ID integer,
Group_ID integer,
F1 number,
F2 number,
F3 number
)
/
Insert into Test1
(ID, GROUP_ID, F1, F2, F3)
Values
(1, 1, 1, 1, 0)
/
Insert into Test1
(ID, GROUP_ID, F1, F2, F3)
Values
(2, 1, 2, 1, 2)
/
Insert into Test1
(ID, GROUP_ID, F1, F2, F3)
Values
(3, 1, 1, 2, 1)
/
Insert into Test1
(ID, GROUP_ID, F1, F2, F3)
Values
(4, 1, 2, 2, 16)
/
Insert into Test1
(ID, GROUP_ID, F1, F2, F3)
Values
(5, 1, 1, 2, 1)
/
Insert into Test1
(ID, GROUP_ID, F1, F2, F3)
Values
(6, 1, 2, 2, 3)
/
Insert into Test1
(ID, GROUP_ID, F1, F2, F3)
Values
(7, 1, 2, 2, 2)
/
Insert into Test1
(ID, GROUP_ID, F1, F2, F3)
Values
(8, 1, 2, 2, 5)
/
COMMIT
/

-- Скрипт :
declare
v_Over_Sum number;
v_Over number;
begin
dbms_output.enable(1000000);
v_Over_Sum := 0;
for R in (select T.* from Test1 T order by ID)
loop
v_Over := R.F1 + R.F2 + v_Over_Sum - R.F3;
if v_Over > 0 then
v_Over_Sum := v_Over_Sum + v_Over;
else
v_Over_Sum := 0;
end if;
dbms_output.put_line(R.ID||". v_Over = "||v_Over||", v_Over_Sum = "||v_Over_Sum);
end loop;
end;

Надо : вытащить тоже самое, что делает скрипт, запросом.
Типа
select T.*, 0 f_Over, 0 f_OverSum from Test1 T order by T.ID
есно, вместо нулей подставить выражения.

Что делал : перекопал всю аналитику, но эти функции не умеют доставать вычисленное поле, тока из таблицы.

← →
Johnmen © (2008-03-19 11:43) [1]

Что такое "вычисленное поле"?

PS
Думается, что здесь ошибка:

> v_Over := R.F1 + R.F2 + v_Over_Sum - R.F3;
> if v_Over > 0 then v_Over_Sum := v_Over_Sum + v_Over;

← →
ANB (2008-03-19 12:10) [2]

> Johnmen © (19.03.08 11:43) [1]

Нету там ошибки. Код фактически содран из реальной хранимки.
Описания и ТЗ нету, т.е. пока стоит задача ускорить, сохранив функционал.
Утвержается, что существующий алгоритм работает правильно (только очень долго). Нашел, что можно в нем оптимизнуть, но это даст прирос скорости процентов на 10. А надо - в 8 раз ускорить. Это только переводом на запросы можно сделать.

Вычисленное поле - v_Over_Sum. Я мог бы считать его на лету, обращаясь к предыдущей записи за предыдущим значением, но аналитическая функция его не видет.

вся загвоздка в
else
v_Over_Sum := 0;
end if;
Иначе все было бы намного проще : я уже и формулу вывел и SQL накопительные суммы без проблем считает. А вот зануление все режет - надо делать case, а потом ссылаться на него. и все. приехали.

← →
Johnmen © (2008-03-19 12:30) [3]

> ANB (19.03.08 12:10) [2]

1. Если я правильно понял по сути, что надо, то ответ - никак.
2. Если бы вдруг можно было бы, то сомневаюсь в сколь-нибудь существенном приращении скорости.
3. По поводу предполагаемой ошибки: какой логическо-математический смысл удваивать сумму на каждой итерации?

← →
ANB (2008-03-19 12:56) [4]

> какой логическо-математический смысл удваивать сумму на
> каждой итерации?

Да не удваивается сумма. Я проверял работу скрипта. Все верно. Это не удваивание, а учет накопившихся недоплат.

> то сомневаюсь в сколь-нибудь существенном приращении скорости.

Проверяли. По индексу нестед лупс - 20 часов. Фулл скан с хэш джойном - 1.5 часа.
А тут мало того, что по индексу идет, так еще и для каждой записи явно хранимка вызывается с примерно таким скриптом. Т.е. совсем тяжкий вариант. Но прежде чем оптимизить планы, надо от хранимки избавится. Милин.

← →
Johnmen © (2008-03-19 13:08) [5]

> Да не удваивается сумма.

Имелось в виду
v_Over_Sum=v_Over_Sum + v_Over
=v_Over_Sum+R.F1+R.F2+v_Over_Sum-R.F3
=2*v_Over_Sum+R.F1+R.F2-R.F3
Ну раз все верно, то и ладно...

> Проверяли.

Если проверяли, то, наверное, сделали. Тогда к чему вопрос [0]?

← →
ANB (2008-03-19 13:19) [6]

А. Точно. Вот так должно быть :

declare
v_Over_Sum number;
v_Over number;
begin
dbms_output.enable(1000000);
v_Over_Sum := 0;
for R in (select T.* from Test1 T order by ID)
loop
v_Over := R.F1 + R.F2 + v_Over_Sum - R.F3;
if v_Over > 0 then
v_Over_Sum := v_Over_Sum + R.F1 + R.F2 - R.F3;
else
v_Over_Sum := 0;
end if;
dbms_output.put_line(R.ID||". v_Over = "||v_Over||", v_Over_Sum = "||v_Over_Sum);
end loop;
end;

← →
Игорь Шевченко © (2008-03-19 14:25) [7]

ANB (19.03.08 13:19) [6]

Вроде написано понятно. А со всякими CASE и аналитикой вся понятность может уйти. Я бы так оставил :)

← →
Sergey13 © (2008-03-19 14:46) [8]

> [2] ANB (19.03.08 12:10)
> вся загвоздка в
> else
> v_Over_Sum := 0;
> end if;

Так может на клиенте это проще прогнать?

← →
ANB (2008-03-19 14:50) [9]

> А со всякими CASE и аналитикой вся понятность может уйти.
> Я бы так оставил :)

Медленно.

> Так может на клиенте это проще прогнать?

Еще медленнее, т.к. добавится траффик сервер - клиент.

Ответ на SQL.ru мне дали, тока под 10. А у меня 9-ка :(

ANB (19.03.08 14:50) [9]

> Ответ на SQL.ru мне дали, тока под 10

MODEL что ли ?

А покажи ответ (точнее ссылку)

http://www.sql.ru/forum/actualthread.aspx?tid=537562

Оракл : достать вычисленное поле из предыдущей записи Найти похожие ветки