Master Yavor from Tryavna

Днес е борих с една интересна заявка и искам да споделя резултата. По принцип аналитичните функции не са ми непознати, но далеч не ги използвам всеки ден. За това видях доста зор за да измисля максимално добро решение.

И така, заданието се следното:
Имаме историческа таблица с колонки 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