Днес е борих с една интересна заявка и искам да споделя резултата. По принцип аналитичните функции не са ми непознати, но далеч не ги използвам всеки ден. За това видях доста зор за да измисля максимално добро решение.
И така, заданието се следното:
Имаме историческа таблица с колонки id, name, date_from, date_to (и други). Примерно:
ID Name Date_from Date_to
------------------------------
1 AAA 01.Nov.08 03.Nov.08
2 AAA 03.Nov.08 04.Nov.08
3 BBB 04.Nov.08 05.Nov.08
4 BBB 05.Nov.08 08.Nov.08
5 BBB 08.Nov.08 09.Nov.08
6 CCC 09.Nov.08 11.Nov.08
7 CCC 11.Nov.08 12.Nov.08
8 AAA 12.Nov.08 13.Nov.08
9 AAA 13.Nov.08 15.Nov.08
10 AAA 15.Nov.08 (null)
Целта е с една заявка да се хване всяка промяна на името – от кога е станала, на какво е променено и до кога важи. В конкретния пример трябва да се покаже следното:
ID Name Date_from Date_to
------------------------------
1 AAA 01.Nov.08 04.Nov.08
3 BBB 04.Nov.08 09.Nov.08
6 CCC 09.Nov.08 12.Nov.08
8 AAA 12.Nov.08 (null)
За начало започнах с опростена задача – да се покаже стойността и датата, от която е валидна. По грубия начин с вложени заявки това може да стане така:
select distinct name,
(select min(date_from)
from table1 p2
where p2.name = p1.name
and p2.date_from > (select max(date_from)
from table1 p3
where p3.name != p2.name
and p3.date_from < p1.date_from)) date_from
from table1 p1
Това решение е много лошо. Първо, кода е доста неразбираем. Но по-големия проблем е, че Oracle ще прерови 3 пъти таблицата. Ако таблицата стане по-голяма, никакви оптимизации няма да помогнат. Освен това за да взема и date_to ми трябват още 2 преравяния в таблицата - общо 5. Ужас! За това още отначало се насочих към аналитичните функции.
Хубавото на аналитичните функции е, че може да се направят много неща с едно минаване през данните. Лошото е, че ако не си свикнал да ги използваш, може да ти е трудно в началото. А понеже аз ги използвам адски рядко, всеки път ми е ново начало.
Една от идеите на аналитичните функции е, че може да си дефинираш "прозорец" от данни, в които да търсиш нещо. Това е и пътя, по който (погрешно) тръгнах в началото. Харесах си функцията FIRST_VALUE и се засилих да дефинирам разни брадати прозорци:
select name,
FIRST_VALUE(date_from) OVER (partition by name ORDER BY date_from ASC range between ??? PRECEDING and UNBOUNDED FOLLOWING)
from table1
Лошото е, че синтаксиса на дефинирането на прозорци е доста слабо описан в документацията. Доста се поблъсках, но не можех да го измисля. В един момент, понеже е и малко спешно, реших да поискам помощ от другарчетата в oracle-l. Колкото и дa е срамно за мен, почти веднага получих 5 решения, като и петте нямах нищо общо с моя подход. Явно много хора са се борили с това и си го знаят. Решението на малката задачка е с функцията LAG. За да стане ясно, ще го разкажа малко по малко.
Първо, започваме с тази заявка:
select name, date_from, lag(name, 1, '*') over(order by date_from) prev_name from table1
Тази заявка ще върне за всеки ред името от реда, записаната в реда дата и името от предния ред, като редовете са подредени по Date_from
:
Name Date_from prev_name
--------------------------
AAA 01.Nov.08 *
AAA 03.Nov.08 AAA
BBB 04.Nov.08 AAA
BBB 05.Nov.08 BBB
BBB 08.Nov.08 BBB
CCC 09.Nov.08 BBB
CCC 11.Nov.08 CCC
AAA 12.Nov.08 CCC
AAA 13.Nov.08 AAA
AAA 15.Nov.08 AAA
Следващата стъпка е проста. Вземаме само редовете, в които има промяна, т.е. редовете, за които name
е различно от prev_name
.
select name, date_from
from (select name, date_from, lag(name, 1, '*') over(order by date_from) prev_name from table1)
where name != prev_name
Получаваме отговора на малката задача:
Name Date_from
-----------------
AAA 01.Nov.08
BBB 04.Nov.08
CCC 09.Nov.08
AAA 12.Nov.08
След това трябваше да добавя и датата, до която е била валидна дадена стойност. Тук отново използвах LAG, с която намерих за всеки от показаните редове, до кога е бил валиден предишния (пък бил той и непоказан) ред:
select name, date_from, prev_date_to
from (select name, date_from, lag(name, 1, '*') over(order by date_from) prev_name, lag(date_to, 1) over(order by date_from) prev_date_to from table1)
where name != prev_name
След това с функцията LEAD изместх във външната заявка prev_date_to към предния ред. В крайна сметка се получи следното хитро решение:
select id, name, date_from, lead(prev_date, 1, null) over (order by date_from) date_to
from (select id, name, date_from,
lag(name, 1, '*') over(order by date_from) prev_name,
lag(date_to, 1, null) over(order by date_from) prev_date
from table1
where ...)
where name != prev_name