Tabibitosan

Vor längerer Zeit hatte ich hier schon mal einen Link auf die Technik Tabibitosan untergebracht, die Aketi Jyuuzou gelegentlich im OTN-Forum vorstellte. Da ich aber gestern auf die Frage nach dem Vorgehen beim Zusammenfassen wiederholter Werte in Intervallangaben auf Anhieb nur antworten konnte: es geht, aber wie, habe ich vergessen - hier noch mal eine kurze Darstellung. Zunächst erstellte ich ein paar Testdaten mit einer Spalte VKP (was Verkaufspreise sein könnten) und einer Datumsangabe:

create table test_vkp
(vkp number
, startdate date);

insert into test_vkp values (2, '01.01.2012');
insert into test_vkp values (2, '02.01.2012');
insert into test_vkp values (2, '03.01.2012');
insert into test_vkp values (3, '04.01.2012');
insert into test_vkp values (3, '05.01.2012');
insert into test_vkp values (3, '06.01.2012');
insert into test_vkp values (3, '07.01.2012');
insert into test_vkp values (3, '08.01.2012');
insert into test_vkp values (2, '09.01.2012');
insert into test_vkp values (2, '10.01.2012');
insert into test_vkp values (3, '11.01.2012');
insert into test_vkp values (3, '12.01.2012');
insert into test_vkp values (4, '13.01.2012');
insert into test_vkp values (5, '14.01.2012');
insert into test_vkp values (2, '15.01.2012');
insert into test_vkp values (2, '16.01.2012');

Ziel ist es nun, die wiederholten Einzelangaben zusammenzufassen und Intervalle zu bilden. Eine einfache Gruppierung mit MIN und MAX wäre dazu nicht in der Lage, da sie das erneute Auftreten eines Wertes nicht berücksichtigen kann:

select vkp
     , min(startdate) min_date
     , max(startdate) max_date
  from test_vkp
 group by vkp
 order by vkp;

VKP MIN_DATE   MAX_DATE
--- ---------- ----------
  2 01.01.2012 16.01.2012
  3 04.01.2012 12.01.2012
  4 13.01.2012 13.01.2012
  5 14.01.2012 14.01.2012

Schon auf den ersten Blick wird deutlich, dass diese Query die maximalen Intervalle liefert, in denen ein VKP-Wert vorkommt. Die Intervalle sind dabei nicht überschneidungsfrei - und absolut nicht das, was ich haben möchte.

Sinnvolle Intervalle kann man in einem solchen Fall mit Analytics bilden. Dabei erzeugt man zunächst eine laufende Nummer über alle Sätze und eine laufende Nummer pro Sub-Gruppe. Wenn man den zweiten Wert vom ersten subtrahiert, erhält man ein Ergebnis, das für die gesamte Gruppe einheitlich ist, und über das man dann gruppieren kann:

select t.*
     , row_number() over(order by startdate) a1
     , row_number() over(partition by vkp order by startdate) a2
     , row_number() over(order by startdate) - row_number() over(partition by vkp  order by startdate) a3
  from test_vkp t;

VKP STARTDATE          A1         A2         A3
--- ---------- ---------- ---------- ----------
  2 01.01.2012          1          1          0
  2 02.01.2012          2          2          0
  2 03.01.2012          3          3          0
  3 04.01.2012          4          1          3
  3 05.01.2012          5          2          3
  3 06.01.2012          6          3          3
  3 07.01.2012          7          4          3
  3 08.01.2012          8          5          3
  2 09.01.2012          9          4          5
  2 10.01.2012         10          5          5
  3 11.01.2012         11          6          5
  3 12.01.2012         12          7          5
  4 13.01.2012         13          1         12
  5 14.01.2012         14          1         13
  2 15.01.2012         15          6          9
  2 16.01.2012         16          7          9

with
basedata as (
select t.*
     , row_number() over(order by startdate) a1
     , row_number() over(partition by vkp order by startdate) a2
     , row_number() over(order by startdate) - row_number() over(partition by vkp order by startdate) a3
  from test_vkp t
)
select vkp
     , min(startdate) min_date
     , max(startdate) max_date
  from basedata
 group by vkp
        , a3
 order by min_date

VKP MIN_DATE   MAX_DATE
--- ---------- ----------
  2 01.01.2012 03.01.2012
  3 04.01.2012 08.01.2012
  2 09.01.2012 10.01.2012
  3 11.01.2012 12.01.2012
  4 13.01.2012 13.01.2012
  5 14.01.2012 14.01.2012
  2 15.01.2012 16.01.2012

Das ist natürlich erst mal ein recht simpler Fall, aber das Verfahren lässt sich ohne Weiteres auf komplexere Fragestellungen erweitern, was im oben erwähnten OTN-Beispiel auch getan wird.