DataWorks+MaxCompute跨年取日期所在周的问题

一、背景

一些数据分析业务需求中，如订单金额、订单数量，时间粒度需要统计到周，如周同比，周环比。
一般我们都会事先创建一个类似如下的时间维度表。

实验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如下：

SELECT
	CONCAT(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 组合”。

一种修复方案如下：

SELECT
	YEAR(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-month(月份一位左侧补零0️⃣)”的展示效果：

SELECT
	CONCAT(YEAR(DATE_SUB(NEXT_DAY(n_day,'monday'),4)),'-',IF(LENGTH()<2, CONCAT('0',WEEKOFYEAR(n_day)),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 组合”