如何计算每位员工在职期间扣除public_holidays后的工作日数？

方法1：通过生成日期数组匹配节假日（适合理解逻辑） #

如果想用GENERATE_DATE_ARRAY来实现，核心思路是先为每个员工生成起止区间内的所有日期，再关联公共节假日表统计每个员工的节假日数量，最后用总天数减去节假日数得到工作日。以下是BigQuery的实现代码：

WITH employee_dates AS (
  SELECT
    e.employee_name,
    e.starting_date,
    e.ending_date,
    UNNEST(GENERATE_DATE_ARRAY(e.starting_date, e.ending_date, INTERVAL 1 DAY)) AS date
  FROM employees e
)
SELECT
  employee_name,
  starting_date,
  ending_date,
  DATE_DIFF(ending_date, starting_date, DAY) + 1 AS total_days,
  COUNT(ph.date) AS holiday_count,
  (DATE_DIFF(ending_date, starting_date, DAY) + 1) - COUNT(ph.date) AS work_days
FROM employee_dates ed
LEFT JOIN public_holidays ph ON ed.date = ph.date
GROUP BY employee_name, starting_date, ending_date
ORDER BY employee_name;

逻辑说明：

1. 用CTEemployee_dates生成每个员工的日期序列，UNNEST将数组拆分为每行一个日期
2. 左关联公共节假日表，匹配每个日期是否为节假日
3. 分组后统计每个员工的节假日数量，总天数需加1（因为起止日期都包含在内），最终计算工作日数

方法2：直接统计区间内节假日数（性能更优） #

如果员工的在职区间较长，生成全量日期会占用过多资源，更高效的方式是直接统计公共节假日中落在员工起止区间内的记录数，无需生成日期数组。以下是通用SQL实现（适配多数数据库）：

SELECT
  e.employee_name,
  e.starting_date,
  e.ending_date,
  -- 注意：不同数据库的日期差函数有差异，根据实际调整
  DATE_DIFF(e.ending_date, e.starting_date, DAY) + 1 AS total_days,
  COUNT(ph.date) AS holiday_count,
  (DATE_DIFF(e.ending_date, e.starting_date, DAY) + 1) - COUNT(ph.date) AS work_days
FROM employees e
LEFT JOIN public_holidays ph 
  ON ph.date BETWEEN e.starting_date AND e.ending_date
GROUP BY e.employee_name, e.starting_date, e.ending_date
ORDER BY e.employee_name;

数据库适配提示：

• BigQuery：使用DATE_DIFF(end_date, start_date, DAY)
• MySQL：替换为DATEDIFF(end_date, start_date)
• PostgreSQL：直接用(ending_date - starting_date) + 1计算总天数
• 如果公共节假日表存在重复日期，将COUNT(ph.date)改为COUNT(DISTINCT ph.date)避免重复统计

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