HIVE函数
show functions;
desc function xxx;
窗口函数
row_number：row_number() over(distribute by dt sort by score desc) 没有并列，相同名次顺序排
rank：有并列，相同名次空位
dense_rank：有并列，相同名次不空位
lag：向上取第n行的数据
lead：向下取第n行的数据
first_value：first_value(score ignore nulls)
last_value：last_value(score) over(distribute by dt sort by score desc rows between unbounded preceding and unbounded following)
ntile(n)：用于将分组数据按照顺序切分成N片，返回当前切片值。ntile不支持rows between，如果切片不均匀，默认增加第一个切片的分布
cume_dist()：小于等于当前值的行数/分组内总行数。
percent_rank()：分组内当前行的rank值-1/分组内总行数-1
preceding:往前
following：往后
current rows：当前行
unbounded：起点
unbounded preceding：表示从前面的起点
unbounded following：表示到后面的终点
时间函数
to_date 下周几的日期
date_sub(next_day(to_date(CURRENT_TIMESTAMP),'MO'),7) --本周1--
date_sub(next_day(to_date(CURRENT_TIMESTAMP),'MO'),14) --上周1--
date_sub(next_day(to_date(CURRENT_TIMESTAMP),'MO'),0) --下周1--
date_add(next_day(to_date(CURRENT_TIMESTAMP),'MO'),7) --下下周1--
date_add(next_day('2019-02-10','MO'),-1) --当前这周的周日--
trunc 时间截取
select trunc(sysdate) from dual --2011-3-18 今天的日期为2011-3-18
select trunc(sysdate, 'mm') from dual --2011-3-1 返回当月第一天.
select trunc(sysdate,'yy') from dual --2011-1-1 返回当年第一天
select trunc(sysdate,'dd') from dual --2011-3-18 返回当前年月日
select trunc(sysdate,'yyyy') from dual --2011-1-1 返回当年第一天
select trunc(sysdate,'d') from dual --2011-3-13 (星期天)返回当前星期的第一天
select trunc(sysdate, 'hh') from dual --2011-3-18 14:00:00 当前时间为14:41
select trunc(sysdate, 'mi') from dual --2011-3-18 14:41:00 TRUNC()函数没有秒的精确
eg:截取小数
9.select trunc(123.458) from dual --123
10.select trunc(123.458,0) from dual --123
11.select trunc(123.458,1) from dual --123.4
12.select trunc(123.458,-1) from dual --120
13.select trunc(123.458,-4) from dual --0
14.select trunc(123.458,4) from dual --123.458
15.select trunc(123) from dual --123
16.select trunc(123,1) from dual --123
17.select trunc(123,-1) from dual --120
date_format 时间格式化
date_format(dt,'yyyy-MM')
datediff 返回日期差，天
datediff('2019-03-19','2019-03-10'); -- result: 9
year(time),month(time),day(time),weekofyear(time) 返回年、月、日、第几周
year('2019-03-19'),month('2019-03-19'),day('2019-03-19'),weekofyear('2019-03-
19');
months_between(date1,date2) 返回月份差
select months_between('2019-04-01','2019-02-20'); -- result: 1.38709677
字符串函数
concat_ws('|', collect_set(user_id))
concat
Split
split('a,b,c,d' , ',') ==> ["a","b","c","d"]
COALESCE(T v1, T v2, …)
返回参数中的第一个非空值；如果所有值都为 NULL，那么返回NULL。
数值
round() 四舍五入
floor() 向下取整
ceil() 向上取整
组合
行列转换
Lateral view 其实就是用来和像类似explode这种UDTF函数联用的。lateral view 会将UDTF生成的结果放
到一个虚拟表中，然后这个虚拟表会和输入行即每个uid进行join 来达到连接UDTF外的select字段的目的
。
列转行
select uid,tag from t12 lateral view explode(split(tags,",")) a as tag;
行转列
concat_ws group by
易混淆概念
SORT BY/ORDER BY
SORT BY ：局部排序，只保证单个的reduce内有序
ORDER BY ：全部排序，保证所有的reduce中的数据有序
如果reduce个数只有一个，则两者的结果一样
两者通常都和ASC DESC搭配，默认使用升序ASC
distribute by：按照指定的字段对数据进行划分输出到不同的reduce中。
cluster by：除了具有 distribute by 的功能外还兼具 sort by 的功能。
排名函数
row_number()：没有并列，相同名次顺序排
rank()：有并列，相同名次空位
dense_rank()：有并列，相同名次不空位
集合
collect_list
collect_set
自定义函数
编写UDF的方式：
1、继承UDF，重写evaluate()，允许重载。
2、继承genericUDF,重写initlizer()\getdisplay()\evaluate()。
hive的自定义模块：serde、自定义函数、输入输出格式等。
UDF：一对一的输入输出。（常用）
UDAF：多对一的输入输出。
UDTF：用户自定义的表生成函数。
添加自定义函数方法
当前session有效
第一种
1、将编写好的 UDF的jar包上传到服务器，并添加到hive的class path中
add jar /root/gp1813Demo-1.0-SNAPSHOT.jar;
2、创建一个自定义的临时函数名：
create temporary function myUpperCase as 'com.qfedu.bigdata.hiveUDF.firstUDF';
3、测试：
dual
create table dual (id string);
insert into dual values(' ');
select myUpperCase('gaoyuanyuan') from dual;
4、确定没有调用该函数时可以注销函数（小心）
drop temporary function myuppercase;
第二种：(相当于临时函数)
1、将编写好的UDF的jar包上传到服务器
2、编写配置脚本
vi ./hive-init
add jar /root/gp1813Demo-1.0-SNAPSHOT.jar;
create temporary function myUpperCase as 'com.qfedu.bigdata.hiveUDF.firstUDF';
3、启动hive的时候带上初始化脚本文件
hive -i ./hive-init
第三种：（临时函数）
1、将编写好的UDF的jar包上传到服务器
2、在hive的安装目录的bin目录下，创建一个文件，文件名.hiverc：
vi bin/.hiverc
add jar /root/gp1813Demo-1.0-SNAPSHOT.jar;
create temporary function myUpperCase as 'com.qfedu.bigdata.hiveUDF.firstUDF';
3、启动：
hive
创建永久函数
一
1、将编写好的 UDF的jar包上传到服务器，并将jar包上传到hdfs上，并添加到hive的class path中
hdfs dfs -put /root/gp1813Demo-1.0-SNAPSHOT.jar /hiveUDF/
add jar hdfs://qianfeng/hiveUDF/gp1813Demo-1.0-SNAPSHOT.jar;
2、创建一个自定义的函数名：
create function myUpperCase as 'com.qfedu.bigdata.hiveUDF.firstUDF';
二
编译源码（费劲）
1)将写好的Java文件拷贝到~/install/hive-0.8.1/src/ql/src/java/org/apache/hadoop/hive/ql/udf/
cd ~/install/hive-0.8.1/src/ql/src/java/org/apache/hadoop/hive/ql/udf/
ls -lhgt |head
2)修改~/install/hive-0.8.1/src/ql/src/java/org/apache/hadoop/hive/ql/exec/FunctionRegistry.java，增加import和RegisterUDF
import com.meilishuo.hive.udf.UDFIp2Long; //添加import
registerUDF("ip2long", UDFIp2Long.class, false); //添加register
3)在~/install/hive-0.8.1/src下运行ant -Dhadoop.version=1.0.1 package
cd ~/install/hive-0.8.1/src
ant -Dhadoop.version=1.0.1 package
4)替换exec的jar包，新生成的包在/hive-0.8.1/src/build/ql目录下,替换链接
cp hive-exec-0.8.1.jar /hadoop/hive/lib/hive-exec-0.8.1.jar.0628
rm hive-exec-0.8.1.jar
ln -s hive-exec-0.8.1.jar.0628 hive-exec-0.8.1.jar
5)重启进行测试