系统函数
1 collect_set函数：把同一分组的不同行的数据聚合成一个集合
1）创建原数据表
hive (gmall)> drop table if exists stud; create table stud (name string, area string, course string, score int); insert into table stud values('zhang3','bj','math',88); insert into table stud values('li4','bj','math',99); insert into table stud values('wang5','sh','chinese',92); insert into table stud values('zhao6','sh','chinese',54); insert into table stud values('tian7','bj','chinese',91); hive (gmall)> select * from stud stud.name stud.area stud.course stud.score zhang3 bj math 88 li4 bj math 99 wang5 sh chinese 92 zhao6 sh chinese 54 tian7 bj chinese 91 hive (gmall)> select * from stud; stud.name stud.area stud.course stud.score zhang3 bj math 88 li4 bj math 99 wang5 sh chinese 92 zhao6 sh chinese 54 tian7 bj chinese 91
2）把同一分组的不同行的数据聚合成一个集合
hive (gmall)> select course, collect_set(area), avg(score) from stud group by course; chinese ["sh","bj"] 79.0 math ["bj"] 93.5
3） 用下标可以取某一个
hive (gmall)> select course, collect_set(area)[0], avg(score) from stud group by course; chinese sh 79.0 math bj 93.5
5.2.2 日期处理函数
1）date_format函数（根据格式整理日期）
hive (gmall)> select date_format('2019-02-10','yyyy-MM'); 2019-02
2）date_add函数（加减日期）
hive (gmall)> select date_add('2019-02-10',-1); 2019-02-09 hive (gmall)> select date_add('2019-02-10',1); 2019-02-11
3）next_day函数
（1）取当前天的下一个周一
hive (gmall)> select next_day('2019-02-12','MO') 2019-02-18
说明：星期一到星期日的英文（Monday，Tuesday、Wednesday、Thursday、Friday、Saturday、Sunday）
（2）取当前周的周一
hive (gmall)> select date_add(next_day('2019-02-12','MO'),-7); 2019-02-11 4）last_day函数（求当月最后一天日期） hive (gmall)> select last_day('2019-02-10'); 2019-02-28
datediff 两个日天相减，差的天数。
select datediff('2020-03-25','2020-03-20') ; 返回 5
CAST 操作显示进行数据类型转换
例如CAST('1' AS INT)将把字符串'1' 转换成整数1；如果强制类型转换失败，如执行CAST('X' AS INT)，表达式返回空值 NULL。
hive常用时间转换：https://blog.csdn.net/kuanghongjiang/article/details/81015057
行转列
1．相关函数说明
CONCAT(string A/col, string B/col…)：返回输入字符串连接后的结果，支持任意个输入字符串;
CONCAT_WS(separator, str1, str2,...)：它是一个特殊形式的 CONCAT()。第一个参数剩余参数间的分隔符。分隔符可以是与剩余参数一样的字符串。如果分隔符是 NULL，返回值也将为 NULL。这个函数会跳过分隔符参数后的任何 NULL 和空字符串。分隔符将被加到被连接的字符串之间;
COLLECT_SET(col)：函数只接受基本数据类型，它的主要作用是将某字段的值进行去重汇总，产生array类型字段。
2．数据准备
表6-6 数据准备
|
name |
constellation |
blood_type |
|
孙悟空 |
白羊座 |
A |
|
大海 |
射手座 |
A |
|
宋宋 |
白羊座 |
B |
|
猪八戒 |
白羊座 |
A |
|
凤姐 |
射手座 |
A |
3．需求
把星座和血型一样的人归类到一起。结果如下：
射手座,A 大海|凤姐
白羊座,A 孙悟空|猪八戒
白羊座,B 宋宋
4．创建本地constellation.txt，导入数据
[atguigu@hadoop102 datas]$ vi constellation.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 t1.base, concat_ws('|', collect_set(t1.name)) name from (select name, concat(constellation, ",", blood_type) base from person_info) t1 group by t1.base; |
列转行
1．函数说明
EXPLODE(col)：将hive一列中复杂的array或者map结构拆分成多行。
LATERAL VIEW 侧写 相当于 select movie,explode(category) from movie_info;
[Error 10081]: UDTF's are not supported outside the SELECT clause, nor nested in expressions
在SELECT子句之外不支持UDTF，也不在表达式中嵌套UDTF
用法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias
解释：用于和split, explode等UDTF一起使用，它能够将一列数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。
2．数据准备
表6-7 数据准备
|
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]$ vi movie.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.txt" into table movie_info; |
6．按需求查询数据
|
select movie, category_name from movie_info lateral view explode(category) table_tmp as category_name; |
最近七天内连续三天活跃用户数（思路SQL）
select
'2019-02-12', concat(date_add('2019-02-12',-6),'_','2019-02-12'), count(*)
from(
select mid_id from
(
select mid_id from
(
select
mid_id, date_sub(dt,rank) date_dif from
(
select mid_id, dt, rank() over(partition by mid_id order by dt) rank
from dws_uv_detail_day
where dt>=date_add('2019-02-12',-6) and dt<='2019-02-12'
)t1
)t2 group by mid_id,date_dif having count(*)>=3
)t3
group by mid_id
)t4;
这个SQL 就是说查询一周内按照 用户分组然后按活跃时间排序， 在每个用户的活跃时间组内排序。 然后拿他们活跃时间减去排序，如果能相等那么证明这几条数据日期是挨着的，如果不相等就是隔开的日期。然后大于等于3的 就是我们要查的连续3天活跃用户。比如（ID 张三 10、11、12活跃 然后组内序号是 1 2 3）10-1 11-2 12-3 都等于9 如果说是不挨着的 10 12 14 顺序是 1 2 3 他们就是 10-1=9 12-2=10 14-3=11 这三个日期就证明是没挨着的。 然后这个SQL写完还有一个小bug，如果一个用户在10、11、12、14、15、16都活跃 排序是1 2 3 4 5 6 等于 9 9 9 10 10 10 就会查出两个相同用户数据重复。我们在这里再group by 进行一下分租去重就好。
浙公网安备 33010602011771号