SQL如何基于每月1号计算周数及对应周的开始与结束日期

AI 小北

2026-9-24

SQL实现方案

核心计算规则

以每月1号为当月周数的计算基准，周数从1开始计数，周的起始/结束可根据业务规则调整（下方示例默认以周一为周起始、周日为周结束，可自行修改偏移量适配其他规则）。

常用数据库实现代码

MySQL 版本

假设源表名为source_table，存储日期的字段名为dt：

SELECT 
    dt,
    -- 计算当月周数字段
    FLOOR(DATEDIFF(dt, DATE_SUB(DATE_FORMAT(dt, '%Y-%m-01'), INTERVAL WEEKDAY(DATE_FORMAT(dt, '%Y-%m-01')) DAY)) / 7) + 1 AS month_week_num,
    -- 计算当前周开始日期（周一）
    DATE_SUB(dt, INTERVAL WEEKDAY(dt) DAY) AS week_start_date,
    -- 计算当前周结束日期（周日）
    DATE_ADD(dt, INTERVAL (6 - WEEKDAY(dt)) DAY) AS week_end_date
FROM source_table;

如果需要以周日为周起始，调整偏移量即可：

SELECT 
    dt,
    FLOOR(DATEDIFF(dt, DATE_SUB(DATE_FORMAT(dt, '%Y-%m-01'), INTERVAL (DAYOFWEEK(DATE_FORMAT(dt, '%Y-%m-01')) - 1) DAY)) / 7) + 1 AS month_week_num,
    DATE_SUB(dt, INTERVAL (DAYOFWEEK(dt) - 1) DAY) AS week_start_date,
    DATE_ADD(dt, INTERVAL (7 - DAYOFWEEK(dt)) DAY) AS week_end_date
FROM source_table;

Hive/Spark SQL 版本

SELECT 
    dt,
    FLOOR(DATEDIFF(dt, DATE_SUB(DATE_TRUNC('month', dt), WEEKDAY(DATE_TRUNC('month', dt)))) / 7) + 1 AS month_week_num,
    DATE_SUB(dt, WEEKDAY(dt)) AS week_start_date,
    DATE_ADD(dt, 6 - WEEKDAY(dt)) AS week_end_date
FROM source_table;