如何通过SQL修正汇率数据集：拆分跨日期记录补全每日数据？

针对你遇到的汇率记录跨天导致发票日期无法匹配的问题，有两种核心解决思路：查询时动态生成每日汇率（无需修改原表） 和 直接修正原表数据，以下分场景给出具体实现：

一、查询时动态生成每日汇率（推荐，无数据修改风险） #

这种方式不需要改动原始数据集，在查询时自动将跨天记录拆分为每日一行，确保发票日期能精准匹配。

1. PostgreSQL 实现 #

利用 generate_series 生成日期序列拆分跨天记录：

-- 生成完整的每日汇率列表
SELECT
    generate_series(valid_from, valid_to, INTERVAL '1 day')::DATE AS valid_date,
    exchange_rate
FROM
    exchange_rates
WHERE
    valid_from <> valid_to
UNION ALL
-- 合并原本正常的单日记录
SELECT
    valid_from AS valid_date,
    exchange_rate
FROM
    exchange_rates
WHERE
    valid_from = valid_to
ORDER BY
    valid_date;

2. SQL Server 实现 #

通过递归CTE生成日期范围：

WITH DateRange AS (
    -- 初始化跨天记录的起始日期
    SELECT
        valid_from AS date_val,
        valid_to,
        exchange_rate
    FROM
        exchange_rates
    WHERE
        valid_from <> valid_to
    UNION ALL
    -- 递归生成后续日期
    SELECT
        DATEADD(DAY, 1, date_val),
        valid_to,
        exchange_rate
    FROM
        DateRange
    WHERE
        date_val < valid_to
)
-- 合并拆分后的记录与正常单日记录
SELECT
    date_val AS valid_date,
    exchange_rate
FROM
    DateRange
UNION ALL
SELECT
    valid_from AS valid_date,
    exchange_rate
FROM
    exchange_rates
WHERE
    valid_from = valid_to
ORDER BY
    valid_date
OPTION (MAXRECURSION 0); -- 解除递归层数限制，支持超过100天的跨天记录

3. MySQL 8.0+ 实现 #

用递归CTE或数字辅助表生成日期序列：

-- 方法1：递归CTE（MySQL 8.0及以上支持）
WITH DateRange AS (
    SELECT
        valid_from AS date_val,
        valid_to,
        exchange_rate
    FROM
        exchange_rates
    WHERE
        valid_from <> valid_to
    UNION ALL
    SELECT
        DATE_ADD(date_val, INTERVAL 1 DAY),
        valid_to,
        exchange_rate
    FROM
        DateRange
    WHERE
        date_val < valid_to
)
SELECT
    date_val AS valid_date,
    exchange_rate
FROM
    DateRange
UNION ALL
SELECT
    valid_from AS valid_date,
    exchange_rate
FROM
    exchange_rates
WHERE
    valid_from = valid_to
ORDER BY
    valid_date;

-- 方法2：数字辅助表（兼容低版本MySQL）
-- 先创建一个数字表（可按需扩展数字范围）
CREATE TABLE IF NOT EXISTS numbers (n INT);
INSERT INTO numbers VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

SELECT
    DATE_ADD(er.valid_from, INTERVAL num.n DAY) AS valid_date,
    er.exchange_rate
FROM
    exchange_rates er
JOIN (
    -- 生成0-199的数字范围，支持最多200天的跨天记录
    SELECT n1.n * 10 + n2.n AS n FROM numbers n1, numbers n2
    UNION ALL
    SELECT 100 + n1.n *10 +n2.n FROM numbers n1, numbers n2
) num ON DATE_ADD(er.valid_from, INTERVAL num.n DAY) <= er.valid_to
WHERE
    er.valid_from <> valid_to
UNION ALL
SELECT
    valid_from AS valid_date,
    exchange_rate
FROM
    exchange_rates
WHERE
    valid_from = valid_to
ORDER BY
    valid_date;

二、直接修正原表数据（永久修改） #

如果需要彻底修复数据集，可先插入拆分后的每日记录，再删除原跨天记录：

PostgreSQL 示例 #

-- 第一步：插入拆分后的每日记录
INSERT INTO exchange_rates (valid_from, valid_to, exchange_rate)
SELECT
    generate_series(valid_from, valid_to, INTERVAL '1 day')::DATE,
    generate_series(valid_from, valid_to, INTERVAL '1 day')::DATE,
    exchange_rate
FROM
    exchange_rates
WHERE
    valid_from <> valid_to;

-- 第二步：删除原跨天记录
DELETE FROM exchange_rates
WHERE
    valid_from <> valid_to;

注意事项 #

1. 操作前务必备份原数据，避免误操作导致数据丢失；
2. 如果表有主键，需确保插入的新行主键唯一（例如用valid_from作为主键的一部分）；
3. 若存在重叠的跨天记录（同一日期对应多条汇率），需先通过ROW_NUMBER()等逻辑保留最新/正确的汇率。

三、特殊场景：取历史最新汇率而非跨天记录的汇率 #

如果跨天记录的汇率并非实际每日汇率，需要取该日期之前的历史最新汇率，可通过以下逻辑实现（以PostgreSQL为例）：

-- 生成所有需要覆盖的日期范围
WITH AllDates AS (
    SELECT generate_series(
        (SELECT MIN(valid_from) FROM exchange_rates),
        (SELECT MAX(valid_to) FROM exchange_rates),
        INTERVAL '1 day'
    )::DATE AS invoice_date
),
-- 匹配每个日期对应的所有汇率记录，取最新的一条
LatestRates AS (
    SELECT
        ad.invoice_date,
        er.exchange_rate,
        ROW_NUMBER() OVER (PARTITION BY ad.invoice_date ORDER BY er.valid_from DESC) AS rn
    FROM
        AllDates ad
    LEFT JOIN
        exchange_rates er ON ad.invoice_date BETWEEN er.valid_from AND er.valid_to
)
SELECT
    invoice_date AS valid_date,
    exchange_rate
FROM
    LatestRates
WHERE
    rn = 1
ORDER BY
    valid_date;

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