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

相关文章

Spring的RedisTemplate的json反序列泛型丢失问题解决

《Spring的RedisTemplate的json反序列泛型丢失问题解决》本文主要介绍了SpringRedisTemplate中使用JSON序列化时泛型信息丢失的问题及其提出三种解决方案,可以根据性... 目录背景解决方案方案一方案二方案三总结背景在使用RedisTemplate操作redis时我们针对

Kotlin Map映射转换问题小结

《KotlinMap映射转换问题小结》文章介绍了Kotlin集合转换的多种方法,包括map(一对一转换)、mapIndexed(带索引)、mapNotNull(过滤null)、mapKeys/map... 目录Kotlin 集合转换:map、mapIndexed、mapNotNull、mapKeys、map

nginx中端口无权限的问题解决

《nginx中端口无权限的问题解决》当Nginx日志报错bind()to80failed(13:Permissiondenied)时,这通常是由于权限不足导致Nginx无法绑定到80端口,下面就来... 目录一、问题原因分析二、解决方案1. 以 root 权限运行 Nginx(不推荐)2. 为 Nginx

解决1093 - You can‘t specify target table报错问题及原因分析

《解决1093-Youcan‘tspecifytargettable报错问题及原因分析》MySQL1093错误因UPDATE/DELETE语句的FROM子句直接引用目标表或嵌套子查询导致,... 目录报js错原因分析具体原因解决办法方法一:使用临时表方法二:使用JOIN方法三:使用EXISTS示例总结报错原

Windows环境下解决Matplotlib中文字体显示问题的详细教程

《Windows环境下解决Matplotlib中文字体显示问题的详细教程》本文详细介绍了在Windows下解决Matplotlib中文显示问题的方法,包括安装字体、更新缓存、配置文件设置及编码調整,并... 目录引言问题分析解决方案详解1. 检查系统已安装字体2. 手动添加中文字体(以SimHei为例)步骤

SpringSecurity整合redission序列化问题小结(最新整理)

《SpringSecurity整合redission序列化问题小结(最新整理)》文章详解SpringSecurity整合Redisson时的序列化问题,指出需排除官方Jackson依赖,通过自定义反序... 目录1. 前言2. Redission配置2.1 RedissonProperties2.2 Red

nginx 负载均衡配置及如何解决重复登录问题

《nginx负载均衡配置及如何解决重复登录问题》文章详解Nginx源码安装与Docker部署,介绍四层/七层代理区别及负载均衡策略,通过ip_hash解决重复登录问题,对nginx负载均衡配置及如何... 目录一:源码安装:1.配置编译参数2.编译3.编译安装 二,四层代理和七层代理区别1.二者混合使用举例

怎样通过分析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数据进制问题的解决中文乱码问题解决总结