BigQuery中如何从指定日期减去3个工作日？

Got it, let's break down how to tackle this—since subtracting working days means we need to skip weekends (and optionally holidays, if you need that level of precision), it's a bit different from the straightforward DATE_SUB you already know.

Basic Version: Exclude Only Weekends #

If you just need to ignore Saturdays and Sundays, the most reliable way is to generate a range of dates, filter out weekends, then pick the date that's 3 working days before your input date. Here's how:

WITH date_input AS (
  SELECT DATE "2008-12-25" AS input_date -- Your target date here
)
SELECT
  input_date,
  (SELECT date 
   FROM UNNEST(GENERATE_DATE_ARRAY(DATE_SUB(input_date, INTERVAL 10 DAY), input_date)) date
   WHERE EXTRACT(DAYOFWEEK FROM date) NOT IN (1, 7) -- 1 = Sunday, 7 = Saturday in BigQuery
   ORDER BY date DESC
   LIMIT 1 OFFSET 3) AS three_working_days_ago
FROM date_input;

How this works: #

• GENERATE_DATE_ARRAY creates a list of dates starting 10 days before your input date (plenty of buffer to cover any weekend gaps) up to the input date itself.
• We filter out Sundays (1) and Saturdays (7) using EXTRACT(DAYOFWEEK).
• Sorting the dates in descending order lets us "count backward" from the input date. OFFSET 3 picks the 4th date in this sorted list (since offsets start at 0)—which is exactly 3 working days before the input date.

For your example date 2008-12-25 (a Thursday), this will return 2008-12-22 (the previous Monday).

Advanced Version: Include Custom Holidays #

If you need to account for company-specific or public holidays, you can add a holiday filter to the query. Here's an example with a custom holiday list:

WITH date_input AS (
  SELECT DATE "2024-01-03" AS input_date -- Example: Wednesday after New Year's Day
),
company_holidays AS (
  SELECT DATE "2024-01-01" AS holiday_date -- Add all your holidays here
)
SELECT
  input_date,
  (SELECT date 
   FROM UNNEST(GENERATE_DATE_ARRAY(DATE_SUB(input_date, INTERVAL 15 DAY), input_date)) date
   WHERE EXTRACT(DAYOFWEEK FROM date) NOT IN (1, 7)
     AND date NOT IN (SELECT holiday_date FROM company_holidays)
   ORDER BY date DESC
   LIMIT 1 OFFSET 3) AS three_working_days_ago
FROM date_input;

This will skip both weekends and your defined holidays. For the example date 2024-01-03, it returns 2024-01-28 (since New Year's Day is a holiday, we have to skip that too).

A Note on "Quick Fix" Math Methods #

You might see simpler CASE statements that adjust based on the day of the week (like subtracting extra days if the result falls on a weekend), but these only work for small numbers of working days and fail if you cross multiple weekends. The date array method is more robust, especially if you ever need to subtract more than 2-3 working days.

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