Hive的基础函数
字符串函数
-- lower（转小写）
select lower('ABC');
--upper（转大写）
select upper('abc');
--length（字符串长度，字符数）
select length('abc');
-- concat（字符串拼接）
select concat("A", 'B');
-- concat_ws（指定分隔符）
select concat_ws('-','a' ,'b','c');
-- substr（求子串）
select substr('abcde',3);
-- split(str,regex) 切分字符串，返回数组
select split("a-b-c-d-e-f","-");
select concat_ws('&',split('a,b,c,d,f',','));
select replace('a,b,c,d,f',',','&');
以下这个函数不是字符串函数：
select explode(split('h-e-l-l-o','-'));类型转换函数
--round 四舍五入((42.3 =>42))
select round(42.3);
--ceil 向上取整(42.3 =>43)
select ceil(42.3);
--floor 向下取整(42.3 =>42)
select floor(42.3);
-- 求绝对值
select abs(-1);
-- 取模函数hive的其他函数
1）nvl 判断一个数值是否为null,如果为null,给一个默认值
select nvl(null,100);
select nvl(200,100);
select nvl(comm,0)+sal from emp;2) 函数case when then ....when ...then.. else... end
举例说明：
数据如下：
张三 A 男
李四 A 男
王五 B 男
赵六 A 女
琪琪 B 女
巴巴 B 女
求男女数量。
建表：
create table emp_sex(
name string,
dept_id string,
sex string)
row format delimited fields terminated by "\t";
导入数据：
load data local inpath '/home/hivedata/test_a.txt' into table emp_sex;
sql 编写：
select sex,count(1) from emp_sex group by sex;
还有其他写法：
select
sum(case when sex='男' then 1 else 0 end) as `男`,
sum(case when sex='女' then 1 else 0 end) as `女`
from emp_sex;
还可以使用类似于switch的写法：
select
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;hive中的字段的别名，英文别名不要使用单引号或者双引号，直接写就行，如果是中文的别名需要添加反引号``
3）get_json_object 从json数据中获取值
select get_json_object('{"name":"jack","age":19}','$.age');4） parse_url 解析一个字符串中的url参数
举例： 获取url中的HOST
hive (yhdb)> select parse_url('http://www.baidu.com/path1/path2?k1=v1&k2=v2','HOST');
OK
www.baidu.com
Time taken: 0.437 seconds, Fetched: 1 row(s)
-- 获取PROTOCOL中的协议
hive (yhdb)> select parse_url('http://www.baidu.com/path1/path2?k1=v1&k2=v2','PROTOCOL');
-- 获取Path
OK
http
Time taken: 0.194 seconds, Fetched: 1 row(s)
hive (yhdb)> select parse_url('http://www.baidu.com/path1/path2?k1=v1&k2=v2','PATH');
OK
/path1/path2
Time taken: 0.183 seconds, Fetched: 1 row(s)
// 区分大小写
hive (yhdb)> select parse_url('http://www.baidu.com/path1/path2?k1=v1&k2=v2','path');
OK
_c0
NULL
获取url后面的参数以及参数的值
select parse_url('http://www.baidu.com/path1/parth2?name=zhangsan&age=18','QUERY');
select parse_url('http://www.baidu.com/path1/parth2?name=zhangsan&age=18','QUERY','name');5）if(p1,p2,p3)
语法格式：
if和case差不多，都是处理单个列的查询结果
语法: if(boolean testCondition, T valueTrue, T valueFalseOrNull)
返回值: T
e.g.
select if(1==1,1,2) ;
select if(name!='a',name,'aaa') from user01;
select source,if(source='猎聘',1,2) as flag from t_cal_dowell_resume;
拓展：以下三个一个效果
select sal,if(comm is null,0,comm) from emp;
select sal,nvl(comm,0) from emp;
select sal,case when comm is null then 0 else comm end from emp;6） coalesce(col1,col2,col3...)返回第一个不为空的数据
select coalesce(null,1,23,4);
返回结果为17）取模函数
select pmod(3,2); -- 1
select pmod(5,3); -- 2
-- b + am == -5+ m * 3 ==m=2 1 / 3
-- -7 + a * 3= 正数 a=3 2/3= 2
-- 假如第一个值是负数，一般采用公式 a + bm = xxx
-- a= 被除数 m 等于除数 b等于多少取决于 这个公式什么时候是正数，最后将正数 除以 除数 获得的余数就是结果
select pmod(-5,3); -- 1
select pmod(-7,3);8) arry_contains
arry_contains（）作用：判断数组是否包含某元素
语法：array_contains(数组，值），返回布尔类型
第五题：
有如下数据，表示1、2、3三名学生选修了a、b、c、d、e、f中的若干课程
id course
1 a
1 b
1 c
1 e
2 a
2 c
2 d
2 f
3 a
3 b
3 c
3 e
根据如上数据，查询出如下结果，其中1表示选修，0表示未选修
id a b c d e f
1 1 1 1 0 1 0
2 1 0 1 1 0 1
3 1 1 1 0 1 0
SQL:
--第一种方法
select id,
sum(case when course='a' then 1 else 0 end ) a,
sum(case when course='b' then 1 else 0 end ) b,
sum(case when course='c' then 1 else 0 end ) c,
sum(case when course='d' then 1 else 0 end ) d,
sum(case when course='e' then 1 else 0 end ) e,
sum(case when course='f' then 1 else 0 end ) f
from zhoukao03 group by id;
--第二种方法
select id,
if(array_contains(collect_set(course),'a'),1,0) a,
if(array_contains(collect_set(course),'b'),1,0) b,
if(array_contains(collect_set(course),'c'),1,0) c,
if(array_contains(collect_set(course),'d'),1,0) d,
if(array_contains(collect_set(course),'e'),1,0) e,
if(array_contains(collect_set(course),'f'),1,0) f
from courses group by id;
假如查询出如下结果
id a b c d e f
1 选修 选修 选修 未选修 选修 未选修
2 选修 未选修 选修 选修 未选修 选修
3 选修 选修 选修 未选修 选修 未选修
create table courses (
id int,
course string
)
row format delimited
fields terminated by '\t';
load data local inpath '/home/hivedata/zuoye5.txt' into table courses;
select id,
if(array_contains(collect_set(course),'a'),'选修','未选修') a,
if(array_contains(collect_set(course),'b'),'选修','未选修') b,
if(array_contains(collect_set(course),'c'),'选修','未选修') c,
if(array_contains(collect_set(course),'d'),'选修','未选修') d,
if(array_contains(collect_set(course),'e'),'选修','未选修') e,
if(array_contains(collect_set(course),'f'),'选修','未选修') f
from courses group by id;