在BigQuery中用SQL计算指定日期后N个工作日的更新截止日期

首先确认你的dateholidays CTE是无重复日期的（datetable作为连续日期表，左关联holidays后每个日期唯一），这一步的逻辑没问题。要计算工单的更新截止日期，核心是找到update_date之后的第N个工作日（N由优先级定义，比如低优先级取5），之前用COUNTIF和分区函数无效，是因为你没以每个工单的更新日期为起点累计工作日，而是按全局日期分区统计了同日期数量。

完整SQL示例（通用版本） #

WITH dateholidays AS (
    SELECT 
      datetable.date, 
      CASE 
        WHEN holidays.date IS NOT NULL OR EXTRACT(DAYOFWEEK FROM datetable.date) IN (1, 7)
          THEN False
          ELSE True
      END AS businessday 
    FROM datetable 
    LEFT JOIN holidays ON datetable.date = holidays.date
),
-- 给每个工单的更新日期之后的工作日按顺序编号
ranked_business_days AS (
    SELECT 
        refs.ref_date AS update_date,
        dh.date AS business_date,
        ROW_NUMBER() OVER (PARTITION BY refs.ref_date ORDER BY dh.date) AS day_rank
    FROM (
        -- 提取所有工单的更新日期作为参考基准
        SELECT DISTINCT update_date AS ref_date FROM tickets
    ) AS refs
    INNER JOIN dateholidays dh 
        ON dh.date > refs.ref_date 
        AND dh.businessday = True
)
-- 关联工单表，匹配对应优先级的截止日期
SELECT 
    t.ticket_id,
    t.priority,
    t.update_date,
    -- 根据优先级定义所需工作日数，示例：低优先级取第5个工作日
    CASE t.priority
        WHEN '低' THEN rbd.business_date
        WHEN '中' THEN (SELECT business_date FROM ranked_business_days WHERE update_date = t.update_date AND day_rank = 3)
        WHEN '高' THEN (SELECT business_date FROM ranked_business_days WHERE update_date = t.update_date AND day_rank = 1)
    END AS deadline_date
FROM tickets t
LEFT JOIN ranked_business_days rbd
    ON rbd.update_date = t.update_date
    AND rbd.day_rank = 5 -- 对应低优先级的5个工作日
ORDER BY t.ticket_id;

简化写法（支持LATERAL JOIN的数据库：PostgreSQL/SQL Server等） #

如果你的数据库支持LATERAL JOIN，可以更简洁地实现：

WITH dateholidays AS (
    SELECT 
      datetable.date, 
      CASE 
        WHEN holidays.date IS NOT NULL OR EXTRACT(DAYOFWEEK FROM datetable.date) IN (1, 7)
          THEN False
          ELSE True
      END AS businessday 
    FROM datetable 
    LEFT JOIN holidays ON datetable.date = holidays.date
)
SELECT 
    t.ticket_id,
    t.priority,
    t.update_date,
    dh.date AS deadline_date
FROM tickets t
LEFT JOIN LATERAL (
    SELECT date
    FROM dateholidays
    WHERE date > t.update_date AND businessday = True
    ORDER BY date
    LIMIT 1 OFFSET 4 -- OFFSET 4对应第5个工作日（从0开始计数）
) dh ON true
WHERE t.priority = '低'; -- 去掉此条件可配合CASE处理所有优先级

关键说明 #

• 先单独处理日期序列：以每个工单的更新日期为基准，对之后的工作日排序编号，避免提前关联工单表导致的重复行问题。
• ROW_NUMBER() OVER (PARTITION BY ref_date ORDER BY dh.date)：按每个工单的更新日期分组，对后续工作日按日期生成连续序号，直接取对应序号的日期就是截止日。
• 注意EXTRACT(DAYOFWEEK)的数据库差异：比如MySQL中DAYOFWEEK()返回1=周日、7=周六，和你的代码逻辑匹配；如果是Oracle，需用TO_CHAR(date, 'D')调整判断逻辑。

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