根据时间确认当年第几周

使用weekofyear函数,传进去一个日期,判断这是当年第几周,一周的定义是从周一到周日,也可以是从周日到周六,根据业务情况来使用。

1
2
3
4
5
6
7
8
9
10
select
CAL_DT
, DAY_OF_WEEK_ID
, day_of_week
, weekofyear(CAL_DT) as week_of_year
, week_of_year_id
from dw_cal_dt
where cal_dt between '2022-01-01' and '2022-01-10'
order by 1
;
CAL_DT DAY_OF_WEEK_ID day_of_week week_of_year week_of_year_id
2022-01-01 7 Sat 52 1
2022-01-02 1 Sun 52 2
2022-01-03 2 Mon 1 2
2022-01-04 3 Tue 1 2
2022-01-05 4 Wed 1 2
2022-01-06 5 Thu 1 2
2022-01-07 6 Fri 1 2
2022-01-08 7 Sat 1 2
2022-01-09 1 Sun 1 3
2022-01-10 2 Mon 2 3

如果一周的定义是周一到周日:

Code 用途 注释
select date_sub(next_day("2022-02-21","mo"),7) as Monday; 取当前周的周一
select date_sub(next_day("2022-02-21","mo"),6) as Tuesday; 取当前周的周二
select date_sub(next_day("2022-02-21","mo"),5) as Wednesday; 取当前周的周三
select date_sub(next_day("2022-02-21","mo"),4) as Thursday; 取当前周的周四
select date_sub(next_day("2022-02-21","mo"),3) as Friday; 取当前周的周五
select date_sub(next_day("2022-02-21","mo"),2) as Saturday; 取当前周的周六
select date_sub(next_day("2022-02-21","mo"),1) as Sunday; 取当前周的周日