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

相关文章

好题——hdu2522(小数问题:求1/n的第一个循环节)

好喜欢这题,第一次做小数问题,一开始真心没思路,然后参考了网上的一些资料。 知识点***********************************无限不循环小数即无理数,不能写作两整数之比*****************************(一开始没想到,小学没学好) 此题1/n肯定是一个有限循环小数,了解这些后就能做此题了。 按照除法的机制,用一个函数表示出来就可以了,代码如下

hdu1043(八数码问题,广搜 + hash(实现状态压缩) )

利用康拓展开将一个排列映射成一个自然数,然后就变成了普通的广搜题。 #include<iostream>#include<algorithm>#include<string>#include<stack>#include<queue>#include<map>#include<stdio.h>#include<stdlib.h>#include<ctype.h>#inclu

购买磨轮平衡机时应该注意什么问题和技巧

在购买磨轮平衡机时,您应该注意以下几个关键点: 平衡精度 平衡精度是衡量平衡机性能的核心指标,直接影响到不平衡量的检测与校准的准确性,从而决定磨轮的振动和噪声水平。高精度的平衡机能显著减少振动和噪声,提高磨削加工的精度。 转速范围 宽广的转速范围意味着平衡机能够处理更多种类的磨轮,适应不同的工作条件和规格要求。 振动监测能力 振动监测能力是评估平衡机性能的重要因素。通过传感器实时监

缓存雪崩问题

缓存雪崩是缓存中大量key失效后当高并发到来时导致大量请求到数据库,瞬间耗尽数据库资源,导致数据库无法使用。 解决方案: 1、使用锁进行控制 2、对同一类型信息的key设置不同的过期时间 3、缓存预热 1. 什么是缓存雪崩 缓存雪崩是指在短时间内,大量缓存数据同时失效,导致所有请求直接涌向数据库,瞬间增加数据库的负载压力,可能导致数据库性能下降甚至崩溃。这种情况往往发生在缓存中大量 k

6.1.数据结构-c/c++堆详解下篇(堆排序,TopK问题)

上篇:6.1.数据结构-c/c++模拟实现堆上篇(向下,上调整算法,建堆,增删数据)-CSDN博客 本章重点 1.使用堆来完成堆排序 2.使用堆解决TopK问题 目录 一.堆排序 1.1 思路 1.2 代码 1.3 简单测试 二.TopK问题 2.1 思路(求最小): 2.2 C语言代码(手写堆) 2.3 C++代码(使用优先级队列 priority_queue)

【VUE】跨域问题的概念,以及解决方法。

目录 1.跨域概念 2.解决方法 2.1 配置网络请求代理 2.2 使用@CrossOrigin 注解 2.3 通过配置文件实现跨域 2.4 添加 CorsWebFilter 来解决跨域问题 1.跨域概念 跨域问题是由于浏览器实施了同源策略,该策略要求请求的域名、协议和端口必须与提供资源的服务相同。如果不相同,则需要服务器显式地允许这种跨域请求。一般在springbo

题目1254:N皇后问题

题目1254:N皇后问题 时间限制:1 秒 内存限制:128 兆 特殊判题:否 题目描述: N皇后问题,即在N*N的方格棋盘内放置了N个皇后,使得它们不相互攻击(即任意2个皇后不允许处在同一排,同一列,也不允许处在同一斜线上。因为皇后可以直走,横走和斜走如下图)。 你的任务是,对于给定的N,求出有多少种合法的放置方法。输出N皇后问题所有不同的摆放情况个数。 输入

vscode中文乱码问题,注释,终端,调试乱码一劳永逸版

忘记咋回事突然出现了乱码问题,很多方法都试了,注释乱码解决了,终端又乱码,调试窗口也乱码,最后经过本人不懈努力,终于全部解决了,现在分享给大家我的方法。 乱码的原因是各个地方用的编码格式不统一,所以把他们设成统一的utf8. 1.电脑的编码格式 开始-设置-时间和语言-语言和区域 管理语言设置-更改系统区域设置-勾选Bata版:使用utf8-确定-然后按指示重启 2.vscode

Android Environment 获取的路径问题

1. 以获取 /System 路径为例 /*** Return root of the "system" partition holding the core Android OS.* Always present and mounted read-only.*/public static @NonNull File getRootDirectory() {return DIR_ANDR

form表单提交编码的问题

浏览器在form提交后,会生成一个HTTP的头部信息"content-type",标准规定其形式为Content-type: application/x-www-form-urlencoded; charset=UTF-8        那么我们如果需要修改编码,不使用默认的,那么可以如下这样操作修改编码,来满足需求: hmtl代码:   <meta http-equiv="Conte