基于末次每日记录复制数据填充日期区间缺失记录的可行性及实现方案咨询

Absolutely! This is a common data gap-filling scenario and totally achievable. Here's a step-by-step breakdown and concrete SQL solutions tailored for popular databases:

Core Approach #

The idea is straightforward:

1. Fetch the valid records from 2020-12-31 for each account + fundnumber pair (your question confirms these are complete, so we can rely on them as the base)
2. Generate every date between 2021-01-01 and 2021-02-28 to cover the gap
3. Cross-join the 2020-12-31 records with the generated dates to create fill-in data
4. Insert these records into your original table, with safeguards to avoid duplicates

Solution for MySQL 8.0+ (Using Recursive CTE) #

MySQL 8.0 and above support recursive common table expressions, which let us generate the missing date range easily:

-- Step 1: Generate all dates in the gap period
WITH RECURSIVE date_range AS (
    SELECT '2021-01-01' AS fill_date
    UNION ALL
    SELECT DATE_ADD(fill_date, INTERVAL 1 DAY)
    FROM date_range
    WHERE fill_date < '2021-02-28'
),
-- Step 2: Pull the 2020-12-31 baseline records
baseline_records AS (
    SELECT account, fundnumber, amount
    FROM your_table_name
    WHERE date = '2020-12-31'
)
-- Step 3: Insert the fill-in data (with duplicate protection)
INSERT INTO your_table_name (account, fundnumber, amount, date)
SELECT br.account, br.fundnumber, br.amount, dr.fill_date
FROM baseline_records br
CROSS JOIN date_range dr
WHERE NOT EXISTS (
    SELECT 1
    FROM your_table_name t
    WHERE t.account = br.account 
      AND t.fundnumber = br.fundnumber 
      AND t.date = dr.fill_date
);

Solution for PostgreSQL #

PostgreSQL has a built-in generate_series function that simplifies date range creation:

-- Step 1: Get the 2020-12-31 baseline records
WITH baseline_records AS (
    SELECT account, fundnumber, amount
    FROM your_table_name
    WHERE date = '2020-12-31'
)
-- Step 2: Generate dates and insert fill-in data
INSERT INTO your_table_name (account, fundnumber, amount, date)
SELECT br.account, br.fundnumber, br.amount, gs::DATE
FROM baseline_records br
CROSS JOIN generate_series(
    '2021-01-01'::DATE, 
    '2021-02-28'::DATE, 
    '1 day'::INTERVAL
) gs
-- Optional: Skip duplicates if any exist in the gap
ON CONFLICT (account, fundnumber, date) DO NOTHING;

Key Notes #

• Replace your_table_name with your actual table name.
• The duplicate protection (either NOT EXISTS or ON CONFLICT) is a safe guard—even if your gap is supposed to be empty, it prevents errors if any unexpected records exist.
• If some account + fundnumber pairs don't have a 2020-12-31 record (your question says they do, but just in case), add a filter or handle those cases separately to avoid missing data.

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