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

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

2024-01-27 705
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介： 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
目录
相关文章
大数据启示录
|
SQL 存储 数据采集
数据中台建设方法论
数据中台建设方法论
大数据启示录
3167 1
热烈的马
|
SQL 存储 分布式计算
【大数据技术Hadoop+Spark】Hive数据仓库架构、优缺点、数据模型介绍（图文解释 超详细）
【大数据技术Hadoop+Spark】Hive数据仓库架构、优缺点、数据模型介绍（图文解释 超详细）
热烈的马
1994 0
让线程再跑一会
|
SQL HIVE 索引
Hive【Hive（五）函数-高级聚合函数、炸裂函数】
Hive【Hive（五）函数-高级聚合函数、炸裂函数】
让线程再跑一会
1134 0
游客7wkr3y7oxyt7a
|
7月前
|
安全 关系型数据库 数据库
数据仓库是什么，一文读懂数据仓库设计步骤
数据仓库是企业整合、存储和分析历史数据的核心工具，支持决策与趋势预测。设计需经历明确业务需求、梳理数据源、概念建模、逻辑设计、物理实现及测试维护等步骤。通过合理规划结构、安全机制与数据集成（如使用FineDataLink），可有效提升数据质量与分析效率，助力企业发挥数据价值。
游客7wkr3y7oxyt7a
1441 3
顾翔
|
SQL JavaScript 前端开发
Hive学习-lateral view 、explode、reflect和窗口函数
Hive学习-lateral view 、explode、reflect和窗口函数
顾翔
864 4
Z_sorrain
|
SQL 数据处理 HIVE
HIVE的数据倾斜调优
hive数据倾斜主要是由shuffle引起的，而引起shuffle的又主要有四种情况，分别为： 1.group by 2.join 3.count(distinct) 4.开窗函数
Z_sorrain
728 8
运营研究坊
|
Python
探索 Python 中链表的实现：从基础到高级
链表是一种由节点组成的基础数据结构，每个节点包含数据和指向下一个节点的引用。本文通过Python类实现单向链表，详细介绍了创建、插入、删除节点等操作，并提供示例代码帮助理解。链表在处理动态数据时具有高效性，适用于大量数据变动的场景。文章为初学者提供了全面的入门指南，助你掌握链表的核心概念与应用。
运营研究坊
673 0
Deephub
|
存储 人工智能 搜索推荐
RAG系统的7个检索指标：信息检索任务准确性评估指南
大型语言模型（LLMs）在生成式AI领域备受关注，但其知识局限性和幻觉问题仍具挑战。检索增强生成（RAG）通过引入外部知识和上下文，有效解决了这些问题，并成为2024年最具影响力的AI技术之一。RAG评估需超越简单的实现方式，建立有效的性能度量标准。本文重点讨论了七个核心检索指标，包括准确率、精确率、召回率、F1分数、平均倒数排名（MRR）、平均精确率均值（MAP）和归一化折损累积增益（nDCG），为评估和优化RAG系统提供了重要依据。这些指标不仅在RAG中发挥作用，还广泛应用于搜索引擎、电子商务、推荐系统等领域。
Deephub
7251 2
RAG系统的7个检索指标：信息检索任务准确性评估指南
Byyyi
|
SQL HIVE
Hive 行列转换
使用`lateral view + explode`或`inline`可将列转换为行，实现数据降维。例如，`explode(array|map)`用于单列转多行，`inline(array_struct)`将结构体数组拆分成多行。同样，通过条件聚合可实现行转列，常用于多行数据聚合到单行中，如示例所示的按月统计订单金额。
Byyyi
735 1
Hive 行列转换
云课程笔记
|
SQL 移动开发 开发工具
Hive 高阶--分组窗口函数--取值分组函数（ LAG,LEAD,FIRST_VALUE,LAST_VALUE）|学习笔记
快速学习 Hive 高阶--分组窗口函数--取值分组函数（ LAG,LEAD,FIRST_VALUE,LAST_VALUE）
云课程笔记
1118 0

大数据与机器学习

热门文章

最新文章

  • 1
    身怀绝技的开发者们，快来DataV玩转可视化组件
  • 2
    【玩转数据系列十】利用阿里云机器学习在深度学习框架下实现智能图片分类
  • 3
    你刚吃的兰州牛肉面，背后就藏着大数据
  • 4
    odps是什么?
  • 5
    数据仓库介绍与实时数仓案例
  • 6
    分布式快照算法: Chandy-Lamport
  • 7
    MaxCompute执行作业慢的原因排查
  • 8
    阿里云MaxCompute（大数据）公开数据集---带你玩转人工智能
  • 9
    优酷背后的大数据秘密
  • 10
    Kibana：数据分析的可视化利器
  • 1
    别让烂数据毁了你的AI！一份人人能懂的数据集入门与避坑指南
    6
  • 2
    为什么显存总是不够：不是模型的问题
    9
  • 3
    让ChatGPT更懂你：深入浅出解析大模型微调中的强化学习（PPO/DPO篇）
    23
  • 4
    大模型太“通用”？一文搞懂模型微调：从原理到实战，让AI听懂你的行话
    24
  • 5
    一个项目能长期活下去，靠的从来不是模型
    88
  • 6
    Nginx 反向代理：原理、优势与配置指南
    23
  • 7
    京东商品评论API（jd.item_review）开发指南
    24
  • 8
    当客服系统开始稳定运行，模型往往已经退居二线
    27
  • 9
    智能体来了：从0到1教你三步构建属于你的 AI 数字分身
    44
  • 10
    AGI 的临界点：机器究竟什么时候，才算“真的理解了这个世界”？
    33

    • 相关电子书

    更多
  • 低代码开发师（初级）实战教程
  • 冬季实战营第三期：MySQL数据库进阶实战
  • 阿里巴巴DevOps 最佳实践手册
    • 下一篇
    第五届伏魔挑战赛如约来袭，诚邀各路高手来战！