HIVE总结

一：hive作用

Hive是基于Hadoop的一个数据仓库工具，可以将结构化的数据文件映射为一张表，并提供类SQL查询功能。

Hive本质：将HQL转化成MapReduce程序

（1）Hive处理的数据存储在HDFS

（2）Hive分析数据底层的实现是MapReduce

（3）执行程序运行在Yarn上

1.1：Hive优缺点

1>Hive支持用户自定义函数，用户可以根据自己的需求来实现自己的函数;

缺点：

1）Hive的HQL表达能力有限

（1）迭代式算法无法表达

（2）数据挖掘方面不擅长，由于MapReduce数据处理流程的限制，效率更高的算法却无法实现。

2）Hive的效率比较低 (Hive调优比较困难，粒度较粗)

1.2:Hive和 数据库比较

从结构上来看，Hive 和数据库除了拥有类似的查询语言，再无类似之处

1.3:数据更新

Hive是针对数据仓库应用设计的，而数据仓库的内容是读多写少的。因此，Hive中不建议对数据的改写，所有的数据都是在加载的时候确定好的。

1.4:执行延迟

Hive 在查询数据的时候，由于没有索引，需要扫描整个表，因此延迟较高。

二：HIve使用

2.1：本地模式

set hive.exec.mode.local.auto=true;  //开启本地mr

注意：当切换成本地模式，可能回报一个 内存溢出 的错误，因为本地模式默认只给我们分配 256M 内存。可以通过修改 hive-env.sh.template 

2.2：一般操作

 2.3：客户端查看hdfs

 

 三:Hive中的数据类型

基本数据类型和集合数据类型

 

 四：Hive操作

load data local inpath '/opt/module/hive/datas/test.txt' into table 表名;

 Hdfs上创建一个库放到指定路径下

 

 

 4.1：外部表

 

 五：Hive操作表

 5.1：上传文件到HDFS下的表

hadoop fs -put student.txt /user/hive/warehouse/student

-- 查看当前表的详情                desc student;

desc formatted student; -- 查看当前表的详细信息

 

 

 

 五：DML数据操作

 

 5.2：通过查询语句向表中插入数据（Insert）

 5.3：导入数据的方式

-- 3. 导入数据
 --- 本地不覆盖导入
 load data local inpath '/opt/module/hive-3.1.2/datas/ztudent.txt'
 into table student;
 --- 本地覆盖导入
 load data local inpath '/opt/module/hive-3.1.2/datas/student1.txt'
 overwrite
 into table student;

 --- HDFS导入
 load data inpath '/student/ztudent.txt'
 into table student;
 --- 注意：从本地导入数据属于cp行为，如果从HDFS导入属于mv行为


 1.3 通过查询语句向表中插入数据（Insert）
 -- 1. 建表
 create table stu1(id int, name string)
 row format delimited fields terminated by '\t';

 -- 2. 基本插入数据
 insert into table  stu1 values(1,'wangwu'),(2,'zhaoliu');

 -- 3. 基本模式插入（根据单张表查询结果）
 insert into table stu1 select id, name from student;

 1.4 查询语句中创建表并加载数据（As Select）
 create table stu2
 as select id, name from student;

 1.5 创建表时通过Location指定加载数据路径
 create table stu3(id int, name string)
 row format delimited fields terminated by '\t' 
 location '/student';

5.4：Insert数据导出(通常用于数据迁移)

2.1 Insert导出
 --1. 将查询的结果导出到本地
 insert overwrite local directory '/opt/module/hive-3.1.2/datas/export_data'
 select * from student;

 --2. 将查询的结果导出到本地(对数据格式约束)
 insert overwrite local directory '/opt/module/hive-3.1.2/datas/export_data'
 row format delimited fields terminated by "\t"
 select * from student;

 --3. 将查询的结果导出到HDFS上(没有local)
 insert overwrite directory '/export_data'
 row format delimited fields terminated by "\t"
 select * from student;

 2.2 Export导出到HDFS上 & Import数据到指定Hive表中
 -- export导出到HDFS
 export table student to '/export/student';

 -- import 导入Hive的表中(导数据的过程中会根据元数据进行建表)
 import table bucunzai from '/export/student';
  
 -- 注意 Export & Import 使用场景：数据迁移的时候常用！！！

 2.3 清除表中数据（Truncate）
 注意：当清楚数据的表是内部表的时候才允许清楚，外部表不允许清除数据
 truncate table student;

六：Hive函数

6.1：常用函数

HiveSQL语法有限制   

 6.2：SQL的执行顺序

 案例一：

 Having语句 (只能和 group by 结合使用)
-- 计算每个部门最高薪水的那个哥们，并且薪资大于等于3000

使用group by，select后面的 字段只能是分组字段和 要求的值，如平均工资；

select 
t1.deptno,
t2.ename,
t1.max_sal
from 
(
 select 
 deptno,
 max(sal) as max_sal
 from emp 
 group by deptno having max_sal >= 3000
) t1 join emp t2
on t1.deptno = t2.deptno
where t1.max_sal = t2.sal
;

 

 七：JOIN

(emp 可以看成左表 dept可以看成右表  表连接的形式与key有关   )

emp 和 dept的交集数据：     join

emp的全部数据 和 dept匹配到的数据    ：  left join 

dept 的全部数据 和 emp 匹配到的数据   ：   right join   

获取emp独有的数据   left join    where d.deptno is null

获取dept独有的数据   right join    where e.deptno is null

获取 emp 和dept 的全部数据(全连接)   （  union 传统的方式    full join 进行全连接(Hive支  持，传统SQL不支持，oracle中支持)）

获取emp和dept各自独有的数据    full join  (

where e.deptno is null or d.deptno is null   )

 full join 进行全连接(Hive支持，传统SQL不支持，oracle中支持)

4.1 emp 和 dept的交集数据
select 
e.empno,e.ename,e.deptno,d.deptno,d.dname
from emp e 
join
dept d 
on e.deptno = d.deptno; 

+----------+----------+-----------+-----------+-------------+
| e.empno  | e.ename  | e.deptno  | d.deptno  |   d.dname   |
+----------+----------+-----------+-----------+-------------+
| 7369     | SMITH    | 20        | 20        | RESEARCH    |
| 7499     | ALLEN    | 30        | 30        | SALES       |
| 7521     | WARD     | 30        | 30        | SALES       |
| 7566     | JONES    | 20        | 20        | RESEARCH    |
| 7654     | MARTIN   | 30        | 30        | SALES       |
| 7698     | BLAKE    | 30        | 30        | SALES       |
| 7782     | CLARK    | 10        | 10        | ACCOUNTING  |
| 7788     | SCOTT    | 20        | 20        | RESEARCH    |
| 7839     | KING     | 10        | 10        | ACCOUNTING  |
| 7844     | TURNER   | 30        | 30        | SALES       |
| 7876     | ADAMS    | 20        | 20        | RESEARCH    |
| 7900     | JAMES    | 30        | 30        | SALES       |
| 7902     | FORD     | 20        | 20        | RESEARCH    |
| 7934     | MILLER   | 10        | 10        | ACCOUNTING  |
+----------+----------+-----------+-----------+-------------+


4.2 emp的全部数据 和 dept匹配到的数据
select
e.empno,e.ename,e.deptno,d.deptno,d.dname
from emp e 
left join 
dept d 
on e.deptno = d.deptno;

+----------+----------+-----------+-----------+-------------+
| e.empno  | e.ename  | e.deptno  | d.deptno  |   d.dname   |
+----------+----------+-----------+-----------+-------------+
| 7369     | SMITH    | 20        | 20        | RESEARCH    |
| 7499     | ALLEN    | 30        | 30        | SALES       |
| 7521     | WARD     | 30        | 30        | SALES       |
| 7566     | JONES    | 20        | 20        | RESEARCH    |
| 7654     | MARTIN   | 30        | 30        | SALES       |
| 7698     | BLAKE    | 30        | 30        | SALES       |
| 7782     | CLARK    | 10        | 10        | ACCOUNTING  |
| 7788     | SCOTT    | 20        | 20        | RESEARCH    |
| 7839     | KING     | 10        | 10        | ACCOUNTING  |
| 7844     | TURNER   | 30        | 30        | SALES       |
| 7876     | ADAMS    | 20        | 20        | RESEARCH    |
| 7900     | JAMES    | 30        | 30        | SALES       |
| 7902     | FORD     | 20        | 20        | RESEARCH    |
| 7934     | MILLER   | 10        | 10        | ACCOUNTING  |
| 6666     | MILLER   | 60        | NULL      | NULL        |
+----------+----------+-----------+-----------+-------------+


4.3 dept 的全部数据 和 emp 匹配到的数据
select
e.empno,e.ename,e.deptno,d.deptno,d.dname
from emp e 
right join 
dept d 
on e.deptno = d.deptno;
+----------+----------+-----------+-----------+-------------+
| e.empno  | e.ename  | e.deptno  | d.deptno  |   d.dname   |
+----------+----------+-----------+-----------+-------------+
| 7782     | CLARK    | 10        | 10        | ACCOUNTING  |
| 7839     | KING     | 10        | 10        | ACCOUNTING  |
| 7934     | MILLER   | 10        | 10        | ACCOUNTING  |
| 7369     | SMITH    | 20        | 20        | RESEARCH    |
| 7566     | JONES    | 20        | 20        | RESEARCH    |
| 7788     | SCOTT    | 20        | 20        | RESEARCH    |
| 7876     | ADAMS    | 20        | 20        | RESEARCH    |
| 7902     | FORD     | 20        | 20        | RESEARCH    |
| 7499     | ALLEN    | 30        | 30        | SALES       |
| 7521     | WARD     | 30        | 30        | SALES       |
| 7654     | MARTIN   | 30        | 30        | SALES       |
| 7698     | BLAKE    | 30        | 30        | SALES       |
| 7844     | TURNER   | 30        | 30        | SALES       |
| 7900     | JAMES    | 30        | 30        | SALES       |
| NULL     | NULL     | NULL      | 40        | OPERATIONS  |
+----------+----------+-----------+-----------+-------------+

4.4 获取emp独有的数据
select
e.empno,e.ename,e.deptno,d.deptno,d.dname
from emp e 
left join 
dept d 
on e.deptno = d.deptno
where d.deptno is null
;
+----------+----------+-----------+-----------+----------+
| e.empno  | e.ename  | e.deptno  | d.deptno  | d.dname  |
+----------+----------+-----------+-----------+----------+
| 6666     | MILLER   | 60        | NULL      | NULL     |
+----------+----------+-----------+-----------+----------+

4.5 获取dept独有的数据
select
e.empno,e.ename,e.deptno,d.deptno,d.dname
from emp e 
right join 
dept d 
on e.deptno = d.deptno
where e.deptno is null
;

+----------+----------+-----------+-----------+-------------+
| e.empno  | e.ename  | e.deptno  | d.deptno  |   d.dname   |
+----------+----------+-----------+-----------+-------------+
| NULL     | NULL     | NULL      | 40        | OPERATIONS  |
+----------+----------+-----------+-----------+-------------+


4.6 获取 emp 和dept 的全部数据(全连接)
-- 实现方式一： union 传统的方式
select
e.empno,e.ename,e.deptno,d.deptno,d.dname
from emp e 
left join 
dept d 
on e.deptno = d.deptno
union
select
e.empno,e.ename,e.deptno,d.deptno,d.dname
from emp e 
right join 
dept d 
on e.deptno = d.deptno;

+------------+------------+-------------+------------+
| _u1.empno  | _u1.ename  | _u1.deptno  | _u1.dname  |
+------------+------------+-------------+------------+
| NULL       | NULL       | NULL        | 40         |
| 6666       | MILLER     | 60          | NULL       |
| 7369       | SMITH      | 20          | 20         |
| 7499       | ALLEN      | 30          | 30         |
| 7521       | WARD       | 30          | 30         |
| 7566       | JONES      | 20          | 20         |
| 7654       | MARTIN     | 30          | 30         |
| 7698       | BLAKE      | 30          | 30         |
| 7782       | CLARK      | 10          | 10         |
| 7788       | SCOTT      | 20          | 20         |
| 7839       | KING       | 10          | 10         |
| 7844       | TURNER     | 30          | 30         |
| 7876       | ADAMS      | 20          | 20         |
| 7900       | JAMES      | 30          | 30         |
| 7902       | FORD       | 20          | 20         |
| 7934       | MILLER     | 10          | 10         |
+------------+------------+-------------+------------+

-- 方式二： full join 进行全连接(Hive支持，传统SQL不支持，oracle中支持)
select
e.empno,e.ename,e.deptno,d.deptno,d.dname
from emp e 
full join 
dept d 
on e.deptno = d.deptno;

4.7  获取emp和dept各自独有的数据
select
e.empno,e.ename,e.deptno,d.deptno,d.dname
from emp e 
full join 
dept d 
on e.deptno = d.deptno
where e.deptno is null or d.deptno is null
;
+----------+----------+-----------+-----------+-------------+
| e.empno  | e.ename  | e.deptno  | d.deptno  |   d.dname   |
+----------+----------+-----------+-----------+-------------+
| NULL     | NULL     | NULL      | 40        | OPERATIONS  |
| 6666     | MILLER   | 60        | NULL      | NULL        |
+----------+----------+-----------+-----------+-------------+

八：Group By（）Having语句 和 LIke

GROUP BY语句通常会和聚合函数一起使用，按照一个或者多个列结果进行分组，然后对每个组执行聚合操作。

 Having语句

 九：order by()   sort by()  Distribute By() Cluster By()

set mapreduce.job.reduces=3;

set hive.exec.mode.local.auto=false;

运行结果

-- 小结：Hive中如果单独使用sort by 它也能进行局部排序，但是每个文件中的数据是随机进入，如果
-- 如果 将 distribute by + sort by 使用   就完全和Hadoop中的HashPartitioner相吻合！！！

十：分区表(Hive中分区就是分目录)

Hive中没有索引的概念

分区表在查询的时候要加上分区字段

 针对庞大的数据集，存储的时候考虑用分区表表存储，只要按照一定规律
--       进行分区，将来查询的时候就可以使用分区字段结合查询条件小范围匹配数据
--       最终避免全表扫描，从而提升查询效率。

即可以将表的分区字段看成表的普通字段 

-- 查看分区详情
show partitions dept_par;

-- 准备数据 导入
load data [local] inpath '数据的path' [overwrite] into table student
[partition (partcol1=val1,…)];

load data local inpath '/opt/module/hive-3.1.2/datas/dept_20200401.log' into table dept_par
partition (day='20200401');

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

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

注意：再给分区表中导入数据的时候一定要加上 分区 属性partition(day='20200403')

2. 增加分区
--增加单个分区
alter table dept_par add partition(day='20200404');
--增加多个分区
alter table dept_par add partition(day='20200405') partition(day='20200406');
3. 删除分区
alter table dept_par drop partition(day='20200404');
alter table dept_par drop partition(day='20200405'), partition(day='20200406');

二级分区：

二级分区：
1. 创建分区表 
-- 建表
create table dept_par1(
deptno int, dname string, loc string
)
partitioned by (day string, hour string)
row format delimited fields terminated by '\t';


-- 准备数据 导入
load data [local] inpath '数据的path' [overwrite] into table student
[partition (partcol1=val1,…)];

load data local inpath '/opt/module/hive-3.1.2/datas/dept_20200401.log' into table dept_par1
partition (day='20200401', hour='00');

load data local inpath '/opt/module/hive-3.1.2/datas/dept_20200402.log' into table dept_par1
partition (day='20200401', hour='01');

load data local inpath '/opt/module/hive-3.1.2/datas/dept_20200403.log' into table dept_par1
partition (day='20200401', hour='02');

--思考：分区是不是越多越好？
-- 分区越多导致文件的数量就会增加，在HDFS尽量避免出现大量小文件，在正常使用中一般做到2级分区就可以啦！

10.1：分区表和数据产生关联的三种方式

分区表和数据产生关联的三种方式：(分区表和普通表不一样需要此三种操作才能找到对应的元数据)

方式一：上传数据后修复  （msck repair table dept_par2;）

方式二：上传数据后 添加分区     

(alter table dept_par add partition(day='20200401');)

方式三：创建文件夹后load数据到分区

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

10.2：动态分区

 相关参数 ：

 注意通过insert into的方式给分区表中插入数据一定要指定 partition(loc)

-- 进行动态分区（动态的往 dept_par_dy 插入数据）
insert into table dept_par_dy partition(loc)
select deptno, dname, loc from dept;

10.3:分桶表(分桶操作实际上就是Hadoop中的分区)

面对海量数据表查询效率低下，此时就考虑缩小数据的管理范围，
--       从而达到避免查询的时候进行全表扫描，分桶操作实质就是Hadoop
--       中默认规则的分区操作

分桶表在查询的时候要加上分桶字段，类似于分区表在查询的时候要加上分区字段

注意:在进行分桶操作的时候要设置   set mapreduce.job.reduces=-1;  (因为分区分的是分目录(分区上传文件的时候是每个区的文件上传到对应的目录下边)，分桶分的是分文件(上传文件的时候只上传一个文件，hive会按照指定的分桶字段创建不同的桶))

创建分桶表 
CREATE TABLE  table_name 
[(col_name data_type [COMMENT col_comment], ...)] 
[CLUSTERED BY (col_name, col_name, ...) 
INTO num_buckets BUCKETS]

-- 建表
create table 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.txt' 
into table stu_bucket;

10.4:抽样查询 

抽样查询取是按照id进行随机分桶，然后选择第x个桶，并不是真正意义上按照分桶的规则进行划分，然后获取第x个桶的数据，而是随机的。

四、 抽样查询
-- 语法: TABLESAMPLE(BUCKET x OUT OF y ON 分桶字段) 

select * from stu_bucket tablesample(bucket 1 out of 4 on id);

select * from emp tablesample(bucket 1 out of 4 on empno);

十一：Hive中的函数(思路：按照需求缺什么 补什么)

2> CASE WHEN THEN ELSE END（逻辑判断函数）

1). 按照部门分组,再按照性别分组，求统计
select 
dept_id,
sex,
count(name) as num
from emp_sex
group by dept_id,sex;
+----------+------+------+
| dept_id  | sex  | num  |
+----------+------+------+
| A        | 女    | 1    |
| A        | 男    | 2    |
| B        | 女    | 2    |
| B        | 男    | 1    |
+----------+------+------+

2). 实现目标结果数据
select 
dept_id,
sum(CASE sex WHEN '男' THEN 1 ELSE 0 END) as man,
sum(CASE sex WHEN '女' THEN 1 ELSE 0 END) as women
from emp_sex
group by dept_id;
+----------+------+--------+
| dept_id  | man  | women  |
+----------+------+--------+
| A        | 2    | 1      |
| B        | 1    | 2      |
+----------+------+--------+
3). 扩展 if() 函数

select 
dept_id,
sum(if(sex='男',1,0)) as man,
sum(if(sex='女',1,0)) as women
from emp_sex
group by dept_id;
+----------+------+--------+
| dept_id  | man  | women  |
+----------+------+--------+
| A        | 2    | 1      |
| B        | 1    | 2      |
+----------+------+--------+

 11.1：函数第二波

 COLLECT_SET(col)：函数只接受基本数据类型，它的主要作用是将某字段的值进行去重汇总，产生array类型字段。

1). 把 constellation 和 blood_type 拼接起来
select 
concat_ws(',',constellation,blood_type) as cb,
name
from person_info; --> t1    因为第二个表查询的时候用到了第一个表中的name，因此要把那么查出来
+--------+-------+
|   cb   | name  |
+--------+-------+
| 白羊座,A  | 孙悟空   |
| 射手座,A  | 大海    |
| 白羊座,B  | 宋宋    |
| 白羊座,A  | 猪八戒   |
| 射手座,A  | 凤姐    |
| 白羊座,B  | 苍老师   |
+--------+-------+

2). 根据 cb 字段进行分组
select 
t1.cb,
concat_ws('|',collect_set(t1.name))
from
(
 select 
 concat_ws(',',constellation,blood_type) as cb,
 name
 from person_info
) t1
group by t1.cb;
+--------+----------+
| t1.cb  |   _c1    |
+--------+----------+
| 射手座,A  | 大海|凤姐    |
| 白羊座,A  | 孙悟空|猪八戒  |
| 白羊座,B  | 宋宋|苍老师   |
+--------+----------+

侧写函数LATERAL VIEW 语法 ：(因为SQL语法局限 需要 LATERAL VIEW）

explode 一进多出函数， 侧写函数代表虚拟表的过程；执行过程

炸开用explode    对应key 用侧写函数LATERAL VIEW

EXPLODE(col)：将hive一列中复杂的array或者map结构拆分成多行。
select 
explode(split(category,',')) as category_name,
movie
from movie_info;
+------+
| col  |
+------+
| 悬疑   |
| 动作   |
| 科幻   |
| 剧情   |
| 悬疑   |
| 警匪   |
| 动作   |
| 心理   |
| 剧情   |
| 战争   |
| 动作   |
| 灾难   |
+------+

select 
movie,
category_name
from movie_info
lateral view explode(split(category,',')) movie_info_tmp as category_name
;

+--------------+----------------+
|    movie     | category_name  |
+--------------+----------------+
| 《疑犯追踪》       | 悬疑             |
| 《疑犯追踪》       | 动作             |
| 《疑犯追踪》       | 科幻             |
| 《疑犯追踪》       | 剧情             |
| 《Lie to me》  | 悬疑             |
| 《Lie to me》  | 警匪             |
| 《Lie to me》  | 动作             |
| 《Lie to me》  | 心理             |
| 《Lie to me》  | 剧情             |
| 《战狼2》        | 战争             |
| 《战狼2》        | 动作             |
| 《战狼2》        | 灾难             |
+--------------+----------------+

11.2：函数第三波 

窗口函数就是针对SQL中的限制重新开辟一个处理数据空间，
--       可以灵活的的控制窗口的大小，窗口的大小取决于数据的记录的条数。

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

-- 开窗函数此处应用  大意是 按照名字分组 开辟了一个新的统计名字个数的空间，这个空间的范围是 从每一个组中的起始位置到最后位置  即每一个组统计一次。 

WINDOW 规范默认为 
ROW BETWEEN UNBOUNDED PRECEDING(从前面的起点) AND UNBOUNDED FOLLOWING(到后面的终点).

count(name) over() totalNum 统计姓名的个数  此时如果不分组的情况 会对相同的名字进行个数统计，因此需要加上 group by()  而over() 是在group by()之后，

开辟了新的统计组个数的空间(即要统计总人数就要分组

select
name ,
count(name) 
from business
where substring(orderdate,0,7) = '2017-04'
group by name ;

name	_c1
jack	1
mart	4

)但是分组的结果会重复统计相同的人名，此时开窗的作用就是开辟了新的统计人数的空间，即一个组统计一次；

1) 根据需求的日期过滤数据
select 
orderdate ,
name
from business 
where substring(orderdate,0,7) = '2017-04' ;

orderdate	name
2017-04-06	jack
2017-04-08	mart
2017-04-09	mart
2017-04-11	mart
2017-04-13	mart

2). 根据 name 进行分组,然后使用开窗函数重新处理分组后的结果 求统计
    -- (因为用到了分组，而分组要查询的字段 是分组字段  因此 查询字段不能带日期)

select
name,
count(name) over() totalNum
from business
where substring(orderdate,0,7) = '2017-04'
group by name ;
-- 开窗函数此处应用  大意是 按照名字分组 开辟了一个新的统计名字个数的空间，这个空间的范围是
-- 从每一个组中的起始位置到最后位置 。 


如果
select
name,
count(name) over() totalNum
from business
where substring(orderdate,0,7) = '2017-04'
没有开窗语句也没有order by语句 则 默认窗口大小从起点到终点
name	totalnum
mart	5
mart	5
mart	5
mart	5
jack	5

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

目的是统计  顾客的购买明细(每个顾客每个月的花费金额)  及  月购买总额(一个月内所有顾客花费的总金额)  因此SQL(

sum(cost) over(partition by substring(orderdate,0,7)) costByMonth 表示在求一个月内所有顾客花费的总金额的时候 重新开辟了一个新的空间，新的空间的大小是按照substring(orderdate,0,7)一个月进行分区汇总的。

（partition by 可以控制新空间的大小)只有窗口语句没有order by语句 此时窗口的大小是每一个分区内的起点到终点

select 
name,
cost,
orderdate,
sum(cost) over(partition by substring(orderdate,0,7)) total_cost
from business;

需求三：查询 每个顾客的购买明细 及 (每个)月购买总额

select
name,
cost,
orderdate,
sum(cost) over(partition by substring(orderdate,0,7),name) oneCostByMonth
from business ;

(sum(cost) over(partition by substring(orderdate,0,7),name) oneCostByMonth)

开窗函数新空间的大小由按照月份和顾客确定。 只有窗口语句没有order by语句 此时窗口的大小是每一个分区内的起点到终点

需求四：上述的场景, 将每个顾客的cost按照日期进行累加

-- 方式一
select
name,
cost,
orderdate,
sum(cost) over(partition by name order by orderdate) oneCustTotal
from business ;

-- 方式二

select
name,
cost,
orderdate,
sum(cost) over(partition by name order by orderdate 
rows between UNBOUNDED PRECEDING and current row
) oneCustTotal
from business ;

  开窗Sql代码

（sum(cost) over(partition by name order by orderdate) oneCustTotal）

每个顾客的cost按照日期进行累加，即开窗的新的空间是按照顾客名字进行分区，按照日期进行累加，因此要排序，开窗规则的默认窗口是从起始行累加到当前行。

(sum(cost) over(partition by name order by orderdate rows between UNBOUNDED PRECEDING and current row) 开窗规则的默认窗口是从起始行累加到当前行。

注意： 
当 ORDER BY 指定时缺少 WINDOW 子句，WINDOW 窗口大小规范默认为 
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

当 ORDER BY 和 WINDOW 子句都缺失时 即单纯的over()，WINDOW 窗口大小规范默认为 
ROW BETWEEN UNBOUNDED PRECEDING 起点 AND UNBOUNDED FOLLOWING. 终点

-- 需求五：查询每个顾客上次的购买时间和下一次购买时间
LAG(col,n,default_val)：往前第n行数据
LEAD(col,n, default_val)：往后第n行数据

select
name ,
orderdate,
lag(orderdate,1,'startTime') over(partition by name order by orderdate)  preTime,
lead(orderdate,1,'endTime') over(partition by name order by orderdate)  nextTime
from business ;
name	orderdate	pretime	nexttime
jack	2017-01-01	startTime	2017-01-05
jack	2017-01-05	2017-01-01	2017-01-08
jack	2017-01-08	2017-01-05	2017-02-03
jack	2017-02-03	2017-01-08	2017-04-06
jack	2017-04-06	2017-02-03	endTime
mart	2017-04-08	startTime	2017-04-09
mart	2017-04-09	2017-04-08	2017-04-11
mart	2017-04-11	2017-04-09	2017-04-13
mart	2017-04-13	2017-04-11	endTime
neil	2017-05-10	startTime	2017-06-12
neil	2017-06-12	2017-05-10	endTime
tony	2017-01-02	startTime	2017-01-04
tony	2017-01-04	2017-01-02	2017-01-07
tony	2017-01-07	2017-01-04	endTime

开窗函数是作为对其它函数在统计的时候由于SQL的局限，进行辅助作用的 辅助主函数的，因此不能单独存在； 因此以下写法不正确，应该将over()开窗函数放在主函数的后边。且开窗函数不能单独使用(以下两种写法都是错误的)

select
name ,
orderdate,
over(partition by name order by orderdate)  lag(orderdate,1,'startTime')  preTime,
over(partition by name order by orderdate)  lead(orderdate,1,'endTime')  nextTime
from business ;


开窗函数不能单独使用
select
name ,
orderdate,
over(partition by name order by orderdate)  preTime,
over(partition by name order by orderdate)  nextTime
from business ;

需求六：查询前20%时间的订单信息

 NTILE(n)：把有序窗口的行分发到指定数据的组中， NTILE(n) n为需要的组数各个组有默认有编号，编号从1开始，对于每一行，
NTILE函数返回此行所属的组的编号。注意：n必须为int类型。

select
t1.name,
t1.orderdate,
t1.cost
from
(
select
name,
orderdate,
cost,
-- 分成5组 按照日期排序  
ntile(5) over(order by orderdate) as group_id
from business 
) t1 
where t1.group_id = 1;

t1.name	t1.orderdate	t1.cost
jack	2017-01-01	10
tony	2017-01-02	15
tony	2017-01-04	29

 rank() over(partition by subject order by score desc) rankOrder,
dense_rank() over(partition by subject order by score desc) dense_rank_Order,
row_number() over(partition by subject order by score desc) row_number_orderNum  开窗函数辅助主函数   row_number()进行统计

select
name ,
subject,
score ,
rank() over(partition by subject order by score desc) rankOrder,
dense_rank() over(partition by subject order by score desc) dense_rank_Order,
row_number() over(partition by subject order by score desc) row_number_orderNum
from score ;

name	subject	score	rankorder	dense_rank_order	row_number_ordernum
孙悟空	数学	95	1	1	1
宋宋	数学	86	2	2	2
婷婷	数学	85	3	3	3
大海	数学	56	4	4	4
宋宋	英语	84	1	1	1
大海	英语	84	1	1	2
婷婷	英语	78	3	2	3
孙悟空	英语	68	4	3	4
大海	语文	94	1	1	1
孙悟空	语文	87	2	2	2
婷婷	语文	65	3	3	3
宋宋	语文	64	4	4	4

十二：自定义函数(看文档)

 1). 获取客户端连接对象
2). 调用API完成具体功能
3). 关闭资源

十三：Hive企业级调优(看文档)

十四：Hive ETL案例分析

ETL阶段：清洗字段数目

 

 数据清洗（ETL）
-- 操作数据的注意点
1). 对字段的数量进行验证
2). 对category字段中的空格进行处理
3). 对关联视频字段 进行处理

-- ETL的思路 
1). 通过MR程序进行数据清洗（编码）
2). 打包，上传至Linux中 
3). 执行数据清洗

-- 进行数据清洗
1). 把待处理的数据 上传至HDFS 

2). 执行MR清洗程序
hadoop jar /opt/module/hive-3.1.2/datas/GuliETL-1.0-SNAPSHOT.jar com.atguigu.mr.GuliEtlDriver /gulivideo/video /gulivideo/video/output

需求分析：

 需求一： 统计视频(字段用videoId视频id) 观看数(views用字段观看次数) Top10

select
videoId,
views
from gulivideo_orc
order by views desc
limit 10;

+--------------+-----------+
|   videoid    |   views   |
+--------------+-----------+
| dMH0bHeiRNg  | 42513417  |
| 0XxI-hvPRRA  | 20282464  |
| 1dmVU08zVpA  | 16087899  |
| RB-wUgnyGv0  | 15712924  |
| QjA5faZF1A8  | 15256922  |
| -_CSo1gOd48  | 13199833  |
| 49IDp76kjPw  | 11970018  |
| tYnn51C3X_w  | 11823701  |
| pv5zWaTEVkI  | 11672017  |
| D2kJZOfq7zk  | 11184051  |
+--------------+-----------+

需求二：统计视频类别热度Top10(热度用视频的数量来体现)

统计视频类别热度Top10(热度用视频的数量来体现)
-- 分析：
1). 获取视频类别并炸开
select 
videoId,
category_name
from gulivideo_orc
lateral view explode(category) gulivideo_orc_tmp as category_name; --> t1 

2). 根据 t1 中的 category_name 进行分组 求统计 获取 hot值并根据hot值倒序取前十
select 
category_name,
count(t1.videoId) as hot
from 
(
 select 
 videoId,
 category_name
 from gulivideo_orc
 lateral view explode(category) gulivideo_orc_tmp as category_name
) t1 
group by t1.category_name
order by hot desc 
limit 10
;

+----------------+---------+
| category_name  |   hot   |
+----------------+---------+
| Music          | 179049  |
| Entertainment  | 127674  |
| Comedy         | 87818   |
| Animation      | 73293   |
| Film           | 73293   |
| Sports         | 67329   |
| Games          | 59817   |
| Gadgets        | 59817   |
| People         | 48890   |
| Blogs          | 48890   |
+----------------+---------+

需求三： 统计出视频观看数最高的20个视频的所属类别以及类别包含Top20视频的个数

视频(字段用videoId视频id) 观看数(views用字段观看次数) 最高的20个视频
select
videoId ,
views ,
category ,
category_name
from gulivideo_orc
order by views desc
limit 20 ;    -- t1

-- top 20 视频的videoId 与 类别
select
t1.videoId videoId_t2,
category_name
from (
select
videoId ,
views ,
category 
from gulivideo_orc
order by views desc
limit 20
) t1
LATERAL VIEW explode(t1.category) gulivideo_orc_temp as category_name ;   -- t2

以及   类别   包含Top20视频的个数 (按照类别进行分组  统计视频的个数)
select
t2.category_name,
count(t2.videoId_t2) as num
from (
select
t1.videoId videoId_t2,
category_name
from (
select
videoId ,
views ,
category 
from gulivideo_orc
order by views desc
limit 20
) t1
LATERAL VIEW explode(t1.category) gulivideo_orc_temp as category_name
) t2
group by t2.category_name  ;


t2.category_name	_c1
Blogs	            2
Comedy	            6
Entertainment	    6
Music	            5
People	            2
UNA	                1

需求四： 统计视频观看数Top50所关联视频的所属类别排名

统计视频，观看次数，和视频相关id
select 
videoId ,
views ,
relatedId 
from gulivideo_orc
order by views desc
limit 50 ;   -- t1
将 t1 中的关联视频字段炸开
select
t1.videoId ,
videoId_Id
from (
select 
videoId ,
views ,
relatedId 
from gulivideo_orc
order by views desc
limit 50
) t1
LATERAL VIEW explode(t1.relatedId) 
gulivideo_orc_temp as videoId_Id ;   -- t2

通过和原表Join获取关联视频对应的所属类别 

select 
t2.videoId_Id videoId_t4,
t3.category category_t4
from(
select
t1.videoId ,
videoId_Id
from (
select 
videoId ,
views ,
relatedId 
from gulivideo_orc
order by views desc
limit 50
) t1
LATERAL VIEW explode(t1.relatedId) 
gulivideo_orc_temp as videoId_Id
) t2 
join gulivideo_orc t3
on t2.videoId_Id = t3.videoId  ;  -- t4

将 t4 结果中的 category 炸开

select
t4.videoId_t4 videoId_t5,
category_name_t4 category_t5
from
(
select 
t2.videoId_Id videoId_t4,
t3.category category_t4
from(
select
t1.videoId ,
videoId_Id
from (
select 
videoId ,
views ,
relatedId 
from gulivideo_orc
order by views desc
limit 50
) t1
LATERAL VIEW explode(t1.relatedId) 
gulivideo_orc_temp as videoId_Id
) t2 
join gulivideo_orc t3
on t2.videoId_Id = t3.videoId 
) t4
LATERAL VIEW explode(t4.category_t4) t4_temp as category_name_t4 ;   -- t5

根据 t5 的 category_name 进行分组 然后求统计

select
t5.category_t5 category_t6,
count(t5.videoId_t5) num
from
(
select
t4.videoId_t4 videoId_t5,
category_name_t4 category_t5
from
(
select 
t2.videoId_Id videoId_t4,
t3.category category_t4
from(
select
t1.videoId ,
videoId_Id
from (
select 
videoId ,
views ,
relatedId 
from gulivideo_orc
order by views desc
limit 50
) t1
LATERAL VIEW explode(t1.relatedId) 
gulivideo_orc_temp as videoId_Id
) t2 
join gulivideo_orc t3
on t2.videoId_Id = t3.videoId 
) t4
LATERAL VIEW explode(t4.category_t4) t4_temp as category_name_t4
) t5
group by t5.category_t5  ;  -- t6

根据 t6 中num 进行排名
select
t6.category_t6,
t6.num ,
rank() over(order by t6.num desc) as r_k
from
(
select
t5.category_t5 category_t6,
count(t5.videoId_t5) num
from
(
select
t4.videoId_t4 videoId_t5,
category_name_t4 category_t5
from
(
select 
t2.videoId_Id videoId_t4,
t3.category category_t4
from(
select
t1.videoId ,
videoId_Id
from (
select 
videoId ,
views ,
relatedId 
from gulivideo_orc
order by views desc
limit 50
) t1
LATERAL VIEW explode(t1.relatedId) 
gulivideo_orc_temp as videoId_Id
) t2 
join gulivideo_orc t3
on t2.videoId_Id = t3.videoId 
) t4
LATERAL VIEW explode(t4.category_t4) t4_temp as category_name_t4
) t5
group by t5.category_t5
) t6 ;

需求五： 统计每个类别中的视频热度Top10，以Music为例 (热度以观看数衡量)

方式一：

获取视频类别的并炸开
select
videoId,
views,
category_name
from gulivideo_orc
lateral view explode(category) gulivideo_orc_tmp as category_name; --> t1

2). 根据 t1 中的 category_name 分组然后求统计
select 
t1.category_name,
t1.views
from 
(
 select
videoId,
views,
category_name
from gulivideo_orc
lateral view explode(category) gulivideo_orc_tmp as category_name
) t1 
where t1.category_name = 'Music'
order by t1.views desc 
limit 10
;

+-------------------+-----------+
| t1.category_name  | t1.views  |
+-------------------+-----------+
| Music             | 15256922  |
| Music             | 11823701  |
| Music             | 11672017  |
| Music             | 9579911   |
| Music             | 7533070   |
| Music             | 6946033   |
| Music             | 6935578   |
| Music             | 6193057   |
| Music             | 5581171   |
| Music             | 5142238   |
+-------------------+-----------+

方式二：

统计类别为Music 的videoId
select
videoId,
views,
category_name
from gulivideo_orc
LATERAL VIEW explode(category) gulivideo_orc_temp as category_name 
where category_name = "Music"   
order by views desc
limit 10;

需求六： 统计每个类别中的视频热度Top10

1).  获取视频类别的并炸开
select
videoId,
views,
category_name
from gulivideo_orc
LATERAL VIEW explode(category) gulivideo_orc_temp as category_name ; -- t1


2). 根据 观看数 对每个类别进行排序
select
t1.videoId videoId_t2,
t1.views views_t2,
t1.category_name category_t2,
rank() over(partition by t1.category_name order by views desc) r_k
from (
select
videoId,
views,
category_name
from gulivideo_orc
LATERAL VIEW explode(category) gulivideo_orc_temp as category_name
) t1    ;    -- t2


获取前10 

select 
t2.videoId_t2 ,
t2.views_t2 ,
t2.category_t2,
t2.r_k
from (
select
t1.videoId videoId_t2,
t1.views views_t2,
t1.category_name category_t2,
rank() over(partition by t1.category_name order by views desc) r_k
from (
select
videoId,
views,
category_name
from gulivideo_orc
LATERAL VIEW explode(category) gulivideo_orc_temp as category_name
) t1 
) t2
where t2.r_k <=10 ;

每个类别都是前十名  
aRNzWyD7C9o	8825788	UNA	1
jtExxsiLgPM	5320895	UNA	2
PxNNR4symuE	4033376	UNA	3
8cjTSvvoddc	3486368	UNA	4
LIhbap3FlGc	2849832	UNA	5
lCSTULqmmYE	2179562	UNA	6
UyTxWvp8upM	2106933	UNA	7
y6oXEWowirI	1666084	UNA	8
_x2-AmY8FI8	1403113	UNA	9
ICoDFooBXpU	1376215	UNA	10
RjrEQaG5jPM	2803140	Vehicles	1
cv157ZIInUk	2773979	Vehicles	2
Gyg9U1YaVk8	1832224	Vehicles	3
6GNB7xT3rNE	1412497	Vehicles	4
tth9krDtxII	1347317	Vehicles	5
46LQd9dXFRU	1262173	Vehicles	6
pdiuDXwgrjQ	1013697	Vehicles	7
kY_cDpENQLE	956665	Vehicles	8
YtxfbxGz1u4	942604	Vehicles	9
aCamHfJwSGU	847442	Vehicles	10

十五：SQL练习

需求一：

 

方式一：
一： 求出每个学生所有科目成绩的最小值
select
uid,
min(score) over(partition by uid) min_score
from score  ;   -- t1

二： 求出学科的平均成绩的最大值
select
subject_id,
avg(score)  avg_score
from score  
group by subject_id 
order by avg_score desc
limit 1 ;  -- t2

三： 求出每个学生所有科目成绩的最小值 大于 
	 各个学科的平均成绩的最大值的uid

select
uid
from(
select
uid,
min(score) over(partition by uid) min_score
from score
) t1 , (

select
subject_id,
avg(score)  avg_score
from score  
group by subject_id 
order by avg_score desc
limit 1 
) t2
where t1.min_score > t2.avg_score  ;

uid
1001
1001
1001

 
方式二  (标准方式)
求出每个学科平均成绩

select
uid,
score,
avg(score) over(partition by subject_id) avg_score
from score   ;  	-- t1
求出每个学科平均成绩
1003	70	81.66666666666667
1002	85	81.66666666666667
1001	90	81.66666666666667
1003	70	81.66666666666667
1002	85	81.66666666666667
1001	90	81.66666666666667
1003	85	81.66666666666667
1002	70	81.66666666666667
1001	90	81.66666666666667
根据是否大于平均成绩记录flag，大于则记为0否则记为1
select
t1.uid,
if(t1.score>t1.avg_score,0,1) flag
from(
select
uid,
score,
avg(score) over(partition by subject_id) avg_score
from score
)  t1 ;         -- t2

1003	1
1002	0
1001	0
1003	1
1002	0
1001	0
1003	0
1002	1
1001	0
根据学生id进行分组统计flag的和，和为0则是所有学科都大于平均成绩
select
uid
from (
select
t1.uid,
if(t1.score>t1.avg_score,0,1) flag
from(
select
uid,
score,
avg(score) over(partition by subject_id) avg_score
from score
)  t1
) t2 
group by uid
having sum(flag)=0;

uid
1001

需求二：

 

使用SQL统计出每个用户的累积访问次数

修改数据格式
select
    userId,
    date_format(regexp_replace(visitDate,'/','-'),'yyyy-MM') mn,
    visitCount
from 
    action ;      -- t1

u01	2017-01	5
u02	2017-01	6
u03	2017-01	8
u04	2017-01	3
u01	2017-01	6
u01	2017-02	8
U02	2017-01	6
U01	2017-02	4

计算每人单月访问量
select
t1.userId userId_t2,
t1.mn mn_t2 ,
sum(t1.visitCount) mn_count_t2
from (
select
    userId,
    date_format(regexp_replace(visitDate,'/','-'),'yyyy-MM') mn,
    visitCount
from 
    action
) t1
group by t1.userId , t1.mn ;    -- t2

userid_t2	mn_t2	mn_count_t2
U01	2017-02	4
U02	2017-01	6
u01	2017-01	11
u01	2017-02	8
u02	2017-01	6
u03	2017-01	8
u04	2017-01	3

按月累计访问量

select
t2.userid_t2,
t2.mn_t2,
t2.mn_count_t2,
sum(t2.mn_count_t2) over(partition by t2.userid_t2 order by t2.mn_t2)
from (
select
t1.userId userId_t2,
t1.mn mn_t2 ,
sum(t1.visitCount) mn_count_t2
from (
select
    userId,
    date_format(regexp_replace(visitDate,'/','-'),'yyyy-MM') mn,
    visitCount
from 
    action
) t1
group by t1.userId , t1.mn
)  t2  ;

u01	2017-01	11	11
u01	2017-02	12	23
u02	2017-01	12	12
u03	2017-01	8	8
u04	2017-01	3	3

需求三：

有50W个京东店铺，每个顾客访客访问任何一个店铺的任何一个商品时都会产生一条访问日志，访问日志存储的表名为Visit，访客的用户id为user_id，被访问的店铺名称为shop。

 

方式一  ： (不对，每家店铺应该去除相同顾客，即相同顾客算一次)
select
shop,
count(user_id) num
from visit
group by shop ;
a	9
b	6
c	4
方式二：  一家店去除了相同的顾客
select
shop,
count(distinct user_id) 
from visit 
group by shop  ; 
a	4
b	4
c	3

每个店铺访问次数top3的访客信息。输出店铺名称、访客id、访问次数

（1）查询每个店铺被每个用户访问次数
select
shop ,
user_id ,
count(*) ct
from visit 
group by shop , user_id  ;  -- t1

shop	user_id	ct
a	u1	3
b	u1	2
a	u2	2
b	u2	1
c	u2	2

（2）计算每个店铺被用户访问次数排名
select
t1.shop shop_t2,
t1.user_id user_id_t2,
t1.ct ct_t2,
rank() over(partition by shop order by t1.ct) r_k
from(
select
shop ,
user_id ,
count(*) ct
from visit 
group by shop , user_id
) t1  ;     -- t2

（3）取每个店铺排名前3的
select
t2.shop_t2,
t2.user_id_t2,
t2.ct_t2
from (
select
t1.shop shop_t2,
t1.user_id user_id_t2,
t1.ct ct_t2,
rank() over(partition by shop order by t1.ct ) r_k
from(
select
shop ,
user_id ,
count(*) ct
from visit 
group by shop , user_id
) t1
)  t2 
where t2.r_k <= 3 ;


a	u3	1
a	u2	2
a	u1	3
a	u5	3
b	u2	1
b	u5	1
b	u1	2
b	u4	2
c	u3	1
c	u6	1
c	u2	2

需求四:

数据样例:2017-01-01,10029028,1000003251,33.57。
1）给出 2017年每个月的订单数、用户数、总成交金额。

select
date_format(dt,'yyyy-MM'),
count(order_id),
count(distinct user_id),
sum(amount)
from order_tab
where date_format(dt,'yyyy') = '2017'
group by date_format(dt,'yyyy-MM') ;

2）给出2017年11月的新客数(指在11月才有第一笔订单)
select
count(user_id)
from order_tab
group by user_id
having date_format(min(dt),'yyyy-MM')='2017-11' ;

需求五：

 

select 
    sum(user_total_count),
    sum(user_total_avg_age),
    sum(twice_count),
    sum(twice_count_avg_age)
from 
(select
   0 user_total_count,
   0 user_total_avg_age,
   count(*) twice_count,
   cast(sum(age)/count(*) as decimal(10,2)) twice_count_avg_age
from 
(
   select
   user_id,
   min(age) age
from
   (select
   user_id,
   min(age) age
from
   (
    select
   user_id,
   age,
   date_sub(dt,rk) flag
from
   (
    select
       dt,
       user_id,
       min(age) age,
       rank() over(partition by user_id order by dt) rk
    from
       user_age
    group by
       dt,user_id
    )t1
    )t2
group by
   user_id,flag
having
   count(*)>=2)t3
group by
   user_id 
)t4

union all

select
   count(*) user_total_count,
   cast((sum(age)/count(*)) as decimal(10,1)),
   0 twice_count,
   0 twice_count_avg_age
from 
   (
      select
         user_id,
         min(age) age 
      from 
         user_age 
      group by 
         user_id
   )t5)t6;

需求六：

select
   t1.userid,
   t1.paymenttime,
   od.money
from
   (select
   userid,
   min(paymenttime) paymenttime
from
   ordertable
where
   date_format(paymenttime,'yyyy-MM')='2017-10'
group by
   userid)t1
join
   ordertable od
on
   t1.userid=od.userid
   and
   t1.paymenttime=od.paymenttime;

需求七：

 

select
    ip,
    interface,
    count(*) ct
from
    ip
where
    date_format(time,'yyyy-MM-dd HH')>='2016-11-09 14'
    and 
    date_format(time,'yyyy-MM-dd HH')<='2016-11-09 15'
    and
    interface='/api/user/login'
group by
ip,interface
order by
    ct desc
limit 2;t1