一、目的

ClickHouse指標(biāo)表中有個(gè)字段week_of_month，含義是這條數(shù)據(jù)屬于本月第幾周。

而且將本月第一天所在的那一周視為第一周，無論它是從周幾開始的。比如2024-12-01是周日，即12月第一周。而2024-12-02是周一，即12月第二周

二、文心一言

文心一言的代碼有點(diǎn)問題

三、SQL語句

selectday,toDate(concat(toString(toYear(day)), '-', lpad(toString(toMonth(day)), 2, '0'), '-01')) as first_day_of_month ,DAYOFMONTH(day) - 1 AS days_since_first_day,toDayOfWeek(first_day_of_month) AS first_day_of_week,(7 - (first_day_of_week - 1) % 7) AS days_in_first_week,CASEWHEN days_since_first_day < days_in_first_week THEN 1WHEN days_since_first_day >= days_in_first_week  and days_since_first_day < days_in_first_week+7  THEN 2WHEN days_since_first_day >= days_in_first_week  and days_since_first_day < days_in_first_week+14  THEN 3WHEN days_since_first_day >= days_in_first_week  and days_since_first_day < days_in_first_week+21  THEN 4WHEN days_since_first_day >= days_in_first_week  and days_since_first_day < days_in_first_week+28  THEN 5WHEN days_since_first_day >= days_in_first_week  and days_since_first_day < days_in_first_week+35  THEN 6END AS week_of_month
from hurys_jw.tb_week_of_month
group by day
;

• first_day_of_month?計(jì)算本月的第一天。
• days_since_first_day?計(jì)算從本月第一天到目標(biāo)日期之間的天數(shù)差。
• first_day_of_week?獲取本月第一天是周幾。
• days_to_first_monday?計(jì)算從本月第一天到第一個(gè)周一之間的天數(shù)（如果第一天不是周一）

四、驗(yàn)證查詢結(jié)果

和11月日歷一一驗(yàn)證，沒問題，搞定！