Freitag, August 24, 2012

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.

5 Kommentare:

  1. Dieser Kommentar wurde vom Autor entfernt.

    AntwortenLöschen
  2. Schöne Lösung.

    Ohne Analytics geht's aber auch :)

    WITH
    D AS (SELECT VKP, STARTDATE FROM TEST_VKP ORDER BY STARTDATE, VKP),
    E AS (SELECT D.*, ROWNUM R FROM D),
    F AS (SELECT DISTINCT
    COALESCE((SELECT MIN(R) FROM E X WHERE E.R < X.R AND E.VKP != X.VKP)-1
    ,(SELECT MAX(R) FROM E X WHERE E.R <= X.R AND E.VKP = X.VKP)) MX,
    COALESCE((SELECT MAX(R) FROM E X WHERE E.R > X.R AND E.VKP != X.VKP)+1
    ,(SELECT MIN(R) FROM E X WHERE E.R >= X.R AND E.VKP = X.VKP)) MN
    FROM E
    )
    SELECT A.VKP, A.STARTDATE, B.STARTDATE AS ENDDATE
    FROM F
    JOIN E A ON F.MN = A.R
    JOIN E B ON F.MX = B.R;

    AntwortenLöschen
  3. Hallo Jochen,

    auch hübsch - aber vielleicht noch kommentierungsbedürftiger als die Version mit den Analytics (die ihrerseits schon nach einem Kommentar schreit). Ich stelle mir gerade die Frage, ob es überhaupt einen Fall gibt, in denen analytische Funktionen etwas leisten, was man nicht auch mit einem entsprechenden SELF JOIN erreichen kann (Performance und Lesbarkeit des Codes ausgeklammert) - und vermute auf Anhieb, dass es den nicht gibt.

    Gruß

    Martin

    AntwortenLöschen
  4. Analytics stehen nicht in jeder Datenbank zur Verfügung. Wenn man sie hat kann man sich damit das Leben aber durchaus leichter machen (bzw. einfacher lesbaren Code schreiben).

    Ich erklär's mal kurz:
    - Der Ansatz ist ähnlich wie bei den Analytics - man sortiert die Datensätze, nummeriert sie fortlaufend und schaut dann nach dem jeweils ersten bzw. letzten Eintrag der Gruppe.
    - Zuerst wird das Ergebnis in die gewünschte Reihenfolge gebracht (Tabelle D)
    - Zu dem sortierten Ergebnis wird die Zeilennummer hinzugefügt (Tabelle E)
    - Tabelle F ermittelt die Grenzen der einzelnen Gruppen.
    - Die kleinste Zeilennummer nach der aktuellen, bei der sich das Gruppierungskriterium unterscheidet (VKP) ist der Beginn der nächsten Gruppe; subtrahieren wir davon 1, erhalten wir das Ende der aktuellen Gruppe.
    - Für den Sonderfall "Letze Gruppe" funktioniert dies nicht, hier bestimmen wir die größte Zeilennummer mit gleichem Gruppierungskriterium.
    - Analog dazu wird der Anfang der Gruppe ermittelt.
    - Nun haben wir für jedes Gruppierungskriterium Start und Ende ermittelt. Dies wird mittels distinct von Wiederholungen befreit.
    - Zuguterletzt wird die Zeilennummer wieder aufgelöst und das Start- und Enddatum ermittelt.
    - Vorteil dieses Ansatzes: Man kann im Ergebnis auf komplette Zeilen zugreifen, d.h. auch auf diejenigen Teile, die sich nicht trivial per min() und max() ermitteln lassen oder die nicht zur Gruppierung beitragen.

    Letzteres geht natürlich auch mit den Analytics, man muss das Beispiel dafür etwas erweitern. Dann schwindet der Lesbarkeitsvorteil etwas.

    AntwortenLöschen
  5. Hallo Jochen,

    vielen Dank für den ergänzenden Code-Kommentar. Beim Thema der Datenbankunabhängigkeit fallen mir immer diverse Aussagen von Tom Kyte ein, der davon wenig hält, und seine Abneigung damit gegründet, dass man die Features einer Datenbank, für die man viel Geld bezahlt, auch einsetzen sollte. Darüber hinaus ist mein Eindruck, dass Analytics auf einem recht guten Weg sind, in den allgemeinen SQL-Standard zu wandern. Aber auf jeden Fall ist es immer nützlich, möglichst viele SQL-Varianten für eine Fragestellung zur Verfügung zu haben, um etwaigen Schwächen des CBO (oder des Servers bei der Verarbeitung) aus dem Weg gehen zu können.

    Gruß

    Martin

    AntwortenLöschen