使用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; |
取当前周的周日 |
|