{"id":355,"date":"2012-12-27T16:05:24","date_gmt":"2012-12-27T15:05:24","guid":{"rendered":"http:\/\/www.salvis.com\/blog\/?p=355"},"modified":"2023-11-08T20:02:18","modified_gmt":"2023-11-08T19:02:18","slug":"merging-temporal-intervals-with-gaps","status":"publish","type":"post","link":"https:\/\/www.salvis.com\/blog\/2012\/12\/27\/merging-temporal-intervals-with-gaps\/","title":{"rendered":"Merging Temporal Intervals with Gaps"},"content":{"rendered":"\n

In Joining Temporal Intervals<\/a> I explained how to join multiple temporal tables. The provided solution merges also temporal intervals but – as pointed out in that post – may produce wrong results if the underlying driving table is not gaplessly historized. In this post, I’ll explain how to merge temporal intervals with various data constellations correctly.<\/p>\n\n\n\n

You find the scripts to create and populate the tables for scenarios A and B here<\/a>.<\/p>\n\n\n\n

Scenario A – No overlapping intervals<\/h3>\n\n\n\n

This scenario handles consistent data. This means no overlapping intervals, no duplicate intervals, no including intervals, and no negative intervals (valid_to < valid_from). Here is the content of the example table t1:<\/p>\n\n\n\n

Test Data Set t1<\/span><\/path><\/path><\/svg><\/span>
SQL> SELECT * FROM t1;<\/span><\/span>\n<\/span><\/span>\n       VID        OID VALID_FROM  VALID_TO    C1         C2<\/span><\/span>\n---------- ---------- ----------- ----------- ---------- ----------<\/span><\/span>\n         1          1 01-JAN-2010 31-DEC-2010 A          B1<\/span><\/span>\n         2          1 01-JAN-2011 31-MAR-2011 A          B2<\/span><\/span>\n         3          1 01-JUN-2011 31-JAN-2012 A          B2<\/span><\/span>\n         4          1 01-APR-2012 31-DEC-9999 A          B4<\/span><\/span>\n         5          2 01-JAN-2010 31-JUL-2012 B          B1<\/span><\/span>\n         6          2 01-AUG-2012 31-DEC-9999 B          B2<\/span><\/span>\n        18          4 01-JAN-2010 30-SEP-2011 D          D1<\/span><\/span>\n        19          4 01-OCT-2011 30-SEP-2012            D2<\/span><\/span>\n        20          4 01-OCT-2012 31-DEC-9999 D          D3<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
I’d like to write a query which produces all intervals for the columns OID and C1 honoring gaps in the historization. For OID 1 I expect that records 1 and 2 are merged, but records 3 and 4 are not merged because the intervals are not “connected”. For OID 2 I expected to get a single merged interval. For OID 4 I expect to get 3 records since the records with C1=’D’ are not connected.<\/p>\n\n\n\n
So, the following query result is expected:<\/p>\n\n\n\n
Expected Result<\/span><\/path><\/path><\/svg><\/span>
       OID VALID_FROM  VALID_TO    C1<\/span><\/span>\n---------- ----------- ----------- ----------<\/span><\/span>\n         1 01-JAN-2010 31-MAR-2011 A<\/span><\/span>\n         1 01-JUN-2011 31-JAN-2012 A<\/span><\/span>\n         1 01-APR-2012 31-DEC-9999 A<\/span><\/span>\n         2 01-JAN-2010 31-DEC-9999 B<\/span><\/span>\n         4 01-JAN-2010 30-SEP-2011 D<\/span><\/span>\n         4 01-OCT-2011 30-SEP-2012<\/span><\/span>\n         4 01-OCT-2012 31-DEC-9999 D<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
The next query produces exactly this result.<\/p>\n\n\n\n
Query to Merge Non-Overlapping Intervals<\/span><\/path><\/path><\/svg><\/span>
WITH<\/span><\/span>\n   calc_various <\/span>AS<\/span> (<\/span><\/span>\n      <\/span>-- produces column has_gap with the following meaning:<\/span><\/span>\n      <\/span>-- 1: offset > 0 between current and previous record (gap)<\/span><\/span>\n      <\/span>-- 0: offset = 0 between current and previous record (no gap)<\/span><\/span>\n      <\/span>-- produces column new_group with the following meaning:<\/span><\/span>\n      <\/span>-- 1: group-by-columns differ in current and previous record<\/span><\/span>\n      <\/span>-- 0: same group-by-columns in current and previous record<\/span><\/span>\n      <\/span>SELECT<\/span> <\/span>oid<\/span>,<\/span><\/span>\n             valid_from,<\/span><\/span>\n             valid_to,<\/span><\/span>\n             c1,<\/span><\/span>\n             c2,<\/span><\/span>\n             <\/span>CASE<\/span><\/span>\n                <\/span>WHEN<\/span> <\/span>LAG<\/span>(valid_to, <\/span>1<\/span>, valid_from - <\/span>1<\/span>) <\/span>OVER<\/span>(<\/span><\/span>\n                        <\/span>PARTITION<\/span> <\/span>BY<\/span> <\/span>oid<\/span> <\/span>ORDER BY<\/span> valid_from<\/span><\/span>\n                     ) = valid_from - <\/span>1<\/span> <\/span>THEN<\/span><\/span>\n                   <\/span>0<\/span><\/span>\n                <\/span>ELSE<\/span><\/span>\n                   <\/span>1<\/span><\/span>\n             <\/span>END<\/span> <\/span>AS<\/span> has_gap,<\/span><\/span>\n             <\/span>CASE<\/span><\/span>\n                <\/span>WHEN<\/span> <\/span>LAG<\/span>(c1, <\/span>1<\/span>, c1) <\/span>OVER<\/span>(<\/span><\/span>\n                        <\/span>PARTITION<\/span> <\/span>BY<\/span> <\/span>oid<\/span> <\/span>ORDER BY<\/span> valid_from<\/span><\/span>\n                     ) = c1 <\/span>THEN<\/span><\/span>\n                   <\/span>0<\/span><\/span>\n                <\/span>ELSE<\/span><\/span>\n                   <\/span>1<\/span><\/span>\n             <\/span>END<\/span> <\/span>AS<\/span> new_group<\/span><\/span>\n        <\/span>FROM<\/span> t1<\/span><\/span>\n   ),<\/span><\/span>\n   calc_group <\/span>AS<\/span> (<\/span><\/span>\n      <\/span>-- produces column group_no, records with the same group_no<\/span><\/span>\n      <\/span>-- are mergeable, group_no is calculated per oid <\/span><\/span>\n      <\/span>SELECT<\/span> <\/span>oid<\/span>,<\/span><\/span>\n             valid_from,<\/span><\/span>\n             valid_to,<\/span><\/span>\n             c1,<\/span><\/span>\n             c2,<\/span><\/span>\n             <\/span>SUM<\/span>(has_gap + new_group) <\/span>OVER<\/span>(<\/span><\/span>\n                <\/span>PARTITION<\/span> <\/span>BY<\/span> <\/span>oid<\/span> <\/span>ORDER BY<\/span> <\/span>oid<\/span>, valid_from<\/span><\/span>\n             ) <\/span>AS<\/span> group_no<\/span><\/span>\n        <\/span>FROM<\/span> calc_various<\/span><\/span>\n   ),<\/span><\/span>\n   merged <\/span>AS<\/span> (<\/span><\/span>\n      <\/span>-- produces the final merged result<\/span><\/span>\n      <\/span>-- grouping by group_no ensures that gaps are honored<\/span><\/span>\n      <\/span>SELECT<\/span> <\/span>oid<\/span>,<\/span><\/span>\n             <\/span>MIN<\/span>(valid_from) <\/span>AS<\/span> valid_from,<\/span><\/span>\n             <\/span>MAX<\/span>(valid_to) <\/span>AS<\/span> valid_to,<\/span><\/span>\n             c1<\/span><\/span>\n        <\/span>FROM<\/span> calc_group<\/span><\/span>\n       <\/span>GROUP BY<\/span> <\/span>oid<\/span>, c1, group_no<\/span><\/span>\n       <\/span>ORDER BY<\/span> <\/span>oid<\/span>, valid_from<\/span><\/span>\n   )<\/span><\/span>\n-- main <\/span><\/span>\nSELECT<\/span> * <\/span>FROM<\/span> merged;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
The usability of the WITH clause aka Subquery Factoring Clause improved significantly with 11gR2. Since then it’s not necessary to reference all named queries anymore. The named queries become real transient views and this simplifies debugging a lot. – If you replace the content of line 57 with “SELECT * FROM calc_various ORDER BY oid, valid_from;” the query produces the following result:<\/p>\n\n\n\n
Result of calc_various<\/span><\/path><\/path><\/svg><\/span>
OID VALID_FROM  VALID_TO    C1 C2 HAS_GAP NEW_GROUP<\/span><\/span>\n--- ----------- ----------- -- -- ------- ---------<\/span><\/span>\n  1 01-JAN-2010 31-DEC-2010 A  B1       0         0<\/span><\/span>\n  1 01-JAN-2011 31-MAR-2011 A  B2       0         0<\/span><\/span>\n  1 01-JUN-2011 31-JAN-2012 A  B2       1         0<\/span><\/span>\n  1 01-APR-2012 31-DEC-9999 A  B4       1         0<\/span><\/span>\n  2 01-JAN-2010 31-JUL-2012 B  B1       0         0<\/span><\/span>\n  2 01-AUG-2012 31-DEC-9999 B  B2       0         0<\/span><\/span>\n  4 01-JAN-2010 30-SEP-2011 D  D1       0         0<\/span><\/span>\n  4 01-OCT-2011 30-SEP-2012    D2       0         1<\/span><\/span>\n  4 01-OCT-2012 31-DEC-9999 D  D3       0         1<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
You see that the value 1 for HAS_GAP indicates that the record is not “connected” with the previous record. Additionally, the value 1 for the column NEW_GROUP indicates that the records must not be merged even if they are connected.<\/p>\n\n\n\n
To simplify the calculation of NEW_GROUP for multiple group by columns (used in the named query “merged”) build a concatenated string of all relevant columns to deal with a single column similar to column C1 in this example.<\/p>\n\n\n\n
HAS_GAP and NEW_GROUP are used in the subsequent named query calc_group which produces the following result:<\/p>\n\n\n\n
Result of calc_group<\/span><\/path><\/path><\/svg><\/span>
OID VALID_FROM  VALID_TO    C1 C2 GROUP_NO<\/span><\/span>\n--- ----------- ----------- -- -- --------<\/span><\/span>\n  1 01-JAN-2010 31-DEC-2010 A  B1        0<\/span><\/span>\n  1 01-JAN-2011 31-MAR-2011 A  B2        0<\/span><\/span>\n  1 01-JUN-2011 31-JAN-2012 A  B2        1<\/span><\/span>\n  1 01-APR-2012 31-DEC-9999 A  B4        2<\/span><\/span>\n  2 01-JAN-2010 31-JUL-2012 B  B1        0<\/span><\/span>\n  2 01-AUG-2012 31-DEC-9999 B  B2        0<\/span><\/span>\n  4 01-JAN-2010 30-SEP-2011 D  D1        0<\/span><\/span>\n  4 01-OCT-2011 30-SEP-2012    D2        1<\/span><\/span>\n  4 01-OCT-2012 31-DEC-9999 D  D3        2<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
The GROUP_NO is calculated per OID. It’s technically a running total of HAS_GAP + NEW_GROUP. All intervals with the same GROUP_NO are mergeable. E.g. record 2, 3 and 4 have a different GROUP_NO which ensures that every single gap is honored for OID 1.<\/p>\n\n\n\n
Scenario B – With Overlapping intervals<\/h3>\n\n\n\n
The reality is that we have sometimes to deal with inconsistent data. E.g. duplicate data intervals, overlapping data intervals or even negative data intervals (valid_to < valid_from). I’ve created an example table t2 which is in fact a copy of t1 but includes an additional messy OID 3 with the following data:<\/p>\n\n\n\n
Test Data Set t2<\/span><\/path><\/path><\/svg><\/span>
SQL> SELECT * FROM t2 WHERE oid >= 3 ORDER BY vid;<\/span><\/span>\n<\/span><\/span>\n       VID        OID VALID_FROM  VALID_TO    C1         C2<\/span><\/span>\n---------- ---------- ----------- ----------- ---------- ----------<\/span><\/span>\n         7          3 01-JAN-2010 31-DEC-2010 C          B1<\/span><\/span>\n         8          3 01-JAN-2010 31-MAR-2010 C          B2<\/span><\/span>\n         9          3 01-JUN-2010 31-AUG-2010 C          B3<\/span><\/span>\n        10          3 01-OCT-2010 31-DEC-2010 C          B4<\/span><\/span>\n        11          3 01-FEB-2011 30-JUN-2011 C          B5<\/span><\/span>\n        12          3 01-FEB-2011 30-JUN-2011 C          B6<\/span><\/span>\n        13          3 01-JUN-2011 31-AUG-2011 C          B7<\/span><\/span>\n        14          3 31-AUG-2011 30-SEP-2011 C          B8<\/span><\/span>\n        15          3 01-DEC-2011 31-MAY-2012 C          B9<\/span><\/span>\n        16          3 01-DEC-2011 31-MAY-2012 C          B9<\/span><\/span>\n        17          3 01-JUN-2012 31-DEC-2012 C          B10<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
The following figure visualizes the intervals of OID 3. The green patterns are expected to be used and the red patterns are expected to be ignored. The rationale is that the VID (version ID) is typically based on an Oracle Sequence and therefore it’s assumed that higher VIDs are newer and therefore more adequate. In real cases, you may have additional information such as created_at or modified_on timestamps which helps you identify the record to be used in conflicting situations. Since data is considered inconsistent in this scenario the cleansing strategy might be very different in real cases. However, cleansing is always the first step and in this example, the highest VID has the highest priority in case of conflicts.<\/p>\n\n\n\n
\"MergingOverviewScenarioB\"<\/a><\/figure>\n\n\n\n
In cleansing step 1 we gather all potential starting intervals. We do not need all these intervals, but since we will merge intervals in the last processing step, we do not have to care about some unnecessary intervals right now.<\/p>\n\n\n\n
Cleansing Step 1<\/span><\/path><\/path><\/svg><\/span>
SQL> WITH<\/span><\/span>\n  2     o AS (<\/span><\/span>\n  3        -- object identifier and their valid_from values<\/span><\/span>\n  4        SELECT oid, valid_from FROM t2<\/span><\/span>\n  5        UNION<\/span><\/span>\n  6        SELECT oid, valid_to + 1 FROM t2<\/span><\/span>\n  7         WHERE valid_to != DATE '9999-12-31'<\/span><\/span>\n  8     )<\/span><\/span>\n  9  -- main<\/span><\/span>\n 10  SELECT * FROM o WHERE oid = 3;<\/span><\/span>\n<\/span><\/span>\n       OID VALID_FROM<\/span><\/span>\n---------- -----------<\/span><\/span>\n         3 01-JAN-2010<\/span><\/span>\n         3 01-APR-2010<\/span><\/span>\n         3 01-JUN-2010<\/span><\/span>\n         3 01-SEP-2010<\/span><\/span>\n         3 01-OCT-2010<\/span><\/span>\n         3 01-JAN-2011<\/span><\/span>\n         3 01-FEB-2011<\/span><\/span>\n         3 01-JUN-2011<\/span><\/span>\n         3 01-JUL-2011<\/span><\/span>\n         3 31-AUG-2011<\/span><\/span>\n         3 01-SEP-2011<\/span><\/span>\n         3 01-OCT-2011<\/span><\/span>\n         3 01-DEC-2011<\/span><\/span>\n         3 01-JUN-2012<\/span><\/span>\n         3 01-JAN-2013<\/span><\/span>\n<\/span><\/span>\n15 rows selected.<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
In cleansing step 2 we calculate the relevant VID for every previous result. Additionally, we may inexpensively calculate if an interval is a gap.<\/p>\n\n\n\n
Cleansing Step 1 and 2<\/span><\/path><\/path><\/svg><\/span>
SQL> WITH<\/span><\/span>\n  2     o AS (<\/span><\/span>\n  3        -- object identifier and their valid_from values<\/span><\/span>\n  4        SELECT oid, valid_from FROM t2<\/span><\/span>\n  5        UNION<\/span><\/span>\n  6        SELECT oid, valid_to + 1 FROM t2<\/span><\/span>\n  7         WHERE valid_to != DATE '9999-12-31'<\/span><\/span>\n  8     ),<\/span><\/span>\n  9     v AS (<\/span><\/span>\n 10        -- relevant version identifier per valid_from<\/span><\/span>\n 11        -- produces column is_gap<\/span><\/span>\n 12        SELECT o.oid,<\/span><\/span>\n 13               MAX(vid) AS vid,<\/span><\/span>\n 14               o.valid_from,<\/span><\/span>\n 15               NVL2(MAX(vid), 0, 1) AS is_gap<\/span><\/span>\n 16          FROM o<\/span><\/span>\n 17          LEFT JOIN t2<\/span><\/span>\n 18            ON t2.oid = o.oid<\/span><\/span>\n 19               AND o.valid_from BETWEEN t2.valid_from AND t2.valid_to<\/span><\/span>\n 20         GROUP BY o.oid, o.valid_from<\/span><\/span>\n 21     )<\/span><\/span>\n 22  -- main<\/span><\/span>\n 23  SELECT * FROM v WHERE oid = 3 ORDER BY valid_from;<\/span><\/span>\n<\/span><\/span>\n       OID        VID VALID_FROM      IS_GAP<\/span><\/span>\n---------- ---------- ----------- ----------<\/span><\/span>\n         3          8 01-JAN-2010          0<\/span><\/span>\n         3          7 01-APR-2010          0<\/span><\/span>\n         3          9 01-JUN-2010          0<\/span><\/span>\n         3          7 01-SEP-2010          0<\/span><\/span>\n         3         10 01-OCT-2010          0<\/span><\/span>\n         3            01-JAN-2011          1<\/span><\/span>\n         3         12 01-FEB-2011          0<\/span><\/span>\n         3         13 01-JUN-2011          0<\/span><\/span>\n         3         13 01-JUL-2011          0<\/span><\/span>\n         3         14 31-AUG-2011          0<\/span><\/span>\n         3         14 01-SEP-2011          0<\/span><\/span>\n         3            01-OCT-2011          1<\/span><\/span>\n         3         16 01-DEC-2011          0<\/span><\/span>\n         3         17 01-JUN-2012          0<\/span><\/span>\n         3            01-JAN-2013          1<\/span><\/span>\n<\/span><\/span>\n15 rows selected.<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
In cleansing step 3 we extend the previous result by the missing columns from table t2 and calculate the NEW_GROUP column with the same logic as in scenario A.<\/p>\n\n\n\n
Cleansing Step 1, 2 and 3<\/span><\/path><\/path><\/svg><\/span>
SQL> WITH<\/span><\/span>\n  2     o AS (<\/span><\/span>\n  3        -- object identifier and their valid_from values<\/span><\/span>\n  4        SELECT oid, valid_from FROM t2<\/span><\/span>\n  5        UNION<\/span><\/span>\n  6        SELECT oid, valid_to + 1 FROM t2<\/span><\/span>\n  7         WHERE valid_to != DATE '9999-12-31'<\/span><\/span>\n  8     ),<\/span><\/span>\n  9     v AS (<\/span><\/span>\n 10        -- relevant version identifier per valid_from<\/span><\/span>\n 11        -- produces column is_gap<\/span><\/span>\n 12        SELECT o.oid,<\/span><\/span>\n 13               MAX(vid) AS vid,<\/span><\/span>\n 14               o.valid_from,<\/span><\/span>\n 15               NVL2(MAX(vid), 0, 1) AS is_gap<\/span><\/span>\n 16          FROM o<\/span><\/span>\n 17          LEFT JOIN t2<\/span><\/span>\n 18            ON t2.oid = o.oid<\/span><\/span>\n 19               AND o.valid_from BETWEEN t2.valid_from AND t2.valid_to<\/span><\/span>\n 20         GROUP BY o.oid, o.valid_from<\/span><\/span>\n 21     ),<\/span><\/span>\n 22     combined AS (<\/span><\/span>\n 23        -- combines previous intermediate result v with t2<\/span><\/span>\n 24        -- produces the valid_to and new_group columns<\/span><\/span>\n 25        SELECT t2.vid,<\/span><\/span>\n 26               v.oid,<\/span><\/span>\n 27               v.valid_from,<\/span><\/span>\n 28               LEAD(v.valid_from - 1, 1, DATE '9999-12-31') OVER (<\/span><\/span>\n 29                  PARTITION BY v.oid ORDER BY v.valid_from<\/span><\/span>\n 30               ) AS valid_to,<\/span><\/span>\n 31               t2.c1,<\/span><\/span>\n 32               t2.c2,<\/span><\/span>\n 33               v.is_gap,<\/span><\/span>\n 34               CASE<\/span><\/span>\n 35                  WHEN LAG(t2.c1, 1, t2.c1) OVER(<\/span><\/span>\n 36                          PARTITION BY t2.oid ORDER BY t2.valid_from<\/span><\/span>\n 37                       ) = c1 THEN<\/span><\/span>\n 38                     0<\/span><\/span>\n 39                  ELSE<\/span><\/span>\n 40                     1<\/span><\/span>\n 41               END AS new_group<\/span><\/span>\n 42          FROM v<\/span><\/span>\n 43          LEFT JOIN t2<\/span><\/span>\n 44            ON t2.oid = v.oid<\/span><\/span>\n 45               AND t2.vid = v.vid<\/span><\/span>\n 46               AND v.valid_from BETWEEN t2.valid_from AND t2.valid_to<\/span><\/span>\n 47     )<\/span><\/span>\n 48  -- main<\/span><\/span>\n 49  SELECT * FROM combined WHERE oid = 3 ORDER BY valid_from;<\/span><\/span>\n<\/span><\/span>\nVID OID VALID_FROM  VALID_TO    C1 C2  IS_GAP NEW_GROUP<\/span><\/span>\n--- --- ----------- ----------- -- --- ------ ---------<\/span><\/span>\n  8   3 01-JAN-2010 31-MAR-2010 C  B2       0         0<\/span><\/span>\n  7   3 01-APR-2010 31-MAY-2010 C  B1       0         0<\/span><\/span>\n  9   3 01-JUN-2010 31-AUG-2010 C  B3       0         0<\/span><\/span>\n  7   3 01-SEP-2010 30-SEP-2010 C  B1       0         0<\/span><\/span>\n 10   3 01-OCT-2010 31-DEC-2010 C  B4       0         0<\/span><\/span>\n      3 01-JAN-2011 31-JAN-2011             1         1<\/span><\/span>\n 12   3 01-FEB-2011 31-MAY-2011 C  B6       0         0<\/span><\/span>\n 13   3 01-JUN-2011 30-JUN-2011 C  B7       0         0<\/span><\/span>\n 13   3 01-JUL-2011 30-AUG-2011 C  B7       0         0<\/span><\/span>\n 14   3 31-AUG-2011 31-AUG-2011 C  B8       0         0<\/span><\/span>\n 14   3 01-SEP-2011 30-SEP-2011 C  B8       0         0<\/span><\/span>\n      3 01-OCT-2011 30-NOV-2011             1         1<\/span><\/span>\n 16   3 01-DEC-2011 31-MAY-2012 C  B9       0         0<\/span><\/span>\n 17   3 01-JUN-2012 31-DEC-2012 C  B10      0         0<\/span><\/span>\n      3 01-JAN-2013 31-DEC-9999             1         1<\/span><\/span>\n<\/span><\/span>\n15 rows selected.<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
Now we have cleansed the data and are ready for the final steps “calc_group” and “merge” which are very similar to scenario A. The relevant difference is the highlighted line 70 which filters non-gap records. Here is the complete statement and the query result:<\/p>\n\n\n\n
SQL<\/span><\/path><\/path><\/svg><\/span>
WITH<\/span><\/span>\n   o <\/span>AS<\/span> (<\/span><\/span>\n      <\/span>-- object identifier and their valid_from values<\/span><\/span>\n      <\/span>SELECT<\/span> <\/span>oid<\/span>, valid_from <\/span>FROM<\/span> t2<\/span><\/span>\n      <\/span>UNION<\/span><\/span>\n      <\/span>SELECT<\/span> <\/span>oid<\/span>, valid_to + <\/span>1<\/span> <\/span>FROM<\/span> t2 <\/span><\/span>\n       <\/span>WHERE<\/span> valid_to != <\/span>DATE<\/span> <\/span>'9999-12-31'<\/span><\/span>\n   ),<\/span><\/span>\n   v <\/span>AS<\/span> (<\/span><\/span>\n      <\/span>-- relevant version identifier per valid_from <\/span><\/span>\n      <\/span>-- produces column is_gap<\/span><\/span>\n      <\/span>SELECT<\/span> o.oid,<\/span><\/span>\n             <\/span>MAX<\/span>(vid) <\/span>AS<\/span> vid,<\/span><\/span>\n             o.valid_from,<\/span><\/span>\n             NVL2(<\/span>MAX<\/span>(vid), <\/span>0<\/span>, <\/span>1<\/span>) <\/span>AS<\/span> is_gap<\/span><\/span>\n        <\/span>FROM<\/span> o<\/span><\/span>\n        <\/span>LEFT JOIN<\/span> t2<\/span><\/span>\n          <\/span>ON<\/span> t2.oid = o.oid<\/span><\/span>\n             <\/span>AND<\/span> o.valid_from <\/span>BETWEEN<\/span> t2.valid_from <\/span>AND<\/span> t2.valid_to<\/span><\/span>\n       <\/span>GROUP BY<\/span> o.oid, o.valid_from<\/span><\/span>\n   ),<\/span><\/span>\n   combined <\/span>AS<\/span> (<\/span><\/span>\n      <\/span>-- combines previous intermediate result v with t2<\/span><\/span>\n      <\/span>-- produces the valid_to and new_group columns<\/span><\/span>\n      <\/span>SELECT<\/span> t2.vid,<\/span><\/span>\n             v.oid,<\/span><\/span>\n             v.valid_from,<\/span><\/span>\n             <\/span>LEAD<\/span>(v.valid_from - <\/span>1<\/span>, <\/span>1<\/span>, <\/span>DATE<\/span> <\/span>'9999-12-31'<\/span>) <\/span>OVER<\/span> (<\/span><\/span>\n                <\/span>PARTITION<\/span> <\/span>BY<\/span> v.oid <\/span>ORDER BY<\/span> v.valid_from<\/span><\/span>\n             ) <\/span>AS<\/span> valid_to,<\/span><\/span>\n             t2.c1,<\/span><\/span>\n             t2.c2,<\/span><\/span>\n             v.is_gap,<\/span><\/span>\n             <\/span>CASE<\/span><\/span>\n                <\/span>WHEN<\/span> <\/span>LAG<\/span>(t2.c1, <\/span>1<\/span>, t2.c1) <\/span>OVER<\/span>(<\/span><\/span>\n                        <\/span>PARTITION<\/span> <\/span>BY<\/span> t2.oid <\/span>ORDER BY<\/span> t2.valid_from<\/span><\/span>\n                     ) = c1 <\/span>THEN<\/span><\/span>\n                   <\/span>0<\/span><\/span>\n                <\/span>ELSE<\/span><\/span>\n                   <\/span>1<\/span><\/span>\n             <\/span>END<\/span> <\/span>AS<\/span> new_group<\/span><\/span>\n        <\/span>FROM<\/span> v<\/span><\/span>\n        <\/span>LEFT JOIN<\/span> t2<\/span><\/span>\n          <\/span>ON<\/span> t2.oid = v.oid<\/span><\/span>\n             <\/span>AND<\/span> t2.vid = v.vid<\/span><\/span>\n             <\/span>AND<\/span> v.valid_from <\/span>BETWEEN<\/span> t2.valid_from <\/span>AND<\/span> t2.valid_to <\/span><\/span>\n   ),<\/span><\/span>\n   calc_group <\/span>AS<\/span> (<\/span><\/span>\n      <\/span>-- produces column group_no, records with the same group_no<\/span><\/span>\n      <\/span>-- are mergeable, group_no is calculated per oid <\/span><\/span>\n      <\/span>SELECT<\/span> <\/span>oid<\/span>,<\/span><\/span>\n             valid_from,<\/span><\/span>\n             valid_to,<\/span><\/span>\n             c1,<\/span><\/span>\n             c2,<\/span><\/span>\n             is_gap,<\/span><\/span>\n             <\/span>SUM<\/span>(is_gap + new_group) <\/span>OVER<\/span>(<\/span><\/span>\n                <\/span>PARTITION<\/span> <\/span>BY<\/span> <\/span>oid<\/span> <\/span>ORDER BY<\/span> <\/span>oid<\/span>, valid_from<\/span><\/span>\n             ) <\/span>AS<\/span> group_no<\/span><\/span>\n        <\/span>FROM<\/span> combined<\/span><\/span>\n   ),<\/span><\/span>\n   merged <\/span>AS<\/span> (<\/span><\/span>\n      <\/span>-- produces the final merged result<\/span><\/span>\n      <\/span>-- grouping by group_no ensures that gaps are honored<\/span><\/span>\n      <\/span>SELECT<\/span> <\/span>oid<\/span>,<\/span><\/span>\n             <\/span>MIN<\/span>(valid_from) <\/span>AS<\/span> valid_from,<\/span><\/span>\n             <\/span>MAX<\/span>(valid_to) <\/span>AS<\/span> valid_to,<\/span><\/span>\n             c1<\/span><\/span>\n        <\/span>FROM<\/span> calc_group<\/span><\/span>\n       <\/span>WHERE<\/span> is_gap = <\/span>0<\/span><\/span>\n       <\/span>GROUP BY<\/span> <\/span>OID<\/span>, c1, group_no<\/span><\/span>\n       <\/span>ORDER BY<\/span> <\/span>OID<\/span>, valid_from<\/span><\/span>\n   )<\/span><\/span>\n-- main <\/span><\/span>\nSELECT<\/span> * <\/span>FROM<\/span> merged;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
Final Query Result<\/span><\/path><\/path><\/svg><\/span>
       OID VALID_FROM  VALID_TO    C1<\/span><\/span>\n---------- ----------- ----------- ----------<\/span><\/span>\n         1 01-JAN-2010 31-MAR-2011 A<\/span><\/span>\n         1 01-JUN-2011 31-JAN-2012 A<\/span><\/span>\n         1 01-APR-2012 31-DEC-9999 A<\/span><\/span>\n         2 01-JAN-2010 31-DEC-9999 B<\/span><\/span>\n         3 01-JAN-2010 31-DEC-2010 C<\/span><\/span>\n         3 01-FEB-2011 30-SEP-2011 C<\/span><\/span>\n         3 01-DEC-2011 31-DEC-2012 C<\/span><\/span>\n         4 01-JAN-2010 30-SEP-2011 D<\/span><\/span>\n         4 01-OCT-2011 30-SEP-2012<\/span><\/span>\n         4 01-OCT-2012 31-DEC-9999 D<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
If you change line 70 to “WHERE is_gap = 1” you’ll get all gap records, just a way to query non-existing intervals.<\/p>\n\n\n\n
All Gap Records<\/span><\/path><\/path><\/svg><\/span>
       OID VALID_FROM  VALID_TO    C1<\/span><\/span>\n---------- ----------- ----------- ----------<\/span><\/span>\n         1 01-APR-2011 31-MAY-2011<\/span><\/span>\n         1 01-FEB-2012 31-MAR-2012<\/span><\/span>\n         3 01-JAN-2011 31-JAN-2011<\/span><\/span>\n         3 01-OCT-2011 30-NOV-2011<\/span><\/span>\n         3 01-JAN-2013 31-DEC-9999<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
Conclusion<\/h3>\n\n\n\n
Merging temporal intervals is challenging, especially if the history has gaps and the data is inconsistent as in scenario B. However, the SQL engine is a powerful tool to clean up data and merge the temporal intervals efficiently in a single SQL statement.<\/p>\n","protected":false},"excerpt":{"rendered":"
In Joining Temporal Intervals I explained how to join multiple temporal tables. The provided solution merges also temporal intervals but – as pointed out in that post – may produce wrong results if the underlying driving table is not gaplessly historized. In this post, I’ll explain how to merge temporal intervals with various [\u2026]<\/span><\/p>\n","protected":false},"author":1,"featured_media":766,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5],"tags":[42,43],"class_list":["post-355","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-oracle","tag-temporal-database","tag-valid-time"],"yoast_head":"\nMerging Temporal Intervals with Gaps - Philipp Salvisberg's Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.salvis.com\/blog\/2012\/12\/27\/merging-temporal-intervals-with-gaps\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Merging Temporal Intervals with Gaps - Philipp Salvisberg's Blog\" \/>\n<meta property=\"og:description\" content=\"In Joining Temporal Intervals I explained how to join multiple temporal tables. The provided solution merges also temporal intervals but – as pointed out in that post – may produce wrong results if the underlying driving table is not gaplessly historized. In this post, I’ll explain how to merge temporal intervals with various [\u2026]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.salvis.com\/blog\/2012\/12\/27\/merging-temporal-intervals-with-gaps\/\" \/>\n<meta property=\"og:site_name\" content=\"Philipp Salvisberg's Blog\" \/>\n<meta property=\"article:published_time\" content=\"2012-12-27T15:05:24+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2023-11-08T19:02:18+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2012\/12\/MergingOverviewScenarioB1.png\" \/>\n\t<meta property=\"og:image:width\" content=\"749\" \/>\n\t<meta property=\"og:image:height\" content=\"294\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Philipp Salvisberg\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@phsalvisberg\" \/>\n<meta name=\"twitter:site\" content=\"@phsalvisberg\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Philipp Salvisberg\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"8 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2012\\\/12\\\/27\\\/merging-temporal-intervals-with-gaps\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2012\\\/12\\\/27\\\/merging-temporal-intervals-with-gaps\\\/\"},\"author\":{\"name\":\"Philipp Salvisberg\",\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/#\\\/schema\\\/person\\\/34352245c48678b1a5a05d4bc1339515\"},\"headline\":\"Merging Temporal Intervals with Gaps\",\"datePublished\":\"2012-12-27T15:05:24+00:00\",\"dateModified\":\"2023-11-08T19:02:18+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2012\\\/12\\\/27\\\/merging-temporal-intervals-with-gaps\\\/\"},\"wordCount\":830,\"commentCount\":1,\"publisher\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/#\\\/schema\\\/person\\\/34352245c48678b1a5a05d4bc1339515\"},\"image\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2012\\\/12\\\/27\\\/merging-temporal-intervals-with-gaps\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/2012\\\/12\\\/MergingOverviewScenarioB1.png\",\"keywords\":[\"Temporal Database\",\"Valid Time\"],\"articleSection\":[\"Oracle\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2012\\\/12\\\/27\\\/merging-temporal-intervals-with-gaps\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2012\\\/12\\\/27\\\/merging-temporal-intervals-with-gaps\\\/\",\"url\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2012\\\/12\\\/27\\\/merging-temporal-intervals-with-gaps\\\/\",\"name\":\"Merging Temporal Intervals with Gaps - Philipp Salvisberg's Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2012\\\/12\\\/27\\\/merging-temporal-intervals-with-gaps\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2012\\\/12\\\/27\\\/merging-temporal-intervals-with-gaps\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/2012\\\/12\\\/MergingOverviewScenarioB1.png\",\"datePublished\":\"2012-12-27T15:05:24+00:00\",\"dateModified\":\"2023-11-08T19:02:18+00:00\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2012\\\/12\\\/27\\\/merging-temporal-intervals-with-gaps\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2012\\\/12\\\/27\\\/merging-temporal-intervals-with-gaps\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2012\\\/12\\\/27\\\/merging-temporal-intervals-with-gaps\\\/#primaryimage\",\"url\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/2012\\\/12\\\/MergingOverviewScenarioB1.png\",\"contentUrl\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/2012\\\/12\\\/MergingOverviewScenarioB1.png\",\"width\":749,\"height\":294},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2012\\\/12\\\/27\\\/merging-temporal-intervals-with-gaps\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Merging Temporal Intervals with Gaps\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/\",\"name\":\"Philipp Salvisberg's Blog\",\"description\":\"Database-centric development\",\"publisher\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/#\\\/schema\\\/person\\\/34352245c48678b1a5a05d4bc1339515\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/#\\\/schema\\\/person\\\/34352245c48678b1a5a05d4bc1339515\",\"name\":\"Philipp Salvisberg\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/2010\\\/11\\\/phs_trivadis4.jpg\",\"url\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/2010\\\/11\\\/phs_trivadis4.jpg\",\"contentUrl\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/2010\\\/11\\\/phs_trivadis4.jpg\",\"width\":400,\"height\":400,\"caption\":\"Philipp Salvisberg\"},\"logo\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/2010\\\/11\\\/phs_trivadis4.jpg\"},\"sameAs\":[\"http:\\\/\\\/www.salvis.com\\\/\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Merging Temporal Intervals with Gaps - Philipp Salvisberg's Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.salvis.com\/blog\/2012\/12\/27\/merging-temporal-intervals-with-gaps\/","og_locale":"en_US","og_type":"article","og_title":"Merging Temporal Intervals with Gaps - Philipp Salvisberg's Blog","og_description":"In Joining Temporal Intervals I explained how to join multiple temporal tables. The provided solution merges also temporal intervals but – as pointed out in that post – may produce wrong results if the underlying driving table is not gaplessly historized. In this post, I’ll explain how to merge temporal intervals with various [\u2026]","og_url":"https:\/\/www.salvis.com\/blog\/2012\/12\/27\/merging-temporal-intervals-with-gaps\/","og_site_name":"Philipp Salvisberg's Blog","article_published_time":"2012-12-27T15:05:24+00:00","article_modified_time":"2023-11-08T19:02:18+00:00","og_image":[{"width":749,"height":294,"url":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2012\/12\/MergingOverviewScenarioB1.png","type":"image\/png"}],"author":"Philipp Salvisberg","twitter_card":"summary_large_image","twitter_creator":"@phsalvisberg","twitter_site":"@phsalvisberg","twitter_misc":{"Written by":"Philipp Salvisberg","Est. reading time":"8 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.salvis.com\/blog\/2012\/12\/27\/merging-temporal-intervals-with-gaps\/#article","isPartOf":{"@id":"https:\/\/www.salvis.com\/blog\/2012\/12\/27\/merging-temporal-intervals-with-gaps\/"},"author":{"name":"Philipp Salvisberg","@id":"https:\/\/www.salvis.com\/blog\/#\/schema\/person\/34352245c48678b1a5a05d4bc1339515"},"headline":"Merging Temporal Intervals with Gaps","datePublished":"2012-12-27T15:05:24+00:00","dateModified":"2023-11-08T19:02:18+00:00","mainEntityOfPage":{"@id":"https:\/\/www.salvis.com\/blog\/2012\/12\/27\/merging-temporal-intervals-with-gaps\/"},"wordCount":830,"commentCount":1,"publisher":{"@id":"https:\/\/www.salvis.com\/blog\/#\/schema\/person\/34352245c48678b1a5a05d4bc1339515"},"image":{"@id":"https:\/\/www.salvis.com\/blog\/2012\/12\/27\/merging-temporal-intervals-with-gaps\/#primaryimage"},"thumbnailUrl":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2012\/12\/MergingOverviewScenarioB1.png","keywords":["Temporal Database","Valid Time"],"articleSection":["Oracle"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.salvis.com\/blog\/2012\/12\/27\/merging-temporal-intervals-with-gaps\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.salvis.com\/blog\/2012\/12\/27\/merging-temporal-intervals-with-gaps\/","url":"https:\/\/www.salvis.com\/blog\/2012\/12\/27\/merging-temporal-intervals-with-gaps\/","name":"Merging Temporal Intervals with Gaps - Philipp Salvisberg's Blog","isPartOf":{"@id":"https:\/\/www.salvis.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.salvis.com\/blog\/2012\/12\/27\/merging-temporal-intervals-with-gaps\/#primaryimage"},"image":{"@id":"https:\/\/www.salvis.com\/blog\/2012\/12\/27\/merging-temporal-intervals-with-gaps\/#primaryimage"},"thumbnailUrl":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2012\/12\/MergingOverviewScenarioB1.png","datePublished":"2012-12-27T15:05:24+00:00","dateModified":"2023-11-08T19:02:18+00:00","breadcrumb":{"@id":"https:\/\/www.salvis.com\/blog\/2012\/12\/27\/merging-temporal-intervals-with-gaps\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.salvis.com\/blog\/2012\/12\/27\/merging-temporal-intervals-with-gaps\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.salvis.com\/blog\/2012\/12\/27\/merging-temporal-intervals-with-gaps\/#primaryimage","url":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2012\/12\/MergingOverviewScenarioB1.png","contentUrl":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2012\/12\/MergingOverviewScenarioB1.png","width":749,"height":294},{"@type":"BreadcrumbList","@id":"https:\/\/www.salvis.com\/blog\/2012\/12\/27\/merging-temporal-intervals-with-gaps\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.salvis.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Merging Temporal Intervals with Gaps"}]},{"@type":"WebSite","@id":"https:\/\/www.salvis.com\/blog\/#website","url":"https:\/\/www.salvis.com\/blog\/","name":"Philipp Salvisberg's Blog","description":"Database-centric development","publisher":{"@id":"https:\/\/www.salvis.com\/blog\/#\/schema\/person\/34352245c48678b1a5a05d4bc1339515"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.salvis.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"https:\/\/www.salvis.com\/blog\/#\/schema\/person\/34352245c48678b1a5a05d4bc1339515","name":"Philipp Salvisberg","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2010\/11\/phs_trivadis4.jpg","url":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2010\/11\/phs_trivadis4.jpg","contentUrl":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2010\/11\/phs_trivadis4.jpg","width":400,"height":400,"caption":"Philipp Salvisberg"},"logo":{"@id":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2010\/11\/phs_trivadis4.jpg"},"sameAs":["http:\/\/www.salvis.com\/"]}]}},"_links":{"self":[{"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/posts\/355","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/comments?post=355"}],"version-history":[{"count":9,"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/posts\/355\/revisions"}],"predecessor-version":[{"id":12749,"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/posts\/355\/revisions\/12749"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/media\/766"}],"wp:attachment":[{"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/media?parent=355"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/categories?post=355"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/tags?post=355"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}