Hive的使用和入门案例

来源：千峰科技王从明老师

第一节：HIVE的基本操作：库、表

1.1 说明和命名规范

- hive有一个默认的数据库default，在操作HQL时，如果不明确的指定要使用哪个库，则使用默认数据库。
- hive的数据库名、表名都不区分大小写
- 名字不能使用数字开头
- 不能使用关键字
- 尽量不使用特殊符号

1.2 库操作语法

1.2.1 创建数据库

语法1：create database mydb;
语法2：create database if not exists mydb;
语法3：create database if not exists mydb comment 'this is my database';

1.2.2 查看所有数据库

语法：show databases; 

1.2.3 切换数据库

语法：use mydb;

扩展：显示当前数据库的名称：
hive> set  hive.cli.print.current.db=true;

1.2.4 查看数据库信息

语法1：desc database databaseName;
语法2：desc database extended databaseName;
语法3：describe database extended databaseName;

1.2.5 删除数据库

语法1：drop database databasename;             #这个只能删除空库
语法2：drop database databasename cascade;     #如果不是空库，则可以加cascade强制删除

1.3 表操作语法

1.3.1 创建表

语法1：create table t_user(id int,name string);
语法2：create table mydb.t_user(id int,name string);

语法3：
create table if not exists t1(
uname string comment 'this is name',
chinese int,
math int,
english int
)
comment 'this is my table'
row format delimited
fields terminated by '\t'
lines terminated by '\n'
stored as textfile;

1.3.2 查看当前表空间中的所有表名

语法：show tables; 

1.3.3 查看表结构

desc tableName
desc extended tableName;
describe extended tableName;

1.3.4 修改表结构

- 修改表名
    alter table oldTableName rename to newTableName;

- 修改列名：change column    
    alter table tableName change column oldName newName colType;
- 修改字段类型：
    alter table tableName change column colName colName newColType; 

- 修改列的位置
    alter table tableName change column colName colName colType after colName1;   
    alter table tableName change column colName colName colType first;

- 增加字段：add columns
    alter table tableName add columns (sex int,...);

- 删除字段：replace columns
    alter table tableName replace columns(
    id int,
    name int,
    size int,
    pic string
    );
    注意：实际上是保留小括号内的字段。

1.3.5 删除表

drop table tableName;

1.4 创建库和表的本质

- 创建数据库
    其实就是在hive的参数${hive.metastore.warehouse.dir}对应的目录下，创建一个新的目录，此目录的名
    称为： 库名.db。
- 创建表
    其实就是在对应的数据库目录下面创建一个子目录，目录名为表名。数据文件就存在这个目录下。
- 元数据库
    在创建库或者表时除了创建目录外，还会在mysql中(元数据库)，添加元数据（描述信息）

第二节： 加载(导入)数据的方式

方法1：使用hdfs dfs -put将本地文件上传到表目录下

hdfs dfs -put ./u1.txt /user/hive/warehouse/mydb1.db/u1

方法2：在hive中使用load 命令

load data [local] inpath '文件路径' [overwrite] into table 表名

方法3：从另外一张表(也可称之为备份表)中动态加载数据

insert into table tableName2 select [.....] from tableName1;


扩展内容：向多张表中插入数据的语法
    from tableName1
    insert into tableName2 select * where 条件
    insert into tableName3 select * where 条件
    .....

<font color='red'>注意：</font> tableName2表中的字段个数必须和tableName1表中查询出来的个数相同

方法4：复制表数据

- create table if not exists tableName2 as select [....] from tableName1;
- create table if not exists tableName2 like tableName1 location 'tableName1的存储目录的路径'

扩展内容：只复制表结构
create table if not exists tableName2 like tableName1;

案例演示：

CREATE TABLE flow(
id             string COMMENT 'this is id column',
phonenumber     bigint,
mac             string,
ip               string,
url              string,
type      string,
uppacket         int,
downpacket       int,
upflow            int,
downflow         int,
status    string
)
COMMENT 'this is flow table'
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n'
stored as textfile;

加载数据：
load data local inpath './data/HTTP_20130313143750.dat' into table flow;

1、统计每个电话号码的总流量(M)
  --解析：1.按照手机号码分组，查询每个手机号码的总上行流量和总下行流量
 select phonenumber,sum(nvl(upflow,0)),sum(nvl(downflow,0)) from flow group by phonenumber;
  --解析：2 将总上行流量和总下行流量相加
 select phonenumber,sum(nvl(upflow,0))+sum(nvl(downflow,0)) from flow group by phonenumber;
 --解析：3 添加MB的单位
  select phonenumber,concat(round(sum(nvl(upflow,0))+sum(nvl(downflow,0))/1024.0,2),'MB') from flow group by phonenumber;

 
  扩展：将hive执行模式修改为本地模式
  set hive.exec.mode.local.auto=true;

2、第二个需求，求访问次数排名前3的url：

    --解析:按照url分组统计个数，并降序排序，取前三行
    select url,count(*) totalSize from flow group by url order by totalSize  desc limit 3;

第三节：查询语句的基本语法

3.1 基本使用方法

3.1.1 子句与关键字

select ..from ..join [tableName] on ..where ..group by ...having ..order by ..sort by ..limit ..union | union all ...

7.1.2 执行顺序

第一步: FROM <left_table>
第二步: ON <join_condition>
第三步: <join_type> JOIN <right_table>
第四步: WHERE <where_condition>
第五步: GROUP BY <group_by_list>
第六步: HAVING <having_condition>
第七步: SELECT
第八步: DISTINCT <select_list>
第九步: ORDER BY <order_by_condition>
第十步: LIMIT <limit_number>

7.1.3 查询原则

1. 尽量不使用子查询
2. 尽量不使用[not] in。
3. 尽量避免join连接查询，但是通常避免不了
4. 查询永远是小表驱动大表（小表放在前面）

7.2. join的语法与特点

7.2.1 连接查询。

有的业务所需要的数据，不是在一张表中，通常会存在多张表中，而这些表中通常应该会存在"有关系"的字段。多表查询时，使用关联字段"连接"（join）在一起，组合成一个新的数据集，就是连接查询。

连接查询操作分为两大类：内连接和外连接，而外连接有细分为三种类型。参考下图

1. 内连接:  [inner] join
2. 外连接 (outer join)：（引出一个驱动表的概念：驱动表里的数据全部显示)
  - 左外连接:left [outer] join, 左表是驱动表
  - 右外连接:right [outer] join, 右表是驱动表
  - 全外连接:full [outer] join, hive支持，mysql不支持.两张表里的数据全部显示出来
3. 注意： join连接只支持等值连接  

20190910204044.jpg

需要大家注意的是，两张表的关联字段的值往往是不一致的。比如，表 A 包含张三和李四，表 B 包含李四和王五，匹配的只有李四这一条记录。从上图很容易看出，一共有四种处理方式和结果。下图就是四种连接的图示，这张图比上面的维恩图更易懂，也更准确。

join.png

上图中，表 A 的记录是 123，表 B 的记录是 ABC，颜色表示匹配关系。返回结果中，如果另一张表没有匹配的记录，则用 null 填充。

此外，还存在一种特殊的连接，叫做"交叉连接"（cross join），指的是表 A 和表 B 不存在关联字段，这时表 A（共有 n 条记录）与表 B （共有 m 条记录）连接后，会产生一张包含 n x m 条记录(笛卡尔积)的新表（见下图）。

cross.png

案例演示：

准备数据
u1文件中的数据如下：
1,a
2,b
3,c
4,d
7,y
8,u

u2文件中的数据如下：
2,bb
3,cc
7,yy
9,pp

create table if not exists u1(
id int,
name string
)
row format delimited 
fields terminated by ','
;

create table if not exists u2(
id int,
name string
)
row format delimited fields terminated by ','
;

load data local inpath './data/u1.txt' into table u1;
load data local inpath './data/u2.txt' into table u2;

7.3. Hive中join的专有特点

7.3.1 left semi join

在hive中，有一种专有的join操作,left semi join,我们称之为半开连接。它是left join的一种优化形式，只能查询左表的信息，主要用于解决hive中左表的数据是否存在的问题。相当于exists关键字的用法。

exists关键字：满足条件返回true,不满足条件返回false

练习:  查询有领导的员工信息
      select * from emp where mgr is not null
      select * from emp A where exists (select 1 from emp B where B.empno = A.mgr ) 
      查询有下属的员工信息
      select * from emp A where exists (select 1 from emp B where B.mgr = A.empno )
      查看有部门的所有员工的信息
      select * from emp A where exists (select 1 from dept B where B.deptno = A.deptno )

7.3.2 left semi join的写法。

select * from u1 left join u2 on u1.id =u2.id;
select * from u1 left outer join u2 on u1.id =u2.id;

# 左外连接，显示左表信息
select u1.* from u1 left join u2 on u1.id =u2.id; 

# 只显示左表中的满足条件的数据，和内连接显示左表数据效果一致，但是原理不一样。
# left semi join 是判断满足条件的数据在不在，如果在，就显示。
select * from u1 left semi join u2 on u1.id =u2.id;

# 内连接写法，显示左表信息
select u1.* from u1 inner join u2 on u1.id =u2.id;

# exists写法与left semi join 原理一样
select u1.* from u1 where exists (select 1 from u2 where u2.id =u1.id);

--验证left semi join 是否可以显示第二张表的信息：错误
select A.*, B.* from u1 A left semi join u2 B on A.id =B.id;

<font color='red'>注意：</font> hive中不支持right semi join

7.3.3 map-side join

1. Map-side-join思想：分布式缓存文件，读到内存中.
2. Map-side-join实用场景:
    在那些需要处理的表中，存在一个非常大的表和一个非常小的表时，可以将小表的数据放到内存中。放到内存即
    是将小表文件通过DistinctCache放到HDFS服务器，并读取到HashMap、List等数据结构中，在maptask执行
    前将其发送到此次Job需要启动map的节点上。
3. 优点：
    将小表缓存，可以高效查询；由于在map阶段进行连接，所以将会大大减小map到reduce端的数据传输，从而减
    少不必要的shuffle耗时，提高整个mr的执行效率。
4. 缺点：
    如果业务全是大表不适合
5. hive-1.2.1版本已经默认开启map-side join
    hive.auto.convert.join=true
    hive.mapjoin.smalltable.filesize=25000000    约23.8MB  

7.4 where语句特点

where后不能使用聚合函数，可以使用子查询，也可以是普通函数。

#hive中子查询可以使用在where子句中，不能使用=进行条件表达。

7.5 group by语句特点

group by: 分组，通常和聚合函数搭配使用

查询的字段要么出现在group by 后面，要么出现在聚合函数里面

count的执行
1. 执行效果上：
    - count(*)包括了所有的列，相当于行数，在统计结果的时候不会忽略null值
    - count(1)包括了所有列，用1代表代码行，在统计结果的时候也不会忽略null值
    - count(列名)只包括列名那一列，在统计结果时，会忽略null值

2.执行效率上：
    - 列名为主键，count(列名)会比count(1)快
    - 列名不为主键，count(1)会比count(列名)快
    - 如果表中有多个列并且没有主键，count（1）的效率高于count(*)
    - 如果有主键count(主键)效率是最高的
    - 如果表中只有一个字段count(*)效率最高

7.6 having子句特点

对分组以后的结果集进行过滤。

students;
查询学院人数大于4;
select academy,count(*) from students group by academy having count(*)>4;

7.7 limit语句特点

limit ： 从结果集中取数据的条数
将set hive.limit.optimize.enable=true 时，limit限制数据时就不会全盘扫描，而是根据限制的数量进行抽样。
# 在hive中，limit子句后只能有一个参数，表示查询前N条记录


同时还有两个配置项需要注意：
hive.limit.row.max.size        这个是控制最大的抽样数量
hive.limit.optimize.limit.file 这个是抽样的最大文件数量

7.8 union | union all

union all：将两个或者多个查询的结果集合并到一起。不去重
union：将两个或者多个查询的结果集合并到一起，去重合并后的数据并排序
#union语句字段的个数要求相同，字段的顺序要求相同。    

第四节：数据类型的讲解

在hive中，数据类型分为基础数据类型和复杂数据类型两大类型

8.1 数据类型

分类	类型	描述	字面量示例
基本类型	BOOLEAN	true/false	TRUE
	TINYINT	1字节的有符号整数 -128~127	1Y
	SMALLINT	2个字节的有符号整数，-32768~32767	1S
	INT	4个字节的带符号整数	1
	BIGINT	8字节带符号整数	1L
	FLOAT	4字节单精度浮点数	1.0
	DOUBLE	8字节双精度浮点数	1.0
	DEICIMAL	任意精度的带符号小数	1.0
	STRING	字符串，可变长度	“a”,’b’
	VARCHAR	变长字符串,要设置长度	“a”,’b’
	CHAR	固定长度字符串	“a”,’b’
	BINARY	字节数组	无法表示
	TIMESTAMP	时间戳，纳秒精度	122327493795
	DATE	日期	‘2016-03-29’
复杂类型	ARRAY	有序的的同类型的集合	array(1,2)
	MAP	key-value,key必须为原始类型，value可以任意类型	map(‘a’,1,’b’,2)
	STRUCT	字段集合,类型可以不同	struct(‘1’,1,1.0), named_stract(‘col1’,’1’,’col2’,1,’clo3’,1.0)
	UNION	在有限取值范围内的一个值	create_union(1,’a’,63)

示例:

create table if not exists datatype1(
id1 tinyint,
id2 smallint,
id3 int,
id4 bigint,
slary float,
comm double,
isok boolean,
content binary,
dt timestamp
)
row format delimited 
fields terminated by '\t'
;

233 12  342523  455345345   30000   60000   nihao   helloworld  2017-06-02
126 13  342526  455345346   80000   100000  true    helloworld1 2017-06-02 11:41:30

load data local inpath './data/datatype.txt' into table datatype1;

8.2 数据类型转换

8.2.1 自动转换(隐式转换)

- 在做运算时，小范围类型的数据都会自动转为大范围类型的数据，再做运算，这是隐式转换
   tinyint -->smallint-->int-->bigint-->float-->double   
   纯数字的string-->double   

8.2.2 强制转换

在hive中，不能强制转换。可以使用函数cast(val as type).
含义是：将val转为type类型

8.2 复杂数据类型之array

8.2.1 定义格式如下：

create table tableName(
......
colName array<基本类型>
......
)

说明：下标从0开始，越界不报错，以null代替

8.2.2 案例准备：

zhangsan    78,89,92,96
lisi    67,75,83,94
王五      23,12

create table if not exists arr1(
name string,
scores array<String>
)
row format delimited 
fields terminated by '\t'
;

create table if not exists arr2(
name string,
scores array<String>
)
row format delimited 
fields terminated by '\t'
collection items terminated by ','
;

load data local inpath './data/arr1.txt' into table arr1;
load data local inpath './data/arr1.txt' into table arr2;

8.2.3 查询语句：

select * from arr1;
#查询考了4或4科以上的学生的第二科成绩
select name,scores[1] from arr2 where size(scores) > 3;
#查询有第三科成绩的学生
select name from arr2 where scores[2] is not null;

8.2.4 想要一种效果：

每个人的每科成绩分别占一行的效果：

zhangsan        78
zhangsan        89
zhangsan        92
zhangsan        96
lisi            67
lisi            75
........

8.3. 展开函数的使用

8.3.1 简介

- explode：
    展开函数(UDTF函数中的一种),作用是：接受一个数据行，然后返回产生多个数据行  
- lateral view:虚拟表。
    会将UDTF函数生成的结果放到一个虚拟表中，然后这个虚拟表会和输入行进行join来达到数据聚合的目的

8.3.2 上案例：

- select explode(scores) score from arr2;
- select name,cj from arr2 lateral view explode(score) mytable as cj;
- 统计每个学生的总成绩：
select name,sum(cj) as totalscore from arr2 lateral view explode(score) mytable as cj 
group by name;

-- 写法2
select A.name,sum(A.cj)
from 
(select name,cj from arr2 lateral view explode(score) mytable as cj) A
group by A.name;

8.3.3 向array字段中写数据(动态加载)

8.3.3.1 准备数据

create table arr_temp
as
select name,cj from arr2 lateral view explode(score) score as cj;

8.3.3.2 借助collect_set函数，列转行函数，有去重效果。

create table if not exists arr3(
name string,
score array<string>
)
row format delimited 
fields terminated by ' '
collection items terminated by ','
;

将数据写成array格式：
insert into arr3
select name,collect_set(cj) from arr_temp group by name;

查询每个人的最后一科的成绩
select name,score[size(score)-1] lastsubject from arr3;

8.4. 复杂数据类型之map

8.4.1 定义格式如下：

create table tableName(
.......
colName map<T,T>
......
)

8.4.2 案例准备：

zhangsan chinese:90,math:87,english:63,nature:76
lisi chinese:60,math:30,english:78,nature:0
wangwu chinese:89,math:25,english:81,nature:9

create table if not exists map1(
name string,
score map<string,int>
)
row format delimited 
fields terminated by ' '
collection items terminated by ','
map keys terminated by ':'
;

load data local inpath './data/map1.txt' into table map1;

8.4.3 查询语句：

查询数学大于35分的学生的英语和自然成绩：
select 
m.name,
m.score['english'] ,
m.score['nature']
from map1 m
where m.score['math'] > 35
;

8.4.4 展开查询

- 展开效果
zhangsan    chinese     90
zhangsan    math    87
zhangsan    english     63
zhangsan    nature      76

- explode展开数据：
    select explode(score) as (m_class,m_score) from map1;

- 使用lateral view explode 结合查询：
    select name,m_class,m_score from map2 lateral view explode(score) score as  
    m_class,m_score;

8.4.5 将数据写入map字段中

将下面的数据格式
zhangsan        chinese 90
zhangsan        math    87
zhangsan        english 63
zhangsan        nature  76
lisi    chinese 60
lisi    math    30
lisi    english 78
lisi    nature  0
wangwu  chinese 89
wangwu  math    25
wangwu  english 81
wangwu  nature  9
转成：
zhangsan chinese:90,math:87,english:63,nature:76
lisi chinese:60,math:30,english:78,nature:0
wangwu chinese:89,math:25,english:81,nature:9

8.4.5.1 准备数据

create table map_temp
as
select name,m_class,m_score from map1 lateral view explode(score) score as m_class,m_score;

8.4.5.2 开始写：

第一步：将科目和成绩组合在一起，concat
select name,concat(m_class,':',m_score) as score from map_temp;

第二步: 将所有属于同一个人的商品组合在一起
select name,collect_set(concat(m_class,":",m_score)) 
from map_temp
group by name
;

第三步:将数组变成一个字符串concat_ws
select name,concat_ws(",",collect_set(concat(m_class,":",m_score)))
from map_temp
group by name
;

第四步:将字符串转成map 使用函数str_to_map(text, delimiter1, delimiter2)
text：是字符串
delimiter1：多个键值对之间的分隔符
delimiter2：key和value之间的分隔符

select
name,
str_to_map(concat_ws(",",collect_set(concat(m_class,":",m_score))),',',':')
from map_temp
group by name
;

第五步：存储准备的表中
create table map2 as
select
name,
str_to_map(concat_ws(",",collect_set(concat(m_class,":",m_score))),',',':') score
from map_temp
group by name
;

8.5. 复杂数据类型 struct

8.5.1 定义格式：

create table tableName(
........
colName struct<subName1:Type,subName2:Type,........>
........
)

8.5.2 案例准备：

zhangsan    90,87,63,76
lisi    60,30,78,0
wangwu  89,25,81,9

create table if not exists str1(
name string,
score struct<chinese:int,math:int,english:int,natrue:int>
)
row format delimited 
fields terminated by '\t'
collection items terminated by ','
;

导入数据：
load data local inpath './data/arr1.txt' into table str1;

8.5.3 查询数据：

查询数学大于35分的学生的英语和语文成绩：
select name,
score.english,
score.chinese
from str2
where score.math > 35
;

8.5.4 复杂数据类型案例

uid uname belong tax addr
1   xdd ll,lw,lg,lc,lz  wx:600,gongjijin:1200,shebao:450    北京,西城,中南海
2   lkq lw,lg,lc,lz,lq  wx:1000,gongjijin:600,shebao:320    河北,石家庄,中山路
3   zs  lw,lg,lc        wx:2000,gongjijin:300,shebao:10     江西,南昌,八一大道

create table t1(
uid int,
uname string,
belong array<string>,
tax map<string,int>,
addr struct<province:string,city:string,street:string>
)
row format delimited
fields terminated by '\t'
collection items terminated by ','
map keys terminated by ':'
;

load data local inpath './data/t1.txt' into table t1;

查询：下属个数大于4个，公积金小于1200，省份在河北的数据

select * from t1 where size(belong)>4 and tax['gongjijin']<1200 and addr.province='河北';

8.5.5 嵌套数据类型

举例说明：map<string,array<map<string,struct>>>
嵌套类型：所有元素分隔符都要自己去定义
默认分隔符：^A
输入方式:ctrl+V ctrl+A

第五节:常见函数

9.1 函数查看

# 查看hive的内置函数
show functions;
# 查看内置函数的详细信息
desc function array;

9.2 日期函数

1）当前系统时间函数：current_date()、current_timestamp()、unix_timestamp()

-- 函数1：current_date();      
    当前系统日期      格式："yyyy-MM-dd"
-- 函数2：current_timestamp(); 
    当前系统时间戳：    格式:"yyyy-MM-dd HH:mm:ss.ms"
-- 函数3：unix_timestamp();    
    当前系统时间戳 格式：距离1970年1月1日0点的秒数。

2）日期转时间戳函数：unix_timestamp()

格式：unix_timestamp([date[,pattern]])
案例：
select unix_timestamp('1970-01-01 0:0:0'); 
select unix_timestamp('1970-01-01 8:0:0'); 
select unix_timestamp('0:0:0 1970-01-01',"HH:mm:ss yyyy-MM-dd"); 
select unix_timestamp(current_date());

2）时间戳转日期函数：from_unixtime

语法：from_unixtime(unix_time[,pattern]) 
案例：
select from_unixtime(1574092800); 
select from_unixtime(1574096401,'yyyyMMdd'); 
select from_unixtime(1574096401,'yyyy-MM-dd HH:mm:ss'); 
select from_unixtime(0,'yyyy-MM-dd HH:mm:ss');
select from_unixtime(-28800,'yyyy-MM-dd HH:mm:ss');

3）计算时间差函数：datediff()、months_between()

格式：datediff(date1, date2) - Returns the number of days between date1 and date2
select datediff('2019-11-20','2019-11-01');

格式：months_between(date1, date2) - returns number of months between dates date1 and date2
select months_between('2019-11-20','2019-11-01');
select months_between('2019-10-30','2019-11-30');
select months_between('2019-10-31','2019-11-30');
select months_between('2019-11-00','2019-11-30');

4）日期时间分量函数：year()、month()、day()、hour()、minute()、second()

案例：
select year(current_date);
select month(current_date);
select day(current_date);
select year(current_timestamp);
select month(current_timestamp);
select day(current_timestamp);
select hour(current_timestamp);
select minute(current_timestamp);
select second(current_timestamp);

select dayofmonth(current_date);
select weekofyear(current_date)

5）日期定位函数：last_day()、next_day()

--月末:
select  last_day(current_date)
--下周一
select next_day(current_date,'thursday');

6）日期加减函数：date_add()、date_sub()、add_months()

格式：
date_add(start_date, num_days)
date_sub(start_date, num_days)
案例：
select date_add(current_date,1);
select date_sub(current_date,90);
select add_months(current_date,1);

定位案例：

--当月第1天: 
select date_sub(current_date,dayofmonth(current_date)-1)
--下个月第1天:
select  add_months(date_sub(current_date,dayofmonth(current_date)-1),1)

7. 字符串转时间：to_date()

（字符串必须为：yyyy-MM-dd格式）

select to_date('2017-01-01 12:12:12');

8）日期转字符串(格式化)函数：date_format

select date_format(current_timestamp(),'yyyy-MM-dd HH:mm:ss');
select date_format(current_date(),'yyyyMMdd');
select date_format('2017-01-01','yyyy-MM-dd HH:mm:ss');  

9.3 字符串函数

-- 小写函数：lower
-- 大写函数：upper
-- 统计字符串长度函数：length
-- 字符串拼接函数：concat
-- 字符串拼接函数：concat_ws --（指定分隔符）
-- 截取子串函数：substr

9.4类型转换函数

cast(value as type)

9.5 数学函数

round --四舍五入((42.3 =>42))    round(num,retain)
ceil --向上取整(42.3 =>43)
floor --向下取整(42.3 =>42)

9.6 其他函数

nvl()
isnull()
isnotnull()
case when  then ....when ...then.. else... end
if(p1,p2,p3)

第十节：窗口函数

1 gp1808 80   
2 gp1808 92
3 gp1808 84
4 gp1808 86
5 gp1808 88
6 gp1808 70
7 gp1808 98
8 gp1808 84
9 gp1808 86
10 gp1807 90
11 gp1807 92
12 gp1807 84
13 gp1807 86
14 gp1807 88
15 gp1807 80
16 gp1807 92
17 gp1807 84
18 gp1807 86
19 gp1805 80
20 gp1805 92
21 gp1805 94
22 gp1805 86
23 gp1805 88
24 gp1805 80
25 gp1805 92
26 gp1805 94
27 gp1805 86


create table if not exists classscore(
sid int,
cno string,
score int
)
row format delimited
fields terminated by ' '
;

load data local inpath './data/classscore.txt' into table classscore;

10.1 窗口函数简介

聚合函数是将多行数据按照规则聚集为一行。有些时候我们需要既显示聚合前的数据又要显示聚合后的数据，基于这些需求就引入了窗口函数，来做一些分析类的工作。

窗口函数也称为OLAP函数（分析函数），这种分析函数用于计算基于组的各种聚合值，它和聚合函数不同的是，对于每个组返回多行(聚合函数每个组返回一行)。

OVER()函数用于定义数据窗口大小，决定了函数处理数据的范围。这种窗口会随着行的变化而变化

窗口函数兼具分组和排序两种功能：

- distribute by 分组后的记录集合称为窗口，决定了聚合函数的范围
- sort by 会让输入的数据排序
- 可以使用partition by + order by 去代替

10.2 排名函数 搭配窗口函数

10.2.1 row_number()：没有并列名次情况，顺序递增

效果如下：
98      1
97      2
97      3
96      4
95      5
95      6

row_number()的常用语法：

1. row_number() over(partition by colName1 order by colName2 [asc|desc]);
    按照partition by指定得字段进行分组
    按照order by指定得字段进行组内排序
2. row_number() over(distribute by colName1 sort by colName2);
    同上
3. row_number() over(order by colName1)
    按照order by 指定字段进行排序。注意：整张表为一组
4. row_number() over(sort by colName1)
    按照sort by 指定字段进行排序。注意：整张表为一组

10.2.2 rank()：有并列名次情况，顺序跳跃递增

效果如下：
98      1
97      2
97      2
96      4
95      5
95      5
94      7

rank() over(distribute by colName1 sort by colName1) 
rank() over()

10.2.3 dense_rank()有并列名次情况，顺序递增

效果如下：
98      1
97      2
97      2
96      3
95      4
95      4
94      5

dense_rank() over(distribute by colName1 sort by colName1) 
dense_rank()

10.2.4 案例练习

练习1：在一个语句中显示三种排名效果。 
练习2：取每一种排序效果的前三名
select sid,cno,score,row_number() over(partition by cno order by score desc) from classscore;
select sid,cno,score,rank() over(partition by cno order by score desc) from classscore;
select sid,cno,score,dense_rank() over(partition by cno order by score desc) from classscore;
--查询每个班级的前三名
select * from 
(select sid,cno,score,dense_rank() over(partition by cno order by score desc)  rk from classscore) A where A.rk<4;

10.3 聚合函数搭配窗口函数

10.3.1 语法结构

1. max(colName) over(distribute by colName1 sort by colName1)
2. min(colName) over(distribute by colName1 sort by colName1)
3. sum(colName) over(distribute by colName1 sort by colName1)
4. avg(colName) over(distribute by colName1 sort by colName1)
5. count(colName) over(distribute by colName1 sort by colName1)

10.3.2 案例演示

-- 查询每个班级的最高成绩和最低成绩--使用group by
    select cno,max(score) from classscore group by cno;
-- 查询每个班级的所有成绩及最高成绩、最低成绩
select sid,cno,score,max(score) over(partition by cno) max_s,
min(score) over(partition by cno) min_s
from classscore;
-- 统计每个班级的总成绩，和平均成绩
select cno,sum(score),avg(score) from classscore group by cno;
select cno,sum(score) over(partition by cno),avg(score) over(partition by cno) from classscore;
select distinct A.* from
(select cno,sum(score) over(partition by cno),avg(score) over(partition by cno) from classscore) A;

准备数据：score.txt 查询每次考试的所有成绩及其最高、最低成绩(--多种写法)

1       78      2019-08-03
2       76      2019-08-03
3       92      2019-08-03
4       86      2019-08-03
5       49      2019-08-03
6       60      2019-08-03
7       95      2019-08-03
8       71      2019-08-03
9       52      2019-08-03
10      47      2019-08-03
11      94      2019-08-03
12      87      2019-08-03
13      76      2019-08-03
14      66      2019-08-03
15      60      2019-08-03
16      88      2019-08-03
17      73      2019-08-03
18      75      2019-08-03
19      88      2019-08-03
1       68      2019-08-10
2       62      2019-08-10
3       80      2019-08-10
4       61      2019-08-10
5       60      2019-08-10
6       62      2019-08-10
7       91      2019-08-10
8       62      2019-08-10
9       27      2019-08-10
10      71      2019-08-10
11      86      2019-08-10
12      78      2019-08-10
13      77      2019-08-10
14      29      2019-08-10
15      60      2019-08-10
16      61      2019-08-10
17      33      2019-08-10
18      67      2019-08-10
19      83      2019-08-10
1       71      2019-08-17
2       65      2019-08-17
3       86      2019-08-17
4       73      2019-08-17
5       60      2019-08-17
6       68      2019-08-17
7       88      2019-08-17
8       57      2019-08-17
9       63      2019-08-17
10      61      2019-08-17
11      92      2019-08-17
12      81      2019-08-17
13      50      2019-08-17
14      68      2019-08-17
15      76      2019-08-17
16      74      2019-08-17
17      57      2019-08-17
18      72      2019-08-17
19      85      2019-08-17
1       70      2019-08-22
2       68      2019-08-22
3       80      2019-08-22
4       42      2019-08-22
5       41      2019-08-22
6       60      2019-08-22
7       90      2019-08-22
8       63      2019-08-22
9       60      2019-08-22
10      70      2019-08-22
11      98      2019-08-22
12      71      2019-08-22
13      61      2019-08-22
14      61      2019-08-22
15      60      2019-08-22
16      61      2019-08-22
17      45      2019-08-22
18      63      2019-08-22
19      90      2019-08-22

create table if not exists score_time(
sid int,
score int,
dt string
)
row format delimited
fields terminated by ' '
;

load data local inpath './data/score_time.txt' into table score_time;

10.4 window子句

默认的窗口范围是起始行到当前行，可以使用window子句去定义窗口范围 这种window子句将分组的数据进行了更加细粒度的划分 相对于当前行 。

10.4.1 关键字

preceding:向前 
following:向后 
current row:当前行 
unbounded:起点(没有边界) 
unbounded preceding：向上没有边界 
unbounded following：向下没有边界

10.4.2 案例演示

select name,orderdate,cost, 
sum(cost) over() as sample1,--所有行相加 
sum(cost) over(partition by name) as sample2,--按name分组，组内数据相加 
sum(cost) over(partition by name order by orderdate) as sample3,--按name分组，组内数据累加 
sum(cost) over(partition by name order by orderdate rows between UNBOUNDED PRECEDING and current row )  as sample4 ,--和sample3一样,由起点到当前行的聚合 
sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING   and current row) as sample5, --当前行和前面一行做聚合 
sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING   AND 1 FOLLOWING  ) as sample6,--当前行和前边一行及后面一行 
sum(cost) over(partition by name order by orderdate rows between current row and UNBOUNDED FOLLOWING ) as sample7 --当前行及后面所有行 
from t_window;

10.5 first_value与last_value

10.5.1 解析

 first_value：取分组内排序后，第一个值 
 last_value：取分组内排序后，最后一个值

10.5.2 案例演示

--  第一次考试成绩和最后一次考试成绩.按照id进行分组，按照日期进行排序 
select sid,score,dt,first_value(score) over(partition by sid order by dt),
last_value(score) over(partition by sid order by dt)
from score_time;-- 相对于当前行来说，当前行即最后一行

select sid,score,dt,first_value(score) over(partition by sid order by dt),
last_value(score) over(partition by sid order by dt rows between current row and unbounded following)
from score_time;


第一次登录时间和最后一次登录时间.按照id进行分组，按照日期进行排序 
每次考试成绩的最高和最低分。按照日期分组，按照分数排序

10.6 lag与lead10.6 lag与lead

10.6.1 解析

lag(col,n) -- 取出前第n行的数据（取一行，第n行） 
lead(col,n) -- 取出后第n行的数据（取一行，第n行）

10.6.2 案例演示

-- 需求：求每位学员的每次考试成绩与上一次的成绩对比
select sid,score,lag(score,1) over(partition by sid order by dt) from score_time;
-- 需求：求每位学员的每次考试成绩与大上次的成绩对比
select sid,score,lag(score,2) over(partition by sid order by dt) from score_time;

select sid,score,lag(score,2) over(partition by sid order by dt),
lead(score,1) over(partition by sid order by dt)
from score_time;

-- 5分钟连续点击100次的用户
dt                  id  url
2019-08-22 19:00:01,1,www.baidu.com
2019-08-22 19:01:01,1,www.baidu.com
2019-08-22 19:02:01,1,www.baidu.com
2019-08-22 19:03:01,1,www.baidu.com

nvl(col,"")第一个值为空可以给一个默认值
coalesce(col1,col2,col3...)返回第一个不为空的