如何编写SQL查询输出2024年每月1日汇率，缺失值取滞后值

需要编写SQL查询，输出2024年每个月1日的汇率，共12行记录；若对应日期无数据，需采用该月份的前序滞后汇率值。

示例数据 #

SELECT '01.01.2024'::date AS data, 67 AS cur UNION ALL
SELECT '03.01.2024'::date AS data, 68 AS cur UNION ALL
SELECT '04.01.2024'::date AS data, 69 AS cur UNION ALL
SELECT '06.01.2024'::date AS data, 70 AS cur UNION ALL
SELECT '04.02.2024'::date AS data, 71 AS cur UNION ALL
SELECT '07.02.2024'::date AS data, 72 AS cur UNION ALL
SELECT '06.03.2024'::date AS data, 77 AS cur UNION ALL
SELECT '04.05.2024'::date AS data, 78 AS cur UNION ALL
SELECT '03.06.2024'::date AS data, 79 AS cur UNION ALL
SELECT '04.06.2024'::date AS data, 80 AS cur UNION ALL
SELECT '05.06.2024'::date AS data, 81 AS cur

初始查询（不符合需求） #

SELECT MIN(data) AS first_date, cur AS currency
FROM (
    SELECT data, cur,
        ROW_NUMBER() OVER (PARTITION BY EXTRACT(YEAR FROM data)::int, ((EXTRACT(MONTH FROM data) - 1) / 12)::int ORDER BY data) AS rn
    FROM your_table_name
) subquery
WHERE rn = 1
GROUP BY cur
ORDER BY first_date

预期输出 #

data    | cur 
------------+-----
 2024-01-01 |  67
 2024-02-01 |  70
 2024-03-01 |  72
 2024-04-01 |  77
 2024-05-01 |  77
 2024-06-01 |  78
 2024-07-01 |  81
 2024-08-01 |  81
 2024-09-01 |  81
 2024-10-01 |  81
 2024-11-01 |  81
 2024-12-01 |  81

解决方案 #

要实现需求，需要先生成2024年所有月份的1号日期序列，再关联汇率数据，最后用窗口函数填充缺失的滞后值。具体SQL如下：

-- 生成2024年每个月1日的日期序列
WITH monthly_dates AS (
    SELECT generate_series(
        '2024-01-01'::date,
        '2024-12-01'::date,
        '1 month'::interval
    ) AS target_date
),
-- 获取每个目标日期之前（含）的最新汇率记录
latest_rates AS (
    SELECT 
        md.target_date,
        t.cur
    FROM monthly_dates md
    LEFT JOIN your_table_name t 
        ON t.data = (
            SELECT MAX(data) 
            FROM your_table_name 
            WHERE data <= md.target_date
        )
),
-- 用前序非空汇率值填充缺失项
filled_rates AS (
    SELECT 
        target_date AS data,
        LAST_VALUE(cur IGNORE NULLS) OVER (ORDER BY target_date) AS cur
    FROM latest_rates
)
-- 去重并输出最终12条记录
SELECT DISTINCT data, cur
FROM filled_rates
ORDER BY data;

思路说明 #

1. 生成日期序列：通过generate_series创建2024年12个月份的1号日期，确保每个月都有基础记录。
2. 关联最新汇率：针对每个目标日期，找到该日期之前（含）的最新汇率数据，关联到对应月份。
3. 填充缺失值：使用LAST_VALUE窗口函数并忽略空值，自动将前一个非空的汇率值填充到当前无数据的月份。
4. 去重排序：最终去重并按日期排序，得到符合要求的12条记录。

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