一、背景
一些数据分析业务需求中,如订单金额、订单数量,时间粒度需要统计到周,如周同比,周环比。
一般我们都会事先创建一个类似如下的时间维度表。
实验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 组合”