今天收到一个需求，要根据日期就算它的每月的周数，按自然周(一周从周一开始)计算，如下表：
(由于5月1日是周日，因此第一周只有一天)
DATE_TIMEKEY | 周几 | MONTH_WEEK |
20220501 | 周日 | 2022M05W1 |
20220502 | 周一 | 2022M05W2 |
20220503 | 周二 | 2022M05W2 |
20220504 | 周三 | 2022M05W2 |
20220505 | 周四 | 2022M05W2 |
20220506 | 周五 | 2022M05W2 |
20220507 | 周六 | 2022M05W2 |
20220508 | 周日 | 2022M05W2 |
20220509 | 周一 | 2022M05W3 |
20220510 | 周二 | 2022M05W3 |
20220511 | 周三 | 2022M05W3 |
20220512 | 周四 | 2022M05W3 |
20220513 | 周五 | 2022M05W3 |
20220514 | 周六 | 2022M05W3 |
20220515 | 周日 | 2022M05W3 |
查了一下Oracle有每月周数的格式‘W’，测试了一下是每月1日开始算的前7天为第一周，不符合需求，也没查到其他可以直接实现的函数，因此自己研究写了几个方法实现。
1. 通过日期在每月的天数/7，再取整：
Oracle的‘W’格式的周数，可以通过每月天数/7，再向上取整得到，我们只需计算出所求日期当月1日为周几，在将日期向后移动这个偏移量(月第一天的WEEKDAY-1)，再除以7取整就可以了，具体实现过程如下：
WITH A AS (
SELECT TO_CHAR(TO_DATE('20220101','YYYYMMDD')+LEVEL-1,'YYYYMMDD') AS DATE_TIMEKEY FROM DUAL
CONNECT BY LEVEL <=100
)
SELECT A.DATE_TIMEKEY ,
TO_NUMBER(TO_CHAR(TO_DATE(DATE_TIMEKEY,'YYYYMMDD'),'D')) WEEKDAY, --1代表周日，2代表周一
'周'||SUBSTR('日一二三四五六',TO_NUMBER(TO_CHAR(TO_DATE(DATE_TIMEKEY,'YYYYMMDD'),'D')),1) "周几", --1代表周日，2代表周一
TRUNC(TO_DATE(DATE_TIMEKEY,'YYYYMMDD'),'MM') "每月第一天",
TO_NUMBER(TO_CHAR(TRUNC(TO_DATE(DATE_TIMEKEY,'YYYYMMDD'),'MM'),'D'))-1 "每月第一天周几",
TO_NUMBER(TO_CHAR(TO_DATE(DATE_TIMEKEY,'YYYYMMDD'),'DD')) "每月的第几天",
CEIL((TO_NUMBER(TO_CHAR(TO_DATE(DATE_TIMEKEY,'YYYYMMDD'),'DD'))+MOD(TO_NUMBER(TO_CHAR(TRUNC(TO_DATE(DATE_TIMEKEY,'YYYYMMDD'),'MM'),'D'))+5,7))/7) "每月第几周",
SUBSTR(DATE_TIMEKEY,1,4)||'M'||SUBSTR(DATE_TIMEKEY,5,2)||'W'||CEIL((TO_NUMBER(TO_CHAR(TO_DATE(DATE_TIMEKEY,'YYYYMMDD'),'DD'))+MOD(TO_NUMBER(TO_CHAR(TRUNC(TO_DATE(DATE_TIMEKEY,'YYYYMMDD'),'MM'),'D'))+5,7))/7) MONTH_WEEK
FROM A
ORDER BY DATE_TIMEKEY上述SQL中在计算偏移量时，用了MOD(月第一天的WEEKDAY+5,7)，原因为Oracle中WEEKDAY=1表示周日，2表示周一，以此类推，所以偏移量为月第一天的WEEKDAY-2，但如果月第一天为周日时，上述偏移量表达式的值为-1，而正确的偏移量为6，所以改为用MOD(月第一天的WEEKDAY+5,7)统一表示偏移量。
输出结果如下：最后一列为得到的结果
DATE_TIMEKEY | WEEKDAY | 周几 | 每月第一天 | 每月第一天周几 | 每月的第几天 | 每月第几周 | MONTH_WEEK |
20220101 | 7 | 周六 | 2022-01-01 00:00:00 | 6 | 1 | 1 | 2022M01W1 |
20220102 | 1 | 周日 | 2022-01-01 00:00:00 | 6 | 2 | 1 | 2022M01W1 |
20220103 | 2 | 周一 | 2022-01-01 00:00:00 | 6 | 3 | 2 | 2022M01W2 |
20220104 | 3 | 周二 | 2022-01-01 00:00:00 | 6 | 4 | 2 | 2022M01W2 |
20220105 | 4 | 周三 | 2022-01-01 00:00:00 | 6 | 5 | 2 | 2022M01W2 |
20220106 | 5 | 周四 | 2022-01-01 00:00:00 | 6 | 6 | 2 | 2022M01W2 |
20220107 | 6 | 周五 | 2022-01-01 00:00:00 | 6 | 7 | 2 | 2022M01W2 |
20220108 | 7 | 周六 | 2022-01-01 00:00:00 | 6 | 8 | 2 | 2022M01W2 |
20220109 | 1 | 周日 | 2022-01-01 00:00:00 | 6 | 9 | 2 | 2022M01W2 |
20220110 | 2 | 周一 | 2022-01-01 00:00:00 | 6 | 10 | 3 | 2022M01W3 |
20220111 | 3 | 周二 | 2022-01-01 00:00:00 | 6 | 11 | 3 | 2022M01W3 |
20220112 | 4 | 周三 | 2022-01-01 00:00:00 | 6 | 12 | 3 | 2022M01W3 |
20220113 | 5 | 周四 | 2022-01-01 00:00:00 | 6 | 13 | 3 | 2022M01W3 |
20220114 | 6 | 周五 | 2022-01-01 00:00:00 | 6 | 14 | 3 | 2022M01W3 |
20220115 | 7 | 周六 | 2022-01-01 00:00:00 | 6 | 15 | 3 | 2022M01W3 |
2. 根据ISO标准周按月进行连续排序得到
ISO标准周从周一开始，再将得到的标准周数按月分组进行连续排序(DENSE_RANK)即可得到所需月的周数，注意此方法的计算日期list需包含当月的1日，否则会出现排序错误，大家可以自行测试
--日期必须要从1号开始计算
WITH A AS (
SELECT TO_CHAR(TO_DATE('20220101','YYYYMMDD')+LEVEL-1,'YYYYMMDD') AS DATE_TIMEKEY FROM DUAL
CONNECT BY LEVEL <=100
)
SELECT B.*,
DENSE_RANK() OVER (PARTITION BY MONTH_TIMEKEY ORDER BY WEEKNUM) "每月第几周",
SUBSTR(DATE_TIMEKEY,1,4)||'M'||SUBSTR(DATE_TIMEKEY,5,2)||'W'||DENSE_RANK() OVER (PARTITION BY MONTH_TIMEKEY ORDER BY WEEKNUM) MONTH_WEEK
FROM (
SELECT A.DATE_TIMEKEY ,
SUBSTR(A.DATE_TIMEKEY,1,6) MONTH_TIMEKEY,
TO_CHAR(TO_DATE(DATE_TIMEKEY,'YYYYMMDD'),'IYYYIW') WEEKNUM
FROM A
) B
ORDER BY DATE_TIMEKEY输出结果如下：最后一列为得到的结果
DATE_TIMEKEY | MONTH_TIMEKEY | WEEKNUM | 每月第几周 | MONTH_WEEK |
20220101 | 202201 | 202152 | 1 | 2022M01W1 |
20220102 | 202201 | 202152 | 1 | 2022M01W1 |
20220103 | 202201 | 202201 | 2 | 2022M01W2 |
20220104 | 202201 | 202201 | 2 | 2022M01W2 |
20220105 | 202201 | 202201 | 2 | 2022M01W2 |
20220106 | 202201 | 202201 | 2 | 2022M01W2 |
20220107 | 202201 | 202201 | 2 | 2022M01W2 |
20220108 | 202201 | 202201 | 2 | 2022M01W2 |
20220109 | 202201 | 202201 | 2 | 2022M01W2 |
20220110 | 202201 | 202202 | 3 | 2022M01W3 |
20220111 | 202201 | 202202 | 3 | 2022M01W3 |
20220112 | 202201 | 202202 | 3 | 2022M01W3 |
20220113 | 202201 | 202202 | 3 | 2022M01W3 |
20220114 | 202201 | 202202 | 3 | 2022M01W3 |
20220115 | 202201 | 202202 | 3 | 2022M01W3 |
注意ISO标准的年格式为‘IYYY’，日期20220101对应的ISO标准周数为：202152，如果用‘YYYY’，则会输出错误的结果，如下：
DATE_TIMEKEY | MONTH_TIMEKEY | WEEKNUM | 每月第几周 | MONTH_WEEK |
20220101 | 202201 | 202252 | 6 | 2022M01W6 |
20220102 | 202201 | 202252 | 6 | 2022M01W6 |
20220103 | 202201 | 202201 | 1 | 2022M01W1 |
20220104 | 202201 | 202201 | 1 | 2022M01W1 |
20220105 | 202201 | 202201 | 1 | 2022M01W1 |
20220106 | 202201 | 202201 | 1 | 2022M01W1 |
总结：以上两种方法均可实现需求。
方法1可直接通过日期计算得到结果，简单快捷，推荐使用；
方法2则需要先计算出当月所有日期的ISO周数，然后在排序得到，数据量大的情况下会增加额外消耗，且如果参与计算的日期未包含当月所有日期，可能会得到错误结果。
以上代码是以Oracle为例，其他数据库会有微小变化，如大数据平台impala则方法1需转化为如下写法
CONCAT(SUBSTR(DATE_TIMEKEY,1,4),'M',SUBSTR(DATE_TIMEKEY,5,2),'W',cast (CEIL((day(to_timestamp(time_key,'yyyyMMdd'))+MOD(dayofweek(TRUNC(to_timestamp(time_key,'yyyyMMdd'),'MM'))+5,7))/7) as string)) MONTH_WEEK,
