\r\nSQL> select * from t_target;\r\n\r\n DWH_KEY VALID_FROM VALID_TO C ETL BUS_K FIRST_NAME SECOND_NAM LAST_NAME HIRE_DATE FIRE_DATE SALARY\r\n---------- ---------- --------- - --- ----- ---------- ---------- ---------- --------- --------- ----------\r\n 1 01-DEC-16 02-DEC-16 N UPD 123 Roger Federer 01-JAN-10 900000\r\n 2 01-DEC-16 02-DEC-16 N UPD 456 Rafael Nadal 01-MAY-09 720000\r\n 3 01-DEC-16 01-DEC-16 N UPD 789 Serena Williams 01-JUN-08 650000\r\n 5 02-DEC-16 03-DEC-16 N UPD 789 Serena Jameka Williams 01-JUN-08 650000\r\n 6 02-DEC-16 02-DEC-16 N UPD 345 Venus Williams 01-NOV-16 500000\r\n 10 03-DEC-16 31-DEC-99 Y INS 345 Venus Williams 01-NOV-16 01-DEC-16 500000\r\n 11 03-DEC-16 31-DEC-99 Y INS 123 Roger Federer 01-JAN-10 920000\r\n 12 04-DEC-16 31-DEC-99 Y INS 789 Serena Jameka Williams 01-JUN-08 700000\r\n\r\n 8 rows selected \r\n<\/pre>\nWir sollen die drei gekennzeichneten Datens\u00e4tze f\u00fcr den Business Key 789 vergleichen und den Feldnamen berichten, wo eine \u00c4nderung stattgefunden hat, sowie auch den „alten“ und den „neuen“ Werte. Ich m\u00f6chte eine Abfrage schreiben, die mehr als zwei Versionen auf einmal vergleichen kann. Aus diesem Grund fangen wir mit dem „Identifizieren“ dieser „Versionsp\u00e4rchen“ an:<\/p>\n
\r\nselect a.dwh_key id_a\r\n, lead(a.dwh_key) over (order by a.valid_from) id_b\r\nfrom t_target a \r\nwhere a.bus_key = 789 \r\n\r\n ID_A ID_B\r\n---------- ----------\r\n 3 5\r\n 5 12\r\n 12 \r\n\r\n<\/pre>\nWir nutzen den k\u00fcnstlichen Schl\u00fcssel DWH_KEY, um die Datens\u00e4tze zu identifizieren. Und wir m\u00fcssen die \u00c4nderungen innerhalb zwei P\u00e4rchen von Datens\u00e4tzen untersuchen: 3 <-> 5 and 5 <-> 12. Der dritte Datensatz im Ergebnis ist f\u00fcr uns nicht interessant und wird im n\u00e4chsten Schritt herausgefiltert.<\/p>\n
Jedes P\u00e4rchen besteht aus zwei Datens\u00e4tzen. Daher duplizieren wir jeden Datensatz in diesem Ergebnis durch joinen mit einem „row source generator“, der exakt zwei Datens\u00e4tze hat (die kleine Subquery aus DUAL mit CONNECT BY). Dabei stellen wir den dwh_key wieder zur Verf\u00fcgung, der jeden Datensatz identifiziert (Zeile 9) und generieren einen eindeutigen Schl\u00fcssel, der das P\u00e4rchen indentifiziert (Zeile 10)<\/p>\n
\r\nwith p as ( \r\n select a.dwh_key id_a\r\n , lead(a.dwh_key) over (order by a.valid_from) id_b\r\n from t_target a \r\n where a.bus_key = 789 )\r\n, pairs as (\r\n select id_a\r\n , id_b \r\n , case when lvl = 1 then id_a else id_b end id\r\n , dense_rank() over (order by id_a, id_b) rn \r\n from p\r\n , (select level lvl from dual connect by level<=2) row_source\r\n where id_b is not null )\r\nselect * from pairs;\r\n\r\n ID_A ID_B ID RN\r\n---------- ---------- ---------- ----------\r\n 3 5 5 1\r\n 3 5 3 1\r\n 5 12 12 2\r\n 5 12 5 2\r\n [\/code]\r\n\r\nJetzt sind wir bereit, die Spaltennamen und -Werte zu sammeln. Aber wie? Wir m\u00fcssen die Spalten "kippen", sodass sie zu Datens\u00e4tzen im Ergebnis werden - klassische Aufgabe von einem UNPIVOT-Operator. Und INCLUDE NULLS nicht vergessen, schlie\u00dflich sind die \u00c4nderungen von NULL oder auf NULL-Wert auch zu erkennen!\r\n\r\n[code language="sql"]\r\nSQL> with prep as ( \r\n select a.dwh_key id_a\r\n , lead(a.dwh_key) over (order by a.valid_from) id_b\r\n from t_target a \r\n where a.bus_key = 789 )\r\n, pairs as (\r\n select id_a\r\n , id_b \r\n , case when lvl = 1 then id_a else id_b end id\r\n , dense_rank() over (order by id_a, id_b) rn \r\n from prep\r\n , (select level lvl from dual connect by level<=2) row_source\r\n where id_b is not null )\r\nselect * \r\nfrom ( select p.rn, a.DWH_KEY, a.FIRST_NAME, a.SECOND_NAMES, a.LAST_NAME\r\n from t_target a, pairs p\r\n where a.dwh_key = p.id )\r\nunpivot include nulls (column_value for column_name in (FIRST_NAME, SECOND_NAMES, LAST_NAME ) );\r\n\r\n RN DWH_KEY COLUMN_NAME COLUMN_VALUE \r\n---------- ---------- ------------------------------ ------------------------------\r\n 1 3 FIRST_NAME Serena \r\n 1 3 SECOND_NAMES \r\n 1 3 LAST_NAME Williams \r\n 1 5 FIRST_NAME Serena \r\n 1 5 SECOND_NAMES Jameka \r\n 1 5 LAST_NAME Williams \r\n 2 5 FIRST_NAME Serena \r\n 2 5 SECOND_NAMES Jameka \r\n 2 5 LAST_NAME Williams \r\n 2 12 FIRST_NAME Serena \r\n 2 12 SECOND_NAMES Jameka \r\n 2 12 LAST_NAME Williams \r\n\r\n 12 rows selected \r\n[\/code]\r\n\r\nSieht erst mal gut aus. Jetzt k\u00f6nnen wir nach RN und COLUMN_NAME gruppieren und die \u00c4nderungen sehen. Aber vorher brauchen noch ein paar Schritte... Zum einen, man kann UNPIVOT und GROUP BY in einer Abfrage nicht kombinieren, also m\u00fcssen wir noch einmal verschachteln (Zeilen 14-20). Der andere Punkt zu beachten, ist schon wieder die spezielle Handhabung von NULL-Werten: MIN, MAX and COUNT(distinct ... ) werden NULL-Werte ignorieren. Es sind aber Workarounds f\u00fcr das Problem da: wir k\u00f6nnen KEEP Klausel mit MIN und MAX einsetzen (Zeilen 22,23) und um auch die NULL-Werte zu z\u00e4hlen - COUNT(distinct dump(column_value)) (Zeile 26) (mehr dazu siehe <a href="http:\/\/www.sqlsnippets.com\/en\/topic-12656.html" target="_blank">Nulls and Aggregate Functions<\/a>). \r\n\r\n[code language="sql" highlight="22,23,26"]\r\nSQL> with prep as ( \r\n select a.dwh_key id_a\r\n , lead(a.dwh_key) over (order by a.valid_from) id_b\r\n from t_target a \r\n where a.bus_key = 789 )\r\n, pairs as (\r\n select id_a\r\n , id_b \r\n , case when lvl = 1 then id_a else id_b end id\r\n , dense_rank() over (order by id_a, id_b) rn \r\n from prep\r\n , (select level lvl from dual connect by level<=2) row_source\r\n where id_b is not null )\r\n, unpivot_query as ( \r\n select * \r\n from ( select p.rn, a.DWH_KEY, a.FIRST_NAME, a.SECOND_NAMES, a.LAST_NAME\r\n from t_target a, pairs p\r\n where a.dwh_key = p.id )\r\n unpivot include nulls (column_value for column_name in (FIRST_NAME, SECOND_NAMES, LAST_NAME ) )\r\n )\r\nselect rn\r\n, min (column_value) keep (dense_rank first order by dwh_key) old_value\r\n, max (column_value) keep (dense_rank last order by dwh_key) new_value\r\nfrom unpivot_query\r\ngroup by rn, column_name\r\nhaving count(distinct dump(column_value)) > 1\r\norder by rn;\r\n\r\n RN OLD_VALUE NEW_VALUE \r\n---------- ------------------------------ ------------------------------\r\n 1 Jameka \r\n\r\n<\/pre>\nFertig? Nicht ganz. Wir haben bis jetzt nur die drei VARCHAR2-Spalten verglichen, es gibt aber auch NUMBER- und DATE-Spalten in der Tabelle. Ist das ein Problem? Ja, ist es! Der Versuch, UNPIVOT mit Spalten von unterschiedlichen Datentypen zu nutzen, resultiert in ORA-01790:<\/p>\n
\r\n...\r\nunpivot include nulls (column_value for column_name in (FIRST_NAME, SECOND_NAMES, LAST_NAME, SALARY ) )\r\n...\r\nORA-01790: expression must have same datatype as corresponding expression\r\n<\/pre>\nAber auch dieses Problem k\u00f6nnen wir l\u00f6sen, wenn auch nicht besonders elegant. Einfach drei UNPIVOT subqueries definieren, die mit eigenen Datentypen umgehen k\u00f6nnen (VARCHAR2, DATE, NUMBER), dann die Ergebnisse zu Strings konvertieren und mit UNION ALL zusammenf\u00fchren:<\/p>\n
\r\nSQL> with prep as ( \r\n select a.dwh_key id_a\r\n , lead(a.dwh_key) over (order by a.valid_from) id_b\r\n from t_target a \r\n where a.bus_key = 789 )\r\n, pairs as (\r\n select id_a\r\n , id_b \r\n , case when lvl = 1 then id_a else id_b end id\r\n , dense_rank() over (order by id_a, id_b) rn \r\n from prep\r\n , (select level lvl from dual connect by level<=2) row_source\r\n where id_b is not null )\r\n, unpivot_query as ( \r\n -- CHAR fields\r\n select * from (\r\n select * \r\n from ( select p.rn, a.DWH_KEY, a.FIRST_NAME, a.SECOND_NAMES, a.LAST_NAME\r\n from t_target a, pairs p\r\n where a.dwh_key = p.id )\r\n unpivot include nulls (column_value for column_name in (FIRST_NAME, SECOND_NAMES, LAST_NAME ) )\r\n )\r\n union all\r\n -- DATE fields\r\n select rn, DWH_KEY, column_name, to_char(column_value,'dd.mm.yyyy hh24:mi:ss') \r\n from (select * \r\n from ( select p.rn, a.DWH_KEY, a.HIRE_DATE, FIRE_DATE\r\n from t_target a, pairs p\r\n where a.dwh_key = p.id )\r\n unpivot include nulls (column_value for column_name in (HIRE_DATE, FIRE_DATE ) )\r\n )\r\n union all\r\n --NUMBER fields\r\n select rn, DWH_KEY, column_name, to_char(column_value) \r\n from (select * \r\n from ( select p.rn, a.DWH_KEY, a.SALARY\r\n from t_target a, pairs p\r\n where a.dwh_key = p.id )\r\n unpivot include nulls (column_value for column_name in (SALARY ) )\r\n )\r\n )\r\nselect rn\r\n, listagg(dwh_key,'-') within group (order by dwh_key) dwh_keys\r\n, column_name \r\n, min (column_value) keep (dense_rank first order by dwh_key) old_value\r\n, max (column_value) keep (dense_rank last order by dwh_key) new_value\r\nfrom unpivot_query\r\ngroup by rn, column_name\r\nhaving count(distinct dump(column_value)) > 1\r\norder by rn;\r\n\r\n RN DWH_KEYS COLUMN_NAME OLD_VALUE NEW_VALUE \r\n---------- ---------- ------------------------------ ------------------------------ ------------------------------\r\n 1 3-5 SECOND_NAMES Jameka \r\n 2 5-12 SALARY 650000 700000 \r\n<\/pre>\nIch habe LISTAGG als P\u00e4rchen von DWH_KEYs hinzugef\u00fcgt, um sehen zu k\u00f6nnen, welche zwei Datens\u00e4tze verglichen wurden.
\nOkay, die endg\u00fcltige Abfrage ist komplizierter, als zuerst gedacht. Es war eine spannende SQL-Aufgabe und kann f\u00fcr ad hoc Abfragen evtl. n\u00fctzlich sein. Wenn ich eine L\u00f6sung entwickeln m\u00fcsste, die auf regul\u00e4rer Basis eingesetzt wird, w\u00fcrde ich doch zu PL\/SQL greifen, denke ich. Alleine schon, um die Spalten und deren Datentypen aus dem Dictionary zu holen.<\/p>\n","protected":false},"excerpt":{"rendered":"
Dieser Beitrag ist wieder \u00fcber Slowly Changing Dimensions Type 2, betrachtet aber eine andere Fragestellung. Wie kann man die Erkennung der \u00c4nderungen validieren? Wenn wir mehrere Versionen derselben Daten haben, wie kann man pr\u00fcfen, welche Felder sich von Version zu Version ge\u00e4ndert haben? In Kundeprojekten, wo ich mit Systemen wie Siebel CRM gearbeitet habe, die […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1,39,3,4,22],"tags":[44,43,41,23,45],"class_list":["post-316","post","type-post","status-publish","format-standard","hentry","category-allgemein","category-data-warehouse","category-oracle","category-sql","category-trivadis","tag-deltaerkennung","tag-historisierung","tag-scd2","tag-trivadiscontent","tag-unpivot"],"_links":{"self":[{"href":"https:\/\/blog.sqlora.com\/de\/wp-json\/wp\/v2\/posts\/316","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=316"}],"version-history":[{"count":8,"href":"https:\/\/blog.sqlora.com\/de\/wp-json\/wp\/v2\/posts\/316\/revisions"}],"predecessor-version":[{"id":439,"href":"https:\/\/blog.sqlora.com\/de\/wp-json\/wp\/v2\/posts\/316\/revisions\/439"}],"wp:attachment":[{"href":"https:\/\/blog.sqlora.com\/de\/wp-json\/wp\/v2\/media?parent=316"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.sqlora.com\/de\/wp-json\/wp\/v2\/categories?post=316"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.sqlora.com\/de\/wp-json\/wp\/v2\/tags?post=316"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}