大数据Hive窗口函数应用实例 1

本文涉及的产品
云原生大数据计算服务 MaxCompute,5000CU*H 100GB 3个月
云原生大数据计算服务MaxCompute,500CU*H 100GB 3个月
简介: 大数据Hive窗口函数应用实例

1 连续登陆用户

1.1 需求

当前有一份用户登录数据如下图所示,数据中有两个字段,分别是userId和loginTime。

userId表示唯一的用户ID,唯一标识一个用户,loginTime表示用户的登录日期,例如第一条数据就表示A在2021年3月22日登录了。

现在需要对用户的登录次数进行统计,得到连续登陆N(N>=2)天的用户。

例如统计连续两天的登录的用户,需要返回A和C,因为A在22/23/24都登录了,所以肯定是连续两天登录,C在22和23号登录了,所以也是连续两天登录的。

例如统计连续三天的登录的用户,只能返回A,因为只有A是连续三天登录的。


1.2 分析

基于以上的需求根据数据寻找规律,要想得到连续登陆用户,必须找到两个相同用户ID的行之间登陆日期之间的关系。

例如:统计连续登陆两天的用户,只要用户ID相等,并且登陆日期之间相差1天即可。基于这个规律,我们有两种方案可以实现该需求。

方案一:实现表中的数据自连接,构建笛卡尔积,在结果中找到符合条件的id即可

方案二:使用窗口函数来实现


1.3 建表

➢ 创建表

--切换数据库
use db_function;
--建表
create table tb_login(
  userid string,
  logintime string
) row format delimited fields terminated by '\t';

➢ 创建数据:vim /export/data/login.log\

A       2021-03-22
B       2021-03-22
C       2021-03-22
A       2021-03-23
C       2021-03-23
A       2021-03-24
B       2021-03-24

➢ 加载数据

load data local inpath ‘/export/data/login.log’ into table tb_login;

➢ 查询数据

select * from tb_login;

1.4 方案一:自连接过滤实现

➢ 构建笛卡尔积

select
  a.userid as a_userid,
  a.logintime as a_logintime,
  b.userid as b_userid,
  b.logintime as b_logintime
from tb_login a,tb_login b;

➢ 查看数据

+-----------+--------------+-----------+--------------+
| A_USERID  | A_LOGINTIME  | B_USERID  | B_LOGINTIME  |
+-----------+--------------+-----------+--------------+
| A         | 2021-03-22   | A         | 2021-03-22   |
| B         | 2021-03-22   | A         | 2021-03-22   |
| C         | 2021-03-22   | A         | 2021-03-22   |
| A         | 2021-03-23   | A         | 2021-03-22   |
| C         | 2021-03-23   | A         | 2021-03-22   |
| A         | 2021-03-24   | A         | 2021-03-22   |
| B         | 2021-03-24   | A         | 2021-03-22   |
| A         | 2021-03-22   | B         | 2021-03-22   |
| B         | 2021-03-22   | B         | 2021-03-22   |
| C         | 2021-03-22   | B         | 2021-03-22   |
| A         | 2021-03-23   | B         | 2021-03-22   |
| C         | 2021-03-23   | B         | 2021-03-22   |
| A         | 2021-03-24   | B         | 2021-03-22   |
| B         | 2021-03-24   | B         | 2021-03-22   |
| A         | 2021-03-22   | C         | 2021-03-22   |
| B         | 2021-03-22   | C         | 2021-03-22   |
| C         | 2021-03-22   | C         | 2021-03-22   |
| A         | 2021-03-23   | C         | 2021-03-22   |
| C         | 2021-03-23   | C         | 2021-03-22   |
| A         | 2021-03-24   | C         | 2021-03-22   |
| B         | 2021-03-24   | C         | 2021-03-22   |
| A         | 2021-03-22   | A         | 2021-03-23   |
| B         | 2021-03-22   | A         | 2021-03-23   |
| C         | 2021-03-22   | A         | 2021-03-23   |
| A         | 2021-03-23   | A         | 2021-03-23   |
| C         | 2021-03-23   | A         | 2021-03-23   |
| A         | 2021-03-24   | A         | 2021-03-23   |
| B         | 2021-03-24   | A         | 2021-03-23   |
| A         | 2021-03-22   | C         | 2021-03-23   |
| B         | 2021-03-22   | C         | 2021-03-23   |
| C         | 2021-03-22   | C         | 2021-03-23   |
| A         | 2021-03-23   | C         | 2021-03-23   |
| C         | 2021-03-23   | C         | 2021-03-23   |
| A         | 2021-03-24   | C         | 2021-03-23   |
| B         | 2021-03-24   | C         | 2021-03-23   |
| A         | 2021-03-22   | A         | 2021-03-24   |
| B         | 2021-03-22   | A         | 2021-03-24   |
| C         | 2021-03-22   | A         | 2021-03-24   |
| A         | 2021-03-23   | A         | 2021-03-24   |
| C         | 2021-03-23   | A         | 2021-03-24   |
| A         | 2021-03-24   | A         | 2021-03-24   |
| B         | 2021-03-24   | A         | 2021-03-24   |
| A         | 2021-03-22   | B         | 2021-03-24   |
| B         | 2021-03-22   | B         | 2021-03-24   |
| C         | 2021-03-22   | B         | 2021-03-24   |
| A         | 2021-03-23   | B         | 2021-03-24   |
| C         | 2021-03-23   | B         | 2021-03-24   |
| A         | 2021-03-24   | B         | 2021-03-24   |
| B         | 2021-03-24   | B         | 2021-03-24   |
+-----------+--------------+-----------+--------------+

➢ 保存为表

create table tb_login_tmp as
select
  a.userid as a_userid,
  a.logintime as a_logintime,
  b.userid as b_userid,
  b.logintime as b_logintime
from tb_login a,tb_login b;

➢ 过滤数据:用户id相同并且登陆日期相差1

select
  a_userid,a_logintime,b_userid,b_logintime
from tb_login_tmp
where a_userid = b_userid
and cast(substr(a_logintime,9,2) as int) - 1 = cast(substr(b_logintime,9,2) as int);

➢ 统计连续登陆两天的用户

select
  distinct a_userid
from tb_login_tmp
where a_userid = b_userid
and cast(substr(a_logintime,9,2) as int) - 1 = cast(substr(b_logintime,9,2) as int);

➢ 问题

如果现在需要统计连续3天的用户个数,如何实现呢?或者说需要统计连续5天、连续7天、连续10天、连续30天登陆的用户如何进行计算呢?

如果使用自连接的方式会非常的麻烦才能实现统计连续登陆两天以上的用户,并且性能很差,所以我们需要使用第二种方式来实现。


1.5 方案二:窗口函数实现

➢ 窗口函数lead

➢ 功能:用于从当前数据中基于当前行的数据向后偏移取值

➢ 语法:lead(colName,N,defautValue)

➢colName:取哪一列的值

➢N:向后偏移N行

➢ defaultValue:如果取不到返回的默认值

➢分析

当前数据中记录了每个用户每一次登陆的日期,一个用户在一天只有1条信息,我们可以基于用户的登陆信息,找到如下规律:

连续两天登陆 : 用户下次登陆时间 = 本次登陆以后的第二天

连续三天登陆 : 用户下下次登陆时间 = 本次登陆以后的第三天

……依次类推。

我们可以对用户ID进行分区,按照登陆时间进行排序,通过lead函数计算出用户下次登陆时间,通过日期函数计算出登陆以后第二天的日期,如果相等即为连续两天登录。


➢ 统计连续2天登录

select
  userid,
  logintime,
  --本次登陆日期的第二天
  date_add(logintime,1) as nextday,
  --按照用户id分区,按照登陆日期排序,取下一次登陆时间,取不到就为0
  lead(logintime,1,0) over (partition by userid order by logintime) as nextlogin
from tb_login;

5c2e92049d4941498e82593467053f93.png

with t1 as (

select

userid,

logintime,

–本次登陆日期的第二天

date_add(logintime,1) as nextday,

–按照用户id分区,按照登陆日期排序,取下一次登陆时间,取不到就为0

lead(logintime,1,0) over (partition by userid order by logintime) as nextlogin

from tb_login )

select distinct userid from t1 where nextday = nextlogin;


➢ 统计连续3天登录

select
  userid,
  logintime,
  --本次登陆日期的第三天
  date_add(logintime,2) as nextday,
  --按照用户id分区,按照登陆日期排序,取下下一次登陆时间,取不到就为0
  lead(logintime,2,0) over (partition by userid order by logintime) as nextlogin
from tb_login;

0670fafb32364fd189cace209de39045.png

with t1 as (
select
  userid,
  logintime,
  --本次登陆日期的第三天
  date_add(logintime,2) as nextday,
  --按照用户id分区,按照登陆日期排序,取下下一次登陆时间,取不到就为0
  lead(logintime,2,0) over (partition by userid order by logintime) as nextlogin
from tb_login )
select distinct userid from t1 where nextday = nextlogin;

➢ 统计连续N天登录

select

userid,

logintime,

–本次登陆日期的第N天

date_add(logintime,N-1) as nextday,

–按照用户id分区,按照登陆日期排序,取下下一次登陆时间,取不到就为0

lead(logintime,N-1,0) over (partition by userid order by logintime) as nextlogin

from tb_login;

2 级联累加求和

2.1 需求

当前有一份消费数据如下,记录了每个用户在每个月的所有消费记录,数据表中一共有三列:

➢userId:用户唯一id,唯一标识一个用户

➢mth:用户消费的月份,一个用户可以在一个月多次消费

➢money:用户每次消费的金额

现在需要基于用户每个月的多次消费的记录进行分析,统计得到每个用户在每个月的消费总金额以及当前累计消费总金额,最后结果如下:

以用户A为例:

A在2021年1月份,共四次消费,分别消费5元、15元、8元、5元,所以本月共消费33元,累计消费33元。

A在2021年2月份,共两次消费,分别消费4元、6元,所以本月共消费10元,累计消费43元。

相关实践学习
基于MaxCompute的热门话题分析
本实验围绕社交用户发布的文章做了详尽的分析,通过分析能得到用户群体年龄分布,性别分布,地理位置分布,以及热门话题的热度。
SaaS 模式云数据仓库必修课
本课程由阿里云开发者社区和阿里云大数据团队共同出品,是SaaS模式云原生数据仓库领导者MaxCompute核心课程。本课程由阿里云资深产品和技术专家们从概念到方法,从场景到实践,体系化的将阿里巴巴飞天大数据平台10多年的经过验证的方法与实践深入浅出的讲给开发者们。帮助大数据开发者快速了解并掌握SaaS模式的云原生的数据仓库,助力开发者学习了解先进的技术栈,并能在实际业务中敏捷的进行大数据分析,赋能企业业务。 通过本课程可以了解SaaS模式云原生数据仓库领导者MaxCompute核心功能及典型适用场景,可应用MaxCompute实现数仓搭建,快速进行大数据分析。适合大数据工程师、大数据分析师 大量数据需要处理、存储和管理,需要搭建数据仓库?学它! 没有足够人员和经验来运维大数据平台,不想自建IDC买机器,需要免运维的大数据平台?会SQL就等于会大数据?学它! 想知道大数据用得对不对,想用更少的钱得到持续演进的数仓能力?获得极致弹性的计算资源和更好的性能,以及持续保护数据安全的生产环境?学它! 想要获得灵活的分析能力,快速洞察数据规律特征?想要兼得数据湖的灵活性与数据仓库的成长性?学它! 出品人:阿里云大数据产品及研发团队专家 产品 MaxCompute 官网 https://www.aliyun.com/product/odps 
目录
相关文章
|
4月前
|
SQL 存储 分布式计算
ODPS技术架构深度剖析与实战指南——从零开始掌握阿里巴巴大数据处理平台的核心要义与应用技巧
【10月更文挑战第9天】ODPS是阿里巴巴推出的大数据处理平台,支持海量数据的存储与计算,适用于数据仓库、数据挖掘等场景。其核心组件涵盖数据存储、计算引擎、任务调度、资源管理和用户界面,确保数据处理的稳定、安全与高效。通过创建项目、上传数据、编写SQL或MapReduce程序,用户可轻松完成复杂的数据处理任务。示例展示了如何使用ODPS SQL查询每个用户的最早登录时间。
293 1
|
2月前
|
机器学习/深度学习 人工智能 运维
智能化运维:AI与大数据在IT运维中的应用探索####
本文旨在探讨人工智能(AI)与大数据分析技术如何革新传统IT运维模式,提升运维效率与服务质量。通过具体案例分析,揭示AI算法在故障预测、异常检测及自动化修复等方面的实际应用成效,同时阐述大数据如何助力实现精准运维管理,降低运营成本,提升用户体验。文章还将简要讨论实施智能化运维面临的挑战与未来发展趋势,为IT管理者提供决策参考。 ####
|
3月前
|
机器学习/深度学习 存储 大数据
云计算与大数据技术的融合应用
云计算与大数据技术的融合应用
|
4月前
|
存储 分布式计算 druid
大数据-149 Apache Druid 基本介绍 技术特点 应用场景
大数据-149 Apache Druid 基本介绍 技术特点 应用场景
103 1
大数据-149 Apache Druid 基本介绍 技术特点 应用场景
ly~
|
4月前
|
供应链 搜索推荐 安全
大数据模型的应用
大数据模型在多个领域均有广泛应用。在金融领域,它可用于风险评估与预测、智能营销及反欺诈检测,助力金融机构做出更加精准的决策;在医疗领域,大数据模型能够协助疾病诊断与预测、优化医疗资源管理和加速药物研发;在交通领域,该技术有助于交通流量预测、智能交通管理和物流管理,从而提升整体交通效率;电商领域则借助大数据模型实现商品推荐、库存管理和价格优化,增强用户体验与企业效益;此外,在能源和制造业中,大数据模型的应用范围涵盖从需求预测到设备故障预测等多个方面,全面推动了行业的智能化转型与升级。
ly~
473 2
ly~
|
4月前
|
机器学习/深度学习 人工智能 自然语言处理
大数据在智慧金融中的应用
在智能算法交易中,深度学习揭示价格波动的复杂动力学,强化学习依据市场反馈优化策略,助力投资者获取阿尔法收益。智能监管合规利用自然语言处理精准解读法规,实时追踪监管变化,确保机构紧跟政策。大数据分析监控交易,预警潜在违规行为,变被动防御为主动预防。数智化营销通过多维度数据分析,构建细致客户画像,提供个性化产品推荐。智慧客服借助 AI 技术提升服务质量,增强客户满意度。
ly~
232 3
ly~
|
4月前
|
供应链 搜索推荐 大数据
大数据在零售业中的应用
在零售业中,大数据通过分析顾客的购买记录、在线浏览习惯等数据,帮助零售商理解顾客行为并提供个性化服务。例如,分析网站点击路径以了解顾客兴趣,并利用历史购买数据开发智能推荐系统,提升销售和顾客满意度。此外,大数据还能优化库存管理,通过分析销售数据和市场需求,更准确地预测需求,减少库存积压和缺货现象,提高资金流动性。
ly~
679 2
ly~
|
4月前
|
供应链 监控 搜索推荐
大数据的应用场景
大数据在众多行业中的应用场景广泛,涵盖金融、零售、医疗保健、交通物流、制造、能源、政府公共服务及教育等领域。在金融行业,大数据用于风险评估、精准营销、反欺诈以及决策支持;零售业则应用于商品推荐、供应链管理和门店运营优化等;医疗保健领域利用大数据进行疾病预测、辅助诊断和医疗质量评估;交通物流业通过大数据优化物流配送、交通管理和运输安全;制造业则在生产过程优化、设备维护和供应链协同方面受益;能源行业运用大数据提升智能电网管理和能源勘探效率;政府和公共服务部门借助大数据改善城市管理、政务服务及公共安全;教育行业通过大数据实现个性化学习和资源优化配置;体育娱乐业则利用大数据提升赛事分析和娱乐制作水平。
ly~
1188 2
|
4月前
|
SQL 分布式计算 Java
大数据-96 Spark 集群 SparkSQL Scala编写SQL操作SparkSQL的数据源:JSON、CSV、JDBC、Hive
大数据-96 Spark 集群 SparkSQL Scala编写SQL操作SparkSQL的数据源:JSON、CSV、JDBC、Hive
105 0
|
27天前
|
SQL 数据可视化 大数据
从数据小白到大数据达人:一步步成为数据分析专家
从数据小白到大数据达人:一步步成为数据分析专家
211 92