大数据学习day28-----hive03------1. null值处理,子串,拼接,类型转换 2.行转列,列转行 3. 窗口函数(over,lead,lag等函数) 4.rank(行号函数)5. json解析函数 6.jdbc连接hive,企业级调优

1. null值处理,子串,拼接,类型转换

(1) 空字段赋值(null值处理)  

  当表中的某个字段为null时,比如奖金,当你要统计一个人的总工资时,字段为null的值就无法处理,这个时候就可以使用NVL函数

  NVL:给值为NULL的数据赋值,它的格式是NVL( string1, replace_with)。它的功能是如果string1为NULL,则NVL函数返回replace_with的值,否则返回string1的值,如果两个参数都为NULL ,则返回NULL。

例:

创建dept表,并导入数据

create external table if not exists dept(
deptno int,
dname string,
loc int -------位置
)
row format delimited fields terminated by '\t';
load data local inpath "/root/hive/dept.txt" into table dept;

创建emp表,并导入数据

create external table if not exists emp(
empno int,
ename string,
job string,
mgr int, -------领导
hiredate string,
sal double,
comm double, ------奖金
deptno int)
row format delimited fields terminated by '\t';
load data local inpath '/hive/emp.txt' into table emp;

需求:统计员工的总工资

select empno ,ename ,(sal+nvl(comm,0)) gz  from emp ;

结果

大数据学习day28-----hive03------1. null值处理,子串,拼接,类型转换 2.行转列,列转行 3. 窗口函数(over,lead,lag等函数) 4.rank(行号函数)5. json解析函数 6.jdbc连接hive,企业级调优

(2)substr(字段 , 起始位置1 , 长度)   , substr(字段 , -n) 从后面开始获取n个字符的字符串      

(3)cast() 数据类型转换

2. 行转换,列转换

2.1 行转列

(1)

collect_set(name) 将多行字段的值收集成数组 去重
collect_list(字段) 将多行的字段收集成数组 不去重

范围:全局范围的收集(若加上group by,则是组内收集)

(2)concat_ws 参数一  拼接符  参数二 集合 :将集合中的数据按参数一拼接

(3)concat    参数....  多个元素直接拼接 :

concat(dname , "," , gender) ---这样拼接麻烦

案例:  数据:

大数据学习day28-----hive03------1. null值处理,子串,拼接,类型转换 2.行转列,列转行 3. 窗口函数(over,lead,lag等函数) 4.rank(行号函数)5. json解析函数 6.jdbc连接hive,企业级调优

创建表并导入数据

create table teacher(
name string ,
dname string ,
gender string
)
row format delimited fields terminated by "\t" ; load data local inpath "/hive/teacher.txt" into table teacher ;

需求:求每个部门的各种性别的人的(人名)列表

错误的写法:

select
dname,gender,
name -----此处一列会有多个name,这样必然会报错,此处要么写grou by的key或是聚合函数,如count
from
teacher
group by dname,gender ;

正确的写法:

select
dname,gender,
collect_set(name)
from
teacher
group by dname,gender ;

得到的结果

大数据学习day28-----hive03------1. null值处理,子串,拼接,类型转换 2.行转列,列转行 3. 窗口函数(over,lead,lag等函数) 4.rank(行号函数)5. json解析函数 6.jdbc连接hive,企业级调优

优化,将dname,gender合成一列

select
base_data ,
collect_set(name) -- 组内将name字段收集成一个数组
from
(select
name ,
concat(dname , "," , gender) base_data
from
teacher)t1
group by base_data;

结果(这种形式更加直观)

大数据学习day28-----hive03------1. null值处理,子串,拼接,类型转换 2.行转列,列转行 3. 窗口函数(over,lead,lag等函数) 4.rank(行号函数)5. json解析函数 6.jdbc连接hive,企业级调优

2.2 列转行

数据:

大数据学习day28-----hive03------1. null值处理,子串,拼接,类型转换 2.行转列,列转行 3. 窗口函数(over,lead,lag等函数) 4.rank(行号函数)5. json解析函数 6.jdbc连接hive,企业级调优

建表并导入数据

create table movie(
name string ,
category array<string>
)
row format delimited fields terminated by "\t"
collection items terminated by "," ; load data local inpath "/root/hive/movie.txt" into table movie ;

表格如下

大数据学习day28-----hive03------1. null值处理,子串,拼接,类型转换 2.行转列,列转行 3. 窗口函数(over,lead,lag等函数) 4.rank(行号函数)5. json解析函数 6.jdbc连接hive,企业级调优

2.2.1 函数说明

(1)EXPLODE(col):将hive一列中复杂的array或者map结构拆分成多行。 [爆炸, 炸裂]  

select explode(category) from movie ;

结果

大数据学习day28-----hive03------1. null值处理,子串,拼接,类型转换 2.行转列,列转行 3. 窗口函数(over,lead,lag等函数) 4.rank(行号函数)5. json解析函数 6.jdbc连接hive,企业级调优

(2)Lateral View

lateralView: LATERAL VIEW udtf(expression) tableAlias AS columnAlias (',' columnAlias)*

注意:tableAlias表示虚拟表的别名,columnAlias(列的别名)对应这个虚拟表(一列)

hive官方描述

  Lateral view is used in conjunction with user-defined table generating functions such as explode(). As mentioned in Built-in Table-Generating Functions, a UDTF generates zero or more output rows for each input row. A lateral view first applies the UDTF to each row of base table and then joins resulting output rows to the input rows to form a virtual table having the supplied table alias.

翻译:

  Lateral view与用户定义的表生成函数(如explode())一起使用。正如在内置的表生成函数中提到的,UDTF为每个输入行生成零个或多个输出行。Lateral view首先将UDTF应用于基表的每一行,然后将产生的输出行连接到输入行,形成一个具有表别名的虚拟表。

select name,ty
from
movie
lateral view explode(category) temp_table as ty ;

结果

大数据学习day28-----hive03------1. null值处理,子串,拼接,类型转换 2.行转列,列转行 3. 窗口函数(over,lead,lag等函数) 4.rank(行号函数)5. json解析函数 6.jdbc连接hive,企业级调优

此处不能如下(个人理解:temp_table为表,所以不能select)

select
name,
temp_table
from
movie
lateral view explode(category) temp_table ;

此处若换成如下命令:

select
name
from
movie
lateral view explode(category) temp_table ;

结果如下

大数据学习day28-----hive03------1. null值处理,子串,拼接,类型转换 2.行转列,列转行 3. 窗口函数(over,lead,lag等函数) 4.rank(行号函数)5. json解析函数 6.jdbc连接hive,企业级调优

可见Lateral view可将产生的输出行连接到输入行

3. 窗口函数(over( ))

  我们都知道在sql中有一类函数叫做聚合函数,例如sum()、avg、max()等等,这类函数可以将多行数据按照规则聚集为一行,一般来讲聚集后的行数是要少于聚集前的行数的。但是有时我们想要既显示聚集前的数据,又要显示聚集后的数据,这时我们便引入了窗口函数(*).

  在深入研究Over字句之前,一定要注意:在sql处理中,窗口函数都是最后一步执行,而且仅位于Order by字句之前。

此处以练习0为例 解释上面打“*”号的这句话

该例中若想获取聚集前的数据(name,createtime,cost),聚集后的数据(此处以count(*)为例),当使用以下sql语句查询聚集前后的数据时

SELECT
name,createtime,cost,
count(*)
FROM
orders

会报错(count后只有一条结果,其不是逐行运算的,与聚合前的数据不能放在一起)

大数据学习day28-----hive03------1. null值处理,子串,拼接,类型转换 2.行转列,列转行 3. 窗口函数(over,lead,lag等函数) 4.rank(行号函数)5. json解析函数 6.jdbc连接hive,企业级调优

但就是想要得到聚合前后的数据该怎么办呢========>窗口函数,如下

大数据学习day28-----hive03------1. null值处理,子串,拼接,类型转换 2.行转列,列转行 3. 窗口函数(over,lead,lag等函数) 4.rank(行号函数)5. json解析函数 6.jdbc连接hive,企业级调优

此处的over()可以指定count(*)工作的数据窗口大小,如下sql语句

SELECT
name,createtime,cost,
count(*) over(partition by name) //此处的over()指定count()函数工作的数据窗口大小为同一个人的数据
FROM
orders

运行结果

大数据学习day28-----hive03------1. null值处理,子串,拼接,类型转换 2.行转列,列转行 3. 窗口函数(over,lead,lag等函数) 4.rank(行号函数)5. json解析函数 6.jdbc连接hive,企业级调优

3.1 相关函数的说明

over():窗口函数,指定分析函数、聚合函数等工作的数据窗口大小,这个数据窗口大小可能会随着行的变化而变化(若直接over(),则是分析函数执行前,表格的行数)

CURRENT ROW:当前行  current row

n PRECEDING:往前n行数据  n  preceding

n FOLLOWING:往后n行数据  n following

UNBOUNDED:起点,UNBOUNDED PRECEDING 表示从前面的起点, UNBOUNDED FOLLOWING表示到后面的终点  unbound preceding  unbound following

LAG(col, n):相对某运算的行,往前第n行数据  lag  参数一 字段  n --------见练习(4)

LEAD(col,n):相对某运算的行,往后第n行数据 lead------------见练习(4)

NTILE(n):把有序分区中的行分发到指定数据的组中,各个组有编号,编号从1开始,对于每一行,NTILE返回此行所属的组的编号。注意:n必须为int类型。  ntile(5) ----见练习(5)

大数据学习day28-----hive03------1. null值处理,子串,拼接,类型转换 2.行转列,列转行 3. 窗口函数(over,lead,lag等函数) 4.rank(行号函数)5. json解析函数 6.jdbc连接hive,企业级调优

大数据学习day28-----hive03------1. null值处理,子串,拼接,类型转换 2.行转列,列转行 3. 窗口函数(over,lead,lag等函数) 4.rank(行号函数)5. json解析函数 6.jdbc连接hive,企业级调优

3.2 案例和练习

案例:以1中的emp,dept表为例(over()函数)

此时count(*) over()计算的表格为第一个count()执行后得到的数据

大数据学习day28-----hive03------1. null值处理,子串,拼接,类型转换 2.行转列,列转行 3. 窗口函数(over,lead,lag等函数) 4.rank(行号函数)5. json解析函数 6.jdbc连接hive,企业级调优

当去掉count(*)后,此时,count(*) over()计算的数据为原始数据,得到的结果如下

大数据学习day28-----hive03------1. null值处理,子串,拼接,类型转换 2.行转列,列转行 3. 窗口函数(over,lead,lag等函数) 4.rank(行号函数)5. json解析函数 6.jdbc连接hive,企业级调优

练习0:

数据如下

大数据学习day28-----hive03------1. null值处理,子串,拼接,类型转换 2.行转列,列转行 3. 窗口函数(over,lead,lag等函数) 4.rank(行号函数)5. json解析函数 6.jdbc连接hive,企业级调优

建表并导入数据

create table orders(
name string ,
createtime string ,
cost double
)
row format delimited fields terminated by "," ;
load data local inpath "/root/hive/orders.txt" into table orders ;

得到如下表

大数据学习day28-----hive03------1. null值处理,子串,拼接,类型转换 2.行转列,列转行 3. 窗口函数(over,lead,lag等函数) 4.rank(行号函数)5. json解析函数 6.jdbc连接hive,企业级调优

 (1)查询在2017年4月份购买过的顾客即总人数

第一步

select
name ,
createtime
from
orders
group by name , createtime
having substr(createtime ,1 , 7) = "2017-04" ;

结果

大数据学习day28-----hive03------1. null值处理,子串,拼接,类型转换 2.行转列,列转行 3. 窗口函数(over,lead,lag等函数) 4.rank(行号函数)5. json解析函数 6.jdbc连接hive,企业级调优

第二步

select
name ,
count(*) ,
count(*) over() tatol_num
from
(select
name ,
createtime
from
orders
group by name , createtime
having substr(createtime ,1 , 7) = "2017-04")t
group by name ;

结果

大数据学习day28-----hive03------1. null值处理,子串,拼接,类型转换 2.行转列,列转行 3. 窗口函数(over,lead,lag等函数) 4.rank(行号函数)5. json解析函数 6.jdbc连接hive,企业级调优

第三步(最终结果)

大数据学习day28-----hive03------1. null值处理,子串,拼接,类型转换 2.行转列,列转行 3. 窗口函数(over,lead,lag等函数) 4.rank(行号函数)5. json解析函数 6.jdbc连接hive,企业级调优

(2)查询顾客的购买明细及月购买总额  

select
name ,
createtime,
cost ,
sum(cost) over(partition by name, month(createtime))
from
orders ;

结果

大数据学习day28-----hive03------1. null值处理,子串,拼接,类型转换 2.行转列,列转行 3. 窗口函数(over,lead,lag等函数) 4.rank(行号函数)5. json解析函数 6.jdbc连接hive,企业级调优

 

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

select
name ,
createtime,
cost ,
sum(cost) over(partition by name) month_total,
sum(cost) over(partition by name order by createtime ) --每个人按时间累加
from
orders ;

结果

大数据学习day28-----hive03------1. null值处理,子串,拼接,类型转换 2.行转列,列转行 3. 窗口函数(over,lead,lag等函数) 4.rank(行号函数)5. json解析函数 6.jdbc连接hive,企业级调优

(4)查询顾客上次的购买时间(使用lag)

select
* ,
lag(createtime ,1 , "首次购买") over(partition by name order by createtime) ,
lead(createtime ,1) over(partition by name order by createtime)
from
orders ;

结果

大数据学习day28-----hive03------1. null值处理,子串,拼接,类型转换 2.行转列,列转行 3. 窗口函数(over,lead,lag等函数) 4.rank(行号函数)5. json解析函数 6.jdbc连接hive,企业级调优

 (5)查询前20%时间的订单信息(使用lead)

select
*
from
(select
* ,
ntile(5) over(order by createtime) n
from
orders)t
where n = 1
;

结果

大数据学习day28-----hive03------1. null值处理,子串,拼接,类型转换 2.行转列,列转行 3. 窗口函数(over,lead,lag等函数) 4.rank(行号函数)5. json解析函数 6.jdbc连接hive,企业级调优

 4.rank(行号函数)

4.1 函数说明

 RANK() 排序相同时会重复,总数不会变

 DENSE_RANK() 排序相同时会重复,总数会减少

ROW_NUMBER() 会根据顺序计算--------见练习

案例

数据

大数据学习day28-----hive03------1. null值处理,子串,拼接,类型转换 2.行转列,列转行 3. 窗口函数(over,lead,lag等函数) 4.rank(行号函数)5. json解析函数 6.jdbc连接hive,企业级调优

需求:计算每门学科的成绩排名

创建表并导入数据

create table  stu(
name string ,
subject string ,
score int
)
row format delimited fields terminated by "\t" ;
load data local inpath "/root/hive/stu.txt" into table stu ;

得到如下表

大数据学习day28-----hive03------1. null值处理,子串,拼接,类型转换 2.行转列,列转行 3. 窗口函数(over,lead,lag等函数) 4.rank(行号函数)5. json解析函数 6.jdbc连接hive,企业级调优

sql语句

select
* ,
rank() over(partition by subject order by score desc) , -- 总数量不变
dense_rank() over(partition by subject order by score desc) -- 总数减少
from
stu ;

结果

大数据学习day28-----hive03------1. null值处理,子串,拼接,类型转换 2.行转列,列转行 3. 窗口函数(over,lead,lag等函数) 4.rank(行号函数)5. json解析函数 6.jdbc连接hive,企业级调优

练习1

数据如下,求有连续4天销售记录的店铺

大数据学习day28-----hive03------1. null值处理,子串,拼接,类型转换 2.行转列,列转行 3. 窗口函数(over,lead,lag等函数) 4.rank(行号函数)5. json解析函数 6.jdbc连接hive,企业级调优

第一步:使用row_number()求出各店铺所有销售数

select
*,
row_number() over(partition by name order by ctime)
from
shop ;

结果

大数据学习day28-----hive03------1. null值处理,子串,拼接,类型转换 2.行转列,列转行 3. 窗口函数(over,lead,lag等函数) 4.rank(行号函数)5. json解析函数 6.jdbc连接hive,企业级调优

第二步:使用date_sub函数,并统计差值相同值的个数(此处统计差值大于4)

select
name ,
diff,
count(*) cc
from
(select
*,
date_sub(ctime , n) diff
from
(select
*,
row_number() over(partition by name order by ctime) n
from
shop)t1)t2
group by name , diff having cc >=4 ;

结果

大数据学习day28-----hive03------1. null值处理,子串,拼接,类型转换 2.行转列,列转行 3. 窗口函数(over,lead,lag等函数) 4.rank(行号函数)5. json解析函数 6.jdbc连接hive,企业级调优

 第三步:最终结果

select
distinct name
from
(select
name ,
diff,
count(*) cc
from
(select
*,
date_sub(ctime , n) diff
from
(select
*,
row_number() over(partition by name order by ctime) n
from
shop)t1)t2
group by name , diff having cc >=4)t3 ;

结果

大数据学习day28-----hive03------1. null值处理,子串,拼接,类型转换 2.行转列,列转行 3. 窗口函数(over,lead,lag等函数) 4.rank(行号函数)5. json解析函数 6.jdbc连接hive,企业级调优

练习2

数据

uid seq m
u01,1,1
u01,2,0
u01,3,1
u01,4,1
u01,5,0
u01,6,1
u02,1,1
u02,2,1
u02,3,0
u02,4,1
u02,5,1
u02,6,0
u02,7,0
u02,8,1
u02,9,1
u03,1,1
u03,2,1
u03,3,1
u03,4,1
u03,5,1
u03,6,0

uid:用户;seq:用户第几次打地鼠;m:是否击中,1表示击中,0没击中

需求:求连续打中地鼠3次的用户

(1)建表并导入数据

create table  ds(
uid string ,
seq int ,
m int
)
row format delimited fields terminated by "," ;
load data local inpath "/root/hive/ds.txt" into table ds ;

(2)sql

select
distinct uid
from
(select
uid,diff,
count(*) chit
from
(select
*,
(seq-n) diff
from
(select
*,
row_number() over(partition by uid order by seq) n
from
ds
where m = 1) t1)t2
group by
uid,diff
having chit>=3)t3;

结果

大数据学习day28-----hive03------1. null值处理,子串,拼接,类型转换 2.行转列,列转行 3. 窗口函数(over,lead,lag等函数) 4.rank(行号函数)5. json解析函数 6.jdbc连接hive,企业级调优

5. json解析函数:表生成函数(json_tuple())

需求:有如下json格式的电影评分数据  

大数据学习day28-----hive03------1. null值处理,子串,拼接,类型转换 2.行转列,列转行 3. 窗口函数(over,lead,lag等函数) 4.rank(行号函数)5. json解析函数 6.jdbc连接hive,企业级调优

求电影的均分

(1)创建表并导入数据

create table json(
content string
);
load data local inpath "/root/hive/rating.json" into table json ;

(2)通过表生成函数,解析json得到另一张表

create table mm as
select
json_tuple(content , "movie" , "rate","timeStamp","uid") as (mid ,rate ,times ,uid)
from
json ;

结果

大数据学习day28-----hive03------1. null值处理,子串,拼接,类型转换 2.行转列,列转行 3. 窗口函数(over,lead,lag等函数) 4.rank(行号函数)5. json解析函数 6.jdbc连接hive,企业级调优

(3)sql

select  avg(rate) from mm where uid =1  ;

结果

大数据学习day28-----hive03------1. null值处理,子串,拼接,类型转换 2.行转列,列转行 3. 窗口函数(over,lead,lag等函数) 4.rank(行号函数)5. json解析函数 6.jdbc连接hive,企业级调优

6.jdbc连接hive,企业级调优

  见hive文档

  

上一篇:Linux 文件时间记录属性 调优


下一篇:python面向编程: 常用模块补充与面向对象