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

本文涉及的产品
云原生大数据计算服务MaxCompute,500CU*H 100GB 3个月
云原生大数据计算服务 MaxCompute,5000CU*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 
目录
相关文章
|
7天前
|
SQL 存储 算法
比 SQL 快出数量级的大数据计算技术
SQL 是大数据计算中最常用的工具,但在实际应用中,SQL 经常跑得很慢,浪费大量硬件资源。例如,某银行的反洗钱计算在 11 节点的 Vertica 集群上跑了 1.5 小时,而用 SPL 重写后,单机只需 26 秒。类似地,电商漏斗运算和时空碰撞任务在使用 SPL 后,性能也大幅提升。这是因为 SQL 无法写出低复杂度的算法,而 SPL 提供了更强大的数据类型和基础运算,能够实现高效计算。
|
1月前
|
SQL 存储 数据可视化
手机短信SQL分析技巧与方法
在手机短信应用中,SQL分析扮演着至关重要的角色
|
1月前
|
SQL 消息中间件 分布式计算
大数据-143 - ClickHouse 集群 SQL 超详细实践记录!(一)
大数据-143 - ClickHouse 集群 SQL 超详细实践记录!(一)
71 0
|
1月前
|
SQL 大数据
大数据-143 - ClickHouse 集群 SQL 超详细实践记录!(二)
大数据-143 - ClickHouse 集群 SQL 超详细实践记录!(二)
57 0
|
1月前
|
SQL 大数据 API
大数据-132 - Flink SQL 基本介绍 与 HelloWorld案例
大数据-132 - Flink SQL 基本介绍 与 HelloWorld案例
45 0
|
1月前
|
SQL 分布式计算 大数据
大数据-97 Spark 集群 SparkSQL 原理详细解析 Broadcast Shuffle SQL解析过程(一)
大数据-97 Spark 集群 SparkSQL 原理详细解析 Broadcast Shuffle SQL解析过程(一)
42 0
|
1月前
|
SQL 分布式计算 算法
大数据-97 Spark 集群 SparkSQL 原理详细解析 Broadcast Shuffle SQL解析过程(二)
大数据-97 Spark 集群 SparkSQL 原理详细解析 Broadcast Shuffle SQL解析过程(二)
78 0
|
1月前
|
SQL 分布式计算 Java
大数据-96 Spark 集群 SparkSQL Scala编写SQL操作SparkSQL的数据源:JSON、CSV、JDBC、Hive
大数据-96 Spark 集群 SparkSQL Scala编写SQL操作SparkSQL的数据源:JSON、CSV、JDBC、Hive
34 0
|
1月前
|
SQL 分布式计算 大数据
大数据-94 Spark 集群 SQL DataFrame & DataSet & RDD 创建与相互转换 SparkSQL
大数据-94 Spark 集群 SQL DataFrame & DataSet & RDD 创建与相互转换 SparkSQL
50 0
|
1月前
|
SQL 存储 分布式计算
大数据-93 Spark 集群 Spark SQL 概述 基本概念 SparkSQL对比 架构 抽象
大数据-93 Spark 集群 Spark SQL 概述 基本概念 SparkSQL对比 架构 抽象
37 0