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

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

2024-01-27 198
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
智能开放搜索 OpenSearch行业算法版，1GB 20LCU 1个月
推荐场景：
搭建高质量商品搜索服务
检索分析服务 Elasticsearch 版，2核4GB开发者规格 1个月
推荐场景：
数据可视化分析航班信息
实时计算 Flink 版，5000CU*H 3个月
推荐场景：
实时发现最热Github项目
简介： 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
目录
相关文章
数据分析螺丝钉
|
7月前
|
SQL 算法 大数据
深入解析力扣176题：第二高的薪水（子查询与LIMIT详解及模拟面试问答）
深入解析力扣176题：第二高的薪水（子查询与LIMIT详解及模拟面试问答）
数据分析螺丝钉
54 1
数据分析螺丝钉
|
7月前
|
SQL 算法 大数据
深入解析力扣177题：第N高的薪水（SQL子查询与LIMIT详解及模拟面试问答）
深入解析力扣177题：第N高的薪水（SQL子查询与LIMIT详解及模拟面试问答）
数据分析螺丝钉
68 0
星光下的赶路人
|
SQL HIVE
Hive学习---4、函数（单行函数、高级聚合函数、炸裂函数、窗口函数）（二）
Hive学习---4、函数（单行函数、高级聚合函数、炸裂函数、窗口函数）（二）
星光下的赶路人
252 0
星光下的赶路人
|
SQL JSON Java
Hive学习---4、函数（单行函数、高级聚合函数、炸裂函数、窗口函数）（一）
Hive学习---4、函数（单行函数、高级聚合函数、炸裂函数、窗口函数）（一）
星光下的赶路人
255 0
赵延东的一亩三分地
|
SQL BI
【SQL开发实战技巧】系列（二十七）：数仓报表场景☞通过对移动范围进行聚集来详解分析函数开窗原理以及如何一个SQL打印九九乘法表
本篇文章讲解的主要内容是：***通过执行计划看开窗函数开窗语法rows\range between preceding and current row以及rows\range between unbounded preceding and unbounded following对移动范围的值进行聚集的原理以及区别】、如何通过一个SQL打印九九乘法口表！！！***
赵延东的一亩三分地
330 1
【SQL开发实战技巧】系列（二十七）：数仓报表场景☞通过对移动范围进行聚集来详解分析函数开窗原理以及如何一个SQL打印九九乘法表
赵延东的一亩三分地
|
SQL Oracle 关系型数据库
【SQL开发实战技巧】系列（十三）：讨论一下常用聚集函数&通过执行计划看sum()over()对员工工资进行累加
本篇文章讲解的主要内容是：***常用聚集函数及group by与空值的影响、详解通过执行计划看sum()over()分析函数。***
赵延东的一亩三分地
185 0
【SQL开发实战技巧】系列（十三）：讨论一下常用聚集函数&通过执行计划看sum()over()对员工工资进行累加
兴趣使然黄小黄
|
关系型数据库 MySQL
【MySQL】数据库函数通关教程上篇（聚合、数学、字符串、日期、控制流函数）（下）
文章目录 写在前面 1 聚合函数 1.1 GROUP_CONCAT() 1.2 其他聚合函数 2 数学函数 3 字符串函数 4 日期函数 4.1 常见日期函数与使用 4.2 日期格式 5 控制流函数 5.1 if逻辑判断语句 5.2 case when语句
兴趣使然黄小黄
149 0
【MySQL】数据库函数通关教程上篇（聚合、数学、字符串、日期、控制流函数）（下）
兴趣使然黄小黄
|
关系型数据库 MySQL
【MySQL】数据库函数通关教程上篇（聚合、数学、字符串、日期、控制流函数）（上）
文章目录 写在前面 1 聚合函数 1.1 GROUP_CONCAT() 1.2 其他聚合函数 2 数学函数 3 字符串函数 4 日期函数 4.1 常见日期函数与使用 4.2 日期格式 5 控制流函数 5.1 if逻辑判断语句 5.2 case when语句
兴趣使然黄小黄
251 0
【MySQL】数据库函数通关教程上篇（聚合、数学、字符串、日期、控制流函数）（上）
游客k2gw4fr7y7zie
|
SQL
sql查询练习题（上）
sql查询练习题
游客k2gw4fr7y7zie
618 0
游客k2gw4fr7y7zie
|
SQL
sql查询练习题（下）
sql查询练习题
游客k2gw4fr7y7zie
551 0

大数据与机器学习

热门文章

最新文章

  • 1
    阿里云开源离线同步工具DataX3.0介绍
  • 2
    DataV首次实战分享：教你30分钟创建汽车大屏
  • 3
    2017双11狂欢节模板 让大屏“闪电”起来
  • 4
    【玩转数据系列九】机器学习为您解密雾霾形成原因
  • 5
    DataV回调id——从入门到精通
  • 6
    盘古：阿里云飞天分布式存储系统设计深度解析
  • 7
    odps是什么?
  • 8
    MaxCompute执行作业慢的原因排查
  • 9
    阿里云MaxCompute（大数据）公开数据集---带你玩转人工智能
  • 10
    空格App亿元A轮融资背后：云上多场景技术架构实践与经验
  • 1
    基于龙格库塔算法的锅炉单相受热管建模与matlab数值仿真
    4
  • 2
    分布式Python计算服务MaxFrame测评
    32
  • 3
    Hologres 查询队列全面解析
    13
  • 4
    构建电商数据采集系统初定位
    17
  • 5
    反向海淘中下单、支付方式、订单、库存管理、物流与配送
    22
  • 6
    机器学习领域必知数学符号与概念（一）
    88
  • 7
    AI在交通管理系统中的应用
    31
  • 8
    PyTorch FlexAttention技术实践：基于BlockMask实现因果注意力与变长序列处理
    34
  • 9
    深入解析 Hologres Table Group 与 Shard Count
    81
  • 10
    通过ROS低代码CADT无代码和可视化能力管理云上基础设施
    40

    • 相关电子书

    更多
  • 低代码开发师（初级）实战教程
  • 冬季实战营第三期：MySQL数据库进阶实战
  • 阿里巴巴DevOps 最佳实践手册

    • 相关实验场景

    更多
  • SQL进阶之子句、关键字和操作符
    • 下一篇
    阿里云无影云电脑免费试用，最长可试用3个月