hive的基本 *** 作

hive的基本 *** 作

Hive

• 1.基本概念
• 2.优缺点
• 3.架构原理
• 4.与数据库的比较
• 5.基本数据类型
• 6.DDL数据定义
• • 1.数据库
  • 2.表
• 7.DML（数据 *** 纵语言）
• • 1.数据导入
  • 导入用 inpath 基本格式： **insert into/overwrite table table_name**
  • **load data [local] inpath 'path' into table table_name**
  • 2.数据导出
  • 导出用directory 基本格式: **insert overwrite [local] directory**
• 8.DQL语言
• 9.分区与分桶
• • 1.分区
  • 2.分桶
• 10.函数
• • 1.系统内置函数
  • • 1.1 nvl
    • 1.2 case when then else end
    • 1.3行转列
    • 1.4列转行
    • 1.4窗口函数(重点)
    • 1.5 Rank
  • 2.自定义函数
  • • 1.click here [自定义函数](https://blog.csdn.net/m0_51022702/article/details/121913502)

1.基本概念

1. hive简介
   Hive：由Facebook开源用于解决海量结构化日志的数据统计工具。
   Hive是基于Hadoop的一个数据仓库工具，可以将结构化的数据文件映射为一张表，并提供类SQL查询功能。

重点：1.离线分析,不支持更新

2. hive本质
   

2.优缺点

1. 优点
   （1） *** 作接口采用类SQL语法，提供快速开发的能力（简单、容易上手）。
   （2）避免了去写MapReduce，减少开发人员的学习成本。
   （3）Hive的执行延迟比较高，因此Hive常用于数据分析，对实时性要求不高的场合。
   （4）Hive优势在于处理大数据，对于处理小数据没有优势，因为Hive的执行延迟比较高。
   （5）Hive支持用户自定义函数，用户可以根据自己的需求来实现自己的函数。

1. 使用类sql语法，易于 *** 作
2. 避免了写mapreduce
3. 执行延迟高，一般用于离线
   原因：底层用的是mapreduce
4. 能有效处理大量数据
5. 可以自定义函数

2. 缺点
   1）Hive的HQL表达能力有限
   （1）迭代式算法无法表达
   （2）数据挖掘方面不擅长，由于MapReduce数据处理流程的限制，效率更高的算法却无法实现。
   2）Hive的效率比较低
   （1）Hive自动生成的MapReduce作业，通常情况下不够智能化
   （2）Hive调优比较困难，粒度较粗

3.架构原理

1. 用户接口:client

0. 任何接口 都要先启动meta store 元数据服务 hive --service metastore
1. CLI 命令行接口 hive命令启动
2. JDBC/ODBC java/c/c++ 访问接口
   先启动hiveserver2服务：bin/hive --service hiveserver2
   然后连接：
   bin/beeline -u jdbc:hive2://hadoop102:10000 -n username
3. WEBUI web界面访问

3. 元数据：metastore

默认是自带的derby，但都不用他，因为他自支持单窗口 *** 作，所以要将他改成mysql存储meta store

4. hadoop

使用HDFS进行存储数据,mapreduce计算

5. 驱动器：driver

1. 解析器(SQL Parser):用于语法检查，并将sql字符串转换成抽象语法树AST
2. 编译器（Physical Plan）：将AST生成逻辑执行计划
3. **优化器（Query Optimizer）**对逻辑计划进行优化
4. 执行器（Execution）：把逻辑执行计划转换成可以运行的物理计划，即mapreduce/spqrk

4.与数据库的比较

由于 Hive 采用了类似SQL 的查询语言 HQL(Hive Query Language)，因此很容易将 Hive 理解为数据库。其实从结构上来看，Hive 和数据库除了拥有类似的查询语言，再无类似之处。本文将从多个方面来阐述 Hive 和数据库的差异。数据库可以用在 online 的应用中，但是Hive 是为数据仓库而设计的，清楚这一点，有助于从应用角度理解 Hive 的特性。

hive一般是不会对数据进行更新的，自是对数据进行分析。
而数据库会有更新 *** 作

5.基本数据类型

1. 基本类型
   
2. 集合数据类型

6.DDL数据定义 1.数据库

1. 增

CREATE DATAbase [IF NOT EXISTS] database_name
[COMMENT database_comment]
[LOCATION hdfs_path]
[WITH DBPROPERTIES (property_name=property_value, ...)];

comment : 数据库的注释
location: hdfs的存储路劲
with dbproperties: 数据库的属性

1.数据库的创建

create database if not exists my_db
comment "it is a db"
location '/my_db.db'
with dbproperties ("date" = "2021-12-1");

注：location默认路劲 /user/hive/warehouse/*.db

3. 查

2.查询数据库
	1.显示所有数据库 
		show databases;
	2.带条件显示  
		show databases like "db*";
	3.查看数据库信息 
		desc database database_name;
	4.查看数据库详细信息 
		desc database extended database_name;
	5.切换数据库
		use database_name;
例：
show databases like "*db";
desc database my_db;
desc database extended my_db;
use my_db;

4. 改
5. 删

3.修改数据库
	1.修改属性
		alter database database_name set dpproperties("property_name"="value");
例：
alter database my_db set dbproperties("date"="value");

4.删除数据库
	1.空数据库 drop database if exists database_name
	2.非空数据库 drop database if exists database_name cascade
例：
drop database if exists my_db;	
drop database if exists test1 cascade;

2.表

1. 建表

create [external] table [if not exists] table_name

[(clo_name data_type,col_name data_type....)] 

[comment table_comment]

[partitioned by (clo_name data_type [comment col_comment],...)] 

[clustered by (col_name,col_name, ....)]

[sorted by (col_name [desc]), ...] into num_buckets buckets] 

[row format row_dormat]

[stored as file_format] 

[location hdfs_path]

[tableProperties (property_name=property_value, ...)]

[as select_statement]
字段解释：
2. create table if not exists table_name: 创建表

3. external: 指定表为外部表；默认是内部表 创建时需要指定存储路径
 
5. comment : 注释 一般不用

7. partitioned by（col_name data_type ...）：按照指定字段分区； 本质是分目录方便查询

9. clustered by(col_name ...):分桶；实际就是按照字段进行hash运算取得hash值再对分区数取模，余数就是对应分区

11. sorted by: 不常用 ，对一个桶的一个或多个列例外排序
12. row format: DELIMITED [FIELDS TERMINATED BY char] [COLLECTION ITEMS TERMINATED BY char]
        [MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char] 
   | SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)]
用户在建表的时候可以自定义SerDe或者使用自带的SerDe。如果没有指定ROW FORMAT 或者ROW FORMAT DELIMITED，将会使用自带的SerDe。在建表的时候，用户还需要为表指定列，用户在指定表的列的同时也会指定自定义的SerDe，Hive通过SerDe确定表的具体的列的数据。 
注意一般都要写：
row format delimited fields terminated by ',';

13. stored as:指定存储文件类型

10.location: 指定hdfs上的存储位置

11.as： 后跟查询语句，根据查询结果创建表 （好用哦）

12.like：允许用户复制现有的表结构，但不复制数据 

15. 内部表

简单理解：删除内部表时，hive会把真实的数据删掉

16. 外部表

简单理解：删除外部表时，hive自会删除元数据，但真实的数据仍然会保存在hdfs中
创建时需要指定存储路径

17. 修改表
    1. 改表名

    语法：
    alter table table_name rename to new_name;
    例：
    alter table student3 rename to student4;

    2. 增加/修改/替换列信息

语法：
		1. 跟新列 change
		alter table table_name change old_col new_col data_type [comment '注释'][first|after col_name];
		注：col_old_name col_new_name column_type 改列名
		[COMMENT col_comment] ：列的注释
		[FIRST|AFTER column_name] ： 列的位置
		
		2. 增加或替换列 add/replace
		alter table table_name add|replace (col_name data_type [comment ''])
		注：ADD是代表新增一字段，字段位置在所有列后面(partition列前)，REPLACE则是表示替换表中所有字段。

演示：

alter table student4 change id age int
comment '变为年龄';

注：还是不要随便修改列的位置，会报类型不兼容的错误,莫非是元数据信息为更改？？

alter table student4 add columns (gender string);


alter table student4 replace columns (one int);  就只剩这一个字段了...

18. 删除表
    drop table table_name;
    例：
    drop table student4;

7.DML（数据 *** 纵语言） 1.数据导入

导入用 inpath
基本格式：
insert into/overwrite table table_name load data [local] inpath ‘path’ into table table_name

		1.load
语法：
load data [local] inpath 'path' [overwrite] into table table_name [partition (value = "")];

local: 本地路径
overwrite: 覆盖写
partition: 指定分区

例：
load data local inpath '/opt/module/hive-3.1.2/datas/student' into table student3;

		2.insert
1.插入几行					
insert into/overwrite table table_name values(要求全部字段),(),()...;

例：
insert into table student3 values(1,'a'),(2,'b');

insert overwrite table student3 values(1,'a');

2.根据查询表结果插入
insert into/overwrite table table_name select column_name1,column_name2, from table_name2;

例：
insert into table student3 select id,name from student;

insert into table student3 select * from student;

注：字段数量和类型要求一致

3.多表插入
....
		


		3.as select
create table table_name as select column_name,... from table_name1;

		4.location
create table table_name (name type,name type,...) 
row format delimited fields terminated by 't'
location 'hdfsPath';

		5.import
import table table_name form 'hdfs_path';

例：
import table student4 from '/student2';

注：要先用export 再用import 
export与import 会把副本一起导出导入!!
所以他们是一对的!!

2.数据导出

导出用directory
基本格式:
insert overwrite [local] directory

1.查询结果到出到本地
insert overwrite local directory 'local_path' select * from table_name;

例：
insert overwrite local directory '/opt/module/hive-3.1.2/datas/student2'
select * from student;

注：相当于将文件下载到本地的效果 'studnet2'是目录

		2.查询结果格式化到出到本地
insert overwrite local directory 'local_path' 
row format delimited fields terminated by 't'
select * from table_name;

insert overwrite local directory '/opt/module/hive-3.1.2/datas/student3' 
row format delimited fields terminated by 't'
select * from student;

		3.查询结果到出到hdfs
insert overwrite  directory 'hdfs_path' 
row format delimited fields terminated by 't'
select * from table_name;

insert overwrite  directory '/studnet1' 
row format delimited fields terminated by 't'
select * from student;

		4.export到出到hdfs
export table table_name to 'paht';

例：
export table student3 to '/student2';

注：export和import主要用于两个Hadoop平台集群之间Hive表迁移。

		5.sqoop到出

8.DQL语言

1. 语法

select  select_expr,select_expr,..
from table_name
[where where_condition]
[group by col_list]
[having by col_list]
[order by col_list]
[cluster by col_list | distribute by col_list sort by col_list]
[limit number];

注：

col_list: 表示字段集合，可以有多个字段

where_condition:条件查询 一般紧跟fron后面

group by: 用于分组 *** 作 一般和聚合函数一起使用

having by: 用于分组后的过滤 只有当group by 出现后才能出现

order by : 用于全局排序 常放在句末

distribute by：用与分区 常与sort by 一起使用

sort by : 分区内排序 常与 distribute by 一起使用

cluster by : 当 distribute by 和 sort by 字段相同时，那么可以直接使用 cluster by（只能是升序）

limit : 限制返回结果数

2. 列别名

有两种方式
1. 列名 别名
2. 列名 as 别名

3. 常用聚合函数

1.count(*) 统计信息条数
2.max(clo_name) 求最大值
3.min(col_name) 求最小值
4.sum(col_name) 求和
5.avg(col_name) 求平均值

4. 比较特殊的几种运算符

1.between a and b 在 a 到 b 之间
2.in(a,b) 类似python的for循序
3.a is null 判空
4.a is not null 判空
5.like 类似MySQL的like
5.rlike 正则表达式

5. 逻辑运算符

1. and == &&
   2. or == ||
   3. not == ！

6. join语句

与MySQL很像 可以給表起别名
	1.内连接: 满足连接条件的行保留
	select * from table_name1 join table_name2 on condition;
	
	2.左外连接: 保留主表的所有行 若有不匹配的行则多出来的列的值为 null
	select * from table_name1 left join table_name2 on condition;

	3.右外连接：保留主表的所有行 若有不匹配的行则多出来的列的值为 null
	select * from table_name1 right join table_name2 on condition;

	4.满外连接：保留所有行
	select * from table_name1 full join table_name2 on condition;

	5.多表连接
	select * from table_name1 left 
	join table_name2 
	on condition1
	join table_name3
	on condition2...

	注：
	1.condition为连接条件
	2.left join table_name1 为主表
	3.right join table_name2 为主表

7. 排序

1.order by 全局排序 放在句末一般 可以用别名排序
2.sort by 对每个reduce内部排序
当与distribute by 一起使用时类似与mapreduce的partition *** 作

8.案例实 ***

0.先启动元数据服务
hive --service metastore
//开启本地mr
set hive.exec.mode.local.auto=true; 

		1.创表
create table if not exists dept(
id int,
name string,
loc int
)
row format delimited fields terminated by 't';

create table if not exists emp(
eid int,
ename string,
job string,
mgr string,
hiredate string,
sal double,
comm double,
deptid int
)
row format delimited fields terminated by 't';

load data local inpath '/opt/module/hive-3.1.2/datas/dept.txt' into table dept;

load data local inpath '/opt/module/hive-3.1.2/datas/emp.txt' into table emp;

1.as
select id myid from dept;

select id as myid from dept;

		2.常用函数
1）求总行数（count）
hive (default)> select count(*) cnt from emp;
2）求工资的最大值（max）
hive (default)> select max(sal) max_sal from emp;
3）求工资的最小值（min）
hive (default)> select min(sal) min_sal from emp;
4）求工资的总和（sum）
hive (default)> select sum(sal) sum_sal from emp; 
5）求工资的平均值（avg）
hive (default)> select avg(sal) avg_sal from emp;

		3.limit语句
select * from dept limit 2;

		4.比较运算符
（1）查询出薪水等于5000的所有员工
select * from emp where sal = 5000;
（2）查询工资在500到1000的员工信息
select * from emp where sal between 500 and 1000;
（3）查询comm为空的所有员工信息
select * from emp where comm is null;
（4）查询工资是1500或5000的员工信息
select * from emp where sal in(1500,5000);

（1）查找名字以A开头的员工信息
select * from emp where ename like 'A%';
（2）查找名字中第二个字母为A的员工信息
select * from emp where ename like '_A%';
（3）查找名字中带有A的员工信息
select * from emp where ename rlike '[A]';

		5.排序
 （1）查询员工信息按工资升序排列
select * from emp order by sal;

 （2）查询员工信息按工资降序排列
 select * from emp order by sal desc;
 
 （3）按照别名排序
 select sal*2 as twosal from emp order by twosal;

 reduce排序
 	1.要先设置reduce个数
 	set mapreduce.job.reduces = 3;
 	2.查看reduce个数
 	set mapreduce.job.reduces;
 	3.按照部门编号降序查看信息
 	select * from dept sort by id desc;
 	4.将查询结果导入到文件中（按照部门编号降序排序）
 	insert overwrite local directory '/opt/module/hive-3.1.2/datas/sort1'
 select * from emp sort by eid desc;

 	注：路径不要有空格

 先分区在排序
 	insert overwrite local directory '/opt/module/hive-3.1.2/datas/sort2'
 select * from emp distribute by eid sort by eid;

  上一句等同于
 	insert overwrite local directory '/opt/module/hive-3.1.2/datas/sort3'
 select * from emp cluster by eid ;

9.分区与分桶 1.分区

本质：就是分目录

语法

create table if not exists table_name{
col_name type,
col_name type	
}
partitioned by (str type);

create table if not exists table_name{
col_name type,
col_name type	
}
partitioned by (str type，str type);

注：静态分区时 str 为非表字段 相当于伪字段 可以像字段使用
	动态分区时 str 为表中字段 

-----------------------

1.增加分区
alter table table_name add partition(str='value');

alter table table_name add partition(str='value') partition(str='value2');

2.删除分区
alter table table_name drop partition(str='value');

alter table table_name drop partition(str='value'),
partition(str='value2');

3.查看分区数
show partitions table_name;

4.查看分区表结构
desc formatted table_name;

注：添加多个分区时 用的是空格 删除多个分区时 用的是逗号
-----------------------
多级分区：

需要在创建表时指定多个 字段/伪字段 即刻

1. 静态分区

加载数据方式
1.load data 时指定分区字段
注：这也有不好的地方，就是要指定字段
2. insert into table partition(str=‘value’) values();

2.把数据直接上传到分区目录上，让分区表和数据产生关联的三种方式

1.通过hdfs命令将数据上传到指定分区目录(未存在的目录)
此时在hive元数据中是没有记录的，所以查询不到数据
此时可以执行  修复命令：
msck repair table table_name;

2.上传数据后，执行添加分区命令(分区字段值要与目录格式一致)

3.创建文件夹后load数据到分区

注：其实说白了，就是要让hive中的元数据有记录才可以查询的到

3. 动态分区

挺好用，就是需要额外的配置

1）开启动态分区参数设置
（1）开启动态分区功能（默认true，开启）
hive.exec.dynamic.partition=true
（2）设置为非严格模式（动态分区的模式，默认strict，表示必须指定至少一个分区为静态分区，nonstrict模式表示允许所有的分区字段都可以使用动态分区。）
hive.exec.dynamic.partition.mode=nonstrict
（3）在所有执行MR的节点上，最大一共可以创建多少个动态分区。默认1000
hive.exec.max.dynamic.partitions=1000
（4）在每个执行MR的节点上，最大可以创建多少个动态分区。该参数需要根据实际的数据来设定。比如：源数据中包含了一年的数据，即day字段有365个值，那么该参数就需要设置成大于365，如果使用默认值100，则会报错。
hive.exec.max.dynamic.partitions.pernode=100
（5）整个MR Job中，最大可以创建多少个HDFS文件。默认100000
hive.exec.max.created.files=100000
（6）当有空分区生成时，是否抛出异常。一般不需要设置。默认false
hive.error.on.empty.partition=false

注：动态分区是按照指定字段值创建目录的

---

2.分桶

本质就是对数据的进一步细分。针对的时文件数据

语法

create table if not exists table_name{
col_name type,
col_name type	
}
clustered by (col_name) into number buckets;

2）分桶表 *** 作需要注意的事项:
（1）reduce的个数设置为-1,让Job自行决定需要用多少个reduce或者将reduce的个数设置为大于等于分桶表的桶数
（2）从hdfs中load数据到分桶表中，避免本地文件找不到问题
（3）不要使用本地模式

3. 抽样查询

对于非常大的数据集，有时用户需要使用的是一个具有代表性的查询结果而不是全部结果。Hive可以通过对表进行抽样来满足这个需求。
语法: TABLESAMPLE(BUCKET x OUT OF y)
查询表stu_buck中的数据。
hive (default)> select * from stu_buck tablesample(bucket 1 out of 4 on id);

注意：x的值必须小于等于y的值，否则
FAILED: SemanticException [Error 10061]: Numerator should not be bigger than denominator in sample clause for table stu_buck

4. 案例实 ***

0.创建表
create table if not exists dept(
id int,
str string,
sal int
)
partitioned by (day string)
row format delimited fields terminated by 't';


	1.静态分区加载数据
load data local inpath '/opt/module/hive-3.1.2/datas/dept_partition/dept_20200401.log' into table dept partition(day = '20200401');

load data local inpath '/opt/module/hive-3.1.2/datas/dept_partition/dept_20200402.log' into table dept partition(day = '20200402');

load data local inpath '/opt/module/hive-3.1.2/datas/dept_partition/dept_20200403.log' into table dept partition(day = '20200403');

load data local inpath '/opt/module/hive-3.1.2/datas/dept_partition/dept_20200404.log' into table dept partition(day = '20200404');

	2.查询分区表数据
select * from dept where day = '20200401';

	3.增删查分区

alter table dept add partition(day = '20200405');

alter table dept add partition(day = '20200405') partition(day = '20200406');

alter table dept drop partition(day = '20200405');

alter table dept drop partition(day = '20200405'),partition(day = '20200406');

show partitions dept;

desc formatted dept;

注：若添加已经存在的目录则会报错,删除不存在的当然也会咯

	4.二级分区
create table if not exists dept2(
id int,
str string,
sal int
)
partitioned by (day string,hour string)
row format delimited fields terminated by 't';

insert into table dept2 
partition(day = '1001',hour = '1') values(1,'1',1);

	5.动态分区
hive.exec.dynamic.partition.mode=nonstrict;
hive.exec.max.dynamic.partitions=1000;
hive.exec.max.dynamic.partitions.pernode=100;
hive.exec.max.created.files=100000;
hive.error.on.empty.partition=false;

	6.分桶表
create table if not exists stu_bucket(
id int,
name string
)
clustered by (id) into 4 buckets
row format delimited fields terminated by 't';

load data local inpath '/opt/module/hive-3.1.2/datas/student' into table stu_bucket;

注：要让 reduce个数大于等于分桶表的分桶数或则将reduce的个数设置为-1；
set mapreduce.job.reduces = -1;

10.函数 1.系统内置函数 1.1 nvl

语法：
nvl(value/col_name,default_value)

解释：
如果第一个参数值为null那么就返回default_value
如果第一个参数值不为null那么就返回该值

例：
select nvl(1,-1);
select nvl(null,-1);

注：字段用法一样

1.2 case when then else end

跟case语句很像

语法:
 case a1 when b1 then b2 else b3 end

解释:
a: 变量 一般为字段名
b1: 常量 一般为基本数据类型
b2,b3 : 可以为常量 也可以为表达式

如果 a1 == b1 那么返回 b2
否则 返回b3

例：求出不同部门男女各多少人。
create table emp_sex(
name string, 
id string, 
sex string) 
row format delimited fields terminated by "t";

load data local inpath '/opt/module/hive-3.1.2/datas/emp_sex.txt' into table emp_sex;

select id,
sum(case sex when '男' then 1 else 0 end) males,
sum(case sex when '女' then 1 else 0 end) females
from 
emp_sex
group by id;


注：可以嵌套

1.3行转列

相当于 将一些字符串 拼接在一起
类似于 hbase 的 物理存储结构 变为 逻辑存储结构
是一类聚合函数

语法：
	1.concat(str1/col_name1,str2/col_name2...)
	注：	不常用 ,跟Java '+' 一样
	
	2.concat_ws(separator,str1,str2...)
	注：	常用，像 java中'split' 的逆 *** 作
		参数也可以为数组

	3.collect_set(col)
	注： 直接收基本数据类型，主要就是去重字段的值，形成arr型字段

例：把星座和血型一样的人归类到一起。
name	constellation	blood_type
孙悟空	白羊座	A
大海	射手座	A
宋宋	白羊座	B
猪八戒	白羊座	A
凤姐	射手座	A


苍老师	白羊座	B

create table person_info(
name string, 
constellation string, 
blood_type string) 
row format delimited fields terminated by "t";


load data local inpath "/opt/module/hive-3.1.2/datas/person_info.txt" into table person_info;

select t1.a, 
concat_ws('|',collect_set(t1.name))
from
(select name,
concat_ws(',',constellation,blood_type) a
from person_info)t1
group by a;

select collect_set(name) from person_info;


注 1.将星座和血型拼接成字段 'a'
   2.按照字段 'a' 进行分组
   3.此时 name 字段会被聚合
   4.然后利用 collect_set()函数将name合成一个string数组
   5.再将name进行拼接 即可

1.4列转行

类似于 hbase的逻辑结构 变为 物理结构

语法：
lateral view udtf(expression) tableAlias as columnAlisa

解释：
从侧面观看这个表的某个"集合列"形成"新表"，作为该表的一个列 

注： 用于和split, explode等UDTF一起使用，它能够将一列数据拆
成多行数据，在此基础上可以对拆分后的数据进行聚合。
tableAlias: 表别名 没感觉出有啥用
columnAlias: 列别名 用于后续查询
latercal view: 侧面观看
lateral view 前面一般是表名

例：
movie	category
《疑犯追踪》	悬疑,动作,科幻,剧情
《Lie to me》	悬疑,警匪,动作,心理,剧情
《战狼2》	战争,动作,灾难

create table movie_info(
    movie string, 
    category string) 
row format delimited fields terminated by "t";


load data local inpath "/opt/module/hive-3.1.2/datas/movie_info.txt" into table movie_info;

SELECt movie,category_name 
FROM movie_info 
lateral VIEW
explode(split(category,",")) movie_info_tmp  AS category_name ;

select movie,a.b 
from movie_info 
lateral view
explode(split(category,",")) a  AS b ;

1.4窗口函数(重点)

理解：

0. 先 group by 在执行 over()
1. 每条数据都会有一个窗口
2. 若要详细了解 需要了解 group by 的底层原理
   才能形成鲜明对比

首先窗口函数 是为了 辅助分析函数 而产生的，
原本 分析函数 自能按照分组进行 ，分析范围固定化

窗口函数 可以解决 一对多，多对一 的 问题 group by 会导致数据压缩 （即多行数据变为一行） 这样导致自能于聚合函数配合使用

group by 与 聚合函数 使用 分析：

1. 没有分组 聚合函数 是对整体数据集 分析
2. 有分组 聚合函数 是对每个组数据集 分析

语法：
OVER()：指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的改变而变化。
Current row: 当前行
n preceding: 往前n行数据
n following: 往后n行数据
Unbounded:起点，
Unbounded preceding 表示从前面的起点
Unbounded following 表示到后面的终点	
lag(col,n,default_val)：往前第n行数据
lead(col,n, default_val)：往后第n行数据
ntile(n)：把有序窗口的行分发到指定数据的组中，各个组有编号，编号从1开始，对于每一行，NTILE返回此行所属的组的编号。注意：n必须为int类型。

规则： 下面都以 sum（col_name ）over（） 为例 主要是如何调节窗口大小
	1. 每一条数据都会有一个窗口， 且这个窗口可以任意调节大小  这样就解决了多对一的问题
	2. over() 不能 单独使用  前面必须要有分析函数 如 sum() max()
	3. sum() over() 默认每条数据的窗口大小为整体数据集
	4. sum() over(partition by col_name)  每条数据窗口大小为 所属分区的数据集 支持多级分区用逗号隔开
	4. sum() over(partition by col_name1,col_name2)
	4.注： partition by  也可以用 distribute by 替代
	5. sum() over(order by col_name)    默认每条数据的窗口大小为 起点行 到 当前行
	5.注：order by 也可以用 sort by 替代
	6. sum() over(partition by col_name order by col_name)  先进行分区 然后每条数据的窗口大小为 起点行 到 当前行
	7. sum() over(between '' and '')  从 ... 到 ... 

关键字对比:
	partition by： 在窗口函数中 分区 使用
	partitioned by： 在建表时 指定分区使用 
	cluster by : 在查询中使用  == distribute by col_name sort by col_name
	clustered by： 在建表时 使用 用于分桶
	sort by： 用于 分区内排序
	order by: 在查询 时 是 全局排序  在窗口函数中 也是 全局排序

案例实 *** :
1.数据准备
jack,2017-01-01,10
tony,2017-01-02,15
jack,2017-02-03,23
tony,2017-01-04,29
jack,2017-01-05,46
jack,2017-04-06,42
tony,2017-01-07,50
jack,2017-01-08,55
mart,2017-04-08,62
mart,2017-04-09,68
neil,2017-05-10,12
mart,2017-04-11,75
neil,2017-06-12,80
mart,2017-04-13,94

2.建表 导入数据
create table if not exists business(
name string,
order_date string,
cost int
)
row format delimited fields terminated by ',';

load data local inpath '/opt/module/hive-3.1.2/datas/business.txt' into table business;

需求一: 查询在2017年4月份购买过的顾客及总人数

方法一：

select t1.name,count(t1.name) over()
from
(select  name
from business
where month(order_date) = 4 and year(order_date) = 2017) t1
group by name;

方法二:
select 
   t2.name, count(t2.name) over()
from 
(select
  distinct(t1.name)
from 
  (select
  name, orderdate, cost 
from 
  business 
where substring(orderdate,0,7) = '2017-04')t1) t2

需求二: 查询顾客的购买明细及所有顾客的月购买总额


select 
    name,
    order_date,
    cost ,
    sum(cost) over(partition by month(order_date))
from
    business ;   

需求二: 查询顾客的购买明细及每个顾客的月购买总额
select 
    name,
    order_date,
    cost ,
    sum(cost) over(partition by name,month(order_date))
from
    business ;


需求三: 将每个顾客的cost按照日期进行累加
select
    name,
    sum(cost) over(partition by name order by cost)
from
   business;    


需求三: 将所有顾客的cost按照日期进行累加
select
    name,
    sum(cost) over(order by cost)
from
   business;


需求三: 求所有顾客的购买明细及按照日期进行排序后
       求 所有顾客的cost  第一行 到 当前行 累加
          所有顾客的cost 上一行 到 当前行 的累加和 
          所有顾客的cost 上一行 到 下一行 的累加和
          所有顾客的cost 当前行 到 下一行 的累加和
          所有顾客的cost 当前行 到 最后一行的累加和
Current row: 当前行
n preceding: 往前n行数据
n following: 往后n行数据
Unbounded:起点，
Unbounded preceding 表示从前面的起点
Unbounded following 表示到后面的终点	
lag(col,n,default_val)：往前第n行数据
lead(col,n, default_val)：往后第n行数据
select
    name,
    order_date,
    cost,
    sum(cost) over(order by order_date) a,
    sum(cost) over(order by order_date rows between 1 preceding and current row) b,
    sum(cost) over(order by order_date rows between 1 preceding and 1 following) c,
    sum(cost) over(order by order_date rows between current row  and 1 following) d,
    sum(cost) over(order by order_date rows between current row and unbounded following) e
from
    business;    


需求四: 查询每个顾客上次的购买时间 及 下一次的购买时间 
select
   name,
   cost, 
   order_date c_orderdate,
   lag(order_date ,1 ,'1970-01-01') over(partition by name  order by order_date) p_orderdate,
   lead(order_date ,1 ,'9999-01-01') over(partition by name  order by order_date) p_orderdate
from 
  business;


需求五: 查询前20%时间的订单信息
select 
  t1.name, 
  t1.order_date,
  t1.cost ,
  t1.gid
from 
(select
  name, 
  order_date,
  cost, 
  ntile(5) over(order by order_date ) gid
from 
  business) t1
where t1.gid = 1 ;

1.5 Rank

用于排序名次

语法：
	1.rank()
注：排序相同会重复 但总数不会变

	2.dense_rank()
注: 排序相同时会重复，总数会减少

	3.row_number()

注: 会根据顺序计算
1.数据准备
name	subject	score
孙悟空	语文	87
孙悟空	数学	95
孙悟空	英语	68
大海	语文	94
大海	数学	56
大海	英语	84
宋宋	语文	64
宋宋	数学	86
宋宋	英语	84
婷婷	语文	65
婷婷	数学	85
婷婷	英语	78

2.建表及导入数据
create table if not exists score(
name string,
subject string,
score int
)
row format delimited fields terminated by 't';

load data local inpath '/opt/module/hive-3.1.2/datas/score.txt' into table score;


解释：
分数	    名次     名次           名次
		rank    dense_rank     row_number 
99		1       1              1
99		1       1              2
99		1       1              3
88		4       2              4

需求: 按照学科进行排名

select
    name,
    subject,
    score,
    rank() over(partition by subject order by score desc) a,
    dense_rank() over(partition by subject order by score desc) b,
    row_number() over(partition by subject order by score desc) c
from
    score;    

2.自定义函数 1.click here 自定义函数