MaxCompute 使用SQL进行重叠交叉区间问题分析

本文涉及的产品
云原生大数据计算服务 MaxCompute,5000CU*H 100GB 3个月
云原生大数据计算服务MaxCompute,500CU*H 100GB 3个月
简介: 使用Maxcomputer内置SQL函数进行重叠交叉区间需求分析

日常需求中经常会会遇到重叠交叉区间统计的需求,比如电商场景中某个品牌的打折活动,第一次活动活动时间为2021-08-01到 2021-08-09,第二次活动时间为 2021-08-05到 2021-08-15其中5号到9号为重复天数,这些天只统计一次,即该品牌打折活动共计15天。

    接下来以某公司的需求的简化版为例进行需求分析

需求:每日服务器的在线人数(享受服务的人数)

order_id(单号)

stime(服务器开始日期)

etime(服务器结束日期)

X01

2021-05-01

2021-06-02

X02

2021-05-28

2021-06-01

X03

2021-06-13

2021-07-13

...

...

...

汇总成如下表:

date(日期)

count(人数)

2021-01-01

45

2021-01-02

90

...

...

分析:对每个单号的开始时间和结束日期进行展开,然后对日期去重

  1. 生成一系列数据,并计算每个单号的日期差
with tb1 as(select        order_id,        stime,        etime,        datediff(etime,stime) diff
fromvalues('X01','2021-05-01','2021-06-02'),('X02','2021-05-28','2021-06-01'),('X03','2021-06-13','2021-07-13')               t(order_id,stime,etime))
  1. 使用repeat或者rpad函数对每个单号的开始时间进行增值
--eg:--返回ababab select repeat('ab',3);--返回abcde12121select rpad('abcde',10,'12');
tb2 as(select        order_id,        stime,        etime,        diff,        substr(repeat(stime||',',diff),1,11*diff-1) re_t
from tb1
)
  1. 使用posexplode()方法生成序列
tb3 as(select        order_id,        stime,        etime,        diff,        re_time,        inx,        start_time
from tb2 lateral view posexplode(split(re_time,",")) t as inx,start_time
)--展示数据如下order_id  stime etime diff  re_time inx start_time
X01 2021-05-012021-06-02322021-05-01,2021-05-01,2021-05-01,2021-05-01,2021-05-01,2021-05-01,2021-05-01,2021-05-01,2021-05-01,2021-05-01,2021-05-01,2021-05-01,2021-05-01,2021-05-01,2021-05-01,2021-05-01,2021-05-01,2021-05-01,2021-05-01,2021-05-01,2021-05-01,2021-05-01,2021-05-01,2021-05-01,2021-05-01,2021-05-01,2021-05-01,2021-05-01,2021-05-01,2021-05-01,2021-05-01,2021-05-0102021-05-01X01 2021-05-012021-06-02322021-05-01,2021-05-01,2021-05-01,2021-05-01,2021-05-01,2021-05-01,2021-05-01,2021-05-01,2021-05-01,2021-05-01,2021-05-01,2021-05-01,2021-05-01,2021-05-01,2021-05-01,2021-05-01,2021-05-01,2021-05-01,2021-05-01,2021-05-01,2021-05-01,2021-05-01,2021-05-01,2021-05-01,2021-05-01,2021-05-01,2021-05-01,2021-05-01,2021-05-01,2021-05-01,2021-05-01,2021-05-0112021-05-01X01 2021-05-012021-06-02322021-05-01,2021-05-01,2021-05-01,2021-05-01,2021-05-01,2021-05-01,2021-05-01,2021-05-01,2021-05-01,2021-05-01,2021-05-01,2021-05-01,2021-05-01,2021-05-01,2021-05-01,2021-05-01,2021-05-01,2021-05-01,2021-05-01,2021-05-01,2021-05-01,2021-05-01,2021-05-01,2021-05-01,2021-05-01,2021-05-01,2021-05-01,2021-05-01,2021-05-01,2021-05-01,2021-05-01,2021-05-0122021-05-01.....
  1. 使用date_add将生成的序列与每个单号的初始时间相加,得到每个单号的在线时间
tb4 as(select        order_id,        stime,        etime,        diff,        inx,        start_time,        date_add(start_time,inx)as online_time
from tb3
)--展示数据如下:order_id  stime etime diff  inx start_time  online_time
X01 2021-05-012021-06-023202021-05-012021-05-01X01 2021-05-012021-06-023212021-05-012021-05-02X01 2021-05-012021-06-023222021-05-012021-05-03X01 2021-05-012021-06-023232021-05-012021-05-04X01 2021-05-012021-06-023242021-05-012021-05-05X01 2021-05-012021-06-023252021-05-012021-05-06X01 2021-05-012021-06-023262021-05-012021-05-07X01 2021-05-012021-06-023272021-05-012021-05-08X01 2021-05-012021-06-023282021-05-012021-05-09X01 2021-05-012021-06-023292021-05-012021-05-10...
  1. 按在线日期统计每日在线人数
select online_time,count(*) ct from tb4 groupby online_time;

若不熟悉posexplode方法的童鞋,可以使用row_number函数进行代替也能达到相同的效果。利用上述思路可以很好的解决业务中关于合并重叠区间或时间段的问题。



相关实践学习
基于MaxCompute的热门话题分析
本实验围绕社交用户发布的文章做了详尽的分析,通过分析能得到用户群体年龄分布,性别分布,地理位置分布,以及热门话题的热度。
SaaS 模式云数据仓库必修课
本课程由阿里云开发者社区和阿里云大数据团队共同出品,是SaaS模式云原生数据仓库领导者MaxCompute核心课程。本课程由阿里云资深产品和技术专家们从概念到方法,从场景到实践,体系化的将阿里巴巴飞天大数据平台10多年的经过验证的方法与实践深入浅出的讲给开发者们。帮助大数据开发者快速了解并掌握SaaS模式的云原生的数据仓库,助力开发者学习了解先进的技术栈,并能在实际业务中敏捷的进行大数据分析,赋能企业业务。 通过本课程可以了解SaaS模式云原生数据仓库领导者MaxCompute核心功能及典型适用场景,可应用MaxCompute实现数仓搭建,快速进行大数据分析。适合大数据工程师、大数据分析师 大量数据需要处理、存储和管理,需要搭建数据仓库?学它! 没有足够人员和经验来运维大数据平台,不想自建IDC买机器,需要免运维的大数据平台?会SQL就等于会大数据?学它! 想知道大数据用得对不对,想用更少的钱得到持续演进的数仓能力?获得极致弹性的计算资源和更好的性能,以及持续保护数据安全的生产环境?学它! 想要获得灵活的分析能力,快速洞察数据规律特征?想要兼得数据湖的灵活性与数据仓库的成长性?学它! 出品人:阿里云大数据产品及研发团队专家 产品 MaxCompute 官网 https://www.aliyun.com/product/odps 
目录
相关文章
|
28天前
|
SQL 存储 数据可视化
手机短信SQL分析技巧与方法
在手机短信应用中,SQL分析扮演着至关重要的角色
|
3月前
|
前端开发 Java JSON
Struts 2携手AngularJS与React:探索企业级后端与现代前端框架的完美融合之道
【8月更文挑战第31天】随着Web应用复杂性的提升,前端技术日新月异。AngularJS和React作为主流前端框架,凭借强大的数据绑定和组件化能力,显著提升了开发动态及交互式Web应用的效率。同时,Struts 2 以其出色的性能和丰富的功能,成为众多Java开发者构建企业级应用的首选后端框架。本文探讨了如何将 Struts 2 与 AngularJS 和 React 整合,以充分发挥前后端各自优势,构建更强大、灵活的 Web 应用。
55 0
|
3月前
|
SQL 数据采集 数据挖掘
为什么要使用 SQL 函数?详尽分析
【8月更文挑战第31天】
47 0
|
3月前
|
SQL 数据采集 算法
【电商数据分析利器】SQL实战项目大揭秘:手把手教你构建用户行为分析系统,从数据建模到精准营销的全方位指南!
【8月更文挑战第31天】随着电商行业的快速发展,用户行为分析的重要性日益凸显。本实战项目将指导你使用 SQL 构建电商平台用户行为分析系统,涵盖数据建模、采集、处理与分析等环节。文章详细介绍了数据库设计、测试数据插入及多种行为分析方法,如购买频次统计、商品销售排名、用户活跃时间段分析和留存率计算,帮助电商企业深入了解用户行为并优化业务策略。通过这些步骤,你将掌握利用 SQL 进行大数据分析的关键技术。
159 0
|
3月前
|
SQL 数据挖掘 BI
【超实用技巧】解锁SQL聚合函数的奥秘:从基础COUNT到高级多表分析,带你轻松玩转数据统计与挖掘的全过程!
【8月更文挑战第31天】SQL聚合函数是进行数据统计分析的强大工具,可轻松计算平均值、求和及查找极值等。本文通过具体示例,展示如何利用这些函数对`sales`表进行统计分析,包括使用`COUNT()`、`SUM()`、`AVG()`、`MIN()`、`MAX()`等函数,并结合`GROUP BY`和`HAVING`子句实现更复杂的数据挖掘需求。通过这些实践,你将学会如何高效地应用SQL聚合函数解决实际问题。
49 0
|
3月前
|
SQL 存储 分布式计算
MaxCompute SQL 与传统 SQL 的异同
【8月更文第31天】随着大数据处理的需求日益增长,传统的 SQL 数据库已经无法满足海量数据的分析需求。MaxCompute(又名 ODPS,Open Data Processing Service)是阿里云提供的大数据处理平台,它提供了 SQL 接口,使得用户可以通过熟悉的 SQL 语法来处理大规模的数据集。然而,由于 MaxCompute 设计初衷是为了处理 PB 级别的数据,因此其 SQL 与传统的 SQL 存在一些差异。本文将探讨 MaxCompute SQL 与标准 SQL 的异同,并介绍 MaxCompute SQL 的一些特殊功能。
72 0
|
3月前
|
网络协议 NoSQL 网络安全
【Azure 应用服务】由Web App“无法连接数据库”而逐步分析到解析内网地址的办法(SQL和Redis开启private endpoint,只能通过内网访问,无法从公网访问的情况下)
【Azure 应用服务】由Web App“无法连接数据库”而逐步分析到解析内网地址的办法(SQL和Redis开启private endpoint,只能通过内网访问,无法从公网访问的情况下)
|
4月前
|
SQL 分布式计算 资源调度
MaxCompute操作报错合集之执行SQL Union All操作时,数据类型产生报错,该怎么解决
MaxCompute是阿里云提供的大规模离线数据处理服务,用于大数据分析、挖掘和报表生成等场景。在使用MaxCompute进行数据处理时,可能会遇到各种操作报错。以下是一些常见的MaxCompute操作报错及其可能的原因与解决措施的合集。
122 1
|
4月前
|
SQL 数据处理
SQL 能力问题之合并两个存在交叉的日期区间,如何解决
SQL 能力问题之合并两个存在交叉的日期区间,如何解决
|
4月前
|
SQL 分布式计算 数据处理
SQL 能力问题之MaxCompute(ODPS)SQL有哪些特点
SQL 能力问题之MaxCompute(ODPS)SQL有哪些特点