MySQL WEEK() 函数的模式参数详解
大家好，今天我们来深入探讨 MySQL 中 WEEK() 函数及其模式参数。WEEK() 函数用于返回给定日期所在的周数，在数据分析、报表生成等场景中非常常用。 然而，WEEK() 函数的行为并非总是如我们所愿，因为不同的国家和地区对周的定义可能不同。为了应对这些差异，WEEK() 函数提供了模式参数，允许我们根据不同的标准来计算周数。
WEEK() 函数的基本语法
WEEK() 函数的基本语法如下：
WEEK(date[, mode])date: 要计算周数的日期值。mode: 一个可选的整数参数，用于指定周数的计算模式。如果省略mode，则使用服务器的default_week_format系统变量的值。
模式参数的作用与意义
mode 参数控制了 WEEK() 函数的两个关键行为：
- 周的第一天: 指定一周从哪一天开始（星期日或星期一）。
- 年份的第一周: 确定包含年份第一个星期日的周是否为第一周，或者该周是否属于前一年。
理解这两个行为对于正确使用 WEEK() 函数至关重要。不同的模式参数组合会产生不同的周数结果，尤其是在年份的开始和结束时。
模式参数的取值及含义
mode 参数可以取 0 到 7 的整数值，每个值代表一种特定的周数计算模式。 下表详细列出了每个模式值的含义：
| 模式值 | 周的第一天 | 年份的第一周 |
|---|---|---|
| 0 | 星期日 | 包含年份中第一个星期日的周为第一周 |
| 1 | 星期一 | 包含年份中第一个星期一的周为第一周 |
| 2 | 星期日 | 包含年份中第一个星期日的周为第一周；如果该周少于 4 天，则属于前一年 |
| 3 | 星期一 | 包含年份中第一个星期一的周为第一周；如果该周少于 4 天，则属于前一年 |
| 4 | 星期日 | 包含年份中第一个星期日的周为第一周；如果该周少于 4 天，则属于前一年 (ISO 8601 兼容) |
| 5 | 星期一 | 包含年份中第一个星期一的周为第一周；如果该周少于 4 天，则属于前一年 (ISO 8601 兼容) |
| 6 | 星期日 | 包含年份中第一个星期日的周为第一周；如果该周少于 4 天，则属于前一年 |
| 7 | 星期一 | 包含年份中第一个星期一的周为第一周；如果该周少于 4 天，则属于前一年 |
注意:
- 模式值 0 和 1 是最基本的模式，分别以星期日和星期一作为一周的开始。
- 模式值 2, 3, 4, 5, 6 和 7 引入了 "少于 4 天" 的规则，这与 ISO 8601 标准相关。如果年份的第一周少于 4 天，则该周被认为是前一年的最后一周，并且周数可能达到 52 或 53。
- 模式值 4 和 5 旨在与 ISO 8601 标准兼容，但需要注意的是，它们并不完全符合 ISO 8601 标准，因为它们仍然使用星期日或星期一作为一周的开始，而 ISO 8601 始终使用星期一。
实例演示不同模式参数的影响
为了更好地理解不同模式参数的影响，我们来看一些实际的例子。
示例 1: 使用模式 0 和 1
SELECT
WEEK('2024-01-01', 0) AS week_0,
WEEK('2024-01-01', 1) AS week_1;输出结果：
| week_0 | week_1 |
|---|---|
| 1 | 1 |
在这个例子中，2024-01-01 是星期一。
- 模式 0 (星期日开始): 由于
2024-01-01属于包含 2024 年第一个星期日的周，因此被认为是 2024 年的第一周。 - 模式 1 (星期一开始): 由于
2024-01-01属于包含 2024 年第一个星期一的周，因此也被认为是 2024 年的第一周。
示例 2: 使用模式 0 和 1，观察年份末尾的影响
SELECT
WEEK('2023-12-31', 0) AS week_0,
WEEK('2023-12-31', 1) AS week_1;输出结果：
| week_0 | week_1 |
|---|---|
| 53 | 52 |
在这个例子中，2023-12-31 是星期日。
- 模式 0 (星期日开始):
2023-12-31正好是 2023 年的最后一个星期日，因此属于 2023 年的第 53 周。 - 模式 1 (星期一开始):
2023-12-31属于包含 2023 年最后一个星期一（2023-12-25）的周，因此属于 2023 年的第 52 周。
示例 3: 使用模式 2 和 3，观察年份开始的影响
SELECT
WEEK('2021-01-01', 2) AS week_2,
WEEK('2021-01-01', 3) AS week_3,
WEEK('2020-12-31', 2) AS week_2_last_day,
WEEK('2020-12-31', 3) AS week_3_last_day;输出结果:
| week_2 | week_3 | week_2_last_day | week_3_last_day |
|---|---|---|---|
| 53 | 53 | 53 | 53 |
在这个例子中，2021-01-01 是星期五，2020-12-31 是星期四。
- 模式 2 (星期日开始，少于 4 天):
2021-01-01属于包含 2021 年第一个星期日的周，但该周少于 4 天 (只有星期五和星期六)，因此被认为是 2020 年的第 53 周。2020-12-31也属于同一周，因此也被认为是 2020 年的第 53 周。 - 模式 3 (星期一开始，少于 4 天):
2021-01-01属于包含 2021 年第一个星期一的周，但该周少于 4 天 (只有星期五、星期六和星期日)，因此被认为是 2020 年的第 53 周。2020-12-31也属于同一周，因此也被认为是 2020 年的第 53 周。
示例 4: 使用模式 4 和 5，与 ISO 8601 标准进行对比
虽然模式 4 和 5 旨在与 ISO 8601 标准兼容，但它们并不完全符合。 为了更好地理解这一点，我们先来简单了解一下 ISO 8601 标准：
- ISO 8601: 定义了一周从星期一开始，并且年份的第一周是包含该年份的第一个星期四的周。
SELECT
WEEK('2021-01-01', 4) AS week_4,
WEEK('2021-01-01', 5) AS week_5,
WEEK('2020-12-31', 4) AS week_4_last_day,
WEEK('2020-12-31', 5) AS week_5_last_day,
WEEK('2021-01-04', 4) AS week_4_Jan_4,
WEEK('2021-01-04', 5) AS week_5_Jan_4;
输出结果：
| week_4 | week_5 | week_4_last_day | week_5_last_day | week_4_Jan_4 | week_5_Jan_4 |
|---|---|---|---|---|---|
| 53 | 53 | 53 | 53 | 1 | 1 |
2021-01-01(星期五) 在模式 4 和 5 下都被认为是 2020 年的第 53 周，因为包含该日期的周少于 4 天。2020-12-31(星期四) 在模式 4 和 5 下也被认为是 2020 年的第 53 周，因为它与2021-01-01属于同一周。2021-01-04(星期一) 在模式 4 和 5 下都被认为是 2021 年的第 1 周.
关键区别: ISO 8601 标准会始终以星期一开始，并且使用包含年份中第一个星期四的周作为第一周。 模式 4 和 5 仍然允许你选择星期日或星期一作为一周的开始，但仍然使用 "少于 4 天" 的规则来确定年份的第一周。
为了完全符合 ISO 8601 标准，可以使用 YEARWEEK() 函数和 EXTRACT(YEAR FROM date) 函数结合使用，或者使用 STRFTIME('%V', date) 在支持该函数的数据库系统中。
default_week_format 系统变量
如果在使用 WEEK() 函数时省略了 mode 参数，则会使用服务器的 default_week_format 系统变量的值。 可以通过以下方式查看该变量的值：
SHOW VARIABLES LIKE 'default_week_format';该变量的默认值通常是 0，这意味着默认情况下 WEEK() 函数以星期日作为一周的开始，并且包含年份中第一个星期日的周为第一周。
可以通过以下方式修改该变量的值：
SET GLOBAL default_week_format = 1; -- 修改全局变量
SET SESSION default_week_format = 1; -- 修改会话变量注意: 修改全局变量需要 SUPER 权限，并且会影响所有新的客户端连接。 修改会话变量只会影响当前连接。
模式参数的选择建议
选择正确的 mode 参数取决于具体的业务需求和所遵循的日期标准。
- 如果需要与 ISO 8601 标准兼容， 虽然
WEEK(date, 4)或WEEK(date, 5)并非完全符合标准，但它们可以提供较为接近的结果。 更精确的方法是使用YEARWEEK()函数并结合EXTRACT(YEAR FROM date)或者使用支持 ISO week date 的函数，例如STRFTIME('%V', date)。 - 如果只需要简单的周数计算，可以根据一周的起始日选择模式 0 (星期日) 或模式 1 (星期一)。
- 如果需要考虑年份的第一周是否完整，可以使用模式 2, 3, 4, 5, 6 或 7。
在选择模式参数时，务必仔细考虑其含义，并通过实际例子进行验证，确保计算结果符合预期。
避免常见错误
- 混淆 ISO 8601 标准：不要认为
WEEK(date, 4)或WEEK(date, 5)完全符合 ISO 8601 标准。 它们只是在一定程度上兼容，但仍然使用星期日或星期一作为一周的开始。 - 忽略
default_week_format系统变量： 在使用WEEK()函数时，如果没有指定mode参数，则会使用该变量的值。 务必了解该变量的设置，以避免意外的结果。 - 未考虑时区的影响：日期和时间的计算可能会受到时区的影响。 确保数据库服务器和应用程序的时区设置一致。
如何在实际应用中使用
在实际应用中，WEEK() 函数通常与 YEAR() 函数结合使用，以便按年份和周数进行分组和统计。
例如，以下 SQL 语句用于统计每个年份中每周的订单数量：
SELECT
YEAR(order_date) AS order_year,
WEEK(order_date, 1) AS order_week,
COUNT(*) AS order_count
FROM
orders
GROUP BY
order_year,
order_week
ORDER BY
order_year,
order_week;在这个例子中，我们使用 WEEK(order_date, 1) 以星期一作为一周的开始来计算周数。
另一个例子，假设我们需要按照 ISO 8601 标准统计每周的销售额，可以使用 YEARWEEK() 函数：
SELECT
YEARWEEK(sale_date, 3) AS sale_yearweek,
SUM(sale_amount) AS total_sales
FROM
sales
GROUP BY
sale_yearweek
ORDER BY
sale_yearweek;总结：正确选择模式参数，确保周数计算的准确性
WEEK() 函数的模式参数是控制周数计算的关键。理解不同模式参数的含义，并根据实际需求选择正确的模式，是确保数据分析和报表生成的准确性的重要一步。 记住，要特别注意 ISO 8601 标准的兼容性问题，并仔细考虑 default_week_format 系统变量的影响。 通过实际例子进行验证，并结合 YEAR() 和其他日期函数，可以更好地利用 WEEK() 函数进行数据处理。