hadoop入门到实战（9）hive从入门到实战三

转载

mb5ff980b461ced 2020-05-30 18:42:00

文章标签 hive 数据导入数据 mapreduce 字段 文章分类 代码人生

第6章查询

??https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Select
查询语句语法：

[WITH?CommonTableExpression?(,?CommonTableExpression)*]????(Note:?Only?available?starting?with?Hive?0.13.0)
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?number]

6.1 基本查询（select … from）

6.1.1 全表和特定列查询

1、全表查询

hive?(default)>?select?*?from?emp;

2、选择特定列查询

hive?(default)>?select?empno,?ename?from?emp;

注意：
（1）SQL 语言大小写不敏感。
（2）SQL 可以写在一行或者多行。
（3）关键字不能被缩写也不能分行。
（4）各子句一般要分行写。
（5）使用缩进提高语句的可读性。

6.1.2 列别名

1、重命名一个列
2、便于计算
3、紧跟列名，也可以在列名和别名之间加入关键字as
4、案例实操
查询名称和部门

hive?(default)>?select?ename?as?name,?deptno?dn?from?emp;

6.1.3 算术运算符

案例实操：
查询出所有员工的薪水后加1显示。

hive?(default)>?select?sal+1?from?emp;

6.1.4 常用函数（聚合函数）

1、求总行数（count）

hive?(default)>?select?count(*)?cnt?from?emp;

面试题：count(1)、count(*)、count(column)的区别？
参考链接：https://www.cnblogs.com/chenmingjun/p/10436316.html
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;

6.1.5 limit语句

典型的查询会返回多行数据。LIMIT子句用于限制返回的行数。

hive?(default)>?select?*?from?emp?limit?5;

6.2 where语句

1、使用WHERE子句，将不满足条件的行过滤掉
2、WHERE子句紧随FROM子句
3、案例实操
查询出薪水大于1000的所有员工

hive?(default)>?select?*?from?emp?where?sal>1000;

6.2.1 比较运算符（between/in/is null）

1）下面表中描述了谓词操作符，这些操作符同样可以用于JOIN...ON和HAVING语句中。

2）案例实操
（1）查询出薪水等于5000的所有员工

hive?(default)>?select?*?from?emp?where?sal=5000;

（2）查询工资在500到1000的员工信息

hive?(default)>?select?*?from?emp?where?sal?between?500?and?1000;

（3）查询comm为空的所有员工信息

hive?(default)>?select?*?from?emp?where?comm?is?null;

（4）查询工资是1500和5000的员工信息

hive?(default)>?select?*?from?emp?where?sal?IN?(1500,?5000);

6.2.2 like和rlike

1）使用LIKE运算选择类似的值
2）选择条件可以包含字符或数字:
??%代表零个或多个字符(任意个字符)。
??_ 代表一个字符。
3）RLIKE子句是Hive中这个功能的一个扩展，其可以通过Java的正则表达式这个更强大的语言来指定匹配条件。
4）案例实操
（1）查找以2开头薪水的员工信息

hive?(default)>?select?*?from?emp?where?sal?LIKE?'2%';

emp.empno????emp.ename???emp.job?emp.mgr?emp.hiredate????emp.sal?emp.comm????emp.deptno
7698????BLAKE???MANAGER?7839????1981-5-1????2850.0??NULL????30
7782????CLARK???MANAGER?7839????1981-6-9????2450.0??NULL????10

（2）查找第二个数值为2的薪水的员工信息

hive?(default)>?select?*?from?emp?where?sal?LIKE?'_2%';

emp.empno????emp.ename???emp.job?emp.mgr?emp.hiredate????emp.sal?emp.comm????emp.deptno
7521????WARD????SALESMAN????7698????1981-2-22???1250.0??500.0???30
7654????MARTIN??SALESMAN????7698????1981-9-28???1250.0??1400.0??30

（3）查找薪水中含有2的员工信息

hive?(default)>?select?sal?from?emp?where?sal?RLIKE?'[2]';

sal
1250.0
1250.0
2850.0
2450.0

6.2.3 逻辑运算符（and/or/not）

案例实操
（1）查询薪水大于1000，部门是30

hive?(default)>?select?*?from?emp?where?sal>1000?and?deptno=30;

（2）查询薪水大于1000，或者部门是30

hive?(default)>?select?*?from?emp?where?sal>1000?or?deptno=30;

（3）查询除了20部门和30部门以外的员工信息

hive?(default)>?select?*?from?emp?where?deptno?not?in(30,?20);

6.3 分组

6.3.1 group by语句

??GROUP BY语句通常会和聚合函数一起使用，按照一个或者多个列队结果进行分组，然后对每个组执行聚合操作。
案例实操：
（1）计算emp表中每个部门的平均工资

hive?(default)>?select?avg(sal)?avg_sal?from?emp?group?by?deptno;
avg_sal
NULL
2916.6666666666665
1975.0
1566.6666666666667

hive?(default)>?select?e.deptno,?avg(e.sal)?avg_sal?from?emp?e?group?by?e.deptno;
e.deptno????avg_sal
NULL????NULL
10????2916.6666666666665
20????1975.0
30????1566.6666666666667

注意：要将查询字段放在group by里面。（不包括聚合函数）
（2）计算emp表中每个部门中每个岗位的最高薪水

hive?(default)>?select?e.deptno,?e.job,?max(e.sal)?max_sal?from?emp?e?group?by?e.deptno,?e.job;

e.deptno????e.job???max_sal
NULL????MANAGER?7839????NULL
10????CLERK???1300.0
10????MANAGER?2450.0
10????PRESIDENT???5000.0
20????ANALYST?3000.0
20????CLERK???1100.0
30????CLERK???950.0
30????MANAGER?2850.0
30????SALESMAN????1600.0

6.3.2 having语句

1、having与where不同点
??（1）where针对表中的列发挥作用，查询数据；having针对查询结果中的列发挥作用，筛选数据。
??（2）where后面不能写分组函数，而having后面可以使用分组函数。
??（3）having只用于group by分组统计语句。
2、案例实操
（1）求每个部门的平均薪水大于2000的部门
??求emp表中每个部门的平均工资

hive?(default)>?select?deptno,?avg(sal)?avg_sal?from?emp?
group?by?deptno;

deptno????avg_sal
NULL????NULL
10????2916.6666666666665
20????1975.0
30????1566.6666666666667

??求emp表中每个部门的平均薪水大于2000的部门

hive?(default)>?select?deptno,?avg(sal)?avg_sal?from?emp?
group?by?deptno?
having?avg_sal>2000;

deptno????avg_sal
10????2916.6666666666665

6.4 join语句

6.4.1 等值join

??Hive支持通常的SQL JOIN语句，但是只支持等值连接，不支持非等值连接。
案例实操
（1）根据员工表和部门表中的部门编号相等，查询员工编号、员工名称、部门编号和部门名称；

hive?(default)>?select?e.empno,?e.ename,?d.deptno,?d.dname?
from?emp?e?join?dept?d
on?e.deptno?=?d.deptno;

6.4.2 表的别名

1、好处
??（1）使用别名可以简化查询。
??（2）使用表名前缀可以提高执行效率。
2、案例实操
合并员工表和部门表

hive?(default)>?select?e.empno,?e.ename,?d.deptno,?d.dname?from?emp?e?join?dept?d?on?e.deptno=d.deptno;

6.4.3 内连接

??内连接（A和B表的交集）：只有进行连接的两个表中都存在与连接条件相匹配的数据才会被保留下来。

hive?(default)>?select?e.empno,?e.ename,?d.deptno,?d.dname?from?emp?e?join?dept?d?on?e.deptno=d.deptno;

6.4.4 左外连接

??左外连接（A和B表的交集+A集合）：JOIN操作符左边表中符合WHERE子句的所有记录将会被返回。

hive?(default)>?select?e.empno,?e.ename,?d.deptno,?d.dname?from?emp?e?left?join?dept?d?on?e.deptno=d.deptno;

6.4.5 右外连接

??右外连接（A和B表的交集+B集合）：JOIN操作符右边表中符合WHERE子句的所有记录将会被返回。

hive?(default)>?select?e.empno,?e.ename,?d.deptno,?d.dname?from?emp?e?right?join?dept?d?on?e.deptno=d.deptno;

6.4.6 满外连接

??满外连接（A和B表的交集+A集合+B集合）：将会返回所有表中符合WHERE语句条件的所有记录。如果任一表的指定字段没有符合条件的值的话，那么就使用NULL值替代。

hive?(default)>?select?e.empno,?e.ename,?d.deptno,?d.dname?from?emp?e?full?join?dept?d?on?e.deptno=d.deptno;

6.4.7 多表连接

??注意：连接 n 个表，至少需要 n-1 个连接条件。例如：连接三个表，至少需要两个连接条件。
数据准备
location.txt

1700????Beijing
1800????London
1900????Tokyo

1、创建位置表

create?table?if?not?exists?default.location(
loc?int,
loc_name?string
)
row?format?delimited?fields?terminated?by?'\t';

2、导入数据

hive?(default)>?load?data?local?inpath?'/opt/module/datas/location.txt'?into?table?default.location;

3、多表连接查询

hive?(default)>?select?e.ename,?d.dname,?l.loc_name
from?emp?e?
join?dept?d
on?e.deptno=d.deptno
join?location?l
on?l.loc=d.loc;

??大多数情况下，Hive会对每对JOIN连接对象启动一个MapReduce任务。本例中会首先启动一个MapReduce job对表e和表d进行连接操作，然后会再启动一个MapReduce job将第一个MapReduce job的输出和表l进行连接操作。
??注意：为什么不是表d和表l先进行连接操作呢？这是因为Hive总是按照从左到右的顺序执行的。

6.4.8 笛卡尔积

1、笛卡尔集会在下面条件下产生
??（1）省略连接条件
??（2）连接条件无效
??（3）所有表中的所有行互相连接
2、案例实操

hive?(default)>?select?empno,?dname?from?emp,?dept;

6.4.9 连接谓词中不支持or

hive?(default)>?select?e.empno,?e.ename,?d.deptno?from?emp?e?join?dept?d?on?e.deptno=d.deptno?or?e.ename=d.ename;???错误的

6.5 排序

6.5.1 全局排序（order by）

??order by：全局排序，只有一个Reducer，无论是否手动设置了Reducer的个数，Reducer只有一个。
1、使用 ORDER BY 子句排序
??ASC（ascend）: 升序（默认）从小到大
??DESC（descend）: 降序
2、ORDER BY 子句在SELECT语句的结尾
3、案例实操
（1）查询员工信息按工资升序排列

hive?(default)>?select?*?from?emp?order?by?sal;

（2）查询员工信息按工资降序排列

hive?(default)>?select?*?from?emp?order?by?sal?desc;

6.5.2 按照别名排序

按照员工薪水的2倍排序

hive?(default)>?select?ename,?sal*2?twosal?from?emp?order?by?twosal;

6.5.3 多个列排序

按照部门和工资升序排序

hive?(default)>?select?ename,?deptno,?sal?from?emp?order?by?deptno,?sal;

6.5.4 每个MapReduce内部排序（sort by）

??sort by：对于每个Reducer内部进行排序，对全局结果集来说不是排序，有多个Reducer。
1、设置reduce个数

hive?(default)>?set?mapreduce.job.reduces=3;

2、查看设置reduce个数

hive?(default)>?set?mapreduce.job.reduces;

3、根据部门编号降序查看员工信息

hive?(default)>?select?*?from?emp?sort?by?empno?desc;

4、将查询结果导入到文件中（按照部门编号降序排序）

hive?(default)>?insert?overwrite?local?directory?'/opt/module/datas/sortby-result'
select?*?from?emp?sort?by?deptno?desc;

6.5.5 分区排序（distribute by）

??distribute by：类似MR中partition，作用是进行分区，需要结合sort by使用。
??注意：Hive要求DISTRIBUTE BY语句要写在SORT BY语句之前。
??对于distribute by进行测试，一定要分配多reduce进行处理，否则无法看到distribute by的效果。
案例实操：
（1）先按照部门编号分区，再按照员工编号降序排序。

hive?(default)>?set?mapreduce.job.reduces=3;
hive?(default)>?insert?overwrite?local?directory?'/opt/module/datas/distributeby-result'?
select?*?from?emp?distribute?by?deptno?sort?by?empno?desc;

6.5.6 cluster by

??当distribute by和sorts by的字段相同时，可以使用cluster by方式。
??cluster by除了具有distribute by的功能外还兼具sort by的功能。但是排序只能是升序排序，不能指定排序规则为ASC或者DESC。
1）以下两种写法等价

hive?(default)>?select?*?from?emp?cluster?by?deptno;
hive?(default)>?select?*?from?emp?distribute?by?deptno?sort?by?deptno;

??注意：按照部门编号分区，不一定就是固定死的数值，可以是20号和30号部门分到一个分区里面去。

6.6 分桶及抽样查询

6.6.1 分桶表数据存储

??分区针对的是数据的存储路径(文件夹)；分桶针对的是数据文件(文件)。
??分区提供一个隔离数据和优化查询的便利方式。不过，并非所有的数据集都可形成合理的分区，特别是之前所提到过的要确定合适的划分大小这个疑虑。
??分桶是将数据集分解成更容易管理的若干部分的另一个技术。说明单个文件很大很大。
1、先创建分桶表，通过直接导入数据文件的方式
（1）数据准备
stu_buck.txt

1001????ss1
1002????ss2
1003????ss3
1004????ss4
1005????ss5
1006????ss6
1007????ss7
1008????ss8
1009????ss9
1010????ss10
1011????ss11
1012????ss12
1013????ss13
1014????ss14
1015????ss15
1016????ss16

（2）创建分桶表

create?table?stu_buck(id?int,?name?string)
clustered?by(id)?
into?4?buckets
row?format?delimited?fields?terminated?by?'\t';

（3）查看表结构

hive?(default)>?desc?formatted?stu_buck;
Num?Buckets:????????????4?????

（4）导入数据到分桶表中

hive?(default)>?load?data?local?inpath?'/opt/module/datas/stu_buck.txt'?into?table?stu_buck;

（5）查看创建的分桶表中是否分成4个桶，如下图所示

发现并没有分成4个桶。是什么原因呢？

2、创建分桶表时，数据通过子查询的方式导入
（1）先建一个普通的stu表

create?table?stu(id?int,?name?string)
row?format?delimited?fields?terminated?by?'\t';

（2）向普通的stu表中导入数据

load?data?local?inpath?'/opt/module/datas/stu_buck.txt'?into?table?stu;

（3）清空stu_buck表中数据

hive?(default)>?truncate?table?stu_buck;
hive?(default)>?select?*?from?stu_buck;

（4）导入数据到分桶表，通过子查询的方式

hive?(default)>?insert?into?table?stu_buck
select?id,?name?from?stu;

（5）发现还是只有一个分桶，如下图所示

（6）需要设置一个属性

hive?(default)>?set?hive.enforce.bucketing=true;
hive?(default)>?set?mapreduce.job.reduces=-1;?--?-1表示reduce的个数不是预先设置好了，而是在执行HQL语句的时候自动分析出来需要几个reduce。
hive?(default)>?truncate?table?stu_buck;
hive?(default)>?insert?into?table?stu_buck
select?id,?name?from?stu;

分桶成功截图如下图所示

（7）查询分桶的数据

hive?(default)>?select?*?from?stu_buck;

stu_buck.id????stu_buck.name
1016????ss16
1012????ss12
1008????ss8
1004????ss4
1009????ss9
1005????ss5
1001????ss1
1013????ss13
1010????ss10
1002????ss2
1006????ss6
1014????ss14
1003????ss3
1011????ss11
1007????ss7
1015????ss15

读取文件顺序的解释如下图所示：

6.6.2 分桶抽样查询

??对于非常大的数据集，有时用户需要使用的是一个具有代表性的查询结果而不是全部结果。Hive可以通过对表进行抽样来满足这个需求。
??查询表stu_buck中的数据。

hive?(default)>?select?*?from?stu_buck?tablesample(bucket?1?out?of?4?on?id);
OK
stu_buck.id????stu_buck.name
1016????ss16
1012????ss12
1008????ss8
1004????ss4

或者

hive?(default)>?select?*?from?stu_buck?tablesample(bucket?1?out?of?8?on?id);
OK
stu_buck.id????stu_buck.name
1016????ss16
1008????ss8

??注意：tablesample是抽样语句，语法：TABLESAMPLE(BUCKET x OUT OF y) 。
??y必须是table总bucket数的倍数或者因子。hive根据y的大小，决定抽样的比例。例如，table总共分了4份，当y=2时，抽取(4/2=)2个bucket的数据，当y=8时，抽取(4/8=)1/2个bucket的数据。
??x表示从哪个bucket开始抽取，如果需要取多个分区，以后的分区号为当前分区号加上y。例如，table总bucket数为4，tablesample(bucket 1 out of 2)，表示总共抽取（4/2=）2个bucket的数据，抽取第1(x)个和第3(x+y)个bucket的数据。
??注意：x的值必须小于等于y的值，否则报错如下：

FAILED:?SemanticException?[Error?10061]:?Numerator?should?not?be?bigger?than?denominator?in?sample?clause?for?table?stu_buck

6.7 其他常用查询函数（Hive高级）

6.7.1 给空字段赋值函数

1、函数说明
??NVL：给值为NULL的数据赋值，它的格式是NVL(string1, replace_with)。它的功能是如果string1为NULL，则NVL函数返回replace_with的值，否则返回string1的值，如果两个参数都为NULL，则返回NULL。
2、数据准备：采用员工表
3、查询：如果员工的comm为NULL，则用-1代替

hive?(default)>?select?nvl(comm,-1)?from?emp;
OK
_c0
-1.0
300.0
500.0
20.0
1400.0
-1.0
-1.0
-1.0
-1.0
0.0
-1.0
-1.0
-1.0
-1.0

或者

hive?(default)>?select?nvl(comm,ename)?from?emp;
OK
_c0
SMITH
300.0
500.0
20.0
1400.0
BLAKE
CLARK
SCOTT
KING
0.0
ADAMS
JAMES
FORD
MILLER

4、查询：如果员工的comm为NULL，则用领导id代替

hive?(default)>?select?nvl(comm,mgr)?from?emp;
OK
_c0
7902.0
300.0
500.0
20.0
1400.0
7839.0
7839.0
7566.0
NULL
0.0
7788.0
7698.0
7566.0
7782.0

6.7.2 case … when … then … else … end 函数

??作用：替换数据。
1、数据准备

2、需求
求出不同部门男女各多少人。结果如下：

A?????2???????1
B?????1???????2

3、创建本地emp_sex.txt，导入数据

[atguigu@hadoop102?datas]$?vim?emp_sex.txt
悟空????A???男
大海????A???男
宋宋????B???男
凤姐????A???女
婷姐????B???女
婷婷????B???女

4、创建hive表并导入数据

create?table?emp_sex(
name?string,?
dept_id?string,?
sex?string
)?
row?format?delimited?fields?terminated?by?"\t";

load?data?local?inpath?'/opt/module/datas/emp_sex.txt'?into?table?emp_sex;

5、按需求查询数据

select?
??dept_id,
??sum(case?sex?when?'男'?then?1?else?0?end)?male_count,
??sum(case?sex?when?'女'?then?1?else?0?end)?female_count
from?
??emp_sex
group?by
??dept_id;

6.7.2 行转列相关函数

1、相关函数说明
??1）CONCAT(string A/col, string B/col, …)：返回输入字符串连接后的结果，支持任意个输入字符串。
??2）CONCAT_WS(separator, str1, str2,…)：它是一个特殊形式的CONCAT()。第一个参数是剩余参数间的分隔符。分隔符可以是与剩余参数一样的字符串。如果分隔符是 NULL，返回值也将为 NULL。这个函数会跳过分隔符参数后的任何 NULL 和空字符串。分隔符将被加到被连接的字符串之间。
??3）COLLECT_SET(col)：函数只接受基本数据类型，它的主要作用是将某字段的值进行去重汇总，产生array类型字段。

??注意：CONCAT()和CONCAT_WS()都是UDTF函数，COLLECT_SET()函数类似聚合函数。

示例1）

示例2）

示例3)

2、数据准备
person_info.txt
hadoop入门到实战（9）hive从入门到实战三_mapreduce_12

3、需求
把星座和血型一样的人归类到一起。结果如下：

射手座,A????????????大海|凤姐
白羊座,A????????????孙悟空|猪八戒
白羊座,B????????????宋宋

分析过程：

4、创建本地person_info.txt，导入数据

[atguigu@hadoop102?datas]$?vim?person_info.txt
孙悟空????白羊座?A
大海????射手座?A
宋宋????白羊座?B
猪八戒????白羊座?A
凤姐????射手座?A

5、创建hive表并导入数据

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

load?data?local?inpath?'/opt/module/datas/person_info.txt'?into?table?person_info;

6、按需求查询数据

select?concat_ws(",",?constellation,?blood_type)?as?c_b,?name?from?person_info;

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

select?
??t1.c_b,?collect_set(t1.name)
from?
??(select?concat_ws(",",?constellation,?blood_type)?as?c_b,?name?from?person_info)?t1
group?by
??t1.c_b;

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

select?
??t1.c_b,?concat_ws("|",?collect_set(t1.name))
from?
??(select?concat_ws(",",?constellation,?blood_type)?as?c_b,?name?from?person_info)?t1
group?by
??t1.c_b;

6.7.3 列转行相关函数

1、函数说明
??EXPLODE(col)：将hive一列中复杂的array或者map结构拆分成多行。
??LATERAL VIEW
????用法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias
????解释：lateral view 用于和split,explode等UDTF函数一起使用，它能够将一列数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。
2、数据准备
movie_info.txt

movie???????????category

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

3、需求
将电影分类中的数组数据展开。结果如下：

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

4、创建本地movie.txt，导入数据

[atguigu@hadoop102?datas]$?vim?movie_info.txt
《疑犯追踪》????悬疑,动作,科幻,剧情
《Lie?to?me》???悬疑,警匪,动作,心理,剧情
《战狼2》???战争,动作,灾难

5、创建hive表并导入数据

create?table?movie_info(
movie?string,?
category?array<string>
)?
row?format?delimited?fields?terminated?by?"\t"
collection?items?terminated?by?",";

load?data?local?inpath?"/opt/module/datas/movie_info.txt"?into?table?movie_info;

6、按需求查询数据

select?
??movie
??explode(category)
from
??movie_info;

上面是错误的。假设能执行的话，得到的是笛卡尔积。

小结：像split,explode等UDTF函数，是不能跟原表的字段直接进行查询的，UDTF函数一定要和lateral?view联合在一块用。
-----------------------------------------

select
??movie,
??category_name
from?
??movie_info?
lateral?view?explode(category)?table_tmp?as?category_name;?--lateral?view?对原表的字段进行了侧写，得到侧写表和侧写字段。

6.7.4 窗口函数

1、相关函数说明
??注意：窗口是针对每一行数据来说的。默认窗口大小，就是每一行数据就是一个窗口。
??OVER()：指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化。
??CURRENT ROW：当前行。
??n PRECEDING：往前n行数据。
??n FOLLOWING：往后n行数据。
??UNBOUNDED：起点，UNBOUNDED PRECEDING 表示从前面的起点， UNBOUNDED FOLLOWING 表示到后面的终点。
??LAG(col,n)：往前第n行数据。
??LEAD(col,n)：往后第n行数据。
??NTILE(n)：把有序分区中的行分发到指定数据的组中，各个组有编号，编号从1开始，对于每一行，NTILE返回此行所属的组的编号。注意：n必须为int类型。
2、数据准备
business.txt

name????orderdate???cost

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

3、需求
（1）查询在2017年4月份购买过的顾客及总人数
（2）查询顾客的购买明细及月购买总额
（3）上述的场景,要将cost按照日期进行累加
（4）查询顾客上次的购买时间
（5）查询前20%时间的订单信息
4、创建本地business.txt，导入数据

[atguigu@hadoop102?datas]$?vim?business.txt

5、创建hive表并导入数据

create?table?business(
name?string,?
orderdate?string,
cost?int
)
ROW?FORMAT?DELIMITED?FIELDS?TERMINATED?BY?',';

load?data?local?inpath?"/opt/module/datas/business.txt"?into?table?business;

6、按需求查询数据
（1）查询在2017年4月份购买过的顾客及总人数

select?name,?count(*)?
from?business
where?substring(orderdate,?1,?7)="2017-04"
group?by?name;

+-------+------+--+
|?name??|?_c1??|
+-------+------+--+
|?jack??|?1????|
|?mart??|?4????|
+-------+------+--+

-----------------------------------------
select?name,?count(*)?over()
from?business
where?substring(orderdate,?1,?7)="2017-04"
group?by?name;

+-------+-----------------+--+
|?name??|?count_window_0??|
+-------+-----------------+--+
|?mart??|?2???????????????|
|?jack??|?2???????????????|
+-------+-----------------+--+

（2）查询顾客的购买明细及月购买总额

select?sum(cost)
from?business;

select?*,?
sum(cost)?over()?--?表示每一行数据就是一个窗口
from?business;

select?month(orderdate)?from?business;?--?按月份输出

select?*,?
sum(cost)?over(distribute?by?month(orderdate))?--?按月份分区
from?business;

select?*,?
sum(cost)?over(partition?by?month(orderdate))?--?按月份分区（同上）
from?business;

（3）上述的场景,要将cost按照日期进行累加

select?*?from?business?sort?by?orderdate;

select?*,?
sum(cost)?over(sort?by?orderdate?rows?between?UNBOUNDED?PRECEDING?and?CURRENT?ROW)
from?business;

select?*,?
sum(cost)?over(distribute?by?name?sort?by?orderdate?rows?between?UNBOUNDED?PRECEDING?and?CURRENT?ROW)?
from?business;

select?*,?
sum(cost)?over(partition?by?name?sort?by?orderdate?rows?between?UNBOUNDED?PRECEDING?and?CURRENT?ROW)?
from?business;

select?name,?orderdate,?cost,?
sum(cost)?over()?as?sample1,?--?只有一个分区，所有行相加，得一个值
sum(cost)?over(partition?by?name)?as?sample2,?--?按name分区，有多个分区，分区内数据相加，每个分区得一个值
sum(cost)?over(order?by?orderdate)?as?sample3,?--?按orderdate排序，只有一个分区，区内数据累加
sum(cost)?over(partition?by?name?order?by?orderdate)?as?sample4,?--?按name分区，按orderdate排序，有多个分区，区内数据各自累加
sum(cost)?over(partition?by?name?order?by?orderdate?rows?between?UNBOUNDED?PRECEDING?and?CURRENT?ROW)?as?sample5,?--?和sample4一样，由起点到当前行的聚合
sum(cost)?over(partition?by?name?order?by?orderdate?rows?between?1?PRECEDING?and?CURRENT?ROW)?as?sample6,?--?当前行和前面一行做聚合
sum(cost)?over(partition?by?name?order?by?orderdate?rows?between?1?PRECEDING?and?1?FOLLOWING)?as?sample7,?--?当前行和前边一行及后面一行
sum(cost)?over(partition?by?name?order?by?orderdate?rows?between?CURRENT?ROW?and?UNBOUNDED?FOLLOWING)?as?sample8,?--?当前行及后面所有行
from?business;

演示1如下：

select?name,?orderdate,?cost,
sum(cost)?over()?as?sample1
from?business;
+-------+-------------+-------+----------+--+
|?name??|??orderdate??|?cost??|?sample1??|
+-------+-------------+-------+----------+--+
|?mart??|?2017-04-13??|?94????|?661??????|
|?neil??|?2017-06-12??|?80????|?661??????|
|?mart??|?2017-04-11??|?75????|?661??????|
|?neil??|?2017-05-10??|?12????|?661??????|
|?mart??|?2017-04-09??|?68????|?661??????|
|?mart??|?2017-04-08??|?62????|?661??????|
|?jack??|?2017-01-08??|?55????|?661??????|
|?tony??|?2017-01-07??|?50????|?661??????|
|?jack??|?2017-04-06??|?42????|?661??????|
|?jack??|?2017-01-05??|?46????|?661??????|
|?tony??|?2017-01-04??|?29????|?661??????|
|?jack??|?2017-02-03??|?23????|?661??????|
|?tony??|?2017-01-02??|?15????|?661??????|
|?jack??|?2017-01-01??|?10????|?661??????|
+-------+-------------+-------+----------+--+

演示2如下：

select?name,?orderdate,?cost,
sum(cost)?over(partition?by?name)?as?sample2
from?business;
+-------+-------------+-------+----------+--+
|?name??|??orderdate??|?cost??|?sample2??|
+-------+-------------+-------+----------+--+
|?jack??|?2017-01-05??|?46????|?176??????|
|?jack??|?2017-01-08??|?55????|?176??????|
|?jack??|?2017-01-01??|?10????|?176??????|
|?jack??|?2017-04-06??|?42????|?176??????|
|?jack??|?2017-02-03??|?23????|?176??????|
|?mart??|?2017-04-13??|?94????|?299??????|
|?mart??|?2017-04-11??|?75????|?299??????|
|?mart??|?2017-04-09??|?68????|?299??????|
|?mart??|?2017-04-08??|?62????|?299??????|
|?neil??|?2017-05-10??|?12????|?92???????|
|?neil??|?2017-06-12??|?80????|?92???????|
|?tony??|?2017-01-04??|?29????|?94???????|
|?tony??|?2017-01-02??|?15????|?94???????|
|?tony??|?2017-01-07??|?50????|?94???????|
+-------+-------------+-------+----------+--+

演示3如下：

select?name,?orderdate,?cost,
sum(cost)?over(order?by?orderdate)?as?sample3
from?business;
+-------+-------------+-------+----------+--+
|?name??|??orderdate??|?cost??|?sample3??|
+-------+-------------+-------+----------+--+
|?jack??|?2017-01-01??|?10????|?10???????|
|?tony??|?2017-01-02??|?15????|?25???????|
|?tony??|?2017-01-04??|?29????|?54???????|
|?jack??|?2017-01-05??|?46????|?100??????|
|?tony??|?2017-01-07??|?50????|?150??????|
|?jack??|?2017-01-08??|?55????|?205??????|
|?jack??|?2017-02-03??|?23????|?228??????|
|?jack??|?2017-04-06??|?42????|?270??????|
|?mart??|?2017-04-08??|?62????|?332??????|
|?mart??|?2017-04-09??|?68????|?400??????|
|?mart??|?2017-04-11??|?75????|?475??????|
|?mart??|?2017-04-13??|?94????|?569??????|
|?neil??|?2017-05-10??|?12????|?581??????|
|?neil??|?2017-06-12??|?80????|?661??????|
+-------+-------------+-------+----------+--+

注意：
select?name,?orderdate,?cost,
sum(cost)?over(sort?by?orderdate)?as?sample3
from?business;
演示结果同上。
区别：使用sort?by可以设定reducer的个数，order?by不能够设定reducer的个数，默认是1个。即使设定了也没用！

演示4如下：

select?name,?orderdate,?cost,
sum(cost)?over(partition?by?name?order?by?orderdate)?as?sample4
from?business;
+-------+-------------+-------+----------+--+
|?name??|??orderdate??|?cost??|?sample4??|
+-------+-------------+-------+----------+--+
|?jack??|?2017-01-01??|?10????|?10???????|
|?jack??|?2017-01-05??|?46????|?56???????|
|?jack??|?2017-01-08??|?55????|?111??????|
|?jack??|?2017-02-03??|?23????|?134??????|
|?jack??|?2017-04-06??|?42????|?176??????|
|?mart??|?2017-04-08??|?62????|?62???????|
|?mart??|?2017-04-09??|?68????|?130??????|
|?mart??|?2017-04-11??|?75????|?205??????|
|?mart??|?2017-04-13??|?94????|?299??????|
|?neil??|?2017-05-10??|?12????|?12???????|
|?neil??|?2017-06-12??|?80????|?92???????|
|?tony??|?2017-01-02??|?15????|?15???????|
|?tony??|?2017-01-04??|?29????|?44???????|
|?tony??|?2017-01-07??|?50????|?94???????|
+-------+-------------+-------+----------+--+

演示5如下：

select?name,?orderdate,?cost,
sum(cost)?over(partition?by?name?order?by?orderdate?rows?between?UNBOUNDED?PRECEDING?and?CURRENT?ROW)?as?sample5
from?business;
+-------+-------------+-------+----------+--+
|?name??|??orderdate??|?cost??|?sample5??|
+-------+-------------+-------+----------+--+
|?jack??|?2017-01-01??|?10????|?10???????|
|?jack??|?2017-01-05??|?46????|?56???????|
|?jack??|?2017-01-08??|?55????|?111??????|
|?jack??|?2017-02-03??|?23????|?134??????|
|?jack??|?2017-04-06??|?42????|?176??????|
|?mart??|?2017-04-08??|?62????|?62???????|
|?mart??|?2017-04-09??|?68????|?130??????|
|?mart??|?2017-04-11??|?75????|?205??????|
|?mart??|?2017-04-13??|?94????|?299??????|
|?neil??|?2017-05-10??|?12????|?12???????|
|?neil??|?2017-06-12??|?80????|?92???????|
|?tony??|?2017-01-02??|?15????|?15???????|
|?tony??|?2017-01-04??|?29????|?44???????|
|?tony??|?2017-01-07??|?50????|?94???????|
+-------+-------------+-------+----------+--+

演示6如下：

select?name,?orderdate,?cost,
sum(cost)?over(partition?by?name?order?by?orderdate?rows?between?1?PRECEDING?and?CURRENT?ROW)?as?sample6
from?business;
+-------+-------------+-------+----------+--+
|?name??|??orderdate??|?cost??|?sample6??|
+-------+-------------+-------+----------+--+
|?jack??|?2017-01-01??|?10????|?10???????|
|?jack??|?2017-01-05??|?46????|?56???????|
|?jack??|?2017-01-08??|?55????|?101??????|
|?jack??|?2017-02-03??|?23????|?78???????|
|?jack??|?2017-04-06??|?42????|?65???????|
|?mart??|?2017-04-08??|?62????|?62???????|
|?mart??|?2017-04-09??|?68????|?130??????|
|?mart??|?2017-04-11??|?75????|?143??????|
|?mart??|?2017-04-13??|?94????|?169??????|
|?neil??|?2017-05-10??|?12????|?12???????|
|?neil??|?2017-06-12??|?80????|?92???????|
|?tony??|?2017-01-02??|?15????|?15???????|
|?tony??|?2017-01-04??|?29????|?44???????|
|?tony??|?2017-01-07??|?50????|?79???????|
+-------+-------------+-------+----------+--+

演示7如下：

select?name,?orderdate,?cost,
sum(cost)?over(partition?by?name?order?by?orderdate?rows?between?1?PRECEDING?and?1?FOLLOWING)?as?sample7
from?business;
+-------+-------------+-------+----------+--+
|?name??|??orderdate??|?cost??|?sample7??|
+-------+-------------+-------+----------+--+
|?jack??|?2017-01-01??|?10????|?56???????|
|?jack??|?2017-01-05??|?46????|?111??????|
|?jack??|?2017-01-08??|?55????|?124??????|
|?jack??|?2017-02-03??|?23????|?120??????|
|?jack??|?2017-04-06??|?42????|?65???????|
|?mart??|?2017-04-08??|?62????|?130??????|
|?mart??|?2017-04-09??|?68????|?205??????|
|?mart??|?2017-04-11??|?75????|?237??????|
|?mart??|?2017-04-13??|?94????|?169??????|
|?neil??|?2017-05-10??|?12????|?92???????|
|?neil??|?2017-06-12??|?80????|?92???????|
|?tony??|?2017-01-02??|?15????|?44???????|
|?tony??|?2017-01-04??|?29????|?94???????|
|?tony??|?2017-01-07??|?50????|?79???????|
+-------+-------------+-------+----------+--+

演示8如下：

select?name,?orderdate,?cost,
sum(cost)?over(partition?by?name?order?by?orderdate?rows?between?CURRENT?ROW?and?UNBOUNDED?FOLLOWING)?as?sample8
from?business;
+-------+-------------+-------+----------+--+
|?name??|??orderdate??|?cost??|?sample8??|
+-------+-------------+-------+----------+--+
|?jack??|?2017-01-01??|?10????|?176??????|
|?jack??|?2017-01-05??|?46????|?166??????|
|?jack??|?2017-01-08??|?55????|?120??????|
|?jack??|?2017-02-03??|?23????|?65???????|
|?jack??|?2017-04-06??|?42????|?42???????|
|?mart??|?2017-04-08??|?62????|?299??????|
|?mart??|?2017-04-09??|?68????|?237??????|
|?mart??|?2017-04-11??|?75????|?169??????|
|?mart??|?2017-04-13??|?94????|?94???????|
|?neil??|?2017-05-10??|?12????|?92???????|
|?neil??|?2017-06-12??|?80????|?80???????|
|?tony??|?2017-01-02??|?15????|?94???????|
|?tony??|?2017-01-04??|?29????|?79???????|
|?tony??|?2017-01-07??|?50????|?50???????|
+-------+-------------+-------+----------+--+

（4）查询顾客上次的购买时间

select?*,?
lag(orderdate,?1)?over(distribute?by?name?sort?by?orderdate)
from?business;

查询顾客上次的购买时间和下次购买时间
select?*,?
lag(orderdate,?1)?over(distribute?by?name?sort?by?orderdate)?as?lag1,
lead(orderdate,?1)?over(distribute?by?name?sort?by?orderdate)?as?lead1
from?business;

（5）查询前20%时间的订单信息

select?*,
ntile(5)?over(sort?by?orderdate)?as?gid
from?business;

select?*
from?(select?*,
???????ntile(5)?over(sort?by?orderdate)?as?gid
???????from?business)?as?t
where?t.gid=1;

select?*
from?(select?name,?orderdate,?cost,
???????ntile(5)?over(sort?by?orderdate)?as?gid
???????from?business)?as?t
where?t.gid=1;

6.7.5 rank函数

1、函数说明
??RANK()：排序相同时会重复，总数不会变。（两个100分为列第一名和第二名，99分的为第三名）
??DENSE_RANK()：排序相同时会重复，总数会减少。（两个100分并列第一，99分的为第二名）
??ROW_NUMBER()：会根据顺序计算。
??注意：使用rank函数需要在其后跟上over函数（窗口函数）。
2、数据准备

3、需求
??计算每门学科的成绩排名。
4、创建本地score.txt，导入数据

[atguigu@hadoop102?datas]$?vim?score.txt

5、创建hive表并导入数据

create?table?score(
name?string,
subject?string,?
score?int)?
row?format?delimited?fields?terminated?by?"\t";

load?data?local?inpath?'/opt/module/datas/score.txt'?into?table?score;

6、按需求查询数据

select?*,
rank()?over(partition?by?subject?order?by?score?desc)?rp,
dense_rank()over(partition?by?subject?order?by?score?desc)?drp,
row_number()?over(partition?by?subject?order?by?score?desc)?rnp
from?score;

结果截图：

回到顶部

第7章函数（Hive高级）

7.1 系统内置函数

1、查看系统自带的函数

hive>?show?functions;

2、显示自带的函数的用法

hive>?desc?function?upper;

3、详细显示自带的函数的用法

hive>?desc?function?extended?upper;

7.2 自定义函数

1）Hive 自带了一些函数，比如：max/min等，但是数量有限，自己可以通过自定义UDF来方便的扩展。
2）当Hive提供的内置函数无法满足你的业务处理需要时，此时就可以考虑使用用户自定义函数（UDF：user-defined function）。
3）根据用户自定义函数类别分为以下三种：
??（1）UDF（User-Defined-Function）
????一进一出
??（2）UDAF（User-Defined Aggregation Function）
????聚集函数，多进一出
????类似于：count/max/min等
??（3）UDTF（User-Defined Table-Generating Functions）
????一进多出
????如：lateral view explore()
4）官方文档地址
??https://cwiki.apache.org/confluence/display/Hive/HivePlugins
5）编程步骤：
（1）继承org.apache.hadoop.hive.ql.UDF
（2）需要实现evaluate()函数；evaluate()函数支持重载
（3）在hive的命令行窗口创建函数
??a）添加jar
????add jar linux_jar_path
??b）创建function
????create [temporary] function [dbname.]function_name AS class_name;
（4）在hive的命令行窗口删除函数
????drop [temporary] function [if exists] [dbname.]function_name;
6）注意事项
（1）UDF必须要有返回类型，可以返回null，但是返回类型不能为void。

7.3 自定义UDF函数

1、创建一个Maven工程Hive
2、导入依赖

<dependencies>
????<!--?https://mvnrepository.com/artifact/org.apache.hive/hive-exec?-->
????<dependency>
????????<groupId>org.apache.hive</groupId>
????????<artifactId>hive-exec</artifactId>
????????<version>1.2.1</version>
????</dependency>
</dependencies>

3、创建一个类

package?com.atguigu.hive;

import?org.apache.hadoop.hive.ql.exec.UDF;

/**
?*?@author?chenmingjun
?*?@date?2019-02-27?17:50
?*/
public?class?HiveUDF?extends?UDF?{

????public?String?evaluate(final?String?s)?{

????????if?(s?==?null)?{
????????????return?null;
????????}

????????return?s.toLowerCase();
????}
}

4、打成jar包上传到服务器/opt/module/jars/udf.jar
5、将jar包添加到hive的class path

hive?(default)>?add?jar?/opt/module/jars/udf.jar;

6、创建临时函数与开发好的java class关联

hive?(default)>?create?temporary?function?mylower?as?"com.atguigu.hive.HiveUDF";

7、即可在hql中使用自定义的函数strip

hive?(default)>?select?ename,?mylower(ename)?lowername?from?emp;
OK
ename????lowername
SMITH????smith
ALLEN????allen
WARD????ward
JONES????jones
MARTIN????martin
BLAKE????blake
CLARK????clark
SCOTT????scott
KING????king
TURNER????turner
ADAMS????adams
JAMES????james
FORD????ford
MILLER????miller

第6章查询

??https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Select
查询语句语法：

[WITH?CommonTableExpression?(,?CommonTableExpression)*]????(Note:?Only?available?starting?with?Hive?0.13.0)
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?number]

6.1 基本查询（select … from）

6.1.1 全表和特定列查询

1、全表查询

hive?(default)>?select?*?from?emp;

2、选择特定列查询

hive?(default)>?select?empno,?ename?from?emp;

6.1.2 列别名

1、重命名一个列
2、便于计算
3、紧跟列名，也可以在列名和别名之间加入关键字as
4、案例实操
查询名称和部门

hive?(default)>?select?ename?as?name,?deptno?dn?from?emp;

6.1.3 算术运算符

案例实操：
查询出所有员工的薪水后加1显示。

hive?(default)>?select?sal+1?from?emp;

6.1.4 常用函数（聚合函数）

1、求总行数（count）

hive?(default)>?select?count(*)?cnt?from?emp;

面试题：count(1)、count(*)、count(column)的区别？
参考链接：https://www.cnblogs.com/chenmingjun/p/10436316.html
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;

6.1.5 limit语句

典型的查询会返回多行数据。LIMIT子句用于限制返回的行数。

hive?(default)>?select?*?from?emp?limit?5;

6.2 where语句

1、使用WHERE子句，将不满足条件的行过滤掉
2、WHERE子句紧随FROM子句
3、案例实操
查询出薪水大于1000的所有员工

hive?(default)>?select?*?from?emp?where?sal>1000;

6.2.1 比较运算符（between/in/is null）

1）下面表中描述了谓词操作符，这些操作符同样可以用于JOIN...ON和HAVING语句中。

2）案例实操
（1）查询出薪水等于5000的所有员工

hive?(default)>?select?*?from?emp?where?sal=5000;

（2）查询工资在500到1000的员工信息

hive?(default)>?select?*?from?emp?where?sal?between?500?and?1000;

（3）查询comm为空的所有员工信息

hive?(default)>?select?*?from?emp?where?comm?is?null;

（4）查询工资是1500和5000的员工信息

hive?(default)>?select?*?from?emp?where?sal?IN?(1500,?5000);

6.2.2 like和rlike

hive?(default)>?select?*?from?emp?where?sal?LIKE?'2%';

emp.empno????emp.ename???emp.job?emp.mgr?emp.hiredate????emp.sal?emp.comm????emp.deptno
7698????BLAKE???MANAGER?7839????1981-5-1????2850.0??NULL????30
7782????CLARK???MANAGER?7839????1981-6-9????2450.0??NULL????10

（2）查找第二个数值为2的薪水的员工信息

hive?(default)>?select?*?from?emp?where?sal?LIKE?'_2%';

emp.empno????emp.ename???emp.job?emp.mgr?emp.hiredate????emp.sal?emp.comm????emp.deptno
7521????WARD????SALESMAN????7698????1981-2-22???1250.0??500.0???30
7654????MARTIN??SALESMAN????7698????1981-9-28???1250.0??1400.0??30

（3）查找薪水中含有2的员工信息

hive?(default)>?select?sal?from?emp?where?sal?RLIKE?'[2]';

sal
1250.0
1250.0
2850.0
2450.0

6.2.3 逻辑运算符（and/or/not）

案例实操
（1）查询薪水大于1000，部门是30

hive?(default)>?select?*?from?emp?where?sal>1000?and?deptno=30;

（2）查询薪水大于1000，或者部门是30

hive?(default)>?select?*?from?emp?where?sal>1000?or?deptno=30;

（3）查询除了20部门和30部门以外的员工信息

hive?(default)>?select?*?from?emp?where?deptno?not?in(30,?20);

6.3 分组

6.3.1 group by语句

hive?(default)>?select?avg(sal)?avg_sal?from?emp?group?by?deptno;
avg_sal
NULL
2916.6666666666665
1975.0
1566.6666666666667

hive?(default)>?select?e.deptno,?avg(e.sal)?avg_sal?from?emp?e?group?by?e.deptno;
e.deptno????avg_sal
NULL????NULL
10????2916.6666666666665
20????1975.0
30????1566.6666666666667

注意：要将查询字段放在group by里面。（不包括聚合函数）
（2）计算emp表中每个部门中每个岗位的最高薪水

hive?(default)>?select?e.deptno,?e.job,?max(e.sal)?max_sal?from?emp?e?group?by?e.deptno,?e.job;

e.deptno????e.job???max_sal
NULL????MANAGER?7839????NULL
10????CLERK???1300.0
10????MANAGER?2450.0
10????PRESIDENT???5000.0
20????ANALYST?3000.0
20????CLERK???1100.0
30????CLERK???950.0
30????MANAGER?2850.0
30????SALESMAN????1600.0

6.3.2 having语句

hive?(default)>?select?deptno,?avg(sal)?avg_sal?from?emp?
group?by?deptno;

deptno????avg_sal
NULL????NULL
10????2916.6666666666665
20????1975.0
30????1566.6666666666667

??求emp表中每个部门的平均薪水大于2000的部门

hive?(default)>?select?deptno,?avg(sal)?avg_sal?from?emp?
group?by?deptno?
having?avg_sal>2000;

deptno????avg_sal
10????2916.6666666666665

6.4 join语句

6.4.1 等值join

hive?(default)>?select?e.empno,?e.ename,?d.deptno,?d.dname?
from?emp?e?join?dept?d
on?e.deptno?=?d.deptno;

6.4.2 表的别名

1、好处
??（1）使用别名可以简化查询。
??（2）使用表名前缀可以提高执行效率。
2、案例实操
合并员工表和部门表

hive?(default)>?select?e.empno,?e.ename,?d.deptno,?d.dname?from?emp?e?join?dept?d?on?e.deptno=d.deptno;

6.4.3 内连接

??内连接（A和B表的交集）：只有进行连接的两个表中都存在与连接条件相匹配的数据才会被保留下来。

hive?(default)>?select?e.empno,?e.ename,?d.deptno,?d.dname?from?emp?e?join?dept?d?on?e.deptno=d.deptno;

6.4.4 左外连接

??左外连接（A和B表的交集+A集合）：JOIN操作符左边表中符合WHERE子句的所有记录将会被返回。

hive?(default)>?select?e.empno,?e.ename,?d.deptno,?d.dname?from?emp?e?left?join?dept?d?on?e.deptno=d.deptno;

6.4.5 右外连接

??右外连接（A和B表的交集+B集合）：JOIN操作符右边表中符合WHERE子句的所有记录将会被返回。

hive?(default)>?select?e.empno,?e.ename,?d.deptno,?d.dname?from?emp?e?right?join?dept?d?on?e.deptno=d.deptno;

6.4.6 满外连接

hive?(default)>?select?e.empno,?e.ename,?d.deptno,?d.dname?from?emp?e?full?join?dept?d?on?e.deptno=d.deptno;

6.4.7 多表连接

??注意：连接 n 个表，至少需要 n-1 个连接条件。例如：连接三个表，至少需要两个连接条件。
数据准备
location.txt

1700????Beijing
1800????London
1900????Tokyo

1、创建位置表

create?table?if?not?exists?default.location(
loc?int,
loc_name?string
)
row?format?delimited?fields?terminated?by?'\t';

2、导入数据

hive?(default)>?load?data?local?inpath?'/opt/module/datas/location.txt'?into?table?default.location;

3、多表连接查询

hive?(default)>?select?e.ename,?d.dname,?l.loc_name
from?emp?e?
join?dept?d
on?e.deptno=d.deptno
join?location?l
on?l.loc=d.loc;

6.4.8 笛卡尔积

1、笛卡尔集会在下面条件下产生
??（1）省略连接条件
??（2）连接条件无效
??（3）所有表中的所有行互相连接
2、案例实操

hive?(default)>?select?empno,?dname?from?emp,?dept;

6.4.9 连接谓词中不支持or

hive?(default)>?select?e.empno,?e.ename,?d.deptno?from?emp?e?join?dept?d?on?e.deptno=d.deptno?or?e.ename=d.ename;???错误的

6.5 排序

6.5.1 全局排序（order by）

hive?(default)>?select?*?from?emp?order?by?sal;

（2）查询员工信息按工资降序排列

hive?(default)>?select?*?from?emp?order?by?sal?desc;

6.5.2 按照别名排序

按照员工薪水的2倍排序

hive?(default)>?select?ename,?sal*2?twosal?from?emp?order?by?twosal;

6.5.3 多个列排序

按照部门和工资升序排序

hive?(default)>?select?ename,?deptno,?sal?from?emp?order?by?deptno,?sal;

6.5.4 每个MapReduce内部排序（sort by）

??sort by：对于每个Reducer内部进行排序，对全局结果集来说不是排序，有多个Reducer。
1、设置reduce个数

hive?(default)>?set?mapreduce.job.reduces=3;

2、查看设置reduce个数

hive?(default)>?set?mapreduce.job.reduces;

3、根据部门编号降序查看员工信息

hive?(default)>?select?*?from?emp?sort?by?empno?desc;

4、将查询结果导入到文件中（按照部门编号降序排序）

hive?(default)>?insert?overwrite?local?directory?'/opt/module/datas/sortby-result'
select?*?from?emp?sort?by?deptno?desc;

6.5.5 分区排序（distribute by）

hive?(default)>?set?mapreduce.job.reduces=3;
hive?(default)>?insert?overwrite?local?directory?'/opt/module/datas/distributeby-result'?
select?*?from?emp?distribute?by?deptno?sort?by?empno?desc;

6.5.6 cluster by

hive?(default)>?select?*?from?emp?cluster?by?deptno;
hive?(default)>?select?*?from?emp?distribute?by?deptno?sort?by?deptno;

??注意：按照部门编号分区，不一定就是固定死的数值，可以是20号和30号部门分到一个分区里面去。

6.6 分桶及抽样查询

6.6.1 分桶表数据存储

1001????ss1
1002????ss2
1003????ss3
1004????ss4
1005????ss5
1006????ss6
1007????ss7
1008????ss8
1009????ss9
1010????ss10
1011????ss11
1012????ss12
1013????ss13
1014????ss14
1015????ss15
1016????ss16

（2）创建分桶表

create?table?stu_buck(id?int,?name?string)
clustered?by(id)?
into?4?buckets
row?format?delimited?fields?terminated?by?'\t';

（3）查看表结构

hive?(default)>?desc?formatted?stu_buck;
Num?Buckets:????????????4?????

（4）导入数据到分桶表中

hive?(default)>?load?data?local?inpath?'/opt/module/datas/stu_buck.txt'?into?table?stu_buck;

（5）查看创建的分桶表中是否分成4个桶，如下图所示

发现并没有分成4个桶。是什么原因呢？

2、创建分桶表时，数据通过子查询的方式导入
（1）先建一个普通的stu表

create?table?stu(id?int,?name?string)
row?format?delimited?fields?terminated?by?'\t';

（2）向普通的stu表中导入数据

load?data?local?inpath?'/opt/module/datas/stu_buck.txt'?into?table?stu;

（3）清空stu_buck表中数据

hive?(default)>?truncate?table?stu_buck;
hive?(default)>?select?*?from?stu_buck;

（4）导入数据到分桶表，通过子查询的方式

hive?(default)>?insert?into?table?stu_buck
select?id,?name?from?stu;

（5）发现还是只有一个分桶，如下图所示

（6）需要设置一个属性

hive?(default)>?set?hive.enforce.bucketing=true;
hive?(default)>?set?mapreduce.job.reduces=-1;?--?-1表示reduce的个数不是预先设置好了，而是在执行HQL语句的时候自动分析出来需要几个reduce。
hive?(default)>?truncate?table?stu_buck;
hive?(default)>?insert?into?table?stu_buck
select?id,?name?from?stu;

分桶成功截图如下图所示

（7）查询分桶的数据

hive?(default)>?select?*?from?stu_buck;

stu_buck.id????stu_buck.name
1016????ss16
1012????ss12
1008????ss8
1004????ss4
1009????ss9
1005????ss5
1001????ss1
1013????ss13
1010????ss10
1002????ss2
1006????ss6
1014????ss14
1003????ss3
1011????ss11
1007????ss7
1015????ss15

读取文件顺序的解释如下图所示：

6.6.2 分桶抽样查询

hive?(default)>?select?*?from?stu_buck?tablesample(bucket?1?out?of?4?on?id);
OK
stu_buck.id????stu_buck.name
1016????ss16
1012????ss12
1008????ss8
1004????ss4

或者

hive?(default)>?select?*?from?stu_buck?tablesample(bucket?1?out?of?8?on?id);
OK
stu_buck.id????stu_buck.name
1016????ss16
1008????ss8

FAILED:?SemanticException?[Error?10061]:?Numerator?should?not?be?bigger?than?denominator?in?sample?clause?for?table?stu_buck

6.7 其他常用查询函数（Hive高级）

6.7.1 给空字段赋值函数

hive?(default)>?select?nvl(comm,-1)?from?emp;
OK
_c0
-1.0
300.0
500.0
20.0
1400.0
-1.0
-1.0
-1.0
-1.0
0.0
-1.0
-1.0
-1.0
-1.0

或者

hive?(default)>?select?nvl(comm,ename)?from?emp;
OK
_c0
SMITH
300.0
500.0
20.0
1400.0
BLAKE
CLARK
SCOTT
KING
0.0
ADAMS
JAMES
FORD
MILLER

4、查询：如果员工的comm为NULL，则用领导id代替

hive?(default)>?select?nvl(comm,mgr)?from?emp;
OK
_c0
7902.0
300.0
500.0
20.0
1400.0
7839.0
7839.0
7566.0
NULL
0.0
7788.0
7698.0
7566.0
7782.0

6.7.2 case … when … then … else … end 函数

??作用：替换数据。
1、数据准备

2、需求
求出不同部门男女各多少人。结果如下：

A?????2???????1
B?????1???????2

3、创建本地emp_sex.txt，导入数据

[atguigu@hadoop102?datas]$?vim?emp_sex.txt
悟空????A???男
大海????A???男
宋宋????B???男
凤姐????A???女
婷姐????B???女
婷婷????B???女

4、创建hive表并导入数据

create?table?emp_sex(
name?string,?
dept_id?string,?
sex?string
)?
row?format?delimited?fields?terminated?by?"\t";

load?data?local?inpath?'/opt/module/datas/emp_sex.txt'?into?table?emp_sex;

5、按需求查询数据

select?
??dept_id,
??sum(case?sex?when?'男'?then?1?else?0?end)?male_count,
??sum(case?sex?when?'女'?then?1?else?0?end)?female_count
from?
??emp_sex
group?by
??dept_id;

6.7.2 行转列相关函数

??注意：CONCAT()和CONCAT_WS()都是UDTF函数，COLLECT_SET()函数类似聚合函数。

示例1）

示例2）

示例3)

2、数据准备
person_info.txt
hadoop入门到实战（9）hive从入门到实战三_mapreduce_12

3、需求
把星座和血型一样的人归类到一起。结果如下：

射手座,A????????????大海|凤姐
白羊座,A????????????孙悟空|猪八戒
白羊座,B????????????宋宋

分析过程：

4、创建本地person_info.txt，导入数据

[atguigu@hadoop102?datas]$?vim?person_info.txt
孙悟空????白羊座?A
大海????射手座?A
宋宋????白羊座?B
猪八戒????白羊座?A
凤姐????射手座?A

5、创建hive表并导入数据

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

load?data?local?inpath?'/opt/module/datas/person_info.txt'?into?table?person_info;

6、按需求查询数据

select?concat_ws(",",?constellation,?blood_type)?as?c_b,?name?from?person_info;

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

select?
??t1.c_b,?collect_set(t1.name)
from?
??(select?concat_ws(",",?constellation,?blood_type)?as?c_b,?name?from?person_info)?t1
group?by
??t1.c_b;

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

select?
??t1.c_b,?concat_ws("|",?collect_set(t1.name))
from?
??(select?concat_ws(",",?constellation,?blood_type)?as?c_b,?name?from?person_info)?t1
group?by
??t1.c_b;

6.7.3 列转行相关函数

movie???????????category

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

3、需求
将电影分类中的数组数据展开。结果如下：

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

4、创建本地movie.txt，导入数据

[atguigu@hadoop102?datas]$?vim?movie_info.txt
《疑犯追踪》????悬疑,动作,科幻,剧情
《Lie?to?me》???悬疑,警匪,动作,心理,剧情
《战狼2》???战争,动作,灾难

5、创建hive表并导入数据

create?table?movie_info(
movie?string,?
category?array<string>
)?
row?format?delimited?fields?terminated?by?"\t"
collection?items?terminated?by?",";

load?data?local?inpath?"/opt/module/datas/movie_info.txt"?into?table?movie_info;

6、按需求查询数据

select?
??movie
??explode(category)
from
??movie_info;

上面是错误的。假设能执行的话，得到的是笛卡尔积。

小结：像split,explode等UDTF函数，是不能跟原表的字段直接进行查询的，UDTF函数一定要和lateral?view联合在一块用。
-----------------------------------------

select
??movie,
??category_name
from?
??movie_info?
lateral?view?explode(category)?table_tmp?as?category_name;?--lateral?view?对原表的字段进行了侧写，得到侧写表和侧写字段。

6.7.4 窗口函数

name????orderdate???cost

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

[atguigu@hadoop102?datas]$?vim?business.txt

5、创建hive表并导入数据

create?table?business(
name?string,?
orderdate?string,
cost?int
)
ROW?FORMAT?DELIMITED?FIELDS?TERMINATED?BY?',';

load?data?local?inpath?"/opt/module/datas/business.txt"?into?table?business;

6、按需求查询数据
（1）查询在2017年4月份购买过的顾客及总人数

select?name,?count(*)?
from?business
where?substring(orderdate,?1,?7)="2017-04"
group?by?name;

+-------+------+--+
|?name??|?_c1??|
+-------+------+--+
|?jack??|?1????|
|?mart??|?4????|
+-------+------+--+

-----------------------------------------
select?name,?count(*)?over()
from?business
where?substring(orderdate,?1,?7)="2017-04"
group?by?name;

+-------+-----------------+--+
|?name??|?count_window_0??|
+-------+-----------------+--+
|?mart??|?2???????????????|
|?jack??|?2???????????????|
+-------+-----------------+--+

（2）查询顾客的购买明细及月购买总额

select?sum(cost)
from?business;

select?*,?
sum(cost)?over()?--?表示每一行数据就是一个窗口
from?business;

select?month(orderdate)?from?business;?--?按月份输出

select?*,?
sum(cost)?over(distribute?by?month(orderdate))?--?按月份分区
from?business;

select?*,?
sum(cost)?over(partition?by?month(orderdate))?--?按月份分区（同上）
from?business;

（3）上述的场景,要将cost按照日期进行累加

select?*?from?business?sort?by?orderdate;

select?*,?
sum(cost)?over(sort?by?orderdate?rows?between?UNBOUNDED?PRECEDING?and?CURRENT?ROW)
from?business;

select?*,?
sum(cost)?over(distribute?by?name?sort?by?orderdate?rows?between?UNBOUNDED?PRECEDING?and?CURRENT?ROW)?
from?business;

select?*,?
sum(cost)?over(partition?by?name?sort?by?orderdate?rows?between?UNBOUNDED?PRECEDING?and?CURRENT?ROW)?
from?business;

select?name,?orderdate,?cost,?
sum(cost)?over()?as?sample1,?--?只有一个分区，所有行相加，得一个值
sum(cost)?over(partition?by?name)?as?sample2,?--?按name分区，有多个分区，分区内数据相加，每个分区得一个值
sum(cost)?over(order?by?orderdate)?as?sample3,?--?按orderdate排序，只有一个分区，区内数据累加
sum(cost)?over(partition?by?name?order?by?orderdate)?as?sample4,?--?按name分区，按orderdate排序，有多个分区，区内数据各自累加
sum(cost)?over(partition?by?name?order?by?orderdate?rows?between?UNBOUNDED?PRECEDING?and?CURRENT?ROW)?as?sample5,?--?和sample4一样，由起点到当前行的聚合
sum(cost)?over(partition?by?name?order?by?orderdate?rows?between?1?PRECEDING?and?CURRENT?ROW)?as?sample6,?--?当前行和前面一行做聚合
sum(cost)?over(partition?by?name?order?by?orderdate?rows?between?1?PRECEDING?and?1?FOLLOWING)?as?sample7,?--?当前行和前边一行及后面一行
sum(cost)?over(partition?by?name?order?by?orderdate?rows?between?CURRENT?ROW?and?UNBOUNDED?FOLLOWING)?as?sample8,?--?当前行及后面所有行
from?business;

演示1如下：

select?name,?orderdate,?cost,
sum(cost)?over()?as?sample1
from?business;
+-------+-------------+-------+----------+--+
|?name??|??orderdate??|?cost??|?sample1??|
+-------+-------------+-------+----------+--+
|?mart??|?2017-04-13??|?94????|?661??????|
|?neil??|?2017-06-12??|?80????|?661??????|
|?mart??|?2017-04-11??|?75????|?661??????|
|?neil??|?2017-05-10??|?12????|?661??????|
|?mart??|?2017-04-09??|?68????|?661??????|
|?mart??|?2017-04-08??|?62????|?661??????|
|?jack??|?2017-01-08??|?55????|?661??????|
|?tony??|?2017-01-07??|?50????|?661??????|
|?jack??|?2017-04-06??|?42????|?661??????|
|?jack??|?2017-01-05??|?46????|?661??????|
|?tony??|?2017-01-04??|?29????|?661??????|
|?jack??|?2017-02-03??|?23????|?661??????|
|?tony??|?2017-01-02??|?15????|?661??????|
|?jack??|?2017-01-01??|?10????|?661??????|
+-------+-------------+-------+----------+--+

演示2如下：

select?name,?orderdate,?cost,
sum(cost)?over(partition?by?name)?as?sample2
from?business;
+-------+-------------+-------+----------+--+
|?name??|??orderdate??|?cost??|?sample2??|
+-------+-------------+-------+----------+--+
|?jack??|?2017-01-05??|?46????|?176??????|
|?jack??|?2017-01-08??|?55????|?176??????|
|?jack??|?2017-01-01??|?10????|?176??????|
|?jack??|?2017-04-06??|?42????|?176??????|
|?jack??|?2017-02-03??|?23????|?176??????|
|?mart??|?2017-04-13??|?94????|?299??????|
|?mart??|?2017-04-11??|?75????|?299??????|
|?mart??|?2017-04-09??|?68????|?299??????|
|?mart??|?2017-04-08??|?62????|?299??????|
|?neil??|?2017-05-10??|?12????|?92???????|
|?neil??|?2017-06-12??|?80????|?92???????|
|?tony??|?2017-01-04??|?29????|?94???????|
|?tony??|?2017-01-02??|?15????|?94???????|
|?tony??|?2017-01-07??|?50????|?94???????|
+-------+-------------+-------+----------+--+

演示3如下：

select?name,?orderdate,?cost,
sum(cost)?over(order?by?orderdate)?as?sample3
from?business;
+-------+-------------+-------+----------+--+
|?name??|??orderdate??|?cost??|?sample3??|
+-------+-------------+-------+----------+--+
|?jack??|?2017-01-01??|?10????|?10???????|
|?tony??|?2017-01-02??|?15????|?25???????|
|?tony??|?2017-01-04??|?29????|?54???????|
|?jack??|?2017-01-05??|?46????|?100??????|
|?tony??|?2017-01-07??|?50????|?150??????|
|?jack??|?2017-01-08??|?55????|?205??????|
|?jack??|?2017-02-03??|?23????|?228??????|
|?jack??|?2017-04-06??|?42????|?270??????|
|?mart??|?2017-04-08??|?62????|?332??????|
|?mart??|?2017-04-09??|?68????|?400??????|
|?mart??|?2017-04-11??|?75????|?475??????|
|?mart??|?2017-04-13??|?94????|?569??????|
|?neil??|?2017-05-10??|?12????|?581??????|
|?neil??|?2017-06-12??|?80????|?661??????|
+-------+-------------+-------+----------+--+

注意：
select?name,?orderdate,?cost,
sum(cost)?over(sort?by?orderdate)?as?sample3
from?business;
演示结果同上。
区别：使用sort?by可以设定reducer的个数，order?by不能够设定reducer的个数，默认是1个。即使设定了也没用！

演示4如下：

select?name,?orderdate,?cost,
sum(cost)?over(partition?by?name?order?by?orderdate)?as?sample4
from?business;
+-------+-------------+-------+----------+--+
|?name??|??orderdate??|?cost??|?sample4??|
+-------+-------------+-------+----------+--+
|?jack??|?2017-01-01??|?10????|?10???????|
|?jack??|?2017-01-05??|?46????|?56???????|
|?jack??|?2017-01-08??|?55????|?111??????|
|?jack??|?2017-02-03??|?23????|?134??????|
|?jack??|?2017-04-06??|?42????|?176??????|
|?mart??|?2017-04-08??|?62????|?62???????|
|?mart??|?2017-04-09??|?68????|?130??????|
|?mart??|?2017-04-11??|?75????|?205??????|
|?mart??|?2017-04-13??|?94????|?299??????|
|?neil??|?2017-05-10??|?12????|?12???????|
|?neil??|?2017-06-12??|?80????|?92???????|
|?tony??|?2017-01-02??|?15????|?15???????|
|?tony??|?2017-01-04??|?29????|?44???????|
|?tony??|?2017-01-07??|?50????|?94???????|
+-------+-------------+-------+----------+--+

演示5如下：

select?name,?orderdate,?cost,
sum(cost)?over(partition?by?name?order?by?orderdate?rows?between?UNBOUNDED?PRECEDING?and?CURRENT?ROW)?as?sample5
from?business;
+-------+-------------+-------+----------+--+
|?name??|??orderdate??|?cost??|?sample5??|
+-------+-------------+-------+----------+--+
|?jack??|?2017-01-01??|?10????|?10???????|
|?jack??|?2017-01-05??|?46????|?56???????|
|?jack??|?2017-01-08??|?55????|?111??????|
|?jack??|?2017-02-03??|?23????|?134??????|
|?jack??|?2017-04-06??|?42????|?176??????|
|?mart??|?2017-04-08??|?62????|?62???????|
|?mart??|?2017-04-09??|?68????|?130??????|
|?mart??|?2017-04-11??|?75????|?205??????|
|?mart??|?2017-04-13??|?94????|?299??????|
|?neil??|?2017-05-10??|?12????|?12???????|
|?neil??|?2017-06-12??|?80????|?92???????|
|?tony??|?2017-01-02??|?15????|?15???????|
|?tony??|?2017-01-04??|?29????|?44???????|
|?tony??|?2017-01-07??|?50????|?94???????|
+-------+-------------+-------+----------+--+

演示6如下：

select?name,?orderdate,?cost,
sum(cost)?over(partition?by?name?order?by?orderdate?rows?between?1?PRECEDING?and?CURRENT?ROW)?as?sample6
from?business;
+-------+-------------+-------+----------+--+
|?name??|??orderdate??|?cost??|?sample6??|
+-------+-------------+-------+----------+--+
|?jack??|?2017-01-01??|?10????|?10???????|
|?jack??|?2017-01-05??|?46????|?56???????|
|?jack??|?2017-01-08??|?55????|?101??????|
|?jack??|?2017-02-03??|?23????|?78???????|
|?jack??|?2017-04-06??|?42????|?65???????|
|?mart??|?2017-04-08??|?62????|?62???????|
|?mart??|?2017-04-09??|?68????|?130??????|
|?mart??|?2017-04-11??|?75????|?143??????|
|?mart??|?2017-04-13??|?94????|?169??????|
|?neil??|?2017-05-10??|?12????|?12???????|
|?neil??|?2017-06-12??|?80????|?92???????|
|?tony??|?2017-01-02??|?15????|?15???????|
|?tony??|?2017-01-04??|?29????|?44???????|
|?tony??|?2017-01-07??|?50????|?79???????|
+-------+-------------+-------+----------+--+

演示7如下：

select?name,?orderdate,?cost,
sum(cost)?over(partition?by?name?order?by?orderdate?rows?between?1?PRECEDING?and?1?FOLLOWING)?as?sample7
from?business;
+-------+-------------+-------+----------+--+
|?name??|??orderdate??|?cost??|?sample7??|
+-------+-------------+-------+----------+--+
|?jack??|?2017-01-01??|?10????|?56???????|
|?jack??|?2017-01-05??|?46????|?111??????|
|?jack??|?2017-01-08??|?55????|?124??????|
|?jack??|?2017-02-03??|?23????|?120??????|
|?jack??|?2017-04-06??|?42????|?65???????|
|?mart??|?2017-04-08??|?62????|?130??????|
|?mart??|?2017-04-09??|?68????|?205??????|
|?mart??|?2017-04-11??|?75????|?237??????|
|?mart??|?2017-04-13??|?94????|?169??????|
|?neil??|?2017-05-10??|?12????|?92???????|
|?neil??|?2017-06-12??|?80????|?92???????|
|?tony??|?2017-01-02??|?15????|?44???????|
|?tony??|?2017-01-04??|?29????|?94???????|
|?tony??|?2017-01-07??|?50????|?79???????|
+-------+-------------+-------+----------+--+

演示8如下：

select?name,?orderdate,?cost,
sum(cost)?over(partition?by?name?order?by?orderdate?rows?between?CURRENT?ROW?and?UNBOUNDED?FOLLOWING)?as?sample8
from?business;
+-------+-------------+-------+----------+--+
|?name??|??orderdate??|?cost??|?sample8??|
+-------+-------------+-------+----------+--+
|?jack??|?2017-01-01??|?10????|?176??????|
|?jack??|?2017-01-05??|?46????|?166??????|
|?jack??|?2017-01-08??|?55????|?120??????|
|?jack??|?2017-02-03??|?23????|?65???????|
|?jack??|?2017-04-06??|?42????|?42???????|
|?mart??|?2017-04-08??|?62????|?299??????|
|?mart??|?2017-04-09??|?68????|?237??????|
|?mart??|?2017-04-11??|?75????|?169??????|
|?mart??|?2017-04-13??|?94????|?94???????|
|?neil??|?2017-05-10??|?12????|?92???????|
|?neil??|?2017-06-12??|?80????|?80???????|
|?tony??|?2017-01-02??|?15????|?94???????|
|?tony??|?2017-01-04??|?29????|?79???????|
|?tony??|?2017-01-07??|?50????|?50???????|
+-------+-------------+-------+----------+--+

（4）查询顾客上次的购买时间

select?*,?
lag(orderdate,?1)?over(distribute?by?name?sort?by?orderdate)
from?business;

查询顾客上次的购买时间和下次购买时间
select?*,?
lag(orderdate,?1)?over(distribute?by?name?sort?by?orderdate)?as?lag1,
lead(orderdate,?1)?over(distribute?by?name?sort?by?orderdate)?as?lead1
from?business;

（5）查询前20%时间的订单信息

select?*,
ntile(5)?over(sort?by?orderdate)?as?gid
from?business;

select?*
from?(select?*,
???????ntile(5)?over(sort?by?orderdate)?as?gid
???????from?business)?as?t
where?t.gid=1;

select?*
from?(select?name,?orderdate,?cost,
???????ntile(5)?over(sort?by?orderdate)?as?gid
???????from?business)?as?t
where?t.gid=1;

6.7.5 rank函数

3、需求
??计算每门学科的成绩排名。
4、创建本地score.txt，导入数据

[atguigu@hadoop102?datas]$?vim?score.txt

5、创建hive表并导入数据

create?table?score(
name?string,
subject?string,?
score?int)?
row?format?delimited?fields?terminated?by?"\t";

load?data?local?inpath?'/opt/module/datas/score.txt'?into?table?score;

6、按需求查询数据

select?*,
rank()?over(partition?by?subject?order?by?score?desc)?rp,
dense_rank()over(partition?by?subject?order?by?score?desc)?drp,
row_number()?over(partition?by?subject?order?by?score?desc)?rnp
from?score;

结果截图：

回到顶部

第7章函数（Hive高级）

7.1 系统内置函数

1、查看系统自带的函数

hive>?show?functions;

2、显示自带的函数的用法

hive>?desc?function?upper;

3、详细显示自带的函数的用法

hive>?desc?function?extended?upper;

7.2 自定义函数

7.3 自定义UDF函数

1、创建一个Maven工程Hive
2、导入依赖

<dependencies>
????<!--?https://mvnrepository.com/artifact/org.apache.hive/hive-exec?-->
????<dependency>
????????<groupId>org.apache.hive</groupId>
????????<artifactId>hive-exec</artifactId>
????????<version>1.2.1</version>
????</dependency>
</dependencies>

3、创建一个类

package?com.atguigu.hive;

import?org.apache.hadoop.hive.ql.exec.UDF;

/**
?*?@author?chenmingjun
?*?@date?2019-02-27?17:50
?*/
public?class?HiveUDF?extends?UDF?{

????public?String?evaluate(final?String?s)?{

????????if?(s?==?null)?{
????????????return?null;
????????}

????????return?s.toLowerCase();
????}
}

4、打成jar包上传到服务器/opt/module/jars/udf.jar
5、将jar包添加到hive的class path

hive?(default)>?add?jar?/opt/module/jars/udf.jar;

6、创建临时函数与开发好的java class关联

hive?(default)>?create?temporary?function?mylower?as?"com.atguigu.hive.HiveUDF";

7、即可在hql中使用自定义的函数strip

hive?(default)>?select?ename,?mylower(ename)?lowername?from?emp;
OK
ename????lowername
SMITH????smith
ALLEN????allen
WARD????ward
JONES????jones
MARTIN????martin
BLAKE????blake
CLARK????clark
SCOTT????scott
KING????king
TURNER????turner
ADAMS????adams
JAMES????james
FORD????ford
MILLER????miller

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。

上一篇：hadoop入门到实战（10）hive从入门到实战四

下一篇：hadoop入门到实战（8）hive从入门到实战二

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	便宜云服务器学堂
便宜云服务器	软考资讯	汽车开发者社区