合并日期时间范围数据集：评级数据合并技术问询

Alright, let's tackle this problem. You've got a rating history table where each agency's rating change triggers a new record (with the old one's EndDate filled in), and you need to merge these into unified time intervals that show all agencies' ratings for each period. Here are a few solid technical solutions depending on your environment:

First, let's clarify your sample data #

The goal is to create continuous time intervals where every interval shows the active rating for all agencies at that time.

Solution 1: SQL-Based Interval Merging (Most Common for Databases) #

This works for all major relational databases (SQL Server, PostgreSQL, MySQL, etc.). The core idea is to first identify all critical time points (where any rating changes), then build continuous intervals from those points, and finally map each agency's active rating to each interval.

Example Code (SQL Server Syntax)

WITH AllTimePoints AS (
    -- Grab every date where a rating starts or ends (excluding NULL EndDates for now)
    SELECT StartDate AS TimePoint FROM YourRatingTable
    UNION
    SELECT EndDate AS TimePoint FROM YourRatingTable WHERE EndDate IS NOT NULL
),
TimeIntervals AS (
    -- Create continuous intervals using LEAD() to get the next time point
    SELECT 
        InstrID,
        TimePoint AS IntervalStart,
        LEAD(TimePoint) OVER (PARTITION BY InstrID ORDER BY TimePoint) AS IntervalEnd
    FROM AllTimePoints
    CROSS JOIN (SELECT DISTINCT InstrID FROM YourRatingTable) AS Instrs
),
FinalIntervals AS (
    -- Handle open-ended intervals (NULL EndDate = currently active)
    SELECT 
        InstrID,
        IntervalStart,
        CASE 
            WHEN IntervalEnd IS NULL THEN GETDATE() -- Use current time for active ratings
            ELSE DATEADD(MILLISECOND, -1, IntervalEnd) -- Adjust to avoid overlapping intervals
        END AS IntervalEnd
    FROM TimeIntervals
    WHERE IntervalStart IS NOT NULL
)
-- Join intervals to ratings and pivot to show all agencies in one row
SELECT 
    fi.InstrID,
    fi.IntervalStart,
    fi.IntervalEnd,
    MAX(CASE WHEN rt.RatingName = 'FitchIntLT' THEN rt.RatingValue END) AS FitchRating,
    MAX(CASE WHEN rt.RatingName = 'MoodyIntLT' THEN rt.RatingValue END) AS MoodyRating,
    MAX(CASE WHEN rt.RatingName = 'SPIntLT' THEN rt.RatingValue END) AS SPRating
FROM FinalIntervals fi
LEFT JOIN YourRatingTable rt 
    ON fi.InstrID = rt.InstrID
    AND rt.StartDate <= fi.IntervalStart
    AND (rt.EndDate IS NULL OR rt.EndDate >= fi.IntervalEnd)
GROUP BY fi.InstrID, fi.IntervalStart, fi.IntervalEnd
ORDER BY fi.InstrID, fi.IntervalStart;

Key Notes:

• AllTimePoints ensures we don't miss any rating change events
• LEAD() helps us build seamless, non-overlapping intervals
• The CASE statement in FinalIntervals handles open-ended ratings (still active as of today)
• Pivoting with MAX(CASE...) consolidates all agency ratings into a single row per interval

Solution 2: Python Pandas (For Data Analysis/ETL Workflows) #

If you're working in a Python environment, Pandas makes it easy to manipulate time intervals and merge ratings:

import pandas as pd

# Load your sample data
data = [
    [1234, '2014-09-05 00:00:00.000', '2016-06-26 23:59:59.000', 'FitchIntLT', 'AA+'],
    [1234, '2014-09-05 00:00:00.000', '2017-09-21 23:59:59.000', 'MoodyIntLT', 'Aa1'],
    [1234, '2014-09-05 00:00:00.000', None, 'SPIntLT', 'NR'],
    [1234, '2016-06-27 00:00:00.000', None, 'FitchIntLT', 'AA'],
    [1234, '2017-09-22 00:00:00.000', None, 'MoodyIntLT', 'Aa2'],
]
df = pd.DataFrame(data, columns=['InstrID', 'StartDate', 'EndDate', 'RatingName', 'RatingValue'])

# Clean and format dates
df['StartDate'] = pd.to_datetime(df['StartDate'])
df['EndDate'] = pd.to_datetime(df['EndDate']).fillna(pd.Timestamp.now())

# Generate all critical time points and build intervals
all_dates = pd.Series(df['StartDate'].tolist() + df['EndDate'].tolist()).sort_values().unique()
intervals = pd.DataFrame({
    'IntervalStart': all_dates[:-1],
    'IntervalEnd': all_dates[1:]
})
intervals['InstrID'] = 1234  # For multiple instruments, group by InstrID here

# Merge intervals with ratings and pivot to wide format
merged = pd.merge(
    intervals,
    df,
    on='InstrID',
    how='left'
).query('StartDate <= IntervalStart and EndDate >= IntervalEnd')

result = merged.pivot_table(
    index=['InstrID', 'IntervalStart', 'IntervalEnd'],
    columns='RatingName',
    values='RatingValue',
    aggfunc='first'
).reset_index()

print(result)

Critical Things to Keep in Mind #

1. Handling NULL EndDates: Always replace NULLs with a current/future date to ensure your intervals cover active ratings.
2. Avoiding Overlaps: Adjust interval end times (e.g., subtract 1 millisecond) to prevent two adjacent intervals from sharing the same timestamp.
3. Multi-Instrument Support: All solutions should be grouped by InstrID to ensure each instrument's rating history is processed independently.

内容的提问来源于stack exchange，提问作者chrissy p