先说个我的真实经历。之前做电商运营周报运营负责人跑来跟我说“从这周开始周五到周四算一个周跟平台的大促节奏对齐。”我当时第一反应是不就是按周分组吗MySQL的WEEK/YEARWEEK函数直接搞定。结果写出来一跑周五下单和周一下单被分到了两个“周”里月末最后一天直接串到下一周数据对不上运营拿着报表来问了我三回。后来我把WEEK函数的mode参数翻了个底朝天确认了一个结论MySQL原生周函数只支持周日或周一作为一周的起点而且跨年周的口径跟业务直觉经常对不上。想真正实现“自定义周”就得自己写函数。这篇文章就把我踩过的坑和最终落地的方案完整记录下来包含可直接复用的MySQL函数、跨年周的归属逻辑、索引优化思路以及一组边界测试用例。适合所有用MySQL做报表、数仓ETL、周维度统计的同学看完可以直接抄作业。1. 我为什么要绕过WEEK()去写自定义周1.1 业务里真实存在的“非标准周”需求先盘点一下需求省得有人说“直接用WEEK就行”。我遇到的场景大致有四类财年/财务周很多公司财年不是1月1日财务周从周五或周六开始方便月度关账切分。电商大促周大促往往周四预热、周五开卖运营希望按“周四到下周三”统计GMV和转化。零售/餐饮周门店排班和库存盘点常以“周三到下周二”为一个经营周期因为周一是周中低谷。考勤薪资周有些企业按“周四到次周三”结算工时跨月拆账规则又不一样。这些需求有一个共同点周起点不是周日也不是周一。而MySQL内置的WEEK函数mode参数再怎么变起点只有周日和周一两种顶多调整一下“第一周怎么算、周号范围是0-53还是1-53”。所以遇到周三起点、周五起点原生函数直接给不了答案。1.2 WEEK / WEEKOFYEAR / YEARWEEK 各自的问题先用一段SQL看看这三个函数在跨年日期上的表现SELECT DATE(2023-01-01) AS d, DAYOFWEEK(2023-01-01) AS dow, WEEK(2023-01-01, 1) AS wk1, YEARWEEK(2023-01-01, 1) AS yw1;2023-01-01是周日按mode 1周一起始、新年里至少4天才算第一周算它所在的那一周大部分天数还落在2022年所以周号会跑到2022年最后一两周五去YEARWEEK返回的结果也是类似于202252这样“年第几周”的组合而不是业务直觉里的2023年第1周。这里必须说清楚WEEK函数不是没有跨年处理它是按ISO风格做的只是这个“ISO风格”跟很多业务口径不一致。业务想要的往往是“1月1日落在哪周哪周就是第1周”或者“周起始日落在哪年就归哪年”。这些口径光靠内置函数的mode参数调不出来。1.3 还容易忽略的“周内错位”问题另一个坑来自周起点不一致导致的统计错位。举个例子订单表里一个用户周一和周五各下一单如果按周一作为周起点这两单在一周内如果按周五作为周起点可能就跨到两个周了。同一个订单日期在不同周起点规则下归属的周标签完全不同。这提醒我们做自定义周之前先跟业务确认两件事——周从哪一天开始跨年那一周归到哪一年。这两点定不下来写出来的日期函数再严谨也是白搭。2. 自定义周的两个底层规则起点和归属2.1 周起始日的计算逻辑先把最核心的“周起始日”这个问题解决。MySQL里有个函数叫DAYOFWEEK返回1到7分别代表周日、周一、周二……周六。我们如果约定自定义周起点也用1周日、2周一、3周二……7周六那么一个日期d所在周的起始日可以这样算先算出d是星期几DAYOFWEEK(d)用(DAYOFWEEK(d) - week_start 7) % 7得到距离本周起点过去了几天的偏移量再把d减去这个偏移量就得到本周起始日期例如d 2024-03-06周三DAYOFWEEK4week_start 2周一偏移量 (4 - 2 7) % 7 2减2天后得到2024-03-04正是周一。这个公式是后面所有函数的地基理解它就理解了自定义周的本质。2.2 跨年那周到底归哪年周起始日算出来后紧接的问题是跨年周归属。常见口径有三种口径规则典型场景起始日归属周起始日在哪年整周归哪年财务对账、排班含1月1日归属1月1日落在哪周这周就是该年第1周自然周报表多数天归属一周中超过3天落在新年才归新年ISO 8601、国际业务三种口径没有绝对对错但逻辑完全不同。比如2023-01-01是周日按周一为一周起点这一周的起始日是2022-12-26。按“起始日归属”它属于2022年第52周按“含1月1日归属”它是2023年第1周按ISO多数天归属它还是2022年的周。我最终在函数里默认采用“起始日归属”口径因为它计算最简单、返回值最稳定不会因为周内某几天跨年就来回跳。如果你的业务需要另外两种口径可以在函数返回值后再套一层判断文章后面会说。3. 三个可直接复用的自定义周函数3.1 get_week_start计算任意周起点的周起始日这是整个方案的底座核心函数如下DROP FUNCTION IF EXISTS get_week_start; DELIMITER $$ CREATE FUNCTION get_week_start( d DATE, week_start TINYINT ) RETURNS DATE DETERMINISTIC BEGIN -- week_start: 1周日, 2周一, 3周二, 4周三, 5周四, 6周五, 7周六 -- DAYOFWEEK(d): 1周日, 2周一, ..., 7周六 RETURN DATE_SUB(d, INTERVAL ((DAYOFWEEK(d) - week_start 7) % 7) DAY); END$$ DELIMITER ;函数返回的是“d所在自定义周”的第一天也就是周起始日的日期。举例验证SELECT get_week_start(2024-03-06, 2) AS monday_week_start, -- 2024-03-04 get_week_start(2024-03-06, 4) AS wednesday_week_start, -- 2024-03-06 get_week_start(2024-03-10, 5) AS friday_week_start; -- 2024-03-082024-03-10是周日如果周五是周起点那本周五就是2024-03-08结果没问题。这个函数我用了很久最大的感受是它把“周分组”这件事从业务SQL里彻底抽出来了所有报表统一用一个函数口径不会乱。3.2 get_week_label直接生成周标签很多场景其实不需要周号只要一个“周起始日期”作为分组标签就够了比如报表里按周展示。这时候可以封装一个返回字符串标签的函数DROP FUNCTION IF EXISTS get_week_label; DELIMITER $$ CREATE FUNCTION get_week_label( d DATE, week_start TINYINT ) RETURNS CHAR(10) DETERMINISTIC BEGIN RETURN DATE_FORMAT(get_week_start(d, week_start), %Y-%m-%d); END$$ DELIMITER ;用法示例SELECT course_id, get_week_label(create_date, 3) AS study_week, COUNT(*) AS user_cnt FROM user_learning_log WHERE create_date 2024-01-01 GROUP BY course_id, get_week_label(create_date, 3);这样输出的study_week类似2024-01-03一眼就能看出这是本周三开始的一周比返回一个“第34周”直观得多。3.3 get_week_key处理跨年周号如果业务确实需要年周号那就必须处理跨年。我的实现逻辑是先用get_week_start拿到周起始日再按“起始日所在年份”算该年是第几个周起始日DROP FUNCTION IF EXISTS get_week_key; DELIMITER $$ CREATE FUNCTION get_week_key( d DATE, week_start TINYINT ) RETURNS CHAR(8) DETERMINISTIC BEGIN DECLARE ws DATE; DECLARE yr INT; DECLARE first_ws DATE; DECLARE wk INT; -- d 所在自定义周的起始日 SET ws get_week_start(d, week_start); -- 按起始日归属年份 SET yr YEAR(ws); -- 当年第一个“周起始日”即1月1日之后含第一个 week_start 对应的星期 SET first_ws DATE_ADD( DATE(CONCAT(yr, -01-01)), INTERVAL ((week_start - DAYOFWEEK(CONCAT(yr, -01-01)) 7) % 7) DAY ); -- 该周是当年的第几周 SET wk FLOOR(DATEDIFF(ws, first_ws) / 7) 1; RETURN CONCAT(yr, -, LPAD(wk, 2, 0)); END$$ DELIMITER ;验证几组典型日期日期week_start2周一说明2023-01-012022-52周日仍在以周一开始的2022年最后一周2023-01-022023-01新年第一个周一正式开始2023年第1周2023-12-312023-5212月最后一个周一所在周2024-01-012024-012024-01-01恰好是周一2024-02-292024-09闰年不影响周号计算2024-12-302024-53周一按起始日归属是2024年第53周注意2024-12-30这一行按ISO口径它已经是2025年第1周但按我们采用的“起始日归属”它属于2024-12-30开头的这一周所以是2024-53。这就是口径差异不是bug。如果你要ISO口径可以在get_week_start基础上补一条判断周内属于新年的天数大于等于4个才算新年周否则归上一年。3.4 创建函数前的一个小坑MySQL默认开启了binlog时创建存储函数可能会报这样的错ERROR 1418: This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled处理办法有两种给函数显式加上DETERMINISTIC表示同样的输入必然得到同样的输出。在会话或全局打开log_bin_trust_function_creators1。建议用第一种显式声明DETERMINISTIC因为后面建生成列、建函数索引也需要它是DETERMINISTIC。4. 真实业务里的周报、留存和环比应用4.1 周维度聚合报表先看一个最典型的周维度订单报表SELECT get_week_label(order_date, 5) AS sale_week, SUM(amount) AS gmv, COUNT(DISTINCT user_id) AS buyer_cnt FROM orders WHERE order_date 2024-01-01 GROUP BY get_week_label(order_date, 5) ORDER BY sale_week;这里week_start5表示按周五开始统计。如果业务某天大促改成了周三开始只需要改一个参数所有报表口径统一切换而不是在几十条SQL里去改日期条件。这是封装成函数的最大收益。4.2 连续周环比分析做环比时经常要取出“上一个同口径周”的起始日。有了get_week_start直接减7天就行SELECT get_week_label(order_date, 5) AS sale_week, COUNT(*) AS order_cnt FROM orders WHERE order_date DATE_SUB(get_week_start(CURDATE(), 5), INTERVAL 14 DAY) AND order_date DATE_SUB(get_week_start(CURDATE(), 5), INTERVAL 7 DAY) GROUP BY get_week_label(order_date, 5);这段SQL的含义是取当前周五起始周的上一周从倒数第二个周五到倒数第一个周五之前的数据。注意范围条件我用的是半开区间[起, 止)也就是 开始日期 AND 结束日期这样能避免把下一个周的第一天重复算进来。4.3 留存分析里的“周用户群”做周留存时用户的首购周和回访周都可能用到自定义周。SQL可以是SELECT get_week_label(first_order_date, 4) AS first_week, get_week_label(later_order_date, 4) AS return_week, COUNT(DISTINCT user_id) AS retained_users FROM user_first_order f JOIN user_later_order l USING (user_id) GROUP BY get_week_label(first_order_date, 4), get_week_label(later_order_date, 4);一致性是关键。首购周和回访周必须用同一个week_start否则留存率算出来会忽高忽低。之前我就见过项目里一张表用周一另一张表用周日最后留存率差了三个点。5. 函数好用但查询别乱写索引与范围优化5.1 直接在WHERE上套函数的后果函数封装起来很爽但如果写成下面这样线上会出事SELECT * FROM orders WHERE get_week_label(order_date, 5) 2024-03-08;这条SQL几乎肯定走不了order_date上的索引因为MySQL需要对每一行做函数计算然后再跟字符串比较。数据量一上来全表扫描报表接口直接超时。5.2 三个靠谱的优化方案方案一改成范围查询既然get_week_start能算出周起始日和下周起始日那WHERE条件就不要套函数直接写成日期范围SELECT * FROM orders WHERE order_date 2024-03-08 AND order_date 2024-03-15;order_date上的索引可以正常使用这是最简单高效的做法。方案二生成列 索引MySQL 8.0可以用生成列把周起始日“物化”成表里的一个字段然后建索引ALTER TABLE orders ADD COLUMN week_start_date DATE GENERATED ALWAYS AS (get_week_start(order_date, 5)) STORED; CREATE INDEX idx_orders_week_start ON orders (week_start_date);之后查询SELECT * FROM orders WHERE week_start_date 2024-03-08;生成列在写入时就算好了查询时相当于普通列索引能命中。注意生成列的表达式必须DETERMINISTIC所以我们前面声明函数时加上DETERMINISTIC是非常必要的。方案三函数索引MySQL 8.0.13及以上版本还支持直接在表达式上建索引CREATE INDEX idx_orders_week_func ON orders ((get_week_start(order_date, 5)));和生成列方案类似但更简洁。缺点是某些老版本MySQL不支持5.7之前的线上环境就别想了。5.3 周维表是最好的最终形态如果报表频繁用到周维度我更推荐预先维护一张“日期维表”把每一天对应的自定义周起始日、周标签、年周号都算好存起来CREATE TABLE dim_date ( d DATE PRIMARY KEY, week_start_date DATE, week_label CHAR(10), week_key CHAR(8) );然后用一张日期维表跟事实表关联SQL里就不再调用函数只是普通JOIN和普通列过滤性能表现非常稳定。函数适合在开发期验证逻辑、小表查询维表适合在大规模生产环境长期跑。这是我建议的最终演进方向。6. 边界测试与踩坑记录6.1 函数创建和调用时的常见报错我实操中遇到过的报错主要有这几种错误原因解决ERROR 1418没有声明DETERMINISTIC等属性给函数加DETERMINISTICERROR 1305函数名和已有函数冲突换个不冲突的名字ERROR 1064DELIMITER没写或写错用DELIMITER $$包裹函数体ERROR 1264返回值超出字段长度检查CHAR/LPAD长度ERROR 1046没选数据库就建函数USE库名后再创建其中ERROR 1418是最容易忽略的。很多开发机没开binlog时能建成功一上生产环境就失败问题就出在这里。6.2 边界日期测试用例自定义周函数上线前强烈建议跑一遍下面的测试SELECT d, get_week_key(d, 2) AS key_mon, get_week_key(d, 4) AS key_wed FROM ( SELECT DATE(2023-01-01) AS d UNION ALL SELECT DATE(2023-01-02) UNION ALL SELECT DATE(2023-12-31) UNION ALL SELECT DATE(2024-01-01) UNION ALL SELECT DATE(2024-02-29) UNION ALL SELECT DATE(2024-12-30) UNION ALL SELECT DATE(2024-12-31) ) t;重点观察三件事跨年那一周是否稳定归属到预期年份不会出现周号从52突然跳到1又跳回52的情况。不同week_start参数之间同一个日期的周标签是否有预期的7天偏差。2月29日这种闰年日期是否能正常参与周号计算不报错也不产生偏移。我实际跑下来只要get_week_start公式没问题后面所有函数都不会在闰年出问题因为DATE_SUB和DATEDIFF天然处理了日期长度差异。6.3 跟ISO周并存时口径要统一MySQL 8.0还提供了YEARWEEK、WEEKOFYEAR这些函数如果你在一个SQL里既用了我的自定义周函数又用了内置ISO周函数很可能出现同一天被计算成两个不同的“周”。比如2024-12-30按ISO口径是2025-W01按我的get_week_key(week_start2)是2024-53。这种不一致不一定谁对谁错但混用就是事故。建议在项目文档里明确写清楚默认口径比如“线上所有周维度统一使用周一开始、周起始日归属年份”并且把函数固化到公共库禁止业务SQL里直接写内置WEEK函数。6.4 时区问题别忽略如果订单表存的是DATETIME并且设置了time_zone那么同一个UTC时间在不同时区下转换出来的本地日期可能不同落在哪一周也就不同。我会建议在业务SQL里先显式把时间转成目标时区SELECT get_week_label(CONVERT_TZ(created_at, 00:00, 08:00), 4) AS week_label FROM orders;国内业务基本上都是东八区但如果数据库服务器和业务服务器时区不一致函数计算结果就可能在凌晨边界上出错。这个问题平时不容易暴露跨年、夏令时切换时会非常明显。最后补充一点个人经验自定义周这类逻辑看着简单但一旦口径错了影响的是整条业务线的报表结论比写错一个业务JOIN还难排查。所以我后来养成了一个习惯——先把通配的边界日期查出来用肉眼核对周起始日和年周号再往上接业务SQL。尤其是月初、年末、周一/周日交界日这几个点不出问题基本就能放心用了。如果你的业务里也有非标准周起点需求这三个函数可以直接拿去改核心逻辑就一句话先把日期偏移回自定义周起始日其余的年、周号、标签都是基于这个起始日算出来的。