今天收到一个需求,要根据日期就算它的每月的周数,按自然周(一周从周一开始)计算,如下表:
(由于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,