一、背景
一些数据分析业务需求中，如订单金额、订单数量，时间粒度需要统计到周，如周同比，周环比。
一般我们都会事先创建一个类似如下的时间维度表。
实验SQL环境：DataWorks + MaxCompute
dim_date表清单：
| n_day | n_year | n_quarter | n_month | day_of_month | day_of_week | week_of_year |
|---|---|---|---|---|---|---|
| 2019-12-28 | 2019 | 4 | 12 | 28 | 6 | 52 |
| 2019-12-29 | 2019 | 4 | 12 | 29 | 7 | 52 |
| 2019-12-30 | 2019 | 4 | 12 | 30 | 1 | 1 |
| 2019-12-31 | 2019 | 4 | 12 | 31 | 2 | 1 |
| 2020-01-01 | 2020 | 1 | 1 | 1 | 3 | 1 |
| 2020-01-02 | 2020 | 1 | 1 | 2 | 4 | 1 |
| 2020-01-03 | 2020 | 1 | 1 | 3 | 5 | 1 |
| 2020-01-04 | 2020 | 1 | 1 | 4 | 6 | 1 |
| 2020-01-05 | 2020 | 1 | 1 | 5 | 7 | 1 |
| 2020-01-06 | 2020 | 1 | 1 | 6 | 1 | 2 |
| 2020-01-07 | 2020 | 1 | 1 | 7 | 2 | 2 |
| 2020-01-08 | 2020 | 1 | 1 | 8 | 3 | 2 |
| 2020-01-09 | 2020 | 1 | 1 | 9 | 4 | 2 |
| 2020-01-10 | 2020 | 1 | 1 | 10 | 5 | 2 |
在统计中，我们一般会按照 “年-周” 方式来组织成周格式，再与业务事实表join起来进行度量值的聚合计算。
在这个场景下，如果我们还想统计每周的第一天，一段sql如下：
SELECTCONCAT(n_year, '-', IF(LENGTH(week_of_year) < 2, CONCAT('0', week_of_year), week_of_year)) year_week,n_day AS first_day_of_week,day_of_week
from dim_date
WHERE day_of_week = 1
ORDER BY year_week;
以2019年年尾、2020年初数据为例
| year_week | n_day | day_of_week |
|---|---|---|
| 2019-51 | 2019-12-16 | 1 |
| 2019-52 | 2019-12-23 | 1 |
| 2020-02 | 2020-01-06 | 1 |
| 2020-03 | 2020-01-13 | 1 |
| 2020-04 | 2020-01-20 | 1 |
| 2020-05 | 2020-01-27 | 1 |
| 2020-06 | 2020-02-03 | 1 |
| 2020-07 | 2020-02-10 | 1 |
| 2020-08 | 2020-02-17 | 1 |
| 2020-09 | 2020-02-24 | 1 |
| 2020-10 | 2020-03-02 | 1 |
你会发现根据这种方式计算，为什么 “2020-01” 的数据消失了？
其实不仅是年初的第1周，而且年尾的第53周也可能会消失。
经过排查，不难发现，这段SQL在统计 2020年第一周的时候，根据sql语句：
CONCAT(n_year, '-', IF(LENGTH(week_of_year) < 2, CONCAT('0', week_of_year), week_of_year))
原本是想构建成“2020-01”这样的year + week 组合，但因为在WHERE子句中限定了“day_of_week = 1”，则在跨年的数据场景中，很可能像笔者构建的样例数据一样，取到了上一年（即2019年）的那条数据，这样组合成了“2019-01”这样的year + week 组合。
这种统计逻辑后患无穷，不仅取不到合理的year + week 组合，且在后续与事实表join后进行统计时，会将原本属于“2020-01”周的数据，错误的统计到“2019-01”中去。
二、函数 week_of_year
也许大家会有疑问，为什么有时候看到一些年份的元旦前几天，会被算到上一年的最后一周里。
这里有个注意事项：
这一周算上一年还是下一年，取决于这一周的大多数日期（4天以上）在哪一年。算在前一年，就是前一年的最后一周；算在后一年就是后一年的第一周。
week_of_year 函数说明
作用：
返回日期date位于那一年的第几周。周一作为一周的第一天。
命令格式：
bigint weekofyear (datetime )
参数说明：
date：必填。DATETIME类型日期值。格式为yyyy-mm-dd hh:mi:ss。如果输入为STRING类型，且MaxCompute项目的数据类型版本是1.0，则会隐式转换为DATETIME类型后参与运算。
返回值说明：
返回BIGINT类型。返回规则如下：
- date非DATETIME或STRING类型，或格式不符合要求时，返回NULL。
- date值为NULL时，返回NULL。
三、一种修复方案
借助WEEKOFYEAR函数，我们可以方便获取到任意一天所在的“一年周的第几周”，如果再根据如下结论：
这一周算上一年还是下一年，取决于这一周的大多数日期（4天以上）在哪一年。算在前一年，就是前一年的最后一周；算在后一年就是后一年的第一周。
统计出年初所在的首周位于哪一年，再进行拼装（这里使用YEAR*100+WEEK方法），即可精确算出 “year + week 组合”，一种修复方案如下：
SELECTYEAR(DATE_SUB(NEXT_DAY(n_day,'monday'),4))*100 + WEEKOFYEAR(n_day) year_week,n_day AS first_day_of_week,day_of_week
from dim_date
WHERE day_of_week = 1
ORDER BY year_week;
运行结果：
| year_week | n_day | day_of_week |
|---|---|---|
| 2019-51 | 2019-12-16 | 1 |
| 2019-52 | 2019-12-23 | 1 |
2020-01 | 2019-12-30 | 1 |
| 2020-02 | 2020-01-06 | 1 |
SQL解析：
- 1、
NEXT_DAY(n_day,'monday')：计算指定日的下一个时间单位（这里指代下一个monday）； - 2、
DATE_SUB(NEXT_DAY(n_day,'monday'),4))：计算一周的大多数日期（4天以上）在哪一年； - 3、
YEAR(DATE_SUB(NEXT_DAY(n_day,'monday'),4))*100 + WEEKOFYEAR(n_day)：使用YEAR*100+WEEK思想拼装出“year + week 组合”
