【HIVE】（3）联合查询join、时间戳函数、字符串函数

数据
t_join1.txt
1,a,1
2,b,2
3,c,4t_join2.txt
1,2a
2,2b
3,2c建表、导入：
create table t_join1(id int, name string, cid int) row format delimited fields terminated by ",";
create table t_join2(id int, name string) row format delimited fields terminated by ",";
load data local inpath "/root/example/hive/data/t_join1.txt" into table t_join1;
load data local inpath "/root/example/hive/data/t_join2.txt" into table t_join2;join，多表联合查询
join：只输出on条件相等的行
select * from t_join1 t1 join t_join2 t2 on t1.cid=t2.id;等同于：
select * from t_join1 t1 inner join t_join2 t2 on t1.cid=t2.id;
select * from t_join1 t1, t_join2 t2 where t1.cid=t2.id;left join，输出on条件相等的行 + 左表剩余的行（这些行对应右表的字段使用NULL）
select * from t_join1 t1 left join t_join2 t2 on t1.cid=t2.id;right join，输出on条件相等的行 + 右表剩余的行（这些行对应左表的字段使用NULL）
select * from t_join1 t1 right join t_join2 t2 on t1.cid=t2.id;full join，输出左右两表所有行，on条件不满足的行，使用NULL
select * from t_join1 t1 full join t_join2 t2 on t1.cid=t2.id;built-in function，内置函数1．查看系统自带的函数
hive> show functions;
2．显示自带的函数的用法
hive> desc function year;
3．详细显示自带的函数的用法
hive> desc function extended year;* 数值函数：
round(DOUBLE a)：四舍五入取整；
round(DOUBLE a, INT d)：指定小数点位数；
rand()：0~1之间的随机小数；
select round(rand()*a+b)：取[b, a+b]之间的整数；* 日期函数：
- 当前时间：
current_date
current_timestamp- 时间戳函数：
from_unixtime(bigint unixtime[, string format])：时间戳转换为时间；
unix_timestamp()：获取当前时间戳；
unix_timestamp(string date)：时间转换为时间戳；- 分别获取年、月、日：
year(string date)
quarter(date/timestamp/string)
month(string date)
day(string date) dayofmonth(date)
hour(string date)
minute(string date)
second(string date)
weekofyear(string date)- 时间间隔：
datediff(string enddate, string startdate)：两段时间相隔的天数；
date_add(date/timestamp/string startdate, tinyint/smallint/int days)：增加天数；
date_sub(date/timestamp/string startdate, tinyint/smallint/int days)：减少天数；* 条件函数：
if(boolean testCondition, T valueTrue, T valueFalseOrNull)
isnull( a )
isnotnull ( a )
nvl(T value, T default_value)：替换空值；
COALESCE(T v1, T v2, ...)：返回第一个非空值；重难点： CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END：该函数可以将多行转换为多行多列；
- 增加yuwen和shuxue列：
select * , case course when "yuwen" then score else 0 end as yuwen, case course when "shuxue" then score else 0 end as shuxue from t_course;
- 使用max分组：
select sid, max(yuwen) as yuwen, max(shuxue) as shuxue from (select * , case course when "yuwen" then score else 0 end as yuwen, case course when "shuxue" then score else 0 end as shuxue from t_course) t group by sid;
- 获取语文成绩比数学成绩好的学生：
select * from (select sid, max(yuwen) as yuwen, max(shuxue) as shuxue from (select * , case course when "yuwen" then score else 0 end as yuwen, case course when "shuxue" then score else 0 end as shuxue from t_course) t group by sid) t  where yuwen>shuxue;还有另一种方法，可以完成上面任务，提示：使用collect_set()，大家可以先想想思路。CASE WHEN a THEN b [WHEN c THEN d]* [ELSE e] END* 字符串函数：
concat(string|binary A, string|binary B...)：字符串连接
concat_ws(string SEP, string A, string B...)：使用指定字符连接；
substr(string|binary A, int start, int len)：获取子字符串；
substring(string|binary A, int start, int len)：同上；
trim(string A)：去除两边空格；
lower(string A) lcase(string A) upper(string A) ucase(string A)：大小写转换；
split(string str, string pat)：字符串分割为数组；
str_to_map(text[, delimiter1, delimiter2])：字符串转换为map；