如何通过复制前序记录补全MySQL缺失日期的历史数据

Let's break down how to solve this problem—your goal is to get a continuous date range for the last month, filling in missing dates with the previous day's NodeName and CPUUtilization values. Here's a step-by-step solution:

1. First: Fix the Core Issue with Your Original Query #

Your current query only returns dates where there's a record in the xyz table, which is why you're missing dates like 2019-02-03. Plus, the GROUP BY clause is risky in strict MySQL modes (it'll throw an error because NodeName and CPUUtilization aren't in the GROUP BY or wrapped in an aggregate function). We'll fix that by first ensuring we get one reliable record per day (e.g., the latest entry of the day).

2. Generate a Continuous Date Range #

We need a list of every date in the last month to use as our base. A recursive CTE (Common Table Expression) works perfectly for this in MySQL 8.0+:

WITH RECURSIVE date_range AS (
    -- Start date: First day of the previous month
    SELECT DATE_SUB(LAST_DAY(NOW()) + INTERVAL 1 DAY, INTERVAL 1 MONTH) AS date_val
    UNION ALL
    -- Add one day at a time until we reach today
    SELECT DATE_ADD(date_val, INTERVAL 1 DAY)
    FROM date_range
    WHERE date_val < CURDATE()
)

3. Get Valid Daily Records from Your Table #

Next, we'll pull one record per day (using the latest timestamp for each day to ensure accuracy):

SELECT record_date, NodeName, CPUUtilization
FROM (
    SELECT
        DATE(TimeStamp) AS record_date,
        NodeName,
        CPUUtilization,
        -- Assign a row number to each day's records, ordered by latest timestamp first
        ROW_NUMBER() OVER (PARTITION BY DATE(TimeStamp) ORDER BY TimeStamp DESC) AS rn
    FROM xyz
    WHERE TimeStamp >= DATE_SUB(LAST_DAY(NOW()) + INTERVAL 1 DAY, INTERVAL 1 MONTH)
) t
WHERE rn = 1 -- Keep only the latest record per day

4. Combine and Fill Missing Values #

Now we'll left-join our continuous date range with the daily records, then use window functions to fill in missing values with the last non-null entry:

For MySQL 8.0.22+ (supports IGNORE NULLS) #

WITH RECURSIVE date_range AS (
    SELECT DATE_SUB(LAST_DAY(NOW()) + INTERVAL 1 DAY, INTERVAL 1 MONTH) AS date_val
    UNION ALL
    SELECT DATE_ADD(date_val, INTERVAL 1 DAY)
    FROM date_range
    WHERE date_val < CURDATE()
),
daily_records AS (
    SELECT record_date, NodeName, CPUUtilization
    FROM (
        SELECT
            DATE(TimeStamp) AS record_date,
            NodeName,
            CPUUtilization,
            ROW_NUMBER() OVER (PARTITION BY DATE(TimeStamp) ORDER BY TimeStamp DESC) AS rn
        FROM xyz
        WHERE TimeStamp >= DATE_SUB(LAST_DAY(NOW()) + INTERVAL 1 DAY, INTERVAL 1 MONTH)
    ) t
    WHERE rn = 1
)
SELECT
    dr.date_val AS `Date`,
    -- Fill missing NodeName with the last non-null value
    LAST_VALUE(dr_rec.NodeName) OVER (
        ORDER BY dr.date_val
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        IGNORE NULLS
    ) AS NodeName,
    -- Fill missing CPUUtilization with the last non-null value
    LAST_VALUE(dr_rec.CPUUtilization) OVER (
        ORDER BY dr.date_val
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        IGNORE NULLS
    ) AS CPUUtilization
FROM date_range dr
LEFT JOIN daily_records dr_rec ON dr.date_val = dr_rec.record_date
ORDER BY dr.date_val;

For Older MySQL Versions (pre-8.0.22) #

If you can't use IGNORE NULLS, use user-defined variables to carry forward the last valid values:

-- Reset variables first
SET @prev_node = NULL;
SET @prev_cpu = NULL;

SELECT
    date_val AS `Date`,
    @prev_node := COALESCE(dr_rec.NodeName, @prev_node) AS NodeName,
    @prev_cpu := COALESCE(dr_rec.CPUUtilization, @prev_cpu) AS CPUUtilization
FROM (
    SELECT dr.date_val, dr_rec.NodeName, dr_rec.CPUUtilization
    FROM (
        -- Generate date range using a recursive CTE (or a numbers table if CTEs aren't supported)
        SELECT DATE_SUB(LAST_DAY(NOW()) + INTERVAL 1 DAY, INTERVAL 1 MONTH) AS date_val
        UNION ALL
        SELECT DATE_ADD(date_val, INTERVAL 1 DAY)
        FROM date_range
        WHERE date_val < CURDATE()
    ) dr
    LEFT JOIN (
        SELECT record_date, NodeName, CPUUtilization
        FROM (
            SELECT
                DATE(TimeStamp) AS record_date,
                NodeName,
                CPUUtilization,
                ROW_NUMBER() OVER (PARTITION BY DATE(TimeStamp) ORDER BY TimeStamp DESC) AS rn
            FROM xyz
            WHERE TimeStamp >= DATE_SUB(LAST_DAY(NOW()) + INTERVAL 1 DAY, INTERVAL 1 MONTH)
        ) t
        WHERE rn = 1
    ) dr_rec ON dr.date_val = dr_rec.record_date
    ORDER BY dr.date_val
) AS ordered_data;

Key Notes #

• The date range starts on the first day of the previous month and goes up to today (adjust CURDATE() to LAST_DAY(NOW()) if you only want data up to the end of the previous month).
• We use ROW_NUMBER() to ensure we get the latest record per day—if you need a different aggregate (like average CPU utilization), replace that with AVG(CPUUtilization) in the daily_records CTE and remove the row number logic.

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