【MySQL】计算日期是当前月份的第几周

力扣题

1、题目地址

2993. 发生在周五的交易 I

2、模拟表

表：Purchases

Column Name	Type
user_id	int
purchase_date	date
amount_spend	int

• (user_id, purchase_date, amount_spend) 是该表的主键(具有唯一值的列)。
• purchase_date 的范围从 2023 年 11 月 1 日到 2023 年 11 月 30 日，并包括这两个日期。
• 每一行包含 user id，purchase date 和 amount spend。

3、要求

• 编写一个解决方案，计算用户在 2023 年 11 月 的 每个星期五 的 总花费。
• 输出所有在 周五 有购买记录的周。
• 按照每月的周次序 升序 排列结果表。

4、示例

输入：

Purchases 表：

user_id	purchase_date	amount_spend
11	2023-11-07	1126
15	2023-11-30	7473
17	2023-11-14	2414
12	2023-11-24	9692
8	2023-11-03	5117
1	2023-11-16	5241
10	2023-11-12	8266
13	2023-11-24	12000

输出：

week_of_month	purchase_date	total_amount
1	2023-11-03	5117
4	2023-11-24	21692

解释：

• 在 2023 年 11 月的第一周，于 2023-11-03 星期五发生了总额为 $5,117 的交易。
• 对于 2023 年 11 月的第二周，于 2023-11-10 星期五没有交易。
• 同样，在 2023 年 11 月的第三周，于 2023-11-17 星期五没有交易。
• 在 2023 年 11 月的第四周，于 2023-11-24 星期五发生了两笔交易，分别为 $12,000 和 $9,692，总计为 $21,692。
• 输出表按照 week_of_month 升序排列。

5、代码编写

我的写法

SELECT CEILING((DAY(purchase_date)+WEEKDAY(purchase_date-INTERVAL DAY(purchase_date)-1 DAY)) / 7) AS week_of_month, 
       purchase_date, 
       SUM(amount_spend) AS total_amount
FROM Purchases
WHERE WEEKDAY(purchase_date) = 4
AND YEAR(purchase_date) = '2023'
AND MONTH(purchase_date) = '11'
GROUP BY week_of_month
ORDER BY week_of_month

代码分析

例子：purchase_date = ‘2023-11-03’

1、本月第几天，结果为 3

SELECT DAY(purchase_date);

2、本月第一天日期，结果为 2023-11-01

SELECT purchase_date - INTERVAL DAY(purchase_date)-1 DAY;

3、本月第一天是周几，结果为 2（周三），会发现 WEEKDAY(月份第一天) 的结果值刚好是补齐日期矩阵需要的天数

SELECT WEEKDAY(purchase_date - INTERVAL DAY(purchase_date)-1 DAY);

4、当前天数加上补齐日期矩阵需要的上月占用的天数（相当于是第一周补全），结果为 5

一	二	三	四	五	六	日
		3	4	5	6	7
8	9	10	11	12	13	14
15	16	17	18	19	20	21
22	23	24	25	26	27	28
29	30	31	32

SELECT DAY(purchase_date) + WEEKDAY(purchase_date - INTERVAL DAY(purchase_date)-1 DAY);

5、从上面图很清晰可以看出可以用7的倍数去区分哪一周，第一周除以每周的天数 7，并向上取整，最终结果为 1

SELECT CEIL((DAY(purchase_date) + WEEKDAY(purchase_date - INTERVAL DAY(purchase_date)-1 DAY)) / 7);

网友代码（WEEK函数mode参数选择，比较合理）

思路：计算日期（2023-11-03）周数减去当前日期第一天（2023-11-01）周数加一，就可以算出日期是当月第几周

SELECT WEEK(purchase_date, 1) - WEEK('2023-11-01', 1) + 1 AS week_of_month,
	   purchase_date,
       SUM(amount_spend) AS total_amount
FROM Purchases
WHERE WEEKDAY(purchase_date) = 4
GROUP BY purchase_date
GROUP BY purchase_date

WEEK函数接受两个参数：

• date是要获取周数的日期。
• mode是一个可选参数，用于确定周数计算的逻辑。
  它允许您指定本周是从星期一还是星期日开始，返回周数应在 0 到 52 之间或 0 到 53 之间。
  mode参数在一些网站上写的很多都细节不够，不容易理解，可以看下面参考里面的

参考

MySQL 计算当前日期属于本月第几周
MySQL week() 函数及参数 mode 详解