如何在BigQuery中计算当前日期所在月份的工作日与周末天数？

原查询依赖业务表salesdate的数据覆盖度，当月未结束时只能统计到已产生的日期，无法得到完整月份的天数统计。要实现每个完整月份的周末、工作日天数计算，可通过生成全月日期序列的方式实现，无需依赖业务数据的覆盖情况：

最终SQL代码 #

WITH target_months AS (
  -- 生成需要统计的月份范围：从11月至今（可调整时间范围）
  SELECT 
    DATE_TRUNC(month_date, MONTH) AS mt
  FROM UNNEST(GENERATE_DATE_ARRAY(
      -- 起始月份：当前月份往前推N个月，这里N=2对应11月至今
      DATE_SUB(DATE_TRUNC(CURRENT_DATE(), MONTH), INTERVAL 2 MONTH),
      DATE_TRUNC(CURRENT_DATE(), MONTH),
      INTERVAL 1 MONTH
    )) AS month_date
),
full_month_dates AS (
  -- 为每个月份生成该月的所有日期
  SELECT
    tm.mt,
    day_date
  FROM target_months tm
  CROSS JOIN UNNEST(GENERATE_DATE_ARRAY(
      tm.mt,
      LAST_DAY(tm.mt),
      INTERVAL 1 DAY
    )) AS day_date
)
-- 按月份统计周末、工作日天数（沿用原查询的周末判断逻辑）
SELECT
  mt,
  COUNT(CASE WHEN EXTRACT(DAYOFWEEK FROM day_date) IN (6,7,1) THEN 1 END) AS weekend,
  COUNT(CASE WHEN EXTRACT(DAYOFWEEK FROM day_date) NOT IN (6,7,1) THEN 1 END) AS weekday
FROM full_month_dates
GROUP BY mt
ORDER BY mt DESC;

关键说明 #

1. 目标月份生成：target_months CTE生成从指定起始月到当前月的所有月份起始日期，调整INTERVAL 2 MONTH可修改起始月份（比如要统计半年前至今就改成INTERVAL 6 MONTH）。
2. 全月日期序列：full_month_dates CTE通过交叉连接为每个月份生成完整的日期列表，确保覆盖当月所有天数。
3. 天数统计：沿用原查询的周末判断规则（DAYOFWEEK为1、6、7时判定为周末），按月份分组统计天数，结果与完整月份的实际天数完全匹配。

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