如何按日生成数据行？补全日间缺失的钱包余额记录

首先，你当前的SQL已经完成了「每日最小余额」的计算，但缺少生成连续日期序列和填充缺失值的步骤。下面分步骤拆解，并针对主流数据库给出具体代码：

核心逻辑 #

1. 从原表算出每个用户每天的最小余额（这部分你已经实现）
2. 生成从数据中最早日期到最晚日期的所有连续日期
3. 把日期序列和每日最小余额关联，用前一个有值的余额填充缺失日期的空白

1. MySQL 8.0+版本 #

MySQL 8.0支持窗口函数，用LAST_VALUE可以便捷填充缺失值：

-- 生成目标日期范围内的所有日期
WITH date_range AS (
    SELECT MIN(date) AS start_date, MAX(date) AS end_date FROM wallet
),
dates AS (
    SELECT start_date + INTERVAL (n-1) DAY AS date
    FROM date_range
    -- 数字需覆盖最大日期跨度，示例中为6天；跨度大时可改用递归生成
    JOIN (SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6) nums
    ON start_date + INTERVAL (n-1) DAY <= end_date
),
-- 计算每日最小余额
daily_min_balance AS (
    SELECT user_id, MIN(balance) AS balance, date
    FROM wallet
    GROUP BY user_id, date
)
-- 关联日期序列并填充缺失值
SELECT
    5 AS user_id, -- 若支持多用户，需替换为用户表关联逻辑
    d.date,
    LAST_VALUE(dmb.balance IGNORE NULLS) OVER (ORDER BY d.date) AS balance
FROM dates d
LEFT JOIN daily_min_balance dmb ON d.date = dmb.date AND dmb.user_id = 5
ORDER BY d.date;

如果要支持多用户，改为用户与日期的笛卡尔积：

WITH date_range AS (
    SELECT MIN(date) AS start_date, MAX(date) AS end_date FROM wallet
),
dates AS (
    SELECT start_date + INTERVAL (n-1) DAY AS date
    FROM date_range
    JOIN (SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6) nums
    ON start_date + INTERVAL (n-1) DAY <= end_date
),
users AS (
    SELECT DISTINCT user_id FROM wallet
),
user_dates AS (
    SELECT u.user_id, d.date FROM users u CROSS JOIN dates d
),
daily_min_balance AS (
    SELECT user_id, MIN(balance) AS balance, date
    FROM wallet
    GROUP BY user_id, date
)
SELECT
    ud.user_id,
    ud.date,
    LAST_VALUE(dmb.balance IGNORE NULLS) OVER (PARTITION BY ud.user_id ORDER BY ud.date) AS balance
FROM user_dates ud
LEFT JOIN daily_min_balance dmb ON ud.user_id = dmb.user_id AND ud.date = dmb.date
ORDER BY ud.user_id, ud.date;

2. PostgreSQL #

PostgreSQL自带generate_series函数，生成日期序列更简便：

WITH daily_min_balance AS (
    SELECT user_id, MIN(balance) AS balance, date
    FROM wallet
    GROUP BY user_id, date
),
-- 生成用户+连续日期的组合
user_dates AS (
    SELECT
        u.user_id,
        generate_series(
            (SELECT MIN(date) FROM wallet),
            (SELECT MAX(date) FROM wallet),
            '1 day'::interval
        )::date AS date
    FROM (SELECT DISTINCT user_id FROM wallet) u
)
-- 用LAST_VALUE填充缺失值（PostgreSQL 13+支持IGNORE NULLS）
SELECT
    ud.user_id,
    ud.date,
    LAST_VALUE(dmb.balance IGNORE NULLS) OVER (PARTITION BY ud.user_id ORDER BY ud.date) AS balance
FROM user_dates ud
LEFT JOIN daily_min_balance dmb ON ud.user_id = dmb.user_id AND ud.date = dmb.date
ORDER BY ud.user_id, ud.date;

3. SQL Server #

SQL Server用递归CTE生成日期序列：

WITH date_range AS (
    SELECT MIN(date) AS date FROM wallet
    UNION ALL
    SELECT DATEADD(DAY, 1, date) FROM date_range
    WHERE date < (SELECT MAX(date) FROM wallet)
),
daily_min_balance AS (
    SELECT user_id, MIN(balance) AS balance, date
    FROM wallet
    GROUP BY user_id, date
),
user_dates AS (
    SELECT u.user_id, dr.date FROM (SELECT DISTINCT user_id FROM wallet) u CROSS JOIN date_range dr
)
SELECT
    ud.user_id,
    ud.date,
    LAST_VALUE(dmb.balance) OVER (
        PARTITION BY ud.user_id
        ORDER BY ud.date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS balance
FROM user_dates ud
LEFT JOIN daily_min_balance dmb ON ud.user_id = dmb.user_id AND ud.date = dmb.date
ORDER BY ud.user_id, ud.date;

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