PostgreSQL日期查询需求：构建特定日期列表获取price表数据

我来帮你搞定这个需求——从price表返回匹配特定日期列表的行，同时自动把列表里的节假日替换成最近的非节假日工作日。下面分场景一步步说明解决方案：

前提假设 #

先明确几个基础结构（如果你的表字段名不一样，直接替换就行）：

• price表有日期字段price_date（用来匹配的日期列）和其他业务数据；
• holiday_tbl表存所有节假日，有holiday_date DATE字段；
• 这里定义"最近的非节假日工作日"为：如果目标日期是节假日或周末，往前找最近的、既不是周末也不在节假日表里的日期（要是你需要往后找，后面会说怎么调整）。

第一步：创建通用的工作日转换函数 #

先写一个可复用的函数，用来把任意日期转换成符合要求的有效工作日：

CREATE OR REPLACE FUNCTION get_valid_workday(p_target_date DATE)
RETURNS DATE AS $$
DECLARE
    v_valid_date DATE;
BEGIN
    -- 从目标日期往前遍历，找到第一个非周末且非节假日的日期
    FOR v_valid_date IN SELECT p_target_date - i FROM generate_series(0, 10) AS i LOOP
        IF EXTRACT(DOW FROM v_valid_date) NOT IN (0, 6) -- 0=周日，6=周六
           AND NOT EXISTS (SELECT 1 FROM holiday_tbl WHERE holiday_date = v_valid_date) THEN
            RETURN v_valid_date;
        END IF;
    END LOOP;
    -- 如果遍历10天还没找到（极端情况），返回NULL或根据业务调整
    RETURN NULL;
END;
$$ LANGUAGE plpgsql STABLE;

这个函数会从输入日期开始，最多往前查10天（足够覆盖大部分连续节假日场景），找到第一个符合要求的工作日。

场景1：匹配给定的具体日期列表 #

比如你需要处理的日期列表是('2024-05-01', '2024-05-02', '2024-05-06')，先把每个日期转换成有效工作日，再关联price表：

SELECT p.*
FROM price p
JOIN (
    -- 这里替换成你的目标日期列表
    SELECT unnest(ARRAY['2024-05-01'::DATE, '2024-05-02'::DATE, '2024-05-06'::DATE]) AS target_date
) AS input_dates
ON p.price_date = get_valid_workday(input_dates.target_date);

小说明： #

• 用unnest(ARRAY[...])把数组转换成行，方便逐个处理每个日期；
• 每个日期通过get_valid_workday转换后，再和price表的日期字段匹配。

场景2：匹配日期范围内的工作日（自动处理节假日） #

如果你的需求是获取某个日期范围内的所有有效工作日（排除周末+替换节假日为最近工作日），可以这样写：

SELECT p.*
FROM price p
JOIN (
    -- 先生成日期范围内的所有工作日（排除周末）
    SELECT generate_series('2024-04-01'::DATE, '2024-04-30'::DATE, '1 day') AS raw_date
    WHERE EXTRACT(DOW FROM generate_series) NOT IN (0, 6)
    -- 再转换成有效工作日（替换节假日）
) AS date_range
ON p.price_date = get_valid_workday(date_range.raw_date);

优化小技巧： #

如果日期范围较大，为了避免重复转换相同的有效日期，可以先去重：

SELECT p.*
FROM price p
JOIN (
    SELECT DISTINCT get_valid_workday(raw_date) AS valid_date
    FROM (
        SELECT generate_series('2024-04-01'::DATE, '2024-04-30'::DATE, '1 day') AS raw_date
        WHERE EXTRACT(DOW FROM raw_date) NOT IN (0, 6)
    ) AS work_days
) AS valid_dates
ON p.price_date = valid_dates.valid_date;

额外调整说明 #

1. 如果你的"最近工作日"需求是往后找（比如节假日之后的第一个工作日），只需要把函数里的p_target_date - i改成p_target_date + i，同时调整循环逻辑即可；
2. 如果holiday_tbl包含调休上班的日期（比如周末被调成工作日），需要给表加一个字段（比如is_workday BOOLEAN）标记调休情况，然后在函数里加入判断：如果日期是调休工作日，即使是周末也视为有效；
3. 可以根据实际业务需求，把日期列表的构建逻辑封装成CTE，让查询结构更清晰。

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