如何在SQL查询中按月内日期对周编号（BigQuery适用）

要实现这个需求，核心是把跨月的自然周拆分成当月内的独立分段，再按这些分段汇总销售额。以下是针对BigQuery的具体SQL实现：

实现代码 #

假设你的销售表名为daily_sales，包含日期字段date_col和销售额字段sales_amount：

WITH daily_with_week_boundaries AS (
  SELECT
    date_col,
    sales_amount,
    DATE_TRUNC(date_col, MONTH) AS month_start,
    DATE_ADD(DATE_TRUNC(date_col, MONTH), INTERVAL 1 MONTH) - INTERVAL 1 DAY AS month_end,
    -- 这里定义周起始为周日，若需要周一为起始，替换为 WEEK(MONDAY)
    DATE_TRUNC(date_col, WEEK(SUNDAY)) AS natural_week_start,
    DATE_ADD(DATE_TRUNC(date_col, WEEK(SUNDAY)), INTERVAL 6 DAY) AS natural_week_end
  FROM
    `your-project.your-dataset.daily_sales`
),
daily_with_month_week AS (
  SELECT
    *,
    -- 给当月内的每个周段分配唯一编号
    DENSE_RANK() OVER (
      PARTITION BY month_start
      ORDER BY GREATEST(natural_week_start, month_start)
    ) AS month_week_num,
    -- 计算当月周的实际起止日期（避免跨月）
    GREATEST(natural_week_start, month_start) AS month_week_start,
    LEAST(natural_week_end, month_end) AS month_week_end
  FROM
    daily_with_week_boundaries
)
SELECT
  month_start AS month,
  month_week_num AS week_in_month,
  month_week_start AS week_start_date,
  month_week_end AS week_end_date,
  SUM(sales_amount) AS total_sales
FROM
  daily_with_month_week
GROUP BY
  month_start,
  month_week_num,
  month_week_start,
  month_week_end
ORDER BY
  month_start,
  month_week_num;

代码说明 #

1. 第一个CTE（daily_with_week_boundaries）：

   • 计算每个日期对应的当月起始和结束日期
   • 计算该日期所属自然周的起始（周日）和结束（周六），可根据需求修改周起始日

2. 第二个CTE（daily_with_month_week）：

   • 用DENSE_RANK()按当月分组，根据周段的实际起始日期排序，生成当月内的周编号
   • 通过GREATEST和LEAST函数，确保周段的起止日期不会超出当月范围

3. 最终汇总：

   • 按年月、当月周编号、周段起止日期分组，汇总每个当月周的销售额
   • 结果按年月和周编号排序，方便查看

适配你的例子 #

以2022年12月为例：

• 12月1日（周四）-3日（周六）属于跨月自然周的当月部分，会被标记为week_in_month = 1，起止日期为2022-12-01至2022-12-03
• 12月4日（周日）-10日（周六）为完整自然周，标记为week_in_month = 2，以此类推，直到12月25日-31日的第5个周，完全符合你描述的5个周的结果

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