hive知识汇总

一、hive架构相关

1、hive组件
**【用户接口&＃xff1a;】**包括 CLI、JDBC/ODBC、WebGUI。其中&＃xff0c;CLI(command line interface)为shell命令行&＃xff1b;Hive中的Thrift服务器允许外部客户端通过网络与Hive进行交互&＃xff0c;类似于JDBC或ODBC协议。WebGUI是通过浏览器访问Hive。

**【元数据存储&＃xff1a;】**通常是存储在关系数据库如 mysql/derby中。Hive 中的元数据包括表的名字&＃xff0c;表的列和分区及其属性&＃xff0c;表的属性&＃xff08;是否为外部表等&＃xff09;&＃xff0c;表的数据所在目录等。

【Driver驱动程序】&＃xff0c;包括语法解析器、计划编译器、优化器、执行器:完成 HQL 查询语句从词法分析、语法分析、编译、优化以及查询计划的生成。生成的查询计划存储在 HDFS 中&＃xff0c;并在随后有执行引擎调用执行。

【执行引擎】&＃xff1a;Hive本身并不直接处理数据文件。而是通过执行引擎处理。当下Hive支持MapReduce、Tez、Spark3种执行引擎。

2、hive数据模型
Hive中的数据可以在粒度级别上分为三类&＃xff1a;
Table 表
Partition分区
Bucket 分桶

二、hive的DDL相关

1、完整建表语法树

蓝色字体是建表语法的关键字&＃xff0c;用于指定某些功能。
[]中括号的语法表示可选。
|表示使用的时候&＃xff0c;左右语法二选一。
建表语句中的语法顺序要和上述语法规则保持一致。

2、hive数据类型
Hive中的数据类型指的是Hive表中的列字段类型。Hive数据类型整体分为两个类别&＃xff1a;原生数据类型&＃xff08;primitive data type&＃xff09;和复杂数据类型&＃xff08;complex data type&＃xff09;。
【原生数据类型包括】&＃xff1a;数值类型、时间类型、字符串类型、杂项数据类型&＃xff1b;
【复杂数据类型包括】&＃xff1a;array数组、map映射、struct结构、

Hive支持的原生数据类型如下图所示&＃xff1a;
Hive支持的复杂数据类型如下图所示&＃xff1a;

关于Hive的数据类型&＃xff0c;需要注意&＃xff1a;
英文字母大小写不敏感&＃xff1b;
除SQL数据类型外&＃xff0c;还支持Java数据类型&＃xff0c;比如&＃xff1a;string&＃xff1b;
int和string是使用最多的&＃xff0c;大多数函数都支持&＃xff1b;
复杂数据类型的使用通常需要和分隔符指定语法配合使用。
如果定义的数据类型和文件不一致&＃xff0c;hive会尝试隐式转换&＃xff0c;但是不保证成功。

3、hive中数据类型的转换

显式类型转换使用CAST函数。
例如&＃xff0c;CAST&＃xff08;&＃39;100’as INT&＃xff09;会将100字符串转换为100整数值。 如果强制转换失败&＃xff0c;例如CAST&＃xff08;&＃39;INT’as INT&＃xff09;&＃xff0c;该函数返回NULL。

4、hive中的读写机制

【Hive读取文件机制】&＃xff1a;首先调用InputFormat&＃xff08;默认TextInputFormat&＃xff09;&＃xff0c;返回一条一条kv键值对记录&＃xff08;默认是一行对应一条记录&＃xff09;。然后调用SerDe&＃xff08;默认LazySimpleSerDe&＃xff09;的Deserializer&＃xff0c;将一条记录中的value根据分隔符切分为各个字段。

【Hive写文件机制】&＃xff1a;将Row写入文件时&＃xff0c;首先调用SerDe&＃xff08;默认LazySimpleSerDe&＃xff09;的Serializer将对象转换成字节序列&＃xff0c;然后调用OutputFormat将数据写入HDFS文件中。

【hive中分隔符指定】

hive建表时如果没有row format语法。此时字段之间默认的分割符是’\001’&＃xff0c;是一种特殊的字符&＃xff0c;使用的是ascii编码的值&＃xff0c;键盘是打不出来的。

5、hive内部表和外部表

**内部表&＃xff08;Internal table&＃xff09;**也称为被Hive拥有和管理的托管表&＃xff08;Managed table&＃xff09;。默认情况下创建的表就是内部表&＃xff0c;Hive拥有该表的结构和文件。换句话说&＃xff0c;Hive完全管理表&＃xff08;元数据和数据&＃xff09;的生命周期&＃xff0c;当您删除内部表时&＃xff0c;它会删除数据以及表的元数据。

外部表&＃xff08;External table&＃xff09;中的数据不是Hive拥有或管理的&＃xff0c;只管理表元数据的生命周期。要创建一个外部表&＃xff0c;需要使用EXTERNAL语法关键字。删除外部表只会删除元数据&＃xff0c;而不会删除实际数据。在Hive外部仍然可以访问实际数据。而且外部表更为方便的是可以搭配location语法指定数据的路径。

6、分区表
【静态分区】
静态分区指的是分区的字段值是由用户在加载数据的时候手动指定的。
语法如下&＃xff1a;

load data [local] inpath &＃39; &＃39; into table tablename partition(分区字段&＃61;&＃39;分区值&＃39;...);


Local表示数据是位于本地文件系统还是HDFS文件系统。

**【动态分区】**指的是分区的字段值是基于查询结果自动推断出来的。核心语法就是insert&＃43;select。
启用hive动态分区&＃xff0c;需要在hive会话中设置两个参数&＃xff1a;

set hive.exec.dynamic.partition&＃61;true;
set hive.exec.dynamic.partition.mode&＃61;nonstrict;


第一个参数表示开启动态分区功能&＃xff0c;第二个参数指定动态分区的模式。分为nonstick非严格模式和strict严格模式。strict严格模式要求至少有一个分区为静态分区。

分区表的使用重点在于&＃xff1a;
&＃xff08;1&＃xff09;、建表时根据业务场景设置合适的分区字段。比如日期、地域、类别等&＃xff1b;
&＃xff08;2&＃xff09;、查询的时候尽量先使用where进行分区过滤&＃xff0c;查询指定分区的数据&＃xff0c;避免全表扫描。

7、分桶表
在分桶时&＃xff0c;我们要指定根据哪个字段将数据分为几桶&＃xff08;几个部分&＃xff09;。默认规则是&＃xff1a;Bucket number &＃61; hash_function(bucketing_column) mod num_buckets。
可以发现桶编号相同的数据会被分到同一个桶当中。hash_function取决于分桶字段bucketing_column的类型&＃xff1a;
如果是int类型&＃xff0c;hash_function(int) &＃61;&＃61; int;
如果是其他类型&＃xff0c;比如bigint,string或者复杂数据类型&＃xff0c;hash_function比较棘手&＃xff0c;将是从该类型派生的某个数字&＃xff0c;比如hashcode值。

--分桶表建表语句
CREATE [EXTERNAL] TABLE [db_name.]table_name
[(col_name data_type, ...)]
CLUSTERED BY (col_name)
INTO N BUCKETS;


三、hive中的DML

1、load语法
在将数据load加载到表中时&＃xff0c;Hive不会进行任何转换。
加载操作是将数据文件移动到与Hive表对应的位置的纯复制/移动操作。

-- 从本地加载数据 数据位于HS2&＃xff08;node1&＃xff09;本地文件系统 本质是hadoop fs -put上传操作
LOAD DATA LOCAL INPATH &＃39;/root/hivedata/students.txt&＃39; INTO TABLE students;
--从HDFS加载数据 数据位于HDFS文件系统根目录下 本质是hadoop fs -mv 移动操作
--先把数据上传到HDFS上 hadoop fs -put /root/hivedata/students.txt /
LOAD DATA INPATH &＃39;/students.txt&＃39; INTO TABLE students_HDFS;
这里的本地文件系统指的是Hiveserver2服务所在机器的本地Linux文件系统&＃xff0c;不是Hive客户端所在的本地文件系统。


OVERWRITE
如果使用了OVERWRITE关键字&＃xff0c;则目标表&＃xff08;或者分区&＃xff09;中的内容会被删除&＃xff0c;然后再将 filepath 指向的文件/目录中的内容添加到表/分区中。

2、Insert插入数据
insert&＃43;values的方式插入数据&＃xff0c;执行过程非常非常慢&＃xff0c;底层是使用MapReduce把数据写入HDFS的。

INSERT INTO table_name ( field1, field2,...fieldN )
VALUES
( value1, value2,...valueN );


通常在Hive中我们使用insert&＃43;select语句。即插入表的数据来自于后续select查询语句返回的结果。
2.1 insert &＃43; select
Hive中insert主要是结合select查询语句使用&＃xff0c;将查询结果插入到表中

INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1&＃61;val1, partcol2&＃61;val2 ...) [IF NOT EXISTS]] select_statement1 FROM from_statement;
INSERT INTO TABLE tablename1 [PARTITION (partcol1&＃61;val1, partcol2&＃61;val2 ...)] select_statement1 FROM from_statement;


INSERT OVERWRITE将覆盖表或分区中的任何现有数据。

对于分区表的数据导入加载&＃xff0c;最常见最基础的是通过load命令加载数据

动态分区插入案例演示

--动态分区插入
--1、首先设置动态分区模式为非严格模式 默认已经开启了动态分区功能
set hive.exec.dynamic.partition &＃61; true;
set hive.exec.dynamic.partition.mode &＃61; nonstrict;
--2、当前库下已有一张表student
select * from student;
--3、创建分区表 以sdept作为分区字段
--注意&＃xff1a;分区字段名不能和表中的字段名重复。
create table student_partition(Sno int,Sname string,Sex string,Sage int) partitioned by(Sdept string);
--4、执行动态分区插入操作
insert into table student_partition partition(Sdept)select Sno,Sname,Sex,Sage,Sdept from student;
--其中&＃xff0c;Sno,Sname,Sex,Sage作为表的字段内容插入表中
--Sdept作为分区字段值


三、hive中的基础查询语法DQL

1、基础查询的语法树

[WITH CommonTableExpression (, CommonTableExpression)*]
SELECT [ALL | DISTINCT] select_expr, select_expr, ...
FROM table_reference
[WHERE where_condition]
[GROUP BY col_list]
[ORDER BY col_list]
[CLUSTER BY col_list
| [DISTRIBUTE BY col_list] [SORT BY col_list]
]
[LIMIT [offset,] rows]


table_reference指示查询的输入。它可以是普通物理表&＃xff0c;视图&＃xff0c;join查询结果或子查询结果。表名和列名不区分大小写。

2、ALL 、DISTINCT
ALL和DISTINCT选项指定是否应返回重复的行。如果没有给出这些选项&＃xff0c;则默认值为ALL&＃xff08;返回所有匹配的行&＃xff09;。DISTINCT指定从结果集中删除重复的行。

3、WHERE条件是一个布尔表达式。在WHERE表达式中&＃xff0c;您可以使用Hive支持的任何函数和运算符&＃xff0c;但聚合函数除外。

4、GROUP BY
GROUP BY 语句用于结合聚合函数&＃xff0c;根据一个或多个列对结果集进行分组。需要注意的是&＃xff0c;出现在GROUP BY中select_expr的字段&＃xff1a;要么是GROUP BY分组的字段&＃xff1b;要么是被聚合函数应用的字段。原因很简单&＃xff0c;避免出现一个字段多个值的歧义。

5.HAVING
在SQL中增加HAVING子句原因是&＃xff0c;WHERE关键字无法与聚合函数一起使用。
HAVING子句可以让我们筛选分组后的各组数据,并且可以在Having中使用聚合函数&＃xff0c;因为此时where&＃xff0c;group by已经执行结束&＃xff0c;结果集已经确定。

6、LIMIT子句可用于约束SELECT语句返回的行数。
LIMIT接受一个或两个数字参数&＃xff0c;这两个参数都必须是非负整数常量。

7、Hive SQL查询执行顺序

SELECT [ALL | DISTINCT] select_expr, select_expr, ...
FROM table_reference
[WHERE where_condition]
[GROUP BY col_list]
[ORDER BY col_list]
[CLUSTER BY col_list
| [DISTRIBUTE BY col_list] [SORT BY col_list]
]
[LIMIT [offset,] rows]


在查询过程中执行顺序&＃xff1a;from>where>group&＃xff08;含聚合&＃xff09;>having>order>select。
所以聚合语句(sum,min,max,avg,count)要比having子句优先执行&＃xff0c;而where子句在查询过程中执行优先级别优先于聚合语句(sum,min,max,avg,count)。

三、hive中的高阶查询语法

1、 SORT/ORDER/CLUSTER/DISTRIBUTE BY

order by
Hive SQL中的ORDER BY语法类似于SQL语言中的ORDER BY语法。会对输出的结果进行全局排序&＃xff0c;因此底层使用MapReduce引擎执行的时候&＃xff0c;只会有一个reducetask执行。也因此&＃xff0c;如果输出的行数太大&＃xff0c;会导致需要很长的时间才能完成全局排序。默认排序顺序为升序&＃xff08;ASC&＃xff09;&＃xff0c;也可以指定为DESC降序

DISTRIBUTE BY &＃43;SORT BY
如果说CLUSTER BY的功能是分且排序&＃xff08;同一个字段&＃xff09;&＃xff0c;那么DISTRIBUTE BY &＃43;SORT BY就相当于把cluster by的功能一分为二&＃xff1a;DISTRIBUTE BY负责分&＃xff0c;SORT BY负责分组内排序&＃xff0c;并且可以是不同的字段。如果DISTRIBUTE BY &＃43;SORT BY的字段一样&＃xff0c;可以得出下列结论&＃xff1a;
CLUSTER BY&＃61;DISTRIBUTE BY &＃43;SORT BY&＃xff08;字段一样&＃xff09;

CLUSTER BY
SELECT expression… FROM table CLUSTER BY col_name;
Hive SQL中的CLUSTER BY语法可以指定根据后面的字段将数据分组&＃xff0c;每组内再根据这个字段正序排序&＃xff08;不允许指定排序规则&＃xff09;&＃xff0c;概况起来就是&＃xff1a;根据同一个字段&＃xff0c;分且排序。
分组的规则hash散列。hash_func(col_name) % reduce task nums
分为几组取决于reduce task的个数。

【总结】
order by会对输入做全局排序&＃xff0c;因此只有一个reducer&＃xff0c;会导致当输入规模较大时&＃xff0c;需要较长的计算时间。
sort by不是全局排序&＃xff0c;其在数据进入reducer前完成排序。因此&＃xff0c;如果用sort by进行排序&＃xff0c;并且设置mapred.reduce.tasks>1&＃xff0c;则sort by只保证每个reducer的输出有序&＃xff0c;不保证全局有序。
distribute by(字段)根据指定字段将数据分到不同的reducer&＃xff0c;分发算法是hash散列。
Cluster by(字段) 除了具有Distribute by的功能外&＃xff0c;还会对该字段进行排序。
如果distribute和sort的字段是同一个时&＃xff0c;此时&＃xff0c;cluster by &＃61; distribute by &＃43; sort by

2、Union联合查询
语法规则
UNION用于将来自多个SELECT语句的结果合并为一个结果集。语法如下&＃xff1a;

select_statement UNION [ALL | DISTINCT] select_statement UNION [ALL | DISTINCT] select_statement ...


使用DISTINCT关键字与只使用UNION默认值效果一样&＃xff0c;都会删除重复行。
使用ALL关键字&＃xff0c;不会删除重复行&＃xff0c;结果集包括所有SELECT语句的匹配行&＃xff08;包括重复行&＃xff09;。
1.2.0之前的Hive版本仅支持UNION ALL&＃xff0c;在这种情况下不会消除重复的行。
每个select_statement返回的列的数量和名称必须相同。
注意&＃xff1a;union两边查询的select字段要相同&＃xff0c;union相当于将第二张表的结果集放在了第一张表查询的结果集下方&＃xff0c;组成一张新的表。

3、Subqueries子查询
from子句中子查询
在Hive0.12版本&＃xff0c;仅在FROM子句中支持子查询。而且必须要给子查询一个名称&＃xff0c;因为FROM子句中的每个表都必须有一个名称。
子查询返回结果中的列必须具有唯一的名称。子查询返回结果中的列在外部查询中可用&＃xff0c;就像真实表的列一样。子查询也可以是带有UNION的查询表达式。Hive支持任意级别的子查询&＃xff0c;也就是所谓的嵌套子查询。
Hive 0.13.0和更高版本中的子查询名称之前可以包含可选关键字“ AS” 。

【 from子句中子查询】

--from子句中子查询&＃xff08;Subqueries&＃xff09;
--子查询
SELECT num
FROM (
select num,name from student_local
) tmp;
--包含UNION ALL的子查询的示例
SELECT t3.name
FROM (
select num,name from student_local
UNION distinct
select num,name from student_hdfs
) t3;


【where子句中子查询】

--where子句中子查询&＃xff08;Subqueries&＃xff09;
--不相关子查询&＃xff0c;相当于IN、NOT IN,子查询只能选择一个列。
--&＃xff08;1&＃xff09;执行子查询&＃xff0c;其结果不被显示&＃xff0c;而是传递给外部查询&＃xff0c;作为外部查询的条件使用。
--&＃xff08;2&＃xff09;执行外部查询&＃xff0c;并显示整个结果。　　
SELECT *
FROM student_hdfs
WHERE student_hdfs.num IN (select num from student_local limit 2);
--相关子查询&＃xff0c;指EXISTS和NOT EXISTS子查询
--子查询的WHERE子句中支持对父查询的引用
SELECT A
FROM T1
WHERE EXISTS (SELECT B FROM T2 WHERE T1.X &＃61; T2.Y);


4、Common Table Expressions&＃xff08;CTE&＃xff09;公用表表达式
公用表表达式&＃xff08;CTE&＃xff09;是一个临时结果集&＃xff0c;该结果集是从WITH子句中指定的简单查询派生而来的&＃xff0c;该查询紧接在SELECT或INSERT关键字之前。
CTE仅在单个语句的执行范围内定义。一个或多个CTE可以在Hive SELECT&＃xff0c;INSERT&＃xff0c; CREATE TABLE AS SELECT或CREATE VIEW AS SELECT语句中使用。
可以理解成查询结果集的复用

--选择语句中的CTE
with q1 as (select sno,sname,sage from student where sno &＃61; 95002)
select *
from q1;


5、join连接查询
inner join&＃xff08;内连接&＃xff09;、left join&＃xff08;左连接&＃xff09;、right join&＃xff08;右连接&＃xff09;、full outer join&＃xff08;全外连接&＃xff09;、left semi join&＃xff08;左半开连接&＃xff09;、cross join&＃xff08;交叉连接&＃xff0c;也叫做笛卡尔乘积&＃xff09;。

join_table:
table_reference [INNER] JOIN table_factor [join_condition]
| table_reference {LEFT|RIGHT|FULL} [OUTER] JOIN table_reference join_condition
| table_reference LEFT SEMI JOIN table_reference join_condition
| table_reference CROSS JOIN table_reference [join_condition] (as of Hive 0.10)
table_reference:
table_factor
| join_table
table_factor:
tbl_name [alias]
| table_subquery alias
| ( table_references )
join_condition:
ON expression


四、hive函数(重点)

针对内置的函数&＃xff0c;可以根据函数的应用类型进行归纳分类&＃xff0c;比如&＃xff1a;数值类型函数、日期类型函数、字符串类型函数、集合函数、条件函数等&＃xff1b;
针对用户自定义函数&＃xff0c;可以根据函数的输入输出行数进行分类&＃xff0c;比如&＃xff1a;UDF、UDAF、UDTF。

UDF&＃xff08;User-Defined-Function&＃xff09;普通函数&＃xff0c;一进一出

UDAF&＃xff08;User-Defined Aggregation Function&＃xff09;聚合函数&＃xff0c;多进一出
如&＃xff1a;count&＃xff08;&＃xff09;&＃xff1a;统计函数&＃xff0c;sum:求和&＃xff0c;avg:求平均&＃xff0c;min:最小值&＃xff0c;max:最大值

UDTF&＃xff08;User-Defined Table-Generating Functions&＃xff09;表生成函数&＃xff0c;一进多出
如explode函数–炸裂函数

【UDTF】 explode函数接收map或者array类型的数据作为参数&＃xff0c;然后把参数中的每个元素炸开变成一行数据。一个元素一行。这样的效果正好满足于输入一行输出多行。
(1)explode(array)将array列表里的每个元素生成一行&＃xff1b;
(2)explode(map)将map里的每一对元素作为一行&＃xff0c;其中key为一列&＃xff0c;value为一列&＃xff1b;
(3)一般情况下&＃xff0c;explode函数可以直接使用即可&＃xff0c;也可以根据需要结合lateral view侧视图使用。

select explode(&＃96;array&＃96;(11,22,33)) as item;
select explode(&＃96;map&＃96;("id",10086,"name","zhangsan","age",18));


2、 explode使用限制

在select条件中&＃xff0c;如果只有explode函数表达式&＃xff0c;程序执行是没有任何问题的&＃xff1b;
但是如果在select条件中&＃xff0c;包含explode和其他字段&＃xff0c;就会报错。

explode语法限制原因
(1)、explode函数属于UDTF函数&＃xff0c;即表生成函数&＃xff1b;
(2)、explode函数执行返回的结果可以理解为一张虚拟的表&＃xff0c;其数据来源于源表&＃xff1b;
(3)、在select中只查询源表数据没有问题&＃xff0c;只查询explode生成的虚拟表数据也没问题
(4)、但是不能在只查询源表的时候&＃xff0c;既想返回源表字段又想返回explode生成的虚拟表字段
(5)、通俗点讲&＃xff0c;有两张表&＃xff0c;不能只查询一张表但是返回分别属于两张表的字段&＃xff1b;
(6)、从SQL层面上来说应该对两张表进行关联查询
(7)、Hive专门提供了语法lateral View侧视图&＃xff0c;专门用于搭配explode这样的UDTF函数&＃xff0c;以满足上述需要。

3、Lateral View侧视图

Lateral View是一种特殊的语法&＃xff0c;主要用于搭配UDTF类型功能的函数一起使用&＃xff0c;用于解决UDTF函数的一些查询限制的问题。
侧视图的原理是将UDTF的结果构建成一个类似于视图的表&＃xff0c;然后将原表中的每一行和UDTF函数输出的每一行进行连接&＃xff0c;生成一张新的虚拟表。这样就避免了UDTF的使用限制问题。使用lateral view时也可以对UDTF产生的记录设置字段名称&＃xff0c;产生的字段可以用于group by、order by 、limit等语句中&＃xff0c;不需要再单独嵌套一层子查询。
一般只要使用UDTF&＃xff0c;就会固定搭配lateral view使用。

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


**4、HQL提供了几种内置的UDAF聚合函数&＃xff0c;**例如max&＃xff08;…&＃xff09;&＃xff0c;min&＃xff08;…&＃xff09;和avg&＃xff08;…&＃xff09;。这些我们把它称之为基础的聚合函数。
通常情况下&＃xff0c;聚合函数会与GROUP BY子句一起使用。 如果未指定GROUP BY子句&＃xff0c;默认情况下&＃xff0c;它会汇总所有行数据。

--------------基础聚合函数-------------------
--1、测试数据准备
drop table if exists student;
create table student(
num int,
name string,
sex string,
age int,
dept string)
row format delimited
fields terminated by &＃39;,&＃39;;
--加载数据
load data local inpath &＃39;/root/hivedata/students.txt&＃39; into table student;
--验证
select * from student;
--场景1&＃xff1a;没有group by子句的聚合操作
select count(*) as cnt1,count(1) as cnt2 from student; --两个一样
--场景2&＃xff1a;带有group by子句的聚合操作 注意group by语法限制
select sex,count(*) as cnt from student group by sex;
--场景3&＃xff1a;select时多个聚合函数一起使用
select count(*) as cnt1,avg(age) as cnt2 from student;
--场景4&＃xff1a;聚合函数和case when条件转换函数、coalesce函数、if函数使用
select
sum(CASE WHEN sex &＃61; &＃39;男&＃39;THEN 1 ELSE 0 END)
from student;
select
sum(if(sex &＃61; &＃39;男&＃39;,1,0))
from student;
--场景5&＃xff1a;聚合参数不支持嵌套聚合函数
select avg(count(*)) from student;
--聚合参数针对null的处理方式
--null null 0
select max(null), min(null), count(null);
--下面这两个不支持null
select sum(null), avg(null);
--场景5&＃xff1a;聚合操作时针对null的处理
CREATE TABLE tmp_1 (val1 int, val2 int);
INSERT INTO TABLE tmp_1 VALUES (1, 2),(null,2),(2,3);
select * from tmp_1;
--第二行数据(NULL, 2) 在进行sum(val1 &＃43; val2)的时候会被忽略
select sum(val1), sum(val1 &＃43; val2) from tmp_1;
--可以使用coalesce函数解决
select
sum(coalesce(val1,0)),
sum(coalesce(val1,0) &＃43; val2)
from tmp_1;
--场景6&＃xff1a;配合distinct关键字去重聚合
--此场景下&＃xff0c;会编译期间会自动设置只启动一个reduce task处理数据 性能可能会不会 造成数据拥堵
select count(distinct sex) as cnt1 from student;
--可以先去重 在聚合 通过子查询完成
--因为先执行distinct的时候 可以使用多个reducetask来跑数据
select count(*) as gender_uni_cnt
from (select distinct sex from student) a;
--案例需求&＃xff1a;找出student中男女学生年龄最大的及其名字
--这里使用了struct来构造数据 然后针对struct应用max找出最大元素 然后取值
select sex,
max(struct(age, name)).col1 as age,
max(struct(age, name)).col2 as name
from student
group by sex;
select struct(age, name) from student;
select struct(age, name).col1 from student;
select max(struct(age, name)) from student;


5、Window functions 窗口函数
窗口函数&＃xff08;Window functions&＃xff09;是一种SQL函数&＃xff0c;非常适合于数据分析&＃xff0c;因此也叫做OLAP函数&＃xff0c;其最大特点是&＃xff1a;输入值是从SELECT语句的结果集中的一行或多行的“窗口”中获取的。你也可以理解为窗口有大有小&＃xff08;行有多有少&＃xff09;。

通过OVER子句&＃xff0c;窗口函数与其他SQL函数有所区别。如果函数具有OVER子句&＃xff0c;则它是窗口函数。如果它缺少OVER子句&＃xff0c;则它是一个普通的聚合函数。

窗口函数可以简单地解释为类似于聚合函数的计算函数&＃xff0c;但是通过GROUP BY子句组合的常规聚合会隐藏正在聚合的各个行&＃xff0c;最终输出一行&＃xff0c;窗口函数聚合后还可以访问当中的各个行&＃xff0c;并且可以将这些行中的某些属性添加到结果集中。

&＃xff08;1&＃xff09;窗口函数语法

Function(arg1,..., argn) OVER ([PARTITION BY <...>] [ORDER BY <....>] [<window_expression>])
--其中Function(arg1,..., argn) 可以是下面分类中的任意一个
--聚合函数&＃xff1a;比如sum max avg等
--排序函数&＃xff1a;比如rank row_number等
--分析函数&＃xff1a;比如lead lag first_value等
--OVER [PARTITION BY <...>] 类似于group by 用于指定分组 每个分组你可以把它叫做窗口
--如果没有PARTITION BY 那么整张表的所有行就是一组
--[ORDER BY <....>] 用于指定每个分组内的数据排序规则 支持ASC、DESC
--[<window_expression>] 用于指定每个窗口中 操作的数据范围 默认是窗口中所有行


&＃xff08;2&＃xff09; 窗口聚合函数
这里以sum&＃xff08;&＃xff09;函数为例&＃xff0c;其他聚合函数使用类似。

-----窗口聚合函数的使用-----------
--1、求出每个用户总pv数 sum&＃43;group by普通常规聚合操作
select COOKIEid,sum(pv) as total_pv from website_pv_info group by COOKIEid;
--2、sum&＃43;窗口函数 总共有四种用法 注意是整体聚合 还是累积聚合
--sum(...) over( )对表所有行求和
--sum(...) over( order by ... ) 连续累积求和
--sum(...) over( partition by... ) 同组内所有行求和
--sum(...) over( partition by... order by ... ) 在每个分组内&＃xff0c;连续累积求和
--需求&＃xff1a;求出网站总的pv数 所有用户所有访问加起来
--sum(...) over( )对表所有行求和
select COOKIEid,createtime,pv,
sum(pv) over() as total_pv
from website_pv_info;
--需求&＃xff1a;求出每个用户总pv数
--sum(...) over( partition by... )&＃xff0c;同组内所行求和
select COOKIEid,createtime,pv,
sum(pv) over(partition by COOKIEid) as total_pv
from website_pv_info;
--需求&＃xff1a;求出每个用户截止到当天&＃xff0c;累积的总pv数
--sum(...) over( partition by... order by ... )&＃xff0c;在每个分组内&＃xff0c;连续累积求和
select COOKIEid,createtime,pv,
sum(pv) over(partition by COOKIEid order by createtime) as current_total_pv
from website_pv_info;


3、窗口排序函数
窗口排序函数用于给每个分组内的数据打上排序的标号。注意窗口排序函数不支持窗口表达式。总共有4个函数需要掌握&＃xff1a;
**row_number&＃xff1a;**在每个分组中&＃xff0c;为每行分配一个从1开始的唯一序列号&＃xff0c;递增&＃xff0c;不考虑重复&＃xff1b;
rank: 在每个分组中&＃xff0c;为每行分配一个从1开始的序列号&＃xff0c;考虑重复&＃xff0c;挤占后续位置&＃xff1b;
dense_rank: 在每个分组中&＃xff0c;为每行分配一个从1开始的序列号&＃xff0c;考虑重复&＃xff0c;不挤占后续位置&＃xff1b;

上述这三个函数用于分组TopN的场景非常适合。

--需求&＃xff1a;找出每个用户访问pv最多的Top3 重复并列的不考虑
SELECT * from
(SELECT
COOKIEid,
createtime,
pv,
ROW_NUMBER() OVER(PARTITION BY COOKIEid ORDER BY pv DESC) AS seq
FROM website_pv_info) tmp where tmp.seq <4;