SQL如何计算指定日期每月的周数

今天收到一个需求，要根据日期就算它的每月的周数，按自然周(一周从周一开始)计算，如下表：

(由于5月1日是周日，因此第一周只有一天)

查了一下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)统一表示偏移量。

输出结果如下：最后一列为得到的结果

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 

输出结果如下：最后一列为得到的结果

注意ISO标准的年格式为‘IYYY’，日期20220101对应的ISO标准周数为：202152，如果用‘YYYY’，则会输出错误的结果，如下：

总结：以上两种方法均可实现需求。

方法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,

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！