{"id":220,"date":"2015-03-06T16:34:58","date_gmt":"2015-03-06T14:34:58","guid":{"rendered":"http:\/\/blog.sqlora.com\/de\/?p=220"},"modified":"2015-03-12T10:46:28","modified_gmt":"2015-03-12T08:46:28","slug":"zusammenfuehren-der-zeitintervalle-mit-match_recognize","status":"publish","type":"post","link":"https:\/\/blog.sqlora.com\/de\/zusammenfuehren-der-zeitintervalle-mit-match_recognize\/","title":{"rendered":"Zusammenf\u00fchren der Zeitintervalle mit MATCH_RECOGNIZE"},"content":{"rendered":"

Vor einiger Zeit hat mein Kollege Philipp Salvisberg ein paar interessante Beitr\u00e4ge zum Thema Joinen und Mergen der Zeitintervalle<\/a> gepostet. Neulich war ich auf der Suche nach Anwendungsbeispielen f\u00fcr die neue MATCH_RECOGNIZE Klausel, die in der 12c Datenbank eingef\u00fchrt wurde und fand heraus, dass die Abfragen damit deutlich vereinfacht werden.
\n
\nUm das Problem und die L\u00f6sung zu verstehen, w\u00fcrde ich empfehlen, die urspr\u00fcnglichen Beitr\u00e4ge zu lesen:<\/p>\n

Joining Temporal Intervals<\/a>
\nMerging Temporal Intervals with Gaps<\/a>
\nJoining Temporal Intervals Part 2<\/a><\/p>\n

Kurz zusammengefasst: Wir haben die Versionen der bekannten Tabellen EMPV, DEPTV and JOBV mit einer zeitlichen G\u00fcltigkeit, gesteuert \u00fcber die Datumsspalten VALID_FROM and VALID_TO. Wir m\u00f6chten die Daten joinen mit Ber\u00fccksichtigung der G\u00fcltigkeit, der m\u00f6glichen L\u00fccken und dabei die angrenzenden Zeitintervalle bei Gleichheit der Daten zusammenf\u00fchren. Ein Skript zum Erstellen und Bef\u00fcllen der Tabellen gibt es hier.<\/a><\/p>\n

Das ist die urspr\u00fcngliche Abfrage:<\/p>\n

\r\nWITH\r\n   joined AS (\r\n      -- gap-aware temporal join\r\n      -- produces result_cols to calculate new_group in the subsequent query\r\n      SELECT e.empno,\r\n             g.valid_from,\r\n             LEAST(\r\n                e.valid_to,\r\n                d.valid_to,\r\n                j.valid_to,\r\n                NVL(m.valid_to, e.valid_to),\r\n                LEAD(g.valid_from - 1, 1, e.valid_to) OVER(\r\n                   PARTITION BY e.empno ORDER BY g.valid_from\r\n                )\r\n             ) AS valid_to,\r\n             (\r\n                e.ename\r\n                || ',' || j.job\r\n                || ',' || e.mgr\r\n                || ',' || m.ename\r\n                || ',' || TO_CHAR(e.hiredate,'YYYY-MM-DD')\r\n                || ',' || e.sal\r\n                || ',' || e.comm\r\n                || ',' || e.deptno\r\n                || ',' || d.dname\r\n             ) AS result_cols,\r\n             e.ename,\r\n             j.job,\r\n             e.mgr,\r\n             m.ename AS mgr_ename,\r\n             e.hiredate,\r\n             e.sal,\r\n             e.comm,\r\n             e.deptno,\r\n             d.dname            \r\n        FROM empv e\r\n       INNER JOIN (SELECT valid_from FROM empv\r\n                   UNION\r\n                   SELECT valid_from FROM deptv\r\n                   UNION\r\n                   SELECT valid_from FROM jobv\r\n                   UNION\r\n                   SELECT valid_to + 1 FROM empv\r\n                    WHERE valid_to != DATE '9999-12-31'\r\n                   UNION\r\n                   SELECT valid_to + 1 FROM deptv\r\n                    WHERE valid_to != DATE '9999-12-31'\r\n                   UNION\r\n                   SELECT valid_to + 1 FROM jobv\r\n                    WHERE valid_to != DATE '9999-12-31') g\r\n          ON g.valid_from BETWEEN e.valid_from AND e.valid_to\r\n       INNER JOIN deptv d\r\n          ON d.deptno = e.deptno\r\n             AND g.valid_from BETWEEN d.valid_from AND d.valid_to\r\n       INNER JOIN jobv j\r\n          ON j.jobno = e.jobno\r\n             AND g.valid_from BETWEEN j.valid_from AND j.valid_to\r\n        LEFT JOIN empv m\r\n          ON m.empno = e.mgr\r\n             AND g.valid_from BETWEEN m.valid_from AND m.valid_to\r\n   ),\r\n   calc_various AS (\r\n      -- produces columns has_gap, new_group\r\n      SELECT empno,\r\n             valid_from,\r\n             valid_to,\r\n             result_cols,\r\n             ename,\r\n             job,\r\n             mgr,\r\n             mgr_ename,\r\n             hiredate,\r\n             sal,\r\n             comm,\r\n             deptno,\r\n             dname,\r\n             CASE\r\n                WHEN LAG(valid_to, 1, valid_from - 1) OVER(\r\n                        PARTITION BY empno ORDER BY valid_from\r\n                     ) = valid_from - 1 THEN\r\n                   0\r\n                ELSE\r\n                   1\r\n             END AS has_gap,\r\n             CASE\r\n                WHEN LAG(result_cols, 1, result_cols) OVER (\r\n                        PARTITION BY empno ORDER BY valid_from\r\n                     ) = result_cols THEN\r\n                   0\r\n                ELSE\r\n                   1\r\n             END AS new_group\r\n        FROM joined\r\n   ),\r\n   calc_group AS (\r\n      -- produces column group_no\r\n      SELECT empno,\r\n             valid_from,\r\n             valid_to,\r\n             ename,\r\n             job,\r\n             mgr,\r\n             mgr_ename,\r\n             hiredate,\r\n             sal,\r\n             comm,\r\n             deptno,\r\n             dname,\r\n              SUM(has_gap + new_group) OVER(\r\n                PARTITION BY empno ORDER BY valid_from\r\n             ) AS group_no\r\n        FROM calc_various\r\n   ),\r\n   merged AS (\r\n      -- produces the final merged result\r\n      -- grouping by group_no ensures that gaps are honored\r\n      SELECT empno,\r\n             MIN(valid_from) AS valid_from,\r\n             MAX(valid_to) AS valid_to,\r\n             ename,\r\n             job,\r\n             mgr,\r\n             mgr_ename,\r\n             hiredate,\r\n             sal,\r\n             comm,\r\n             deptno,\r\n             dname\r\n        FROM calc_group\r\n       GROUP BY empno,\r\n                group_no,\r\n                ename,\r\n                job,\r\n                mgr,\r\n                mgr_ename,\r\n                hiredate,\r\n                sal,\r\n                comm,\r\n                deptno,\r\n                dname\r\n       ORDER BY empno,\r\n                valid_from\r\n   )  \r\n-- main\r\nselect * FROM merged WHERE empno = 7788;\r\n<\/pre>\n
Was passiert hier? In der Unterabfrage joined<\/strong><\/em> joinen wir alle m\u00f6glichen Zeitintervalle. Weiter setzen wir „start of the group“<\/a> Method ein. Zuerst kennzeichnen wir die Datens\u00e4tze mit einer L\u00fccke oder wo sich die Daten unterscheiden (HAS_GAP and NEW_GROUP) – Unterabfrage calc_various<\/strong><\/em>. Dann weisen wir eine eindeutige Gruppennummer zu, \u00fcber die wir sp\u00e4ter gruppieren k\u00f6nnen – GROUP_NO in der Unterabfrage calc_group<\/strong><\/em>. Letztendlich k\u00f6nnen wir gruppieren und dabei die Zeitintervalle zusammenf\u00fchren – Unterabfrage merged<\/strong><\/em>.<\/p>\n
Sieht kompliziert aus, oder? Kann man die Abfrage mit MATCH_RECOGNIZE vereinfachen?<\/p>\n
\r\nWITH  joined AS (\r\n      -- gap-aware temporal join\r\n      -- produces result_cols to calculate new_group in the subsequent query\r\n      SELECT e.empno,\r\n             g.valid_from,\r\n             LEAST(\r\n                e.valid_to,\r\n                d.valid_to,\r\n                j.valid_to,\r\n                NVL(m.valid_to, e.valid_to),\r\n                LEAD(g.valid_from - 1, 1, e.valid_to) OVER(\r\n                   PARTITION BY e.empno ORDER BY g.valid_from\r\n                )\r\n             ) AS valid_to,\r\n             e.ename,\r\n             j.job,\r\n             e.mgr,\r\n             m.ename AS mgr_ename,\r\n             e.hiredate,\r\n             e.sal,\r\n             e.comm,\r\n             e.deptno,\r\n             d.dname            \r\n        FROM empv e\r\n       INNER JOIN (SELECT valid_from FROM empv\r\n                   UNION\r\n                   SELECT valid_from FROM deptv\r\n                   UNION\r\n                   SELECT valid_from FROM jobv\r\n                   UNION\r\n                   SELECT valid_to + 1 FROM empv\r\n                    WHERE valid_to != DATE '9999-12-31'\r\n                   UNION\r\n                   SELECT valid_to + 1 FROM deptv\r\n                    WHERE valid_to != DATE '9999-12-31'\r\n                   UNION\r\n                   SELECT valid_to + 1 FROM jobv\r\n                    WHERE valid_to != DATE '9999-12-31') g\r\n          ON g.valid_from BETWEEN e.valid_from AND e.valid_to\r\n       INNER JOIN deptv d\r\n          ON d.deptno = e.deptno AND g.valid_from BETWEEN d.valid_from AND d.valid_to\r\n       INNER JOIN jobv j\r\n          ON j.jobno = e.jobno AND g.valid_from BETWEEN j.valid_from AND j.valid_to\r\n        LEFT JOIN empv m\r\n          ON m.empno = e.mgr AND g.valid_from BETWEEN m.valid_from AND m.valid_to\r\n   )\r\nSELECT empno\r\n,      valid_from\r\n,      valid_to\r\n,      ename\r\n,      job\r\n,      mgr\r\n,      mgr_ename\r\n,      hiredate\r\n,      sal\r\n,      comm\r\n,      deptno\r\n,      dname\r\nFROM joined\r\n      MATCH_RECOGNIZE (\r\n        PARTITION BY empno\r\n                   , ename\r\n                   , job\r\n                   , mgr\r\n                   , mgr_ename\r\n                   , hiredate\r\n                   , sal\r\n                   , comm\r\n                   , deptno\r\n                   , dname            \r\n        ORDER BY valid_from\r\n        MEASURES FIRST(valid_from) valid_from, LAST(valid_to) valid_to\r\n        ONE ROW PER MATCH\r\n        PATTERN ( strt nxt* )\r\n        DEFINE nxt as valid_from = prev(valid_to) + 1\r\n        )\r\nWHERE empno = 7788; \r\n<\/pre>\n
Wir m\u00f6chten ein Muster erkennen, bei dem Zeitintervalle bei gleichen Daten angrenzend sind und die zusammenf\u00fchren.<\/p>\n
    \n
  • Zeilen 61-70: Wir partitionieren die Daten nach inhaltlichen Spalten. Somit werden wir nach dem Muster nur innerhalb der gleichen Daten suchen.<\/li>\n
  • Zeile 71: Da uns die chronologische Reihenfolge interssiert, m\u00fcssen wir die Daten nach VALID_FROM sortieren<\/li>\n
  • Zeilen 74,75: Nun definieren wir das Muster und die Mustervariablen. Das Muster wird durch die Syntax der regul\u00e4ren Ausdr\u00fccke, angewendet auf die logische Mustervariablen, definiert. Wir starten mit der Variable strt.<\/strong> Sie ist speziell, weil wir f\u00fcr 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<\/strong>, die 0 bis N Wiederholungen haben kann. Die Bedingung f\u00fcr diese Mustervariable ist es, dass VALID_FROM vom aktuellen Datensatz ein Tag sp\u00e4ter sein soll, als das VALID_TO Datum vom vorherigen Datensatz. Die Zeitintervalle sind dann „angrenzend“ und werden zusammengef\u00fchrt. Ansonsten haben wir eine L\u00fccke und der aktuelle Datensatz wird nicht als passend zum Muster erkannt. Mit dem wird ein neues Muster begonnen.<\/li>\n
  • 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\u00f6renden Datensatz nehmen: FIRST(valid_from) und LAST(valid_to)<\/li>\n<\/ul>\n
    Sieht schon viel einfacher aus, oder? Vor allem gef\u00e4llt es mir, dass der Code der Problembeschreibung in Englisch sehr \u00e4hnlich aussieht. <\/p>\n","protected":false},"excerpt":{"rendered":"
    Vor einiger Zeit hat mein Kollege Philipp Salvisberg ein paar interessante Beitr\u00e4ge zum Thema Joinen und Mergen der Zeitintervalle gepostet. Neulich war ich auf der Suche nach Anwendungsbeispielen f\u00fcr die neue MATCH_RECOGNIZE Klausel, die in der 12c Datenbank eingef\u00fchrt wurde und fand heraus, dass die Abfragen damit deutlich vereinfacht werden.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[17,1,30,3,4,22],"tags":[24,25,10,28,27,26,23,29],"class_list":["post-220","post","type-post","status-publish","format-standard","hentry","category-12c","category-allgemein","category-match_recognize","category-oracle","category-sql","category-trivadis","tag-12c","tag-match_recognize","tag-merge","tag-mustererkennung","tag-pattern-matching","tag-temporal-validity","tag-trivadiscontent","tag-zeitintervall"],"_links":{"self":[{"href":"https:\/\/blog.sqlora.com\/de\/wp-json\/wp\/v2\/posts\/220","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.sqlora.com\/de\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.sqlora.com\/de\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.sqlora.com\/de\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.sqlora.com\/de\/wp-json\/wp\/v2\/comments?post=220"}],"version-history":[{"count":6,"href":"https:\/\/blog.sqlora.com\/de\/wp-json\/wp\/v2\/posts\/220\/revisions"}],"predecessor-version":[{"id":226,"href":"https:\/\/blog.sqlora.com\/de\/wp-json\/wp\/v2\/posts\/220\/revisions\/226"}],"wp:attachment":[{"href":"https:\/\/blog.sqlora.com\/de\/wp-json\/wp\/v2\/media?parent=220"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.sqlora.com\/de\/wp-json\/wp\/v2\/categories?post=220"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.sqlora.com\/de\/wp-json\/wp\/v2\/tags?post=220"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}