前提:建立一张DIM_DATE表
编写存储过程建立DIM_DATE表
一、以周一为一周的开始
需求1:每个月不满一周的日期补充到下一周
以2024年6月举例,1号和2号属于周六周日,不满一周,需要补充到下一周,即1号到9号作为6月的第一周
WITH MATCH_DATE AS
(
SELECT D_DATE AS FENDDATE,WEEKDAY-1 AS WEEKDAY
,ROUND(TO_NUMBER(TO_DATE(D_DATE,'YYYY-MM-DD')-TO_DATE(SUBSTR(D_DATE,1,7)||'-01','YYYY-MM-DD'))) AS DD
FROM
(
SELECT D_DATE
,TO_CHAR(TO_DATE(D_DATE,'YYYY-MM-DD'),'D') AS WEEKDAY
FROM DIM_DATE
WHERE SUBSTR(D_DATE,1,7) = '2024-06'
)
WHERE WEEKDAY = 2
AND ROUND(TO_NUMBER(TO_DATE(D_DATE,'YYYY-MM-DD')-TO_DATE(SUBSTR(D_DATE,1,7)||'-01','YYYY-MM-DD'))) >= 6
)
SELECT
D_DATE
,SUM(WEEKDAY) OVER(PARTITION BY SUBSTR(D_DATE,1,7) ORDER BY D_DATE)+1 AS WEEKDAY
FROM
(
SELECT
A.D_DATE
,B.FENDDATE
,NVL(B.WEEKDAY,0) AS WEEKDAY
FROM
DIM_DATE A
LEFT JOIN
MATCH_DATE B
ON A.D_DATE = B.FENDDATE
WHERE SUBSTR(A.D_DATE,1,7) = '2024-06'
ORDER BY A.D_DATE
)
思路:
第一步,创建一个MATCH_DATE临时表
先获取2024年6月份的日期,用THAR(DATE类型的字段,'D')获取WEEKDAY
周日返回的WEEKDAY字段为1,以周日为一周的开始
把WEEKDAY字段减1,后续累加用
通过D_DATE字段减去当月第一天的日期,获取DD字段
过滤出WEEKDAY等于2的数据(即周一的日期)
过滤出DD字段大于等于6的数据(获取第二个周一)
第二步,以DIM_DATE表为主表,拼接MATCH_DATE临时表
左链接MATCH_DATE临时表的FENDDATE字段,
左链接MATCH_DATE临时表的WEEKDAY字段,作NVL(WEEKDAY,0)的处理
第三步,嵌套一层查询,以月份为分组,D_DATE为排序
为了方便正常理解,对排序字段都+1处理