Oracle SQL脚本需求：补全缺失日期并回填ID最近有效值

Let's break down how to solve this problem step by step. We need two core outcomes here: generate every date between the earliest and latest date in your dataset, and for each ID, carry forward the most recent available numerical value to fill in the gaps where data is missing.

Step 1: Define the Data Scope #

First, let's lock in our boundaries. Your dataset spans from 2/4/17 to 2/11/17, so we'll generate every date in this interval. We also need all unique IDs present in your data—3, 4, 7, 9—to ensure each ID has a record for every date.

Step 2: Full SQL Script with Explanations #

Here's a complete, commented script that delivers the desired result:

WITH date_range AS (
    -- Generate all dates between the first and last date in your original data
    SELECT TRUNC(TO_DATE('2/4/17', 'MM/DD/YY') + LEVEL - 1) AS fill_date
    FROM dual
    CONNECT BY LEVEL <= (TO_DATE('2/11/17', 'MM/DD/YY') - TO_DATE('2/4/17', 'MM/DD/YY') + 1)
),
unique_ids AS (
    -- Grab all distinct IDs from your source table
    SELECT DISTINCT id FROM your_table_name
),
all_date_id_pairs AS (
    -- Create every possible combination of date and ID to cover missing entries
    SELECT dr.fill_date, ui.id
    FROM date_range dr
    CROSS JOIN unique_ids ui
),
joined_data AS (
    -- Merge with original data to keep existing values, leave NULLs for missing dates
    SELECT adip.fill_date, adip.id, ytn.value
    FROM all_date_id_pairs adip
    LEFT JOIN your_table_name ytn
        ON adip.fill_date = TRUNC(TO_DATE(ytn.date_column, 'MM/DD/YY'))
        AND adip.id = ytn.id
)
-- Use LAST_VALUE to backfill the most recent non-null value for each ID
SELECT 
    TO_CHAR(fill_date, 'MM/DD/YY') AS date,
    id,
    LAST_VALUE(value IGNORE NULLS) OVER (
        PARTITION BY id 
        ORDER BY fill_date 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS filled_value
FROM joined_data
ORDER BY fill_date, id;

Key Details You Should Know: #

• date_range CTE: Uses Oracle's CONNECT BY syntax to generate a continuous sequence of dates. We convert string dates to proper DATE types and use TRUNC to ensure we're working with whole dates (no time components).
• unique_ids CTE: Pulls all distinct IDs so we don't skip any when creating our full set of date-ID pairs.
• all_date_id_pairs CTE: Cross-joins dates and IDs to create a complete grid of every date each ID should have—this fixes the missing date problem.
• LAST_VALUE Window Function: The IGNORE NULLS clause is critical here—it tells Oracle to skip empty values when searching for the most recent valid value. The window (PARTITION BY id ORDER BY fill_date) ensures we only look at values for the same ID, ordered by date, and carry the last non-null value forward to fill gaps.

Quick Notes for Adaptation: #

• Replace your_table_name and date_column with your actual table and date column names.
• If your date column is already a DATE type (not a string), you can remove the TO_DATE conversion in the joined_data CTE.
• For IDs that don't have any prior values (like ID=7, which first appears on 2/11/17), dates before their first entry will remain NULL—this aligns with the "recent available value" requirement, since there's no prior value to backfill.

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