substr函数用法
1. SUBSTR(str,pos,len): 从pos开始的位置，截取len个字符
eg:
substr(string ,1,3) ：取string左边第1位置起，3字长的字符串。
所以结果为： str
substr(string, -1,3)：取string右边第1位置起，3字长的字符串。显然右边第一位置起往右不够3字长。结果只能是： g
substr(string, -3,3)：取string右边第3位置起，3字长的字符串。
结果为: ing
2. SUBSTR(str,pos): pos开始的位置，一直截取到最后
eg:
substr(string ,4) : 从右第4位置截取到最后
结果是： ing
Hive内置date函数：date_add用法
date_add（'yyyy-MM-dd'，n/-m）
返回初试日期n天后、m天前的日期
date_add(start_date, num_days) - Returns the date that is num_days after start_date
eg:
hive> select date_add('2019-06-24',5);
2019-06-29
hive> select date_add('2019-06-24',-2);
2019-06-22
hive-NVL函数
NVL函数的格式如下：NVL(expr1,expr2)
含义是：如果oracle第一个参数为空那么显示第二个参数的值，如果第一个参数的值不为空，则显示第一个参数本来的值。
SQL> select ename,NVL(comm, -1) from emp;
SMITH -1
ALLEN 300
WARD 500
JONES -1
MARTIN 1400
BLAKE -1
FORD -1
MILLER -1
其中显示-1的本来的值全部都是空值的
Hive中rank()、row_number()函数的用法
row_number() 会根据顺序计算 row_number()仅仅是加了序号
eg
需求：计算每门学科成绩排名
分析：按照学科分组，再按照成绩排序，使用partition by subject order by score
hive (default)> select name,subject,score,row_number()
> over(partition by subject order by score desc) as rank from score;
OK
name subject score rank
孙悟空 数学 95 1
猪八戒 数学 86 2
沙僧 数学 85 3
唐僧 数学 56 4
猪八戒 英语 84 1
唐僧 英语 84 2
沙僧 英语 78 3
孙悟空 英语 68 4
唐僧 语文 94 1
孙悟空 语文 87 2
沙僧 语文 65 3
rank() 可以显示相同的数据，下一名的排序+1，即为跳跃排序
hive (default)> select name,subject,score,rank()
> over(partition by subject order by score desc) as rank from score;
OK
name subject score rank
孙悟空 数学 95 1
猪八戒 数学 86 2
沙僧 数学 85 3
唐僧 数学 56 4
猪八戒 英语 84 1
唐僧 英语 84 1
沙僧 英语 78 3
孙悟空 英语 68 4
唐僧 语文 94 1
孙悟空 语文 87 2
沙僧 语文 65 3
猪八戒 语文 64 4
dense_rank()，即使有相同的数据，也会按照连续排序
hive (default)> select name,subject,score,dense_rank()
> over(partition by subject order by score desc) as rank from score;
OK
name subject score rank
孙悟空 数学 95 1
猪八戒 数学 86 2
沙僧 数学 85 3
唐僧 数学 56 4
猪八戒 英语 84 1
唐僧 英语 84 1
沙僧 英语 78 2
孙悟空 英语 68 3
唐僧 语文 94 1
孙悟空 语文 87 2
沙僧 语文 65 3
猪八戒 语文 64 4
over()
ver() 函数在Hive中运用广泛，通常搭配row_number() ,min(),max(),sum()来使用,总结下over函数划定窗口的范围
id name num
1 a 1
2 b 3
3 a 5
4 c 9
5 b 3
SELECT id,name,num,sum(num) over() sum1 from aa
SELECT id,name,num, sum(num) over(order by id) sum1 from aa
SELECT id,name,num, sum(num) over(partition by name) sum1 from aa
Hive中collect相关的函数有collect_list和collect_set。
select username, collect_list(video_name) from t_visit_video group by username ;
按用户分组，取出每个用户每天看过的所有视频的名字
SELECT time 时间, sum(case when score= "90" then 1 else 0 end) as 胜,sum(case when result = "负" then 1 else 0 end) as 负 from Student GROUP BY time;
when score= “90” : 为判断条件,
then 1 selse 0 end: 当以上判断条件成立,则为1; 而该语句是在sum中的,具有累加效果,即可实现对所有成绩为90分的人进行一个统计
from
|