{"id":1316,"date":"2020-03-28T19:37:36","date_gmt":"2020-03-28T17:37:36","guid":{"rendered":"https:\/\/blog.sqlora.com\/en\/?p=1316"},"modified":"2020-03-30T08:26:00","modified_gmt":"2020-03-30T06:26:00","slug":"temporal-joins-with-sql-macros-in-oracle-20c","status":"publish","type":"post","link":"https:\/\/blog.sqlora.com\/en\/temporal-joins-with-sql-macros-in-oracle-20c\/","title":{"rendered":"Temporal Joins with SQL Macros in Oracle 20c"},"content":{"rendered":"\n

In a previous post<\/a> on SQL macros in Oracle Database 20c we saw how SQL macros can be used to create a kind of “parameterized” views to establish a simplified access tier to temporal data. In this post I’d like to explore more possibilities to hide the complexity of SQL statements behind a functional syntax provided by using SQL macros. As an example we’ll stay with a temporal data introduced in the previous post<\/a> and explore how we can do a temporal join of this versioned data.<\/p>\n\n\n\n\n\n\n\n

What is a temporal join? <\/h4>\n\n\n\n
\"\"<\/figure><\/div>\n\n\n\n

About a month ago I had the opportunity to join a Data Warehouse Design Patterns Workshop by Roelant Vos<\/a>. Yes, despite of #COVID19<\/a>, at that time it was not yet quite unthinkable to fly to Zurich to attend a workshop in person \ud83d\ude41 <\/p>\n\n\n\n

Being one of the many interesting topics of this three-day training, the question of joining temporal data arises e.g. when calculating the dimensions from several versioned tables.<\/p>\n\n\n\n

Unlike “point in time” queries, temporal joins produce all possible combinations of validity intervals in the source tables. Although the way you can do it was not new for me, it is quite complex and it led me to the idea of testing the use of SQL macros for that task. <\/p>\n\n\n\n

My colleague Philipp Salvisberg<\/a>, whose data model and data<\/a> I’m using in this example has blogged about this kind of queries<\/a> and I have tested the use of row pattern matching clause to merge the resulting time interval<\/a>s a time ago. I will use Philipp’s picture to show what we are doing:<\/p>\n\n\n\n

\"\"
Figure 1:<\/em><\/strong> Temporal Join<\/figcaption><\/figure>\n\n\n\n

We produce all the time intervals containing different data from joined tables. The exact way of doing this may differ depending on whether you are using open or closed time intervals, can you have gaps, would you like to merge adjacent intervals with the same data and so on. But the exact shape of the query is not important for our purpose. I’d only like to demonstrate how we can implement some kind of functional syntax to support complex types of queries. Let’s say, we prefer following solution for the problem:<\/p>\n\n\n

\nWITH  joined AS (\n      -- gap-aware temporal join\n      -- produces result_cols to calculate new_group in the subsequent query\n      SELECT e.empno,\n             g.valid_from,\n             LEAST(\n                e.valid_to,\n                d.valid_to,\n                LEAD(g.valid_from - 1, 1, e.valid_to) OVER(\n                   PARTITION BY e.empno ORDER BY g.valid_from\n                )\n             ) AS valid_to,\n             e.ename,\n             e.mgr,\n             e.hiredate,\n             e.sal,\n             e.comm,\n             e.deptno,\n             d.dname            \n        FROM empv e\n       INNER JOIN (SELECT valid_from FROM empv\n                   UNION\n                   SELECT valid_from FROM deptv\n                   UNION\n                   SELECT valid_to + 1 FROM empv\n                    WHERE valid_to != DATE '9999-12-31'\n                   UNION\n                   SELECT valid_to + 1 FROM deptv\n                    WHERE valid_to != DATE '9999-12-31') g\n          ON g.valid_from BETWEEN e.valid_from AND e.valid_to\n       INNER JOIN deptv d\n          ON d.deptno = e.deptno AND g.valid_from BETWEEN d.valid_from AND d.valid_to\n          )\nSELECT empno\n,      valid_from\n,      valid_to\n,      ename\n,      mgr\n,      hiredate\n,      sal\n,      comm\n,      deptno\n,      dname\nFROM joined\n      MATCH_RECOGNIZE (\n        PARTITION BY empno\n                   , ename\n                   , mgr\n                   , hiredate\n                   , sal\n                   , comm\n                   , deptno\n                   , dname            \n        ORDER BY valid_from\n        MEASURES FIRST(valid_from) valid_from, LAST(valid_to) valid_to\n        ONE ROW PER MATCH\n        PATTERN ( strt nxt* )\n        DEFINE nxt as valid_from = prev(valid_to) + 1\n        )\n\n<\/pre><\/div>\n\n\n
Listing 1: The shape of the query doing temporal join<\/em><\/strong><\/p>\n\n\n\n
Well, quite lengthy and complex, isn’t it? Note, that I reduced the join from the Figure 1 to join only two tables. What I’d like to have, is a SQL macro which I could call instead of this whole complex query. Just like this:<\/p>\n\n\n
\nSQL>\nSQL>\nSQL>  SELECT * FROM temporal_join ( <parameter> );\nSQL>\nSQL>\n<\/pre><\/div>\n\n\n
Listing 2: the desired functional syntax<\/em><\/strong><\/p>\n\n\n\n
Would it be possible? If you’ve read my previous posts about SQL macros<\/a>, you’ll be confident it is. Why not? Let’s start. <\/p>\n\n\n\n
What parameter do we need to dynamically build a statement like the one from Listing 1? We are joining two tables (let’s start with two for now) so we should know these tables and have two table parameters (DBMS_TF.Table_t). Many things are convention based in this simple example: I suppose to know the the validity column names, that we are using closed time intervals, that we are using a special date 31-DEC-9999 and so on. But there are also things I cannot know and must pass them as parameters. I have to know the business key of the first table (EMPNO) because it is used in PARTITION clause of the analytical function LEAD (line 9 in Listing 1). We define the parameter bk_cols<\/code><\/strong> of type DBMS_TF.COLUMNS_T<\/code><\/strong> for this purpose and can pass just one or even a list of columns in case of a multi-column key.  <\/p>\n\n\n\n
And I have to know how to join those two tables. For simplification I suppose we will always have an equality conditions and I guess it will be true in 99% of cases. Then we just have to know the column names of the columns used as join criteria. I decided to use just one column list (join_cols DBMS_TF.COLUMNS_T<\/code><\/strong>)  but in a special way: a pair of columns to join  followed by another pair if any and so on. For example, if the join condition looks like t1.a=t2.b AND t1.c=t2.d<\/strong><\/code> then you’d have  COLUMNS(a,b,c,d). <\/strong><\/code><\/p>\n\n\n\n
The rest of the implementation is rather boring putting together the desired SQL. I put the function in the package TIME_MACHINE introduced in the previous post about point in time queries in a kind of “parameterized” views:  <\/a><\/p>\n\n\n
\nCREATE OR REPLACE PACKAGE time_machine IS\n...\n...\nFUNCTION temporal_join(t1 DBMS_TF.Table_t\n                     , t2 DBMS_TF.Table_t\n                     , bk_cols DBMS_TF.COLUMNS_T\n                     , join_cols DBMS_TF.COLUMNS_T) \nRETURN VARCHAR2 SQL_MACRO(TABLE);\n\nEND time_machine;\n\/\nCREATE OR REPLACE PACKAGE BODY time_machine IS\n...\n...\nFUNCTION temporal_join(t1 DBMS_TF.Table_t\n                     , t2 DBMS_TF.Table_t\n                     , bk_cols DBMS_TF.COLUMNS_T\n                     , join_cols DBMS_TF.COLUMNS_T) \nRETURN VARCHAR2 SQL_MACRO(TABLE)\nAS\n -- convention based \n v_dates varchar2(1000);\n v_select varchar2(4000);\n v_buskey varchar2(1000);\n v_join_keys varchar2(1000);\n v_name DBMS_ID;\n v_column_list varchar2(1000);\n v_mr varchar2(2000);\nBEGIN\n   v_dates := q'[INNER JOIN (SELECT t1.valid_from FROM t1\n                   UNION\n                   SELECT t2.valid_from FROM t2\n                   UNION\n                   SELECT t1.valid_to + 1 FROM t1\n                    WHERE t1.valid_to != DATE '9999-12-31'\n                   UNION\n                   SELECT t2.valid_to + 1 FROM t2\n                    WHERE t2.valid_to != DATE '9999-12-31') g\n          ON g.valid_from BETWEEN t1.valid_from AND t1.valid_to]';\n    FOR I IN 1..t1.column.count LOOP\n      v_name := t1.column(i).description.name;\n      IF v_name NOT IN ('\"VALID_FROM\"', '\"VALID_TO\"') THEN\n        V_Select := v_select ||', t1.'||v_name||' as '||\n                   replace(t1.table_name,'\"')||'_'||replace(v_name,'\"');\n        v_column_list := v_column_list ||', '||\n                   replace(t1.table_name,'\"')||'_'||replace(v_name,'\"');\n      END IF; \n      IF v_name MEMBER OF bk_cols THEN\n        v_buskey := v_buskey || ', t1.' || v_name;\n      END IF;\n    END LOOP;\n    FOR I IN 1..t2.column.count LOOP\n      v_name := t2.column(i).description.name;\n      IF v_name NOT IN ('\"VALID_FROM\"', '\"VALID_TO\"') THEN\n        V_Select := v_select ||', t2.'||v_name||' as '||\n                  replace(t2.table_name,'\"')||'_'||replace(v_name,'\"');\n        v_column_list := v_column_list ||', '||\n                  replace(t2.table_name,'\"')||'_'||replace(v_name,'\"');\n      END IF; \n    END LOOP;\n    v_column_list := ltrim(v_column_list , ',');\n    v_buskey := ltrim(v_buskey , ',');\n    v_select := 'SELECT '||\n           ltrim(v_select , ',')||\n             q'[, g.valid_from,\n             LEAST(\n                t1.valid_to,\n                t2.valid_to,\n                LEAD(g.valid_from - 1, 1, t1.valid_to) OVER(\n                   PARTITION BY ]'||v_buskey||q'[ ORDER BY g.valid_from\n                )\n             ) AS valid_to\n        FROM t1 ]'\n        || v_dates;\n    FOR i in 1..join_cols.count LOOP\n       IF MOD(i,2) = 0 THEN    \n          v_join_keys := 'AND t1.'||join_cols(i-1)|| '= t2.'||join_cols(i);\n       END IF;\n    END LOOP; \n    v_join_keys := 'ON '||ltrim(v_join_keys , 'AND');\n    v_select := v_select ||' INNER JOIN t2 '||v_join_keys||\n                ' AND g.valid_from BETWEEN t2.valid_from AND t2.valid_to  ';\n    v_mr := ' MATCH_RECOGNIZE ( PARTITION BY '||v_column_list||q'[\n        ORDER BY valid_from\n        MEASURES FIRST(valid_from) valid_from, LAST(valid_to) valid_to\n        ONE ROW PER MATCH\n        PATTERN ( strt nxt* )\n        DEFINE nxt as valid_from = prev(valid_to) + 1\n        )]'; \n   v_select := 'SELECT '||v_column_list||\n               ', r_valid_from valid_from, r_valid_to valid_to '||\n               'FROM ('||v_select ||') '|| v_mr;            \n   return v_select ;    \nEND;\n\nEND time_machine;\n\/\n\n<\/pre><\/div>\n\n\n
Listing 3: SQL macro function implementing temporal join<\/em><\/strong><\/p>\n\n\n\n
Note, how we are using the names of the table parameters T1 and T2 as table names across the whole strings. Inside the strings, without any concatenation. Remember, they will be replaced with real table names just at the time when the result string is returned out of the function. <\/p>\n\n\n\n
To be able to return a unique set of columns we are prefixing all column names with a table name. If you don’t like it, you can do something else, but you should have some convention at place. Otherwise you’ll get “duplicate column name” error. One of the solutions could also be passing column list parameters for each table to define what columns should be in the result set and thus being able to control the uniqueness yourself . <\/p>\n\n\n\n
Well, now try to call the function:<\/p>\n\n\n
\nSQL> SELECT * \nFROM   time_machine.temporal_join (t1 => empv\n                     ,t2 => deptv\n                     ,bk_cols => columns(empno)\n                     ,join_cols => columns( deptno, deptno))\nWHERE  empv_empno = 7788\n\nEMPV_EMPVID EMPV_EMPNO EMPV_ENAME     EMPV_JOBNO   EMPV_MGR EMPV_HIREDATE   EMPV_SAL  EMPV_COMM EMPV_DEPTNO DEPTV_DEPTVID DEPTV_DEPTNO DEPTV_DNAME    DEPTV_LOC     VALID_FROM VALID_TO \n----------- ---------- -------------- ---------- ---------- ------------- ---------- ---------- ----------- ------------- ------------ -------------- ------------- ---------- ---------\n          8       7788 SCOTT                   5       7566 19-APR-87           3000                     20             2           20 RESEARCH       DALLAS        19-APR-87  31-DEC-89\n         22       7788 Scott                   5       7566 19-APR-87           3000                     20             2           20 RESEARCH       DALLAS        01-JAN-90  28-FEB-90\n         22       7788 Scott                   5       7566 19-APR-87           3000                     20             6           20 Research       DALLAS        01-MAR-90  31-MAR-90\n         22       7788 Scott                   5       7566 19-APR-87           3000                     20            10           20 Research       Dallas        01-APR-90  31-MAR-91\n         36       7788 Scott                   5       7566 19-APR-87           3300                     20            10           20 Research       Dallas        01-APR-91  31-DEC-99\n\n5 rows selected.\n<\/pre><\/div>\n\n\n
Listing 4: Calling a SQL macro function <\/em><\/strong><\/p>\n\n\n\n
Looks good! It did what we expected. We can hide really complex implementation behind just one function call. And remember that in fact no PL\/SQL functions are involved at runtime, slowing down your query. The SQL macro function is called only once at parse time and generates the query text which will be incorporated into the invoking query.  What actually runs, is a SQL statement much like the one in Listing 1. You can verify it by calling DBMS_UTILITY.EXPAND_SQL_TEXT<\/strong><\/code> as I did in a previous post<\/a>. <\/p>\n\n\n