时间维度表合并操作：是否存在成熟算法？

Yes, this is a standard operation in temporal database management, commonly referred to as temporal interval overlay or conflicting temporal update resolution. There are well-established algorithms and database features to handle this scenario where new time intervals overwrite existing ones.

Core Algorithm Steps #

The underlying logic follows these key steps:

• Extract critical time points: Gather all from and to values from both the original dataset and the new incoming data. These points are the boundaries where interval splits occur.
• Generate base intervals: Create a set of non-overlapping, contiguous intervals using these critical points, ensuring full coverage of the entire time range involved.
• Resolve precedence: For each base interval, determine which value (original or new) takes priority. Since your requirement is that new data overwrites existing, any interval overlapping with the new data uses the new price; otherwise, retain the original price if the interval falls within an original entry.
• Clean up: Remove any empty intervals (where from equals to) and discard intervals with no valid price assignment.

Database Support #

Major database systems and standards include built-in or extension-based support for this:

• SQL:2011 Standard: Defines temporal table features that handle interval merging and conflict resolution as part of its temporal data handling specifications.
• PostgreSQL: Extensions like temporal_tables or pg_temporal provide functions to manage versioned temporal data, including overlay operations.
• SQL Server: Temporal tables (system-versioned) support updating intervals with automatic history tracking, which can be adapted to this overwrite scenario.

Example SQL Implementation #

For your pricedata scenario, here's a SQL query that implements the merge logic:

WITH all_time_points AS (
    SELECT "from" AS point FROM pricedata
    UNION
    SELECT "to" AS point FROM pricedata
    UNION
    SELECT '2010' AS point
    UNION
    SELECT '2012' AS point
),
sorted_points AS (
    SELECT point, ROW_NUMBER() OVER (ORDER BY point) AS rn
    FROM all_time_points
),
base_intervals AS (
    SELECT
        sp1.point AS "from",
        sp2.point AS "to"
    FROM sorted_points sp1
    JOIN sorted_points sp2 ON sp1.rn = sp2.rn - 1
),
interval_prices AS (
    SELECT
        bi."from",
        bi."to",
        COALESCE(new.price, orig.price) AS price
    FROM base_intervals bi
    LEFT JOIN pricedata orig
        ON bi."from" >= orig."from" AND bi."to" <= orig."to"
    LEFT JOIN (SELECT '2010' AS "from", '2012' AS "to", 109 AS price) new
        ON bi."from" >= new."from" AND bi."to" <= new."to"
)
SELECT "from", "to", price
FROM interval_prices
WHERE price IS NOT NULL AND "from" != "to"
ORDER BY "from";

This query will produce your expected output by splitting intervals at the critical points and applying the new price where it overlaps.

内容的提问来源于stack exchange，提问作者George Menoutis