Vor einiger Zeit hat mein Kollege Philipp Salvisberg ein paar interessante Beiträge zum Thema Joinen und Mergen der Zeitintervalle gepostet. Neulich war ich auf der Suche nach Anwendungsbeispielen für die neue MATCH_RECOGNIZE Klausel, die in der 12c Datenbank eingeführt wurde und fand heraus, dass die Abfragen damit deutlich vereinfacht werden.

Um das Problem und die Lösung zu verstehen, würde ich empfehlen, die ursprünglichen Beiträge zu lesen:

Joining Temporal Intervals
Merging Temporal Intervals with Gaps
Joining Temporal Intervals Part 2

Kurz zusammengefasst: Wir haben die Versionen der bekannten Tabellen EMPV, DEPTV and JOBV mit einer zeitlichen Gültigkeit, gesteuert über die Datumsspalten VALID_FROM and VALID_TO. Wir möchten die Daten joinen mit Berücksichtigung der Gültigkeit, der möglichen Lücken und dabei die angrenzenden Zeitintervalle bei Gleichheit der Daten zusammenführen. Ein Skript zum Erstellen und Befüllen der Tabellen gibt es hier.

Das ist die ursprüngliche Abfrage:

WITH
   joined AS (
      -- gap-aware temporal join
      -- produces result_cols to calculate new_group in the subsequent query
      SELECT e.empno,
             g.valid_from,
             LEAST(
                e.valid_to,
                d.valid_to,
                j.valid_to,
                NVL(m.valid_to, e.valid_to),
                LEAD(g.valid_from - 1, 1, e.valid_to) OVER(
                   PARTITION BY e.empno ORDER BY g.valid_from
                )
             ) AS valid_to,
             (
                e.ename
                || ',' || j.job
                || ',' || e.mgr
                || ',' || m.ename
                || ',' || TO_CHAR(e.hiredate,'YYYY-MM-DD')
                || ',' || e.sal
                || ',' || e.comm
                || ',' || e.deptno
                || ',' || d.dname
             ) AS result_cols,
             e.ename,
             j.job,
             e.mgr,
             m.ename AS mgr_ename,
             e.hiredate,
             e.sal,
             e.comm,
             e.deptno,
             d.dname            
        FROM empv e
       INNER JOIN (SELECT valid_from FROM empv
                   UNION
                   SELECT valid_from FROM deptv
                   UNION
                   SELECT valid_from FROM jobv
                   UNION
                   SELECT valid_to + 1 FROM empv
                    WHERE valid_to != DATE '9999-12-31'
                   UNION
                   SELECT valid_to + 1 FROM deptv
                    WHERE valid_to != DATE '9999-12-31'
                   UNION
                   SELECT valid_to + 1 FROM jobv
                    WHERE valid_to != DATE '9999-12-31') g
          ON g.valid_from BETWEEN e.valid_from AND e.valid_to
       INNER JOIN deptv d
          ON d.deptno = e.deptno
             AND g.valid_from BETWEEN d.valid_from AND d.valid_to
       INNER JOIN jobv j
          ON j.jobno = e.jobno
             AND g.valid_from BETWEEN j.valid_from AND j.valid_to
        LEFT JOIN empv m
          ON m.empno = e.mgr
             AND g.valid_from BETWEEN m.valid_from AND m.valid_to
   ),
   calc_various AS (
      -- produces columns has_gap, new_group
      SELECT empno,
             valid_from,
             valid_to,
             result_cols,
             ename,
             job,
             mgr,
             mgr_ename,
             hiredate,
             sal,
             comm,
             deptno,
             dname,
             CASE
                WHEN LAG(valid_to, 1, valid_from - 1) OVER(
                        PARTITION BY empno ORDER BY valid_from
                     ) = valid_from - 1 THEN
                   0
                ELSE
                   1
             END AS has_gap,
             CASE
                WHEN LAG(result_cols, 1, result_cols) OVER (
                        PARTITION BY empno ORDER BY valid_from
                     ) = result_cols THEN
                   0
                ELSE
                   1
             END AS new_group
        FROM joined
   ),
   calc_group AS (
      -- produces column group_no
      SELECT empno,
             valid_from,
             valid_to,
             ename,
             job,
             mgr,
             mgr_ename,
             hiredate,
             sal,
             comm,
             deptno,
             dname,
              SUM(has_gap + new_group) OVER(
                PARTITION BY empno ORDER BY valid_from
             ) AS group_no
        FROM calc_various
   ),
   merged AS (
      -- produces the final merged result
      -- grouping by group_no ensures that gaps are honored
      SELECT empno,
             MIN(valid_from) AS valid_from,
             MAX(valid_to) AS valid_to,
             ename,
             job,
             mgr,
             mgr_ename,
             hiredate,
             sal,
             comm,
             deptno,
             dname
        FROM calc_group
       GROUP BY empno,
                group_no,
                ename,
                job,
                mgr,
                mgr_ename,
                hiredate,
                sal,
                comm,
                deptno,
                dname
       ORDER BY empno,
                valid_from
   )  
-- main
select * FROM merged WHERE empno = 7788;

Was passiert hier? In der Unterabfrage joined joinen wir alle möglichen Zeitintervalle. Weiter setzen wir „start of the group“ Method ein. Zuerst kennzeichnen wir die Datensätze mit einer Lücke oder wo sich die Daten unterscheiden (HAS_GAP and NEW_GROUP) – Unterabfrage calc_various. Dann weisen wir eine eindeutige Gruppennummer zu, über die wir später gruppieren können – GROUP_NO in der Unterabfrage calc_group. Letztendlich können wir gruppieren und dabei die Zeitintervalle zusammenführen – Unterabfrage merged.

Sieht kompliziert aus, oder? Kann man die Abfrage mit MATCH_RECOGNIZE vereinfachen?

WITH  joined AS (
      -- gap-aware temporal join
      -- produces result_cols to calculate new_group in the subsequent query
      SELECT e.empno,
             g.valid_from,
             LEAST(
                e.valid_to,
                d.valid_to,
                j.valid_to,
                NVL(m.valid_to, e.valid_to),
                LEAD(g.valid_from - 1, 1, e.valid_to) OVER(
                   PARTITION BY e.empno ORDER BY g.valid_from
                )
             ) AS valid_to,
             e.ename,
             j.job,
             e.mgr,
             m.ename AS mgr_ename,
             e.hiredate,
             e.sal,
             e.comm,
             e.deptno,
             d.dname            
        FROM empv e
       INNER JOIN (SELECT valid_from FROM empv
                   UNION
                   SELECT valid_from FROM deptv
                   UNION
                   SELECT valid_from FROM jobv
                   UNION
                   SELECT valid_to + 1 FROM empv
                    WHERE valid_to != DATE '9999-12-31'
                   UNION
                   SELECT valid_to + 1 FROM deptv
                    WHERE valid_to != DATE '9999-12-31'
                   UNION
                   SELECT valid_to + 1 FROM jobv
                    WHERE valid_to != DATE '9999-12-31') g
          ON g.valid_from BETWEEN e.valid_from AND e.valid_to
       INNER JOIN deptv d
          ON d.deptno = e.deptno AND g.valid_from BETWEEN d.valid_from AND d.valid_to
       INNER JOIN jobv j
          ON j.jobno = e.jobno AND g.valid_from BETWEEN j.valid_from AND j.valid_to
        LEFT JOIN empv m
          ON m.empno = e.mgr AND g.valid_from BETWEEN m.valid_from AND m.valid_to
   )
SELECT empno
,      valid_from
,      valid_to
,      ename
,      job
,      mgr
,      mgr_ename
,      hiredate
,      sal
,      comm
,      deptno
,      dname
FROM joined
      MATCH_RECOGNIZE (
        PARTITION BY empno
                   , ename
                   , job
                   , mgr
                   , mgr_ename
                   , hiredate
                   , sal
                   , comm
                   , deptno
                   , dname            
        ORDER BY valid_from
        MEASURES FIRST(valid_from) valid_from, LAST(valid_to) valid_to
        ONE ROW PER MATCH
        PATTERN ( strt nxt* )
        DEFINE nxt as valid_from = prev(valid_to) + 1
        )
WHERE empno = 7788; 

Wir möchten ein Muster erkennen, bei dem Zeitintervalle bei gleichen Daten angrenzend sind und die zusammenführen.

• Zeilen 61-70: Wir partitionieren die Daten nach inhaltlichen Spalten. Somit werden wir nach dem Muster nur innerhalb der gleichen Daten suchen.
• Zeile 71: Da uns die chronologische Reihenfolge interssiert, müssen wir die Daten nach VALID_FROM sortieren
• Zeilen 74,75: Nun definieren wir das Muster und die Mustervariablen. Das Muster wird durch die Syntax der regulären Ausdrücke, angewendet auf die logische Mustervariablen, definiert. Wir starten mit der Variable strt. Sie ist speziell, weil wir für sie keine Bedingung angeben. Das bedeutet, die ist immer TRUE und der erste Datensatz einer Partition wird immer zutreffen. Dann haben wir die Mustervariable nxt, die 0 bis N Wiederholungen haben kann. Die Bedingung für diese Mustervariable ist es, dass VALID_FROM vom aktuellen Datensatz ein Tag später sein soll, als das VALID_TO Datum vom vorherigen Datensatz. Die Zeitintervalle sind dann „angrenzend“ und werden zusammengeführt. Ansonsten haben wir eine Lücke und der aktuelle Datensatz wird nicht als passend zum Muster erkannt. Mit dem wird ein neues Muster begonnen.
• Zeilen 72,73: Wir sagen ONE ROW PER MATCH (das ist Default). Somit bekommen wir nur einen Datensatz im Ergebnis, selbst wenn wir mehrere angrenzende Zeitintervalle in den Ursprungsdaten hatten. Wir erweitern das Zeitintervall von diesem Ergebnis-Datensatz, indem wir das VALID_FROM Datum vom ersten und das VALID_TO vom letzten zum Muster gehörenden Datensatz nehmen: FIRST(valid_from) und LAST(valid_to)

Sieht schon viel einfacher aus, oder? Vor allem gefällt es mir, dass der Code der Problembeschreibung in Englisch sehr ähnlich aussieht.