AI 助理
备案控制台
开发者社区 大数据与机器学习 文章 正文

HQL的中级题目---炸裂函数、窗口函数

2024-01-27 295 发布于河南
版权
举报
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
实时计算 Flink 版，5000CU*H 3个月
推荐场景：
实时发现最热Github项目
智能开放搜索 OpenSearch行业算法版，1GB 20LCU 1个月
推荐场景：
搭建高质量商品搜索服务
实时数仓Hologres，5000CU*H 100GB 3个月
推荐场景：
轻松玩转一站式实时仓库
简介： HQL的中级题目---炸裂函数、窗口函数

一.Hive UDTF之explode函数

image.png

image.png

1.UDTF-----explode(ARRAY a)

select explode(array("a","b","c")) as item;

2.UDTF-----explode(Map m)

select explode(map("a",1,"b",2,"c",3)) as (key,value)

image.png

3.UDTF--posexplode(ARRAY a):返回元素在数组中的索引位置

select posexplode(array("a","b","c")) as (pos,item);

image.png

4.UDTF--inline(ARRAY>a)

select inline(array(named_struct("id",1,"name","zs"),
    named_struct("id",2,"name","sao"),
    named_struct("id",3,"name","ou"))) as (id,name);

image.png

功能介绍

一般情况下，explode函数可以直接单独使用即可； 也可以根据业务需要结合lateral view侧视图一起使用。 explode(array) 将array里的每个元素生成一行； explode(map)   将map里的每一对元素作为一行，其中key为一列，value为一列；

二、Hive Lateral View 侧视图

Lateral View是一种特殊的语法，主要搭配UDTF类型函数一起使用，用于解决UDTF函数的一些查询限制的问题。 一般只要使用UDTF，就会固定搭配lateral view使用。 

将UDTF的结果构建成一个类似于视图的表，然后将原表中的每一行和UDTF函数输出的每一行进行连接，生成一张新的虚拟表。这样就避免了UDTF的使用限制问题。
使用lateral view时也可以对UDTF产生的记录设置字段名称，产生的字段可以用于group by、order by 、limit等语句中，不需要再单独嵌套一层子查询。

image.png

id

name

hobbies

1

zs

[reading,coding]

2

ls

 [coding,running]

变为

id

name

 hobbies

hobby

1

zs

[reading,coding]

reading

1

zs

[reading,coding]

coding

2

ls

[reading,ssss]

reading

2

ls

[reading,ssss]

ssss

--lateral view侧视图基本语法如下 select …… from tabelA lateral view UDTF(xxx) 别名 as col1,col2,col3……; 

select id,name,hobbies,hobby
from person lateral view explode(hobbies) tmp as hobby;

案例演示1

1.数据准备

1）表结构

image.png

2）建表语句

create table movie_info(
    movie string,     --电影名称
    category string   --电影分类
) 
row format delimited fields terminated by "\t";

3）装载语句

insert overwrite table movie_info
values ("《疑犯追踪》", "悬疑,动作,科幻,剧情"),
       ("《Lie to me》", "悬疑,警匪,动作,心理,剧情"),
       ("《战狼2》", "战争,动作,灾难");

2. 需求

image.png

第一步，把category字符串变成数组

select
    movie,
       split(category,",") a
from movie_info;

第二步，因为later view 中的explode字段后面跟上表已经存在的字段，而第一步的是起的别名，所以子查询下

select *from (
             select movie,split(category,",") category
    from movie_info
                 ) t1 lateral view explode(category) tmp as cate;

得到三列十一行的表

image.png

第三步分组聚合

select cate,count(*) from (
             select movie,split(category,",") category
    from movie_info
                 ) t1 lateral view explode(category) tmp as cate
group by cate;

image.png

案例演示2

有一份数据《The_NBA_Championship.txt》，关于部分年份的NBA总冠军球队名单； 第一个字段表示球队名称，第二个字段是获取总冠军的年份； 字段之间以，分割；总冠军年份之间以|进行分割。 需求：使用Hive建表映射成功数据，对数据拆分，要求拆分之后数据如下所示：  

image.png 


create table the_nba_championship(
    team_name string,
    champion_year array<string>

) row format delimited fields terminated by ","
collection items terminated by '|';

image.png

image.png

image.png

执行错误

1.在select条件中，如果只有explode函数表达式，程序执行是没有任何问题的；

2.但是如果在select条件中，包含explode和其他字段，就会报错；

3.如何理解这个错误？为什么在select的时候，explode的旁边不支持其他字段的同时

UDTF's are not supported outside the SELECT clause, nor nested in expressions
不支持在 SELECT 子句之外使用 UDTF，也不支持嵌套在表达式中

image.png

UDTF语法限制

image.png

UDTF语法限制解决

从SQL层面上来说上述问题的解决方案是：对两张表进行join关联查询;

Hive专门提供了语法lateral View侧视图，专门用于搭配explode这样的UDTF函数，以满足上述需要。 

select a.team_name,tmp.year
from the_nba_championship
a lateral view explode(champion_year) tmp as year;

image.png

三、窗口函数（开窗函数）

窗口函数（Window functions）也叫做开窗函数、OLAP函数，其最大特点是：输入值是从SELECT语句的结果集中的一行或多行的“窗口”中获取的。 如果函数具有OVER子句，则它是窗口函数。 窗口函数可以简单地解释为类似于聚合函数的计算函数，但是通过GROUP BY子句组合的常规聚合会隐藏正在聚合的各个行，最终输出一行，窗口函数聚合后还可以访问当中的各个行，并且可以将这些行中的某些属性添加到结果集中。

image.png

image.png

绝大多数的聚合函数都可以配合窗口函数使用，列如max(),min(),sum(),count(),avg()

select order_id,order_date,amount,
sum(amount) over(窗口范围） total_amount
from order_info;

范围有两种：一种是基于行的，一种是基于值的

基于行的：要求每行数据的窗口为上一行到当前行

基于值的：要求每行数据的窗口为值位于当前值-1,到当前值

语法--------窗口---------基于行

sum(amount)over()

row  between                     and

其实：UNBOUNDED PRECEDING 表示从前面的起点，

n PRECEDING：往前n行数据，

CURRENT ROW：当前行

n FOLLOWING：往后n行数据

1.前面两种情况的终点：n PRECEDING：往前n行数据，

CURRENT ROW：当前行

n FOLLOWING：往后n行数据

UNBOUNDED FOLLOWING表示到后面的终点

2.CURRENT ROW的终点：

CURRENT ROW

n FOLLOWING

UNBOUNDED FOLLOWING

3.n FOLLOWING的终点：

n FOLLOWING

UNBOUNDED FOLLOWING

语法--------窗口---------基于值

sum(amount)over()

order by[column] range  between                     and

其实：UNBOUNDED PRECEDING 表示从前面的起点，

n PRECEDING：往前n行数据，

CURRENT ROW：当前行

n FOLLOWING：往后n行数据

1.前面两种情况的终点：n PRECEDING：往前n行数据，

CURRENT ROW：当前行

n FOLLOWING：往后n行数据

UNBOUNDED FOLLOWING表示到后面的终点

2.CURRENT ROW的终点：

CURRENT ROW

n FOLLOWING

UNBOUNDED FOLLOWING

3.n FOLLOWING的终点：

n FOLLOWING

UNBOUNDED FOLLOWING

image.png

order_date<=2022-01-03

image.png

语法------窗口-----分区

image.png

image.png

应用：每个用户截止下单时间总额

image.png

跨行取值函数

（1）lead和lag

image.png

应用：上一次下单时间和上一次下单时间

first_value(order_data,false)是否跳过null

image.png

image.png

RANK() 排序相同时会重复，总数不会变，可以有并列

DENSE_RANK() 排序相同时会重复，总数会减少

ROW_NUMBER() 会根据顺序计算

比如rank 1 1 3

dense_rank 11 2

row_number 1 2 3

注：rank 、dense_rank、row_number不支持自定义窗口。

案例演示

1.数据准备

1）表结构

image.png

2）建表语句

create table order_info
(
    order_id     string, --订单id
    user_id      string, -- 用户id
    user_name    string, -- 用户姓名
    order_date   string, -- 下单日期
    order_amount int     -- 订单金额
);

3）装载语句

insert overwrite table order_info
values ('1', '1001', '小元', '2022-01-01', '10'),
       ('2', '1002', '小海', '2022-01-02', '15'),
       ('3', '1001', '小元', '2022-02-03', '23'),
       ('4', '1002', '小海', '2022-01-04', '29'),
       ('5', '1001', '小元', '2022-01-05', '46'),
       ('6', '1001', '小元', '2022-04-06', '42'),
       ('7', '1002', '小海', '2022-01-07', '50'),
       ('8', '1001', '小元', '2022-01-08', '50'),
       ('9', '1003', '小辉', '2022-04-08', '62'),
       ('10', '1003', '小辉', '2022-04-09', '62'),
       ('11', '1004', '小猛', '2022-05-10', '12'),
       ('12', '1003', '小辉', '2022-04-11', '75'),
       ('13', '1004', '小猛', '2022-06-12', '80'),
       ('14', '1003', '小辉', '2022-04-13', '94');

2. 需求

1）统计每个用户截至每次下单的累积下单总额

select
    order_id,
    user_id,
    user_name,
    order_date,
    order_amount,
    sum(order_amount) over(partition by user_id order by order_date rows between unbounded preceding and current row) sum_so_far
from order_info;

image.png

2）统计每个用户截至每次下单的当月累积下单总额

select
    order_id,
    user_id,
    user_name,
    order_date,
    order_amount,
    sum(order_amount) over(partition by user_id,substring(order_date,1,7) order by order_date rows between unbounded preceding and current row) sum_so_far
from order_info;

image.png

3）统计每个用户每次下单距离上次下单相隔的天数（首次下单按0天算）

select
    order_id,
    user_id,
    user_name,
    order_date,
    order_amount,
    nvl(datediff(order_date,last_order_date),0) diff
from
(
    select
        order_id,
        user_id,
        user_name,
        order_date,
        order_amount,
        lag(order_date,1,null) over(partition by user_id order by order_date) last_order_date
    from order_info
)t1

image.png

4）查询所有下单记录以及每个用户的每个下单记录所在月份的首/末次下单日期

select
    order_id,
    user_id,
    user_name,
    order_date,
    order_amount,
    first_value(order_date) over(partition by user_id,substring(order_date,1,7) order by order_date) first_date,
    last_value(order_date) over(partition by user_id,substring(order_date,1,7) order by order_date rows between unbounded preceding and unbounded following) last_date
from order_info;

image.png

5）为每个用户的所有下单记录按照订单金额进行排名

select
    order_id,
    user_id,
    user_name,
    order_date,
    order_amount,
    rank() over(partition by user_id order by order_amount desc) rk,
    dense_rank() over(partition by user_id order by order_amount desc) drk,
    row_number() over(partition by user_id order by order_amount desc) rn
from order_info;


image.png



     

文章标签：
SQL
HIVE
OLAP
索引
孙玉洁-47170
+关注
93文章3问答
目录
打赏
0
1
2
0
64
分享
相关文章
让线程再跑一会
|
SQL 分布式计算 数据挖掘
Hive SQL初级练习（30题）
Hive SQL初级练习（30题）
让线程再跑一会
1650 0
阿甘兄
|
XML SQL Java
Maven的三种打包方式（jar、shade、assembly）
Maven的三种打包方式（jar、shade、assembly）
阿甘兄
5365 0
游客a4oswvtctqbx6
|
1月前
|
人工智能 JavaScript
生成式人工智能（GAI）认证：2025最值得考的AI证书！
生成式人工智能（GAI）认证由全球教育巨头 Pearson 推出，融合技术原理、实战应用与伦理合规的三维培养框架。该项目与 AI 领域领先企业合作开发，涵盖提示优化、基础提示工程及社会影响等核心内容，助力学习者全面掌握 GAI 技能。中文版认证已落地中国，由达内教育与恒利联创战略合作推广，深度融合本土 AI 平台。作为高含金量的全球认可证书，GAI 认证可提升职业竞争力，满足行业对复合型 AI 人才的需求，为个人和企业开辟数字时代新机遇。
游客a4oswvtctqbx6
303 1
林新育
|
Java Maven
Maven配置阿里云镜像
在setttins.xml文件中找到标签对,进行修改： 1 2 3 nexus-aliyun 4 * 5 Nexus aliyun 6 http://maven.
林新育
93726 0
爱吃糖的范同学
|
SQL Java 数据处理
【Hive】Hive的函数：UDF、UDAF、UDTF的区别？
【4月更文挑战第17天】【Hive】Hive的函数：UDF、UDAF、UDTF的区别？
爱吃糖的范同学
761 6
程序员小海绵
|
10月前
|
设计模式 移动开发 Java
【阿里规约】阿里开发手册解读——代码格式篇
本文所有代码格式规范遵循《阿里规约》，从编码、换行符、空格规则、括号规则、字符数等方面展开，详细阐述方法参数、强制转换、运算符、缩进等元素的编写规范。
程序员小海绵
3742 4
【阿里规约】阿里开发手册解读——代码格式篇
云祁
|
SQL 自然语言处理 关系型数据库
【Hive】函数 concat 、concat_ws 、concat_group 的区别
【Hive】函数 concat 、concat_ws 、concat_group 的区别
云祁
1579 0
孙玉洁-47170
|
SQL HIVE
Hive中日期处理函数的使用（date_format、date_add、date_sub、next_day）
Hive中日期处理函数的使用（date_format、date_add、date_sub、next_day）
孙玉洁-47170
2617 3
嘟嘟嘟嘟嘟嘟
|
12月前
|
分布式计算 DataWorks MaxCompute
DataWorks产品使用合集之怎么更改ODPS表的生命周期为永久
DataWorks作为一站式的数据开发与治理平台，提供了从数据采集、清洗、开发、调度、服务化、质量监控到安全管理的全套解决方案，帮助企业构建高效、规范、安全的大数据处理体系。以下是对DataWorks产品使用合集的概述，涵盖数据处理的各个环节。
嘟嘟嘟嘟嘟嘟
367 4
真的很搞笑
|
分布式计算 DataWorks 关系型数据库
DataWorks产品使用合集之如何进行数据加密解密
DataWorks作为一站式的数据开发与治理平台，提供了从数据采集、清洗、开发、调度、服务化、质量监控到安全管理的全套解决方案，帮助企业构建高效、规范、安全的大数据处理体系。以下是对DataWorks产品使用合集的概述，涵盖数据处理的各个环节。
真的很搞笑
365 2

大数据与机器学习
+关注

热门文章

最新文章

  • 1
    阿里云开源离线同步工具DataX3.0介绍
    69476
  • 2
    DataV首次实战分享：教你30分钟创建汽车大屏
    64836
  • 3
    2017双11狂欢节模板 让大屏“闪电”起来
    48329
  • 4
    【玩转数据系列十】利用阿里云机器学习在深度学习框架下实现智能图片分类
    41149
  • 5
    odps是什么?
    30828
  • 6
    拍立淘-以图搜图中的图像搜索算法
    28722
  • 7
    【玩转数据系列二】机器学习应用没那么难，这次教你玩心脏病预测
    23158
  • 8
    【技术实验】mysql准实时同步数据到Elasticsearch
    21257
  • 9
    数据仓库介绍与实时数仓案例
    20841
  • 10
    MaxCompute执行作业慢的原因排查
    19277
  • 1
    京东拍立淘API-以图搜图中的图像搜索算法
    11
  • 2
    告别低效代码：用对这10个Pandas方法让数据分析效率翻倍
    20
  • 3
    【赵渝强老师】大数据交换引擎Sqoop
    24
  • 4
    【2025更新】视频压缩神器！视频体积瞬间缩小80%，可以指定大小压缩、批量压缩，超级良心免费使用！
    23
  • 5
    AI望远镜：人工智能是如何发现“藏在宇宙角落的新星系”的？
    103
  • 6
    Java 大学期末考试真题与答案 含知识点总结 重难点归纳及题库汇总 Java 期末备考资料
    27
  • 7
    Post-Training on PAI (2)：Ray on PAI，云上一键提交强化学习
    41
  • 8
    官宣 | Fluss 正式加入 Apache 孵化器
    321
  • 9
    革新智能驾驶数据挖掘检索效率！某国内新能源汽车未来出行领导者选择阿里云Milvus构建多模态检索引擎
    47
  • 10
    Spark SQL架构及高级用法
    70

    • 相关电子书

    更多
  • 低代码开发师（初级）实战教程
  • 冬季实战营第三期：MySQL数据库进阶实战
  • 阿里巴巴DevOps 最佳实践手册
    • 下一篇
    就是要你懂负载均衡--lvs和转发模式
    目录
    AI助理

    你好，我是AI助理

    可以解答问题、推荐解决方案等