PostgreSQL如何基于最近工作日获取对应交易日期的价格？

要解决这个问题，核心是准确识别工作日（排除周末和法定假日），并为每个日期匹配对应的「下一个工作日」或「最近的前一个工作日」。以下是具体实现方案：

1. 定义工作日判断逻辑 #

首先需要区分工作日和非工作日（周末+法定假日），我们可以通过创建节假日表和判断函数来实现：

-- 创建节假日表，存储所有法定假日，按需维护
CREATE TABLE IF NOT EXISTS holidays (
    holiday_date date PRIMARY KEY
);

-- 插入示例节假日，根据实际业务场景调整
INSERT INTO holidays (holiday_date) VALUES 
('2023-01-01')
ON CONFLICT DO NOTHING;

-- 创建判断工作日的函数
CREATE OR REPLACE FUNCTION is_workday(p_date date) 
RETURNS boolean AS $$
BEGIN
    RETURN 
        -- isodow返回1=周一，7=周日，1-5为工作日
        EXTRACT(isodow FROM p_date) BETWEEN 1 AND 5
        -- 排除法定假日
        AND NOT EXISTS (SELECT 1 FROM holidays WHERE holiday_date = p_date);
END;
$$ LANGUAGE plpgsql STABLE;

2. 为每个price_date匹配下一个工作日作为trx_date #

使用递归CTE逐个检查日期，跳过非工作日，直到找到第一个符合条件的下一个工作日：

WITH RECURSIVE next_workday_cte AS (
    -- 初始步骤：取每个price_date的下一天作为候选日期
    SELECT 
        price_date,
        price,
        price_date + INTERVAL '1 day'::date AS candidate_date
    FROM price_table
    WHERE is_workday(price_date) -- 过滤非工作日的price_date

    UNION ALL

    -- 递归步骤：若候选日期不是工作日，继续往后推一天
    SELECT 
        n.price_date,
        n.price,
        n.candidate_date + INTERVAL '1 day'::date AS candidate_date
    FROM next_workday_cte n
    WHERE NOT is_workday(n.candidate_date)
)
-- 筛选每个price_date对应的第一个工作日作为trx_date
SELECT 
    price_date,
    price,
    candidate_date AS trx_date
FROM next_workday_cte
WHERE is_workday(candidate_date)
GROUP BY price_date, price, candidate_date
ORDER BY price_date;

针对你的示例数据，执行后会得到：

3. 为非工作日交易匹配节前最后一个工作日的价格 #

如果需要为所有日期（包括非工作日）生成交易记录，让非工作日的交易关联节前最后一个工作日的价格，并将trx_date设为节后第一个工作日，可使用窗口函数实现：

-- 生成需要覆盖的日期范围
WITH date_range AS (
    SELECT generate_series(
        (SELECT min(price_date) FROM price_table),
        (SELECT max(price_date) + INTERVAL '3 days' FROM price_table)::date,
        INTERVAL '1 day'
    )::date AS raw_date
),
-- 标记每个日期的工作日属性及对应前后工作日
workday_info AS (
    SELECT 
        raw_date,
        is_workday(raw_date) AS is_workday,
        -- 找到当前日期及之后的第一个工作日（节后第一个工作日）
        first_value(CASE WHEN is_workday(raw_date) THEN raw_date END) OVER (
            ORDER BY raw_date DESC
            ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
        ) AS trx_date,
        -- 找到当前日期及之前的最后一个工作日（节前最后一个工作日）
        first_value(CASE WHEN is_workday(raw_date) THEN raw_date END) OVER (
            ORDER BY raw_date ASC
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS last_workday_before
    FROM date_range
)
-- 关联价格表得到最终结果
SELECT 
    w.last_workday_before AS price_date,
    p.price,
    w.trx_date
FROM workday_info w
JOIN price_table p ON w.last_workday_before = p.price_date
GROUP BY w.last_workday_before, p.price, w.trx_date
ORDER BY w.raw_date;

针对你的示例，1月3日、4日的记录会显示：

关键说明 #

• 节假日表需根据业务场景维护，比如不同地区的法定假日、公司自定义假日等。
• 递归CTE逻辑直观，适合逐个查找下一个工作日；窗口函数批量处理效率更高，适合大范围日期场景。
• 若price_table中存在非工作日记录，需用WHERE is_workday(price_date)过滤，避免无效数据干扰。

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