1. Hive执行流程
(1)Hive select语法
select [all | distinct] select_expr,select_expr,...
from table_reference [join on]
[where where_condition]
[group by col_list]
[order by col_list]
[cluster by col_list | [distribute by col_list] [sort by col_list]]
[limit number]
[union(去重并排序)/union all(不去重不排序)]
//底层走hadoop的读流程或者底层走mapreduce
//union和union all详细一点的区别:
union : 对两个结果集并集操作，不包括重复行(去重)，同时进行默认asc规则的排序
union all : 对两个结果集并集操作，包括重复行，不进行排序(2)基于MapReduce
Map阶段：
①执行from加载(表扫描)，进行表的查找和加载
②执行where过滤，进行条件过滤和筛选
③执行select查询(因为涉及到列裁剪)，进行输出项的筛选
④执行group by分组(因为在Hive中默认提前做了combiner优化)，描述了分组后需要计算的函数
⑤map端文件合并：map端本地溢出写文件的合并操作，每个map最终形成一个临时文件，然后按照列映射到对应的reduce阶段；
Reduce阶段：
①group by对map端发送过来的数据进行分组聚合
②select最后过滤列用于输出结果
③limit排序后结果输出到HDFS文件
(3)查看Hive语句的执行计划
//查看执行计划: explain sql语句，示例(手敲的，因为对我这种理解能力较差的人来说直接复制粘贴的话还是搞不懂，呜呜呜理解这个执行计划花了我好长时间):
-----------------------------------------码: -------------------------------------------
explain select age,count(*) from student group by age;
STAGE DEPENDENCIES:
Stage-1 is a root stage
Stage-0 depends on stages: Stage-1
STAGE PLANS:
Stage: Stage-1
Map Reduce
Map Operator Tree:
TableScan
alias: student
Statistics: Num rows: 1 Data size: 880 Basic stats: COMPLETE Column stats: NONE
Select Operator
expressions: age (type: int)
ouputColumnNames: age
Statistics: Num rows: 1 Data size: 880 Basic stats: COMPLETE Column stats: NONE
Group by Operator
aggregations: count()
keys: age (type: int)
mode: hash
outputColumnNames: _col0, _col1
Statistics: Num rows: 1 Data size: 880 Basic stats: COMPLETE Column stats: NONE
Reduce Output Operator
key expressions: _col0 (type: int)
sort order: +
Map-reduce partition columns: _col0 (type: int)
Statistics: Num rows: 1 Data size: 880 Basic stats: COMPLETE Column stats: NONE
value expressions: _col1 (type: bigint)
Execution mode: vectorized
Reduce Operator Tree:
Group By Operator
aggregations: count(VALUE._col0)
keys: KEY._col0 (type: int)
mode: mergepartial
outputColumnNames: _col0, _col1
Statistics: Num rows: 1 Data size: 880 Basic stats: COMPLETE Column stats: NONE
File Output Operator
compressed: false
Statistics: Num rows: 1 Data size: 880 Basic stats: COMPLETE Column stats: NONE
table:
input format: org.apache.hadoop.mapred.SequenceFileInputFormat
output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
Stage: Stage-0
Fetch Operator
limit: -1
Processor Tree:
ListSink
-----------------------------------------解释: ------------------------------------------
大概:
Stage-1完成了按照age分组聚合count(*)，因为走的是MapReduce，所以得到的结果写入到HDFS
Stage-0进行了(从HDFS)拉取数据的操作Fetch Operator，将拉取的数据打印到屏幕上，limit为-1不限行数
详细:
Map Operator Tree: map端第一个操作是加载表，即TableScan表扫描，常见属性有:
alias: 表名称
Statistics: 表统计信息，包含表中数据条数，数据大小等
Select Operator: 选取操作，常见属性:
expressions: 需要的字段名称及字段类型
outputColumnNames: 输出的列名称
Statistics: 表统计信息，包含表中数据条数，数据大小等
Group By Operator: 分组聚合操作，常见属性:
aggregations: 显示聚合函数信息
mode: 聚合模式，值有hash: 随机聚合(hash partition)；partial: 局部聚合；final: 最终聚合
keys: 分组字段，如果没有分组则没有此字段
outputColumnNames: 聚合之后输出列名
Statistics: 表统计信息，包含分组聚合之后的数据条数，数据大小等
Reduce Output Operator: 输出到reduce操作，常见属性:
sort order: 值为空不排序，值为+正序排序，值为-倒序排序；值为+-排序的列为两列，第一列为正序排序，第二列倒序排序
Filter Operator: 过滤操作，常见属性:
predicate: 过滤条件，如sql语句中where id>=2，则此处显示(id>=2)
Map Join Operator: join操作，常见属性:
condition map: join方式，如Inner Join 0 to 1 / Left Outer Join 0 to 2
keys: join的条件字段
outputColumnNames: join完成之后输出的字段
Statistics: join完成之后生成的数据条数，大小等
File Output Operator: 文件输出操作，常见属性:
compressed: 是否压缩
table: 表的信息，包含输入输出文件格式化方式，序列化方式等
Fetch Operator客户端获取数据操作(拉取数据)，常见属性:
limit，值为-1表示不限制条数，其他值为限制条数2. Hive Join
hive执行引擎会将HQL翻译成map-reduce任务，如果多张表使用同一列(同一个字段)做join则将被翻译成一个reduce，否则会被翻译成多个map-reduce任务。
(1)两表join
①等值连接:
- 内连接——inner join（相交的部分，相当于join）
-- 创建表test_a、test_b、test_c
create table test_a(id int, name string) row format delimited fields terminated by '\t';
create table test_b(id int, name string) row format delimited fields terminated by '\t';
create table test_c(id int, name string) row format delimited fields terminated by '\t';
--vi test_a
1 a1
2 a2
4 a4
--vi test_b
1 b1
3 b3
4 b4
--vi test_c
1 c1
4 c4
5 c5
--分别导入数据到三个表中
load data local inpath '/home/hadoop/test_a' into table test_a;
load data local inpath '/home/hadoop/test_b' into table test_b;
load data local inpath '/home/hadoop/test_c' into table test_c;
--注: 在Hive中默认做了map join的优化，不走reduce
select * from test_a a join test_b b on a.id=b.id;
--结果:
1 a1 1 b1
4 a4 4 b4
--map join的思路: 一般是大表和小表进行join，会将小表提前加载到Map Task执行的节点，从节点把小表的数据读到内存，和大表进行join，但是读的时候只会读大表(因为小表已经提前加载到内存中)，这样就不会有shuffle，就会提高执行的效率。
--这里通过查看这条语句的执行计划可以得知: 读取的是test_b，而test_a提前加载到程序运行的线程所在的节点，提前把数据读到了内存中，然后读取test_b中的数据，和内存中的数据进行join，输出，没有reduce的操作。
--关闭mapjoin的话，执行下面命令，会有reduce操作 则join发生在reduce阶段
set hive.auto.convert.join=false;
set hive.ignore.mapjoin.hint=false;- 左外连接——left join(等价于left outer join)
在内连接的基础上，拿到左边表有而右边表没有的数据，
select * from test_a a left outer join test_b b on a.id=b.id;
--结果
1 a1 1 b1
2 a2 NULL NULL
4 a4 4 b4- 右外连接——right join(等价于right outer join)
在内连接的基础上，拿到右边表有而左边表没有的数据，
select * from test_a a right join test_b b on a.id=b.id;
--结果
1 a1 1 b1
NULL NULL 3 b3
4 a4 4 b4②非等值连接(也相当于半球连接semi join)
- 查询test_a有，test_b没有的，用left join + is null
--在join后的结果的基础上筛选b.id is null的
select * from test_a a left join test_b b on a.id=b.id where b.id is null;
--结果
2 a2 NULL NULL- 查询test_a没有，test_b有的，用right join + is null
--在join后的结果的基础上筛选a.id is null的
select * from test_a a right join test_b b on a.id=b.id where a.id is null;
--结果
NULL NULL 3 b3
(2)多表join
如果join的字段相同，只生成一个job任务
如果join的字段不同，会生成多个job任务
练习:
(3)要避免的查询操作
--笛卡尔积的sql(两张表中的每条记录都进行连接)
select * from test_a inner join test_b;
select * from test_a,test_b;解决方式:
# 设置严格模式
set hive.mapred.mode=strict;
# 严格模式下:
# ①不允许笛卡尔积查询
# ②不允许分区表查询的时候不加分区
# ③不允许在进行排序order by的时候不加limit
# 取消严格模式set hive.mapred.mode=nonstrict(4) 全外连接——full outer join(相当于full join，mysql中不支持，mysql实现需要左外连接left join union right join右外连接)
--做test_a和test_b的full outer join
select a.*,b.* from test_a a full outer join test_b b on a.id=b.id;
--结果
1 a1 1 b1
2 a2 NULL NULL
NULL NULL 3 b3
4 a4 4 b4
--注(如果使用的是左外连接union右外连接，有坑，hive的高低版本都有):
select * from test_a a left join test_b b on a.id=b.id
union
select * from test_a a right join test_b b on a.id=b.id;
--结果:
NULL NULL
1 a1
2 a2
4 a4
这里的*表示: a.id,a.name,b.id,b.name 合并的时候会把a.id和b.id看成一个字段id，a.name和b.name看成一个字段name
--解决方式: 给a.id,a.name,b.id,b.name分别起别名且不能相同: a.id aid, a.name aname, b.id bid, b.name bname
结果:
NULL NULL 3 b3
1 a1 1 b1
2 a2 NULL NULL
4 a4 4 b4(5)map端的join(hive默认开启)
- 在map端进行join，没有reduce，没有shuffle，数据就不用频繁的溢写磁盘，reduce就不用去磁盘中拉取数据，提高join的执行效率
- 更适合于大表和小表join，map在读取数据的时候只读取大表中的数据，小表中的数据提前缓存到map task所在的节点
①maptask启动到了s1,s2,s3三个节点，三个节点分别都有大表的block块，会在block块所在节点上启动maptask，数据本地化
②maptask在执行读取大表数据的时候，小表数据提前缓存到maptask所在节点
③maptask运行的时候：会把小表中的数据读取到内存中，然后maptask会读取block块中大表的数据，读完后和小表中的内存中的数据join，最后对外输出
这里补充一下task和job的区别(基础有点差想到这里就不太懂task和job，浅浅的检索了一下):
task更多侧重运行状态，而job则是一个目标，侧重于完成状态，一个job通常包括多个task；
task:
- windows下的任务管理器，管理系统的进程
- linux的进程表结构task_struct，记录进程的信息
- Java等编程语言中的各种Task类，偏向描述执行过程
job:
- 操作系统中Job，作业(Job)是计算机操作者(或是一个叫做作业调度器的程序)交给操作系统的执行单位
- Hadoop中的Job，和操作系统的job概念类似，是用户编写的一个程序，读取输入数据完成计算之后输出结果
- quartz中的Job，org.quartz.Job类，类似于一个Runner，包含execute方法，另外数据库的一行记录也叫job
总结:
①job和task是近义词，侧重点不同，很多场景中可以互换
②task常用于多任务系统，task之间存在资源竞争，job则常常用于批处理系统，表示用户的一组任务
③task通常表示一个过程(进程)，job通常表示用户提交的一个任务(目标)，期望明确的结果，job可以包含多个task，可以认为task是一个JobHandler
④在操作系统领域，job通常指后台批处理任务，而task则指用户正在使用的一个进程
详情参考: 计算机领域中Task和Job的区别_task job-CSDN博客
示例：大表: user_install_status_other 小表: country_dict
制作字典文件vi country.dict
--创建表
create table country_dict(
code string,
name string,
region string
)row format delimited fields terminated by '\t';
--加载数据
load data local inpath '/home/hadoop/country.dict' into table country_dict;
--开启mapjoin，默认开启的
--刷入内存表的大小(字节)，根据自己的数据集加大
set hive.mapjoin.smalltable.filesize=2500000;
--设置太大也不会校验，因此要根据实际情况设置
set hive.mapjoin.smalltable.filesize=250000000000000000000;
--大表join小表，一般满足左大右小
select * from user_install_status_other u join country_dict c on u.country=c.code;
3. Hive group by
--创建student_grouping表
create table student_grouping(
id int,
name string,
age int,
sex string
)row format delimited fields terminated by '\t';
--给表导入数据
load data local inpath '/home/hadoop/student' into table student_grouping;
1 name1 12 boy
2 name2 12 boy
3 name3 13 girl
4 name4 13 boy
5 name5 14 boy
6 name6 14 boy
7 name7 15 girl
8 name8 15 girl
--查询总记录数
select count(*) from student_grouping;
--按照年龄分组，统计记录数
select age,count(*) from student_grouping group by age;
--按照性别分组，统计记录数
select sex,count(*) from student_grouping group by sex;
--按照年龄、性别分组，统计记录数
select age,sex,count(*) from student_grouping group by age,sex;
--若想放在一张结果表中，即一条sql查询出上面四种查询结果，则(union字段必须写全):
create table jieguo as
select null as age, null as sex, count(*) from student_grouping
union
select age, null as sex, count(*) from student_grouping group by age
union
select null as age, sex, count(*) from student_grouping group by sex
union
select age, sex, count(*) from student_grouping group by age,sex;
--结果:
NULL NULL 8
NULL boy 5
NULL girl 3
12 NULL 2
12 boy 2
13 NULL 2
13 boy 1
13 girl 1
14 NULL 2
14 boy 2
15 NULL 2
15 girl 2
--优化: 因为union的四条语句在执行的时候没有相互的联系，所以可以设置参数让job任务并行运行(job和job之间没有依赖关系问题，就可以让没有依赖的job同时运行)，在同一个sql中的不同job是否可以同时运行，默认为false
set hive.exec.parallel=true
--一共多少个学生、按性别统计的结果、按年龄统计的结果、每个年龄对应的性别的结果
select num from jieguo where age is null and sex is null;
select sex,num from jieguo where age is null and sex is not null;
select age,num from jieguo where sex is null and age is not null;
select age,sex,num from jieguo where age is not null and sex is not null;
--需求: 按照不同维度统计相关记录，即随机组合的结果(A B C D AB AC AD BC BD CD ABC ABD ACD BCD ABCD)
--就不推荐使用union了，可以使用grouping sets(1)grouping sets是一种将多个group by逻辑写在一个sql语句中的遍历写法(且只会生成一个job)，示例:
--示例一: 按年龄、性别分组
select age,sex,count(*) from student_grouping group by age,sex;
等价于
select age,sex,count(*) from student_grouping group by age,sex grouping sets ((age,sex));
--示例二: 按年龄分组、按年龄和性别分组
select age,null as sex,count(*) from student_grouping group by age
union
select age,sex,count(*) from student_grouping group by age,sex;
等价于
select age,sex,count(*) from student_grouping group by age,sex grouping sets ((age,sex),age);
--示例三: 按年龄分组、按性别分组、按年龄和性别分组、总记录数
create table jieguo2 as
select age,sex,count(*) from student_grouping group by age,sex
union
select age, null as sex, count(*) from student_grouping group by age
union
select null as age,sex,count(*) from student_grouping group by sex
union
select null as age,null as sex,count(*) from student_grouping
等价于
create table jieguo2 as select age,sex,count(*) from student_grouping group by age,sex grouping sets ((age,sex), age,sex,());
--示例:
create table jieguo3 as select age,sex,count(*) from student_grouping group by age,sex grouping sets (age,sex);
(2)with cube是group by中所有key的组合(即随机组合的所有情况)
create table jieguo4 as
select age,sex,count(*) from student_grouping group by age,sex with cube;
等价于
create table jieguo4 as
select age,sex,count(*) from student_grouping group by age,sex grouping sets ((age,sex),age,sex,());
等价于
create table jieguo4 as
select age,sex,count(*) from student_grouping group by age,sex
union
select age,null as sex,count(*) from student_grouping group by age
union
select null as age,sex,count(*) from student_grouping group by sex
union
select null as age, null as sex, count(*) from student_grouping;(3)with rollup是按右侧递减的顺序组合
--group by A,B,C,D grouping sets ((ABCD),(ABC),(AB),(A),())
--等价于
--group by A,B,C,D with rollup
select age,sex,count(*) from student_grouping group by age,sex with rollup;
等价于
select age,sex,count(*) from student_grouping group by age,sex grouping sets ((age,sex),age,());
4.Hive 排序
(1) order by :
- 会对数据做全局排序，因此只有一个reducer，设置reduce个数没用；
- 在hive.mapred.mode=strict模式下，必须指定limit，否则执行报错
-- 设置reduce数量为2，set mapred.reduce.tasks=2;
-- 对更新时间降序排序查询10条数据，数据导出到linux本地，
insert overwrite local directory '/home/hadoop/export_order' select * from user_install_status_limit order by uptime desc limit 10;
-- 设置了reduce但是用的是order by排序，因此结果只会生成1个reducer，只有1个文件
(2) sort by :
- 非全局排序，如果reducer数量设置为2，即有两个reduce task，数据再进入reduce之前会进行排序，即每个reduce会输出一个文件，文件中会进行排序，但只是局部有序；如果指定reducer数量为1，则和order by的作用是相同的
(3) distribute by :
- 类似于分桶，就是把相同的key分到同一个reduce中，根据distribute by指定的字段对数据进行划分到不同的输出reduce文件中。
- 常和sort by一起使用，且distribute by必须在sort by前面
(4) cluster by :
cluster by 字段相当于distribute by 字段 + sort by 字段 (同一个字段)，只能默认升序asc，不能指定排序规则为升序asc或者倒序desc
select * from user_install_status_limit cluster by country;
等价于
select * from user_install_status_limit distribute by country sort by country;5.窗口函数
- Hive中的窗口函数和sql中的窗口函数相类似，都是用来做数据分析类工作，一般用于OLAP(联机分析处理)
- 聚合函数(如sum()、avg()、max())是针对多行数据经过计算返回一个值
- 窗口函数也是针对多行数据经过计算，但最后是每行返回一个值，例如既要显示聚集前的数据又要显示聚集后的数据
- 窗口查询有两个步骤: 将记录分割成多个分区，然后在各个分区上调用窗口函数
- 注: 在sql处理中，窗口函数都是最后一步执行，且仅位于order by字句之前
- 语法: 主要是over( partition by (根据某条件分组，形成一个小组)....order by(在组内进行排序))
(1)聚合函数+over
语法:
over(order by col1) --按照col1排序
over(partition by col1) --按照col1分区
over(partition by col1 order by col2) --按照col1分区，col2排序示例:
--建表
create table wt1(
id int,
name string,
age int
)
row format delimited fields terminated by '\t';
--wt1 表数据
id name age
1 a1 10
2 a2 10
3 a3 10
4 a4 20
5 a5 20
6 a6 20
7 a7 20
8 a8 30
--load data local inpath '/home/hadoop/wt1' into table wt1;
--窗口函数①
select id,name,age,count(*) over(order by age) as n from wt1;
--结果
3 a3 10 3
2 a2 10 3
1 a1 10 3
7 a7 20 7
6 a6 20 7
5 a5 20 7
4 a4 20 7
8 a8 30 8
--分析
3 3 3 3+4 3+4 3+4 3+4 7+1
--窗口函数②
select id,name,age,count(*) over(partition by age order by age) as n from wt1;
--结果
3 a3 10 3
2 a2 10 3
1 a1 10 3
7 a7 20 4
6 a6 20 4
5 a5 20 4
4 a4 20 4
8 a8 30 1
--分析
先按age进行分区，三个区，分别进行count(*)
--窗口函数③
select id,name,age,count(*) over(partition by age order by id desc) as n from wt1;
--结果
3 a3 10 1
2 a2 10 2
1 a1 10 3
7 a7 20 1
6 a6 20 2
5 a5 20 3
4 a4 20 4
8 a8 30 1
--order by是谁就会执行相应计算，然后给每一行输出count(*)的值
(4)window函数
- 如果只使用partition by子句，未指定order by的话，聚合是分组内的聚合
- 使用了order by子句，未使用window子句的情况下，默认是从起点到当前行。
- 当同一个select查询中存在多个窗口函数时，他们相互之间是没有影响的，每个窗口函数应用自己的规则。
①语法、rows窗口函数中的行选择器
--1.带有窗口范围
over(partition by col1 order by col2 rows 窗口范围)
--在窗口范围内，按照col1分区，按照col2排序
--2.语法
rows between
[ n | unbounded preceding ] | [ n | unbounded following ] | [ current row ]
and
[ n | unbounded preceding ] | [ n | unbounded following ] | [ current row ]
--3.参数解释:
n行数
unbounded不限行数，无限行
preceding在前n行
following在后N行
current row当前行
--示例:
--前无限行到当前行
row between unbounded preceding and current row
--前2行到当前行
row between 2 preceding and current row
--当前行到后2行
row between current row and 2 following
--前无限行到后无限行
row between unbounded preceding and unbounded following②窗口函数主要分为3类: 聚合窗口函数、排序窗口函数、偏移窗口函数
聚合窗口函数包括avg，sum，count，max，min等
排序窗口函数包括row_number、rank、dense_rank；
偏移窗口函数包括lag、lead
③窗口函数的作用
在不减少原表行数的情况下进行分组排序等计算，主要应用场景有
- 组内比较问题
- 组内topN问题
- 累计求和问题
- 连续登录、活跃N天问题
- 连续出现N次问题
案例太多，改天再深究，预知后续，请看这位大佬如何讲解:
要特别详细的话，可以看看这位大佬:
最全的SQL窗口函数介绍及使用 - 知乎 (zhihu.com)
(2)窗口函数中的序列函数
row_number : 会对所有数值，输出不同的序号，序号唯一且连续，如 : 1、2、3、4、5。
rank : 会对相同数值，输出相同的序号，且下一个序号间断，如 : 1、1、3、3、5。
dense_rank : 会对相同数值，输出相同的序号，但下一个序号不间断，如 : 1、1、2、2、3。
(3)三个函数的结果对比
--按性别分组，再按照年龄降序排序
--打印头信息(字段)
set hive.cli.print.header=true;
--查看表
select * from student_grouping;
student_grouping.id student_grouping.name student_grouping.age student_grouping.sex
1 name1 12 boy
2 name2 12 boy
3 name3 13 girl
4 name4 13 boy
5 name5 14 boy
6 name6 14 boy
7 name7 15 girl
8 name8 15 girl
--序列函数
select id,name,age,sex,
row_number() over(partition by sex order by age desc) as rn1,
rank() over(partition by sex order by age desc) as rk1,
dense_rank() over(partition by sex order by age desc) as rk2
from student_grouping;
--结果
id name age sex rn1 rk1 rk2
6 name6 14 boy 1 1 1
5 name5 14 boy 2 1 1
4 name4 13 boy 3 3 2
2 name2 12 boy 4 4 3
1 name1 12 boy 5 4 3
8 name8 15 girl 1 1 1
7 name7 15 girl 2 1 1
3 name3 13 girl 3 3 2
案例 :
name score sex
n1 60 boy
n2 80 girl
n3 90 boy
n4 70 girl
①统计每个学生成绩排名
name score 排名
n1 60 4
n2 80 2
n3 90 1
n4 70 3
②按照性别分组，统计每个学生成绩排名
name score 排名
n1 60 2
n3 90 1
n2 80 1
n4 70 2实现 :
create table score(name string, score int, sex string) row format delimited fields terminated by ' ';
n1 60 boy
n2 80 girl
n3 90 boy
n4 70 girl
load data local inpath '/home/hadoop/score' into table score;
--按成绩降序排列
select name, score, sex, row_number() over (order by score desc) as paiming from score;
name score sex paiming
n3 90 boy 1
n2 80 girl 2
n4 70 girl 3
n1 60 boy 4
--按性别分，按成绩降序排列
select name, score, sex, row_number() over (partition by sex order by score desc) as paiming from score;
name score sex paiming
n3 90 boy 1
n1 60 boy 2
n2 80 girl 1
n4 70 girl 2
--想选出男生和女生里面的第一名
select * from (select name, score, sex, row_number() over (partition by sex order by score desc) as paiming from score) t where t.paiming = 1;
t.name t.score t.sex t.paiming
n3 90 boy 1
n2 80 girl 1
如果重写数据，使用有重复成绩的数据:
load data local inpath '/home/hadoop/score' overwrite into table score;
--数据
score.name score.score score.sex
n1 60 boy
n2 80 girl
n3 90 boy
n4 70 girl
n5 90 boy
n6 90 boy
n7 89 boy
n8 77 girl
n9 80 girl
--按成绩降序排序输出前三名
select * from (select name, score, sex, dense_rank() over (order by score desc) as paiming from score) t where t.paiming <= 3;
t.name t.score t.sex t.paiming
n3 90 boy 1
n5 90 boy 1
n6 90 boy 1
n7 89 boy 2
n2 80 girl 3
n9 80 girl 3
over中partition by和distribute by区别 :
- partition by [key..] order by [key..]只能在窗口函数中使用，而distribute by [key..] sort by [key..]在窗口函数和select中都可以使用
- 窗口函数中两者是没有区别的
(4)窗口函数中的取值函数lag和lead、first_value和last_value
1.lead获取分组中往前n行的值
语法:
lead(field,n,default_value) over()
解析:
field是指定的列名，n是往前的行数，行往前导致的最后的m行值为null，可以用default_value代替
2.lag获取分组中往后n行的值
语法:
lag(field,n,default_value) over()
3.first_value获取分组内第一行的值
语法: first_value(col, true/false) over()
作用: 取分组内排序后，截止到当前行，第一个值。
注意:
当第二个参数为true的时候，会跳过空值
当over()中不指定排序的时候，会默认使用表中数据的原排序
4.last_value获取分组内最后一行
语法: last_value(col,true/false) over()
作用: 取分组内排序后，截止到当前行，最后一个值。
如果使用order by排序的时候，想要取最后一个值，需要与rows between unbounded preceding and unbounded following连用后续再添，问题: 不理解窗口函数、分析函数、开窗函数之间的联系
扫一扫
5万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
