如何基于周期复制数据记录，生成连续周期并填充历史数据（SQL）

Got it, let's tackle this problem where we need to fill in the missing periods for each customer up to period 15, using the last available Date and Product values. Here are a couple of solid SQL approaches that work across most modern databases (PostgreSQL, SQL Server, MySQL 8.0+, etc.):

Approach 1: Using Recursive CTE + Interval Matching #

This method first generates a full set of continuous periods for each customer, then maps each period to its corresponding "active" interval from the original data.

WITH cust_periods AS (
    -- Get all unique customer IDs
    SELECT DISTINCT Customer_No
    FROM tbl_cust
),
continuous_periods AS (
    -- Recursively generate periods 1 to 15 for each customer
    SELECT Customer_No, 1 AS period
    FROM cust_periods
    UNION ALL
    SELECT Customer_No, period + 1
    FROM continuous_periods
    WHERE period < 15
),
cust_data_with_boundaries AS (
    -- For each original record, get the start of the next period (or 16 for the last record)
    SELECT 
        *,
        LEAD(period, 1, 16) OVER (PARTITION BY Customer_No ORDER BY period) AS next_period_start
    FROM tbl_cust
)
-- Match each continuous period to its corresponding active interval
SELECT 
    cp.period,
    cp.Customer_No,
    cd.Date,
    cd.Product
FROM continuous_periods cp
JOIN cust_data_with_boundaries cd 
    ON cp.Customer_No = cd.Customer_No
    AND cp.period >= cd.period
    AND cp.period < cd.next_period_start
ORDER BY cp.Customer_No, cp.period;

How this works: #

1. cust_periods: Isolates all unique customers we need to process.
2. continuous_periods: Uses recursion to create every period from 1 to 15 for each customer.
3. cust_data_with_boundaries: Uses the LEAD window function to define the end of each original period's validity (the start of the next period). For the last record, we set next_period_start to 16 so it covers all periods up to 15.
4. The final join matches each continuous period to the original record whose interval it falls into, pulling the correct Date and Product.

Approach 2: Using Last Value Propagation #

This method first combines the continuous periods with the original data (leaving NULLs for missing periods), then uses window functions to fill those NULLs with the last non-NULL value.

For PostgreSQL/SQL Server: #

WITH continuous_periods AS (
    -- Generate periods 1-15 for each customer (PostgreSQL uses generate_series; adjust for SQL Server)
    SELECT 
        Customer_No,
        generate_series(1, 15) AS period
    FROM (SELECT DISTINCT Customer_No FROM tbl_cust) custs
),
combined_data AS (
    -- Join continuous periods with original data (NULLs for missing periods)
    SELECT 
        cp.period,
        cp.Customer_No,
        tc.Date,
        tc.Product
    FROM continuous_periods cp
    LEFT JOIN tbl_cust tc 
        ON cp.Customer_No = tc.Customer_No 
        AND cp.period = tc.period
)
-- Fill NULLs with the last non-NULL Date/Product for each customer
SELECT 
    period,
    Customer_No,
    LAST_VALUE(Date IGNORE NULLS) OVER (
        PARTITION BY Customer_No 
        ORDER BY period 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS Date,
    LAST_VALUE(Product IGNORE NULLS) OVER (
        PARTITION BY Customer_No 
        ORDER BY period 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS Product
FROM combined_data
ORDER BY Customer_No, period;

For MySQL (no IGNORE NULLS support): #

MySQL doesn't support IGNORE NULLS in window functions, so we can use user variables to track the last valid value instead:

WITH RECURSIVE continuous_periods AS (
    SELECT Customer_No, 1 AS period
    FROM (SELECT DISTINCT Customer_No FROM tbl_cust) custs
    UNION ALL
    SELECT Customer_No, period + 1
    FROM continuous_periods
    WHERE period < 15
),
combined_data AS (
    SELECT 
        cp.period,
        cp.Customer_No,
        tc.Date,
        tc.Product
    FROM continuous_periods cp
    LEFT JOIN tbl_cust tc 
        ON cp.Customer_No = tc.Customer_No 
        AND cp.period = tc.period
)
SELECT 
    period,
    Customer_No,
    @prev_date := CASE 
        WHEN Date IS NOT NULL THEN Date 
        ELSE @prev_date 
    END AS Date,
    @prev_prod := CASE 
        WHEN Product IS NOT NULL THEN Product 
        ELSE @prev_prod 
    END AS Product
FROM combined_data, (SELECT @prev_date := NULL, @prev_prod := NULL) vars
ORDER BY Customer_No, period;

How this works: #

1. continuous_periods: Creates the full set of periods for each customer.
2. combined_data: Left joins the continuous periods with the original data, resulting in NULLs where periods are missing.
3. The final select uses either LAST_VALUE (with IGNORE NULLS) or user variables to "carry forward" the last valid Date and Product values to fill in the NULLs.

Both approaches will produce exactly the output you provided, with continuous periods up to 15 and missing values filled from the previous valid record.

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