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

2024-05-31 23:04

本文主要是介绍DataWorks+MaxCompute跨年取日期所在周的问题,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

一、背景

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

实验SQL环境:DataWorks + MaxCompute

dim_date表清单:

n_dayn_yearn_quartern_monthday_of_monthday_of_weekweek_of_year
2019-12-28201941228652
2019-12-29201941229752
2019-12-3020194123011
2019-12-3120194123121
2020-01-01202011131
2020-01-02202011241
2020-01-03202011351
2020-01-04202011461
2020-01-05202011571
2020-01-06202011612
2020-01-07202011722
2020-01-08202011832
2020-01-09202011942
2020-01-102020111052

在统计中,我们一般会按照 “年-周” 方式来组织成周格式,再与业务事实表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_weekn_dayday_of_week
2019-512019-12-161
2019-522019-12-231
2020-022020-01-061
2020-032020-01-131
2020-042020-01-201
2020-052020-01-271
2020-062020-02-031
2020-072020-02-101
2020-082020-02-171
2020-092020-02-241
2020-102020-03-021

你会发现根据这种方式计算,为什么 “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_weekn_dayday_of_week
2019-512019-12-161
2019-522019-12-231
2020-012019-12-301
2020-022020-01-061

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 组合”

这篇关于DataWorks+MaxCompute跨年取日期所在周的问题的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/1019288

相关文章

怎样通过分析GC日志来定位Java进程的内存问题

《怎样通过分析GC日志来定位Java进程的内存问题》:本文主要介绍怎样通过分析GC日志来定位Java进程的内存问题,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录一、GC 日志基础配置1. 启用详细 GC 日志2. 不同收集器的日志格式二、关键指标与分析维度1.

Java 线程安全与 volatile与单例模式问题及解决方案

《Java线程安全与volatile与单例模式问题及解决方案》文章主要讲解线程安全问题的五个成因(调度随机、变量修改、非原子操作、内存可见性、指令重排序)及解决方案,强调使用volatile关键字... 目录什么是线程安全线程安全问题的产生与解决方案线程的调度是随机的多个线程对同一个变量进行修改线程的修改操

Redis出现中文乱码的问题及解决

《Redis出现中文乱码的问题及解决》:本文主要介绍Redis出现中文乱码的问题及解决,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录1. 问题的产生2China编程. 问题的解决redihttp://www.chinasem.cns数据进制问题的解决中文乱码问题解决总结

全面解析MySQL索引长度限制问题与解决方案

《全面解析MySQL索引长度限制问题与解决方案》MySQL对索引长度设限是为了保持高效的数据检索性能,这个限制不是MySQL的缺陷,而是数据库设计中的权衡结果,下面我们就来看看如何解决这一问题吧... 目录引言:为什么会有索引键长度问题?一、问题根源深度解析mysql索引长度限制原理实际场景示例二、五大解决

Springboot如何正确使用AOP问题

《Springboot如何正确使用AOP问题》:本文主要介绍Springboot如何正确使用AOP问题,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录​一、AOP概念二、切点表达式​execution表达式案例三、AOP通知四、springboot中使用AOP导出

Python中Tensorflow无法调用GPU问题的解决方法

《Python中Tensorflow无法调用GPU问题的解决方法》文章详解如何解决TensorFlow在Windows无法识别GPU的问题,需降级至2.10版本,安装匹配CUDA11.2和cuDNN... 当用以下代码查看GPU数量时,gpuspython返回的是一个空列表,说明tensorflow没有找到

解决未解析的依赖项:‘net.sf.json-lib:json-lib:jar:2.4‘问题

《解决未解析的依赖项:‘net.sf.json-lib:json-lib:jar:2.4‘问题》:本文主要介绍解决未解析的依赖项:‘net.sf.json-lib:json-lib:jar:2.4... 目录未解析的依赖项:‘net.sf.json-lib:json-lib:jar:2.4‘打开pom.XM

IDEA Maven提示:未解析的依赖项的问题及解决

《IDEAMaven提示:未解析的依赖项的问题及解决》:本文主要介绍IDEAMaven提示:未解析的依赖项的问题及解决,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝... 目录IDEA Maven提示:未解析的依编程赖项例如总结IDEA Maven提示:未解析的依赖项例如

Redis分片集群、数据读写规则问题小结

《Redis分片集群、数据读写规则问题小结》本文介绍了Redis分片集群的原理,通过数据分片和哈希槽机制解决单机内存限制与写瓶颈问题,实现分布式存储和高并发处理,但存在通信开销大、维护复杂及对事务支持... 目录一、分片集群解android决的问题二、分片集群图解 分片集群特征如何解决的上述问题?(与哨兵模

SpringBoot+Redis防止接口重复提交问题

《SpringBoot+Redis防止接口重复提交问题》:本文主要介绍SpringBoot+Redis防止接口重复提交问题,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不... 目录前言实现思路代码示例测试总结前言在项目的使用使用过程中,经常会出现某些操作在短时间内频繁提交。例