Hive开发--Hive--函数（七）

操作手册：https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF

一、常用命令

select upper(empname) from emp;
select unix_timestamp(trackTime) from bflog limit 3 ;
select year(hiredate) from emp ;
select month(hiredate) from emp ;
select hour(hiredate) from emp ;
select substr(hiredate,1,4) from .emp ;
select split(hiredate,'-')[1] from emp ;
select reverse(hiredate) from emp ;
select concat(empno,'-',empname) from emp ;
  1. 显示所有函数
# 显示所有内置函数：
show functions;
  1. 显示函数信息
# 显示concat函数的使用描述信息：
desc function concat;
  1. 显示函数扩展信息
# 显示concat使用示例：
desc function extended concat;

二、Hive内置函数

（一）创建虚表

dual表的概念来自oracle，数据库建立时即与数据字典一起初始化，该表只有一个varchar2类型名为dummy的字段，表数据只有一行“X”，用来查询一些系统信息，如select sysdate from dual; select user from dual;select seq.nextval from dual等。
为了能在hive中测试一些时间、数学、聚合函数，可以仿照oracle创建dual表。
创建一个名叫DUAL的表。

hive> create table dual (dummy string);
# 插入一条记录，否则没有返回值

直接退出hive，回到Linux里面。生成一个dual.txt的文件

root@hadoopmaster:~# echo 'X' > dual.txt

把dual.txt放到hadoop的hdfs里面去，跟dual表关联起来。

hive> load data local inpath '/root/dual.txt' overwrite into table dual;

使用虚表：

hive> select 'hello' from dual;

（二）简单函数

1. 数学函数

1) rand

取随机数函数: rand

2) floor

向下取整函数: floor

3) ceil

向上取整函数: ceil

4) round

四舍五入

2. 类型转换

1) cast

cast类型转换：
--将浮点转为int

select cast(1.5 as int) from dual;
2) json

json函数：
get_json_object
$.name 取值
(注意数据格式)

# 单字段
hive> select get_json_object('{"name":"张三","age":"20"}','$.name')
     from dual limit ;
# 多字段
0: jdbc:hive2://192.168.71.130:10000> select get_json_object('{"name":"张三","age":20}','$.name')  name,get_json_object('{"name":"张三","age":20}','$.age') age from dual;
OK
+-------+------+
| name  | age  |
+-------+------+
| 张三    | 20   |
+-------+------+
1 row selected (0.394 seconds)
3) parse_url

parse_url('http://baidu.com/path')
通过url可以取值：

select parse_url('http://facebook.com/path/p1.php?query=1', 'PROTOCOL') from dual;   --http
select parse_url('http://facebook.com/path/p1.php?query=1', 'HOST') from dual;---facebook.com​
select parse_url('http://facebook.com/path/p1.php?query=1', 'REF') from dual;---空
select parse_url('http://facebook.com/path/p1.php?query=1', 'PATH') from dual;---/path/p1.php
select parse_url('http://facebook.com/path/p1.php?query=1', 'QUERY') from dual;---空
select parse_url('http://facebook.com/path/p1.php?query=1', 'FILE') from dual;---/path/p1.php?query=1
select parse_url('http://facebook.com/path/p1.php?query=1', 'AUTHORITY') from dual;---facebook.com
select parse_url('http://facebook.com/path/p1.php?query=1', 'USERINFO') from dual;​---空

3. 字符函数

1) length
2) concat

字符串连接函数：
concat

3) concat_ws

带分隔字符串连接函数
concat_ws
使用-进行连接字符串数组中的字符

select concat_ws('-',字符串数组)
from dual;
4) 收集字符串

collect_list

--不去重
collect_list()
select collect_set(id) from winfunc;

collect_set

--去重
collect_set()
select collect_set(id) from winfunc;
5) substring

字符串截取函数：substr,substring

hive> select substr('abcde',3) from dual;
6) regexp_replace

正则表达式替换函数： regexp_replace

hive> select regexp_replace('foobar ', 'oo|ar','') from dual;
7) regexp_extract

正则表达式解析函数： regexp_extract

hive> select regexp_extract('foothebar', 'foo(.*?)(bar)', 1) from dual;
8) space

空格字符串函数：space
语法: space(int n)
返回值: string
说明：返回长度为n的字符串
举例：

hive> select space(10) from dual;
hive> select length(space(10)) from dual;
9) repeat

重复字符串函数：repeat
语法: repeat(string str, int n)
返回值: string
说明：返回重复n次后的str字符串

举例：

hive> select repeat('abc',5) from dual;
10) ascii

首字符ascii函数：ascii
语法: ascii(string str)
返回值: int
说明：返回字符串str第一个字符的ascii码
举例：

hive> select ascii('abcde') from dual;

4. 条件函数

1) COALESCE

非空查找函数: COALESCE
返回值: T
说明: 返回参数中的第一个非空值；如果所有值都为NULL，那么返回NULL

select if(2>1,'v1','v2') from dual;
2) if

if(条件，值1，值2)
--当条件成立，返回值1，不成立，返回值2
示例：

select if(2>1,'v1','v2') from dual;
3) case

case when 判断条件 then 结果
when 判断条件 then 结果
else 结果
end
示例：

select case when id='1001' then 
'v1' 
when id='1002' then 'v2'
else 'v3'
end

5. 日期函数

1) year

日期转年函数: year

hive> select year('2011-12-08 10:03:01') from dual;
2) day

日期转天函数: day

hive> select day('2011-12-08 10:03:01') from dual;
3) datediff

日期比较函数: datediff

hive> select datediff('2012-12-08','2012-05-09') from dual;
4) date_add

日期减少函数: date_add
语法: date_add(string startdate, int days)
返回值: string
说明:返回开始日期startdate减少days天后的日期。

hive> select date_add('2012-12-08',10) from dual;
2012-11-28
5) date_sub

日期减少函数: date_sub
语法: date_sub (string startdate, int days)
返回值: string
说明:返回开始日期startdate减少days天后的日期。

hive> select date_sub('2012-12-08',10) from dual;
2012-11-28
6）unix_timestamp

6. 集合函数

1) size
hive> select size(t) from udftest;
2) map_keys
map_keys(Map<K.V>)                  
返回一个数组，元素是map的key
3) map_values
map_values(Map<K.V>)                
返回一个数组，元素是map的值
4) array_contains
array_contains(Array<T>, value)    
 Array是否包含，Value
5) sort_array
sort_array(Array<T>)               
排序

(三) 聚合函数

1. count

2. sum

3. min

4. max

5. avg

6. histogram_numeric

直方图:histogram_numeric
语法: histogram_numeric(col, b)

返回值: array<struct {‘x’,‘y’}>
说明:以b为基准计算col的直方图信息。

hive> select histogram_numeric(100,5) from dual;

(四) 集合函数

map阶段
find_in_set

(五)特殊函数

1. 窗口函数

与聚集函数一样，窗口函数也针对定义的行集（组）执行聚集，但它不像聚集函数那样每组之返回一个值，窗口函数可以为每组返回多个值。实际上，DB2中称这种函数为联机分析处理OLAP函数，而Oracle把它们称为解析函数，但ISO SQL标准把它们称为窗口函数。窗口函数一般在OLAP分析、制作报表过程中会使用到。

窗口函数：
聚合函数 over()
聚合函数 over(partition by 字段）—分区
聚合函数 over(order by 字段)--框架字句

取每个员工的最小money值
select id,money,
first_value(money) over(partition by id order by money rows)
between 1 preceding and 1 following) from winfunc

1. OVER

SUM — 注意,结果和ORDER BY相关,默认为升序

SELECT cookieid,  
createtime,  
pv,  
SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime) AS pv1, -- 默认为从起点到当前行  
SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS pv2, --从起点到当前行，结果同pv1   
SUM(pv) OVER(PARTITION BY cookieid) AS pv3, --分组内所有行  
SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS pv4,  --当前行+往前3行  
SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING) AS pv5,  --当前行+往前3行+往后1行  
SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS pv6  ---当前行+往后所有行    

pv1: 分组内从起点到当前行的pv累积，如，11号的pv1=10号的pv+11号的pv, 12号=10号+11号+12号
pv2: 同pv1
pv3: 分组内(cookie1)所有的pv累加
pv4: 分组内当前行+往前3行，如，11号=10号+11号， 12号=10号+11号+12号， 13号=10号+11号+12号+13号， 14号=11号+12号+13号+14号
pv5: 分组内当前行+往前3行+往后1行，如，14号=11号+12号+13号+14号+15号=5+7+3+2+4=21
pv6: 分组内当前行+往后所有行，如，13号=13号+14号+15号+16号=3+2+4+4=13，14号=14号+15号+16号=2+4+4=10

如果不指定ROWS BETWEEN,默认为从起点到当前行;
如果不指定ORDER BY，则将分组内所有值累加;
关键是理解ROWS BETWEEN含义,也叫做WINDOW子句：
PRECEDING：往前
FOLLOWING：往后
CURRENT ROW：当前行
UNBOUNDED：起点，UNBOUNDED PRECEDING 表示从前面的起点， UNBOUNDED FOLLOWING：表示到后面的终点

一、常用命令

select upper(empname) from emp;
select unix_timestamp(trackTime) from bflog limit 3 ;
select year(hiredate) from emp ;
select month(hiredate) from emp ;
select hour(hiredate) from emp ;
select substr(hiredate,1,4) from .emp ;
select split(hiredate,'-')[1] from emp ;
select reverse(hiredate) from emp ;
select concat(empno,'-',empname) from emp ;
  1. 显示所有函数
# 显示所有内置函数：
show functions;
  1. 显示函数信息
# 显示concat函数的使用描述信息：
desc function concat;
  1. 显示函数扩展信息
# 显示concat使用示例：
desc function extended concat;

二、Hive内置函数

（一）创建虚表

dual表的概念来自oracle，数据库建立时即与数据字典一起初始化，该表只有一个varchar2类型名为dummy的字段，表数据只有一行“X”，用来查询一些系统信息，如select sysdate from dual; select user from dual;select seq.nextval from dual等。
为了能在hive中测试一些时间、数学、聚合函数，可以仿照oracle创建dual表。
创建一个名叫DUAL的表。

hive> create table dual (dummy string);
# 插入一条记录，否则没有返回值

直接退出hive，回到Linux里面。生成一个dual.txt的文件

root@hadoopmaster:~# echo 'X' > dual.txt

把dual.txt放到hadoop的hdfs里面去，跟dual表关联起来。

hive> load data local inpath '/root/dual.txt' overwrite into table dual;

使用虚表：

hive> select 'hello' from dual;

（二）简单函数

1. 数学函数

1) rand

取随机数函数: rand

2) floor

向下取整函数: floor

3) ceil

向上取整函数: ceil

4) round

四舍五入

2. 类型转换

1) cast

cast类型转换：
--将浮点转为int

select cast(1.5 as int) from dual;
2) json

json函数：

get_json_object
hive> select get_json_object('{"name":"张三","age":"20"}','$.name')
     from dual limit ;
3) parse_url

$.name 取值
(注意数据格式)

parse_url('http://baidu.com/path')
通过url可以取值：
select parse_url('http://facebook.com/path/p1.php?query=1', 'PROTOCOL') from dual;   --http
select parse_url('http://facebook.com/path/p1.php?query=1', 'HOST') from dual;---facebook.com​
select parse_url('http://facebook.com/path/p1.php?query=1', 'REF') from dual;---空
select parse_url('http://facebook.com/path/p1.php?query=1', 'PATH') from dual;---/path/p1.php
select parse_url('http://facebook.com/path/p1.php?query=1', 'QUERY') from dual;---空
select parse_url('http://facebook.com/path/p1.php?query=1', 'FILE') from dual;---/path/p1.php?query=1
select parse_url('http://facebook.com/path/p1.php?query=1', 'AUTHORITY') from dual;---facebook.com
select parse_url('http://facebook.com/path/p1.php?query=1', 'USERINFO') from dual;​---空

3. 字符函数

1) length
2) concat

字符串连接函数：
concat

3) concat_ws

带分隔字符串连接函数
concat_ws
使用-进行连接字符串数组中的字符

select concat_ws('-',字符串数组)
from dual;
4) 收集字符串

collect_list

--不去重
collect_list()
select collect_set(id) from winfunc;

collect_set

--去重
collect_set()
select collect_set(id) from winfunc;
5) substring

字符串截取函数：substr,substring

hive> select substr('abcde',3) from dual;
6) regexp_replace

正则表达式替换函数： regexp_replace

hive> select regexp_replace('foobar ', 'oo|ar','') from dual;
7) regexp_extract

正则表达式解析函数： regexp_extract

hive> select regexp_extract('foothebar', 'foo(.*?)(bar)', 1) from dual;
8) space

空格字符串函数：space
语法: space(int n)
返回值: string
说明：返回长度为n的字符串
举例：

hive> select space(10) from dual;
hive> select length(space(10)) from dual;
9) repeat

重复字符串函数：repeat
语法: repeat(string str, int n)
返回值: string
说明：返回重复n次后的str字符串

举例：

hive> select repeat('abc',5) from dual;
10) ascii

首字符ascii函数：ascii
语法: ascii(string str)
返回值: int
说明：返回字符串str第一个字符的ascii码
举例：

hive> select ascii('abcde') from dual;

4. 条件函数

1) COALESCE

非空查找函数: COALESCE
返回值: T
说明: 返回参数中的第一个非空值；如果所有值都为NULL，那么返回NULL

select if(2>1,'v1','v2') from dual;
2) if

if(条件，值1，值2)
--当条件成立，返回值1，不成立，返回值2
示例：

select if(2>1,'v1','v2') from dual;
3) case

case when 判断条件 then 结果
when 判断条件 then 结果
else 结果
end
示例：

select case when id='1001' then 
'v1' 
when id='1002' then 'v2'
else 'v3'
end

5. 日期函数

1) year

日期转年函数: year

hive> select year('2011-12-08 10:03:01') from dual;
2) day

日期转天函数: day

hive> select day('2011-12-08 10:03:01') from dual;
3) datediff

日期比较函数: datediff

hive> select datediff('2012-12-08','2012-05-09') from dual;
4) date_add

日期减少函数: date_add
语法: date_add(string startdate, int days)
返回值: string
说明:返回开始日期startdate减少days天后的日期。

hive> select date_add('2012-12-08',10) from dual;
2012-11-28
5) date_sub

日期减少函数: date_sub
语法: date_sub (string startdate, int days)
返回值: string
说明:返回开始日期startdate减少days天后的日期。

hive> select date_sub('2012-12-08',10) from dual;
2012-11-28

6. 集合函数

1) size
hive> select size(t) from udftest;
2) map_keys
map_keys(Map<K.V>)                  
返回一个数组，元素是map的key
3) map_values
map_values(Map<K.V>)                
返回一个数组，元素是map的值
4) array_contains
array_contains(Array<T>, value)    
 Array是否包含，Value
5) sort_array
sort_array(Array<T>)               
排序

(三) 聚合函数

1. count

2. sum

3. min

4. max

5. avg

6. histogram_numeric

直方图:histogram_numeric
语法: histogram_numeric(col, b)

返回值: array<struct {‘x’,‘y’}>
说明:以b为基准计算col的直方图信息。

hive> select histogram_numeric(100,5) from dual;

(四) 集合函数

map阶段
find_in_set

(五)特殊函数

1. 窗口函数

与聚集函数一样，窗口函数也针对定义的行集（组）执行聚集，但它不像聚集函数那样每组之返回一个值，窗口函数可以为每组返回多个值。实际上，DB2中称这种函数为联机分析处理OLAP函数，而Oracle把它们称为解析函数，但ISO SQL标准把它们称为窗口函数。窗口函数一般在OLAP分析、制作报表过程中会使用到。

窗口函数：
聚合函数 over()
聚合函数 over(partition by 字段）—分区
聚合函数 over(order by 字段)--框架字句

取每个员工的最小money值
select id,money,
first_value(money) over(partition by id order by money rows)
between 1 preceding and 1 following) from winfunc

1. OVER

SUM — 注意,结果和ORDER BY相关,默认为升序

SELECT cookieid,  
createtime,  
pv,  
SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime) AS pv1, -- 默认为从起点到当前行  
SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS pv2, --从起点到当前行，结果同pv1   
SUM(pv) OVER(PARTITION BY cookieid) AS pv3, --分组内所有行  
SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS pv4,  --当前行+往前3行  
SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING) AS pv5,  --当前行+往前3行+往后1行  
SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS pv6  ---当前行+往后所有行    

pv1: 分组内从起点到当前行的pv累积，如，11号的pv1=10号的pv+11号的pv, 12号=10号+11号+12号
pv2: 同pv1
pv3: 分组内(cookie1)所有的pv累加
pv4: 分组内当前行+往前3行，如，11号=10号+11号， 12号=10号+11号+12号， 13号=10号+11号+12号+13号， 14号=11号+12号+13号+14号
pv5: 分组内当前行+往前3行+往后1行，如，14号=11号+12号+13号+14号+15号=5+7+3+2+4=21
pv6: 分组内当前行+往后所有行，如，13号=13号+14号+15号+16号=3+2+4+4=13，14号=14号+15号+16号=2+4+4=10

如果不指定ROWS BETWEEN,默认为从起点到当前行;
如果不指定ORDER BY，则将分组内所有值累加;
关键是理解ROWS BETWEEN含义,也叫做WINDOW子句：
PRECEDING：往前
FOLLOWING：往后
CURRENT ROW：当前行
UNBOUNDED：起点，UNBOUNDED PRECEDING 表示从前面的起点， UNBOUNDED FOLLOWING：表示到后面的终点

示例：

数据准备：
CREATE EXTERNAL TABLE lxw1234 (  
cookieid string,  
createtime string,   --day   
pv INT  
) ROW FORMAT DELIMITED   
FIELDS TERMINATED BY ','   
stored as textfile location '/tmp/lxw11/';  
   
DESC lxw1234;  
cookieid                STRING   
createtime              STRING   
pv INT   
   
hive> select * from lxw1234;  
OK  
cookie1 2015-04-10      1  
cookie1 2015-04-11      5  
cookie1 2015-04-12      7  
cookie1 2015-04-13      3  
cookie1 2015-04-14      2  
cookie1 2015-04-15      4  
cookie1 2015-04-16      4

2. 序列函数

测试数据：

CREATE EXTERNAL TABLE lxw1234 (  
cookieid string,  
createtime string,   --day   
pv INT  
) ROW FORMAT DELIMITED   
FIELDS TERMINATED BY ','   
stored as textfile location '/tmp/lxw11/';  
   
DESC lxw1234;  
cookieid                STRING   
createtime              STRING   
pv INT   
   
hive> select * from lxw1234;  
OK  
cookie1 2015-04-10      1  
cookie1 2015-04-11      5  
cookie1 2015-04-12      7  
cookie1 2015-04-13      3  
cookie1 2015-04-14      2  
cookie1 2015-04-15      4  
cookie1 2015-04-16      4  
cookie2 2015-04-10      2  
cookie2 2015-04-11      3  
cookie2 2015-04-12      5  
cookie2 2015-04-13      6  
cookie2 2015-04-14      3  
cookie2 2015-04-15      9  
cookie2 2015-04-16      7
1) NTILE
2) ROW_NUMBER
3) RANK
4) DENSE_RANK
5) CUME_DIST
6) PERCENT_RANK

3. 分析函数

测# 一、常用命令

select upper(empname) from emp;
select unix_timestamp(trackTime) from bflog limit 3 ;
select year(hiredate) from emp ;
select month(hiredate) from emp ;
select hour(hiredate) from emp ;
select substr(hiredate,1,4) from .emp ;
select split(hiredate,'-')[1] from emp ;
select reverse(hiredate) from emp ;
select concat(empno,'-',empname) from emp ;
  1. 显示所有函数
# 显示所有内置函数：
show functions;
  1. 显示函数信息
# 显示concat函数的使用描述信息：
desc function concat;
  1. 显示函数扩展信息
# 显示concat使用示例：
desc function extended concat;

二、Hive内置函数

（一）创建虚表

dual表的概念来自oracle，数据库建立时即与数据字典一起初始化，该表只有一个varchar2类型名为dummy的字段，表数据只有一行“X”，用来查询一些系统信息，如select sysdate from dual; select user from dual;select seq.nextval from dual等。
为了能在hive中测试一些时间、数学、聚合函数，可以仿照oracle创建dual表。
创建一个名叫DUAL的表。

hive> create table dual (dummy string);
# 插入一条记录，否则没有返回值

直接退出hive，回到Linux里面。生成一个dual.txt的文件

root@hadoopmaster:~# echo 'X' > dual.txt

把dual.txt放到hadoop的hdfs里面去，跟dual表关联起来。

hive> load data local inpath '/root/dual.txt' overwrite into table dual;

使用虚表：

hive> select 'hello' from dual;

（二）简单函数

1. 数学函数

1) rand

取随机数函数: rand

2) floor

向下取整函数: floor

3) ceil

向上取整函数: ceil

4) round

四舍五入

2. 类型转换

1) cast

cast类型转换：
--将浮点转为int

select cast(1.5 as int) from dual;
2) json

json函数：

get_json_object
hive> select get_json_object('{"name":"张三","age":"20"}','$.name')
     from dual limit ;
3) parse_url

$.name 取值
(注意数据格式)

parse_url('http://baidu.com/path')
通过url可以取值：
select parse_url('http://facebook.com/path/p1.php?query=1', 'PROTOCOL') from dual;   --http
select parse_url('http://facebook.com/path/p1.php?query=1', 'HOST') from dual;---facebook.com​
select parse_url('http://facebook.com/path/p1.php?query=1', 'REF') from dual;---空
select parse_url('http://facebook.com/path/p1.php?query=1', 'PATH') from dual;---/path/p1.php
select parse_url('http://facebook.com/path/p1.php?query=1', 'QUERY') from dual;---空
select parse_url('http://facebook.com/path/p1.php?query=1', 'FILE') from dual;---/path/p1.php?query=1
select parse_url('http://facebook.com/path/p1.php?query=1', 'AUTHORITY') from dual;---facebook.com
select parse_url('http://facebook.com/path/p1.php?query=1', 'USERINFO') from dual;​---空

3. 字符函数

1) length
2) concat

字符串连接函数：
concat

3) concat_ws

带分隔字符串连接函数
concat_ws
使用-进行连接字符串数组中的字符

select concat_ws('-',字符串数组)
from dual;
4) 收集字符串

collect_list

--不去重
collect_list()
select collect_set(id) from winfunc;

collect_set

--去重
collect_set()
select collect_set(id) from winfunc;
5) substring

字符串截取函数：substr,substring

hive> select substr('abcde',3) from dual;
6) regexp_replace

正则表达式替换函数： regexp_replace

hive> select regexp_replace('foobar ', 'oo|ar','') from dual;
7) regexp_extract

正则表达式解析函数： regexp_extract

hive> select regexp_extract('foothebar', 'foo(.*?)(bar)', 1) from dual;
8) space

空格字符串函数：space
语法: space(int n)
返回值: string
说明：返回长度为n的字符串
举例：

hive> select space(10) from dual;
hive> select length(space(10)) from dual;
9) repeat

重复字符串函数：repeat
语法: repeat(string str, int n)
返回值: string
说明：返回重复n次后的str字符串

举例：

hive> select repeat('abc',5) from dual;
10) ascii

首字符ascii函数：ascii
语法: ascii(string str)
返回值: int
说明：返回字符串str第一个字符的ascii码
举例：

hive> select ascii('abcde') from dual;

4. 条件函数

1) COALESCE

非空查找函数: COALESCE
返回值: T
说明: 返回参数中的第一个非空值；如果所有值都为NULL，那么返回NULL

select if(2>1,'v1','v2') from dual;
2) if

if(条件，值1，值2)
--当条件成立，返回值1，不成立，返回值2
示例：

select if(2>1,'v1','v2') from dual;
3) case

case when 判断条件 then 结果
when 判断条件 then 结果
else 结果
end
示例：

select case when id='1001' then 
'v1' 
when id='1002' then 'v2'
else 'v3'
end

5. 日期函数

1) year

日期转年函数: year

hive> select year('2011-12-08 10:03:01') from dual;
2) day

日期转天函数: day

hive> select day('2011-12-08 10:03:01') from dual;
3) datediff

日期比较函数: datediff

hive> select datediff('2012-12-08','2012-05-09') from dual;
4) date_add

日期减少函数: date_add
语法: date_add(string startdate, int days)
返回值: string
说明:返回开始日期startdate减少days天后的日期。

hive> select date_add('2012-12-08',10) from dual;
2012-11-28
5) date_sub

日期减少函数: date_sub
语法: date_sub (string startdate, int days)
返回值: string
说明:返回开始日期startdate减少days天后的日期。

hive> select date_sub('2012-12-08',10) from dual;
2012-11-28

6. 集合函数

1) size
hive> select size(t) from udftest;
2) map_keys
map_keys(Map<K.V>)                  
返回一个数组，元素是map的key
3) map_values
map_values(Map<K.V>)                
返回一个数组，元素是map的值
4) array_contains
array_contains(Array<T>, value)    
 Array是否包含，Value
5) sort_array
sort_array(Array<T>)               
排序

(三) 聚合函数

1. count

2. sum

3. min

4. max

5. avg

6. histogram_numeric

直方图:histogram_numeric
语法: histogram_numeric(col, b)

返回值: array<struct {‘x’,‘y’}>
说明:以b为基准计算col的直方图信息。

hive> select histogram_numeric(100,5) from dual;

(四) 集合函数

map阶段
find_in_set

(五)特殊函数

1. 窗口函数

与聚集函数一样，窗口函数也针对定义的行集（组）执行聚集，但它不像聚集函数那样每组之返回一个值，窗口函数可以为每组返回多个值。实际上，DB2中称这种函数为联机分析处理OLAP函数，而Oracle把它们称为解析函数，但ISO SQL标准把它们称为窗口函数。窗口函数一般在OLAP分析、制作报表过程中会使用到。

窗口函数：
聚合函数 over()
聚合函数 over(partition by 字段）—分区
聚合函数 over(order by 字段)--框架字句

取每个员工的最小money值
select id,money,
first_value(money) over(partition by id order by money rows)
between 1 preceding and 1 following) from winfunc

1. OVER

SUM — 注意,结果和ORDER BY相关,默认为升序

SELECT cookieid,  
createtime,  
pv,  
SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime) AS pv1, -- 默认为从起点到当前行  
SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS pv2, --从起点到当前行，结果同pv1   
SUM(pv) OVER(PARTITION BY cookieid) AS pv3, --分组内所有行  
SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS pv4,  --当前行+往前3行  
SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING) AS pv5,  --当前行+往前3行+往后1行  
SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS pv6  ---当前行+往后所有行    

pv1: 分组内从起点到当前行的pv累积，如，11号的pv1=10号的pv+11号的pv, 12号=10号+11号+12号
pv2: 同pv1
pv3: 分组内(cookie1)所有的pv累加
pv4: 分组内当前行+往前3行，如，11号=10号+11号， 12号=10号+11号+12号， 13号=10号+11号+12号+13号， 14号=11号+12号+13号+14号
pv5: 分组内当前行+往前3行+往后1行，如，14号=11号+12号+13号+14号+15号=5+7+3+2+4=21
pv6: 分组内当前行+往后所有行，如，13号=13号+14号+15号+16号=3+2+4+4=13，14号=14号+15号+16号=2+4+4=10

如果不指定ROWS BETWEEN,默认为从起点到当前行;
如果不指定ORDER BY，则将分组内所有值累加;
关键是理解ROWS BETWEEN含义,也叫做WINDOW子句：
PRECEDING：往前
FOLLOWING：往后
CURRENT ROW：当前行
UNBOUNDED：起点，UNBOUNDED PRECEDING 表示从前面的起点， UNBOUNDED FOLLOWING：表示到后面的终点

一、常用命令

select upper(empname) from emp;
select unix_timestamp(trackTime) from bflog limit 3 ;
select year(hiredate) from emp ;
select month(hiredate) from emp ;
select hour(hiredate) from emp ;
select substr(hiredate,1,4) from .emp ;
select split(hiredate,'-')[1] from emp ;
select reverse(hiredate) from emp ;
select concat(empno,'-',empname) from emp ;
  1. 显示所有函数
# 显示所有内置函数：
show functions;
  1. 显示函数信息
# 显示concat函数的使用描述信息：
desc function concat;
  1. 显示函数扩展信息
# 显示concat使用示例：
desc function extended concat;

二、Hive内置函数

（一）创建虚表

dual表的概念来自oracle，数据库建立时即与数据字典一起初始化，该表只有一个varchar2类型名为dummy的字段，表数据只有一行“X”，用来查询一些系统信息，如select sysdate from dual; select user from dual;select seq.nextval from dual等。
为了能在hive中测试一些时间、数学、聚合函数，可以仿照oracle创建dual表。
创建一个名叫DUAL的表。

hive> create table dual (dummy string);
# 插入一条记录，否则没有返回值

直接退出hive，回到Linux里面。生成一个dual.txt的文件

root@hadoopmaster:~# echo 'X' > dual.txt

把dual.txt放到hadoop的hdfs里面去，跟dual表关联起来。

hive> load data local inpath '/root/dual.txt' overwrite into table dual;

使用虚表：

hive> select 'hello' from dual;

（二）简单函数

1. 数学函数

1) rand

取随机数函数: rand

2) floor

向下取整函数: floor

3) ceil

向上取整函数: ceil

4) round

四舍五入

2. 类型转换

1) cast

cast类型转换：
--将浮点转为int

select cast(1.5 as int) from dual;
2) json

json函数：

get_json_object
hive> select get_json_object('{"name":"张三","age":"20"}','$.name')
     from dual limit ;
3) parse_url

$.name 取值
(注意数据格式)

parse_url('http://baidu.com/path')
通过url可以取值：
select parse_url('http://facebook.com/path/p1.php?query=1', 'PROTOCOL') from dual;   --http
select parse_url('http://facebook.com/path/p1.php?query=1', 'HOST') from dual;---facebook.com​
select parse_url('http://facebook.com/path/p1.php?query=1', 'REF') from dual;---空
select parse_url('http://facebook.com/path/p1.php?query=1', 'PATH') from dual;---/path/p1.php
select parse_url('http://facebook.com/path/p1.php?query=1', 'QUERY') from dual;---空
select parse_url('http://facebook.com/path/p1.php?query=1', 'FILE') from dual;---/path/p1.php?query=1
select parse_url('http://facebook.com/path/p1.php?query=1', 'AUTHORITY') from dual;---facebook.com
select parse_url('http://facebook.com/path/p1.php?query=1', 'USERINFO') from dual;​---空

3. 字符函数

1) length
2) concat

字符串连接函数：
concat

3) concat_ws

带分隔字符串连接函数
concat_ws
使用-进行连接字符串数组中的字符

select concat_ws('-',字符串数组)
from dual;
4) 收集字符串

collect_list

--不去重
collect_list()
select collect_set(id) from winfunc;

collect_set

--去重
collect_set()
select collect_set(id) from winfunc;
5) substring

字符串截取函数：substr,substring

hive> select substr('abcde',3) from dual;
6) regexp_replace

正则表达式替换函数： regexp_replace

hive> select regexp_replace('foobar ', 'oo|ar','') from dual;
7) regexp_extract

正则表达式解析函数： regexp_extract

hive> select regexp_extract('foothebar', 'foo(.*?)(bar)', 1) from dual;
8) space

空格字符串函数：space
语法: space(int n)
返回值: string
说明：返回长度为n的字符串
举例：

hive> select space(10) from dual;
hive> select length(space(10)) from dual;
9) repeat

重复字符串函数：repeat
语法: repeat(string str, int n)
返回值: string
说明：返回重复n次后的str字符串

举例：

hive> select repeat('abc',5) from dual;
10) ascii

首字符ascii函数：ascii
语法: ascii(string str)
返回值: int
说明：返回字符串str第一个字符的ascii码
举例：

hive> select ascii('abcde') from dual;

4. 条件函数

1) COALESCE

非空查找函数: COALESCE
返回值: T
说明: 返回参数中的第一个非空值；如果所有值都为NULL，那么返回NULL

select if(2>1,'v1','v2') from dual;
2) if

if(条件，值1，值2)
--当条件成立，返回值1，不成立，返回值2
示例：

select if(2>1,'v1','v2') from dual;
3) case

case when 判断条件 then 结果
when 判断条件 then 结果
else 结果
end
示例：

select case when id='1001' then 
'v1' 
when id='1002' then 'v2'
else 'v3'
end

5. 日期函数

1) year

日期转年函数: year

hive> select year('2011-12-08 10:03:01') from dual;
2) day

日期转天函数: day

hive> select day('2011-12-08 10:03:01') from dual;
3) datediff

日期比较函数: datediff

hive> select datediff('2012-12-08','2012-05-09') from dual;
4) date_add

日期减少函数: date_add
语法: date_add(string startdate, int days)
返回值: string
说明:返回开始日期startdate减少days天后的日期。

hive> select date_add('2012-12-08',10) from dual;
2012-11-28
5) date_sub

日期减少函数: date_sub
语法: date_sub (string startdate, int days)
返回值: string
说明:返回开始日期startdate减少days天后的日期。

hive> select date_sub('2012-12-08',10) from dual;
2012-11-28

6. 集合函数

1) size
hive> select size(t) from udftest;
2) map_keys
map_keys(Map<K.V>)                  
返回一个数组，元素是map的key
3) map_values
map_values(Map<K.V>)                
返回一个数组，元素是map的值
4) array_contains
array_contains(Array<T>, value)    
 Array是否包含，Value
5) sort_array
sort_array(Array<T>)               
排序

(三) 聚合函数

1. count

2. sum

3. min

4. max

5. avg

6. histogram_numeric

直方图:histogram_numeric
语法: histogram_numeric(col, b)

返回值: array<struct {‘x’,‘y’}>
说明:以b为基准计算col的直方图信息。

hive> select histogram_numeric(100,5) from dual;

(四) 集合函数

map阶段
find_in_set

(五)特殊函数

1. 窗口函数

与聚集函数一样，窗口函数也针对定义的行集（组）执行聚集，但它不像聚集函数那样每组之返回一个值，窗口函数可以为每组返回多个值。实际上，DB2中称这种函数为联机分析处理OLAP函数，而Oracle把它们称为解析函数，但ISO SQL标准把它们称为窗口函数。窗口函数一般在OLAP分析、制作报表过程中会使用到。

窗口函数：
聚合函数 over()
聚合函数 over(partition by 字段）—分区
聚合函数 over(order by 字段)--框架字句

取每个员工的最小money值
select id,money,
first_value(money) over(partition by id order by money rows)
between 1 preceding and 1 following) from winfunc

1. OVER

SUM — 注意,结果和ORDER BY相关,默认为升序

SELECT cookieid,  
createtime,  
pv,  
SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime) AS pv1, -- 默认为从起点到当前行  
SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS pv2, --从起点到当前行，结果同pv1   
SUM(pv) OVER(PARTITION BY cookieid) AS pv3, --分组内所有行  
SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS pv4,  --当前行+往前3行  
SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING) AS pv5,  --当前行+往前3行+往后1行  
SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS pv6  ---当前行+往后所有行    

pv1: 分组内从起点到当前行的pv累积，如，11号的pv1=10号的pv+11号的pv, 12号=10号+11号+12号
pv2: 同pv1
pv3: 分组内(cookie1)所有的pv累加
pv4: 分组内当前行+往前3行，如，11号=10号+11号， 12号=10号+11号+12号， 13号=10号+11号+12号+13号， 14号=11号+12号+13号+14号
pv5: 分组内当前行+往前3行+往后1行，如，14号=11号+12号+13号+14号+15号=5+7+3+2+4=21
pv6: 分组内当前行+往后所有行，如，13号=13号+14号+15号+16号=3+2+4+4=13，14号=14号+15号+16号=2+4+4=10

如果不指定ROWS BETWEEN,默认为从起点到当前行;
如果不指定ORDER BY，则将分组内所有值累加;
关键是理解ROWS BETWEEN含义,也叫做WINDOW子句：
PRECEDING：往前
FOLLOWING：往后
CURRENT ROW：当前行
UNBOUNDED：起点，UNBOUNDED PRECEDING 表示从前面的起点， UNBOUNDED FOLLOWING：表示到后面的终点

示例：

数据准备：
CREATE EXTERNAL TABLE lxw1234 (  
cookieid string,  
createtime string,   --day   
pv INT  
) ROW FORMAT DELIMITED   
FIELDS TERMINATED BY ','   
stored as textfile location '/tmp/lxw11/';  
   
DESC lxw1234;  
cookieid                STRING   
createtime              STRING   
pv INT   
   
hive> select * from lxw1234;  
OK  
cookie1 2015-04-10      1  
cookie1 2015-04-11      5  
cookie1 2015-04-12      7  
cookie1 2015-04-13      3  
cookie1 2015-04-14      2  
cookie1 2015-04-15      4  
cookie1 2015-04-16      4

2. 序列函数

测试数据：

CREATE EXTERNAL TABLE lxw1234 (  
cookieid string,  
createtime string,   --day   
pv INT  
) ROW FORMAT DELIMITED   
FIELDS TERMINATED BY ','   
stored as textfile location '/tmp/lxw11/';  
   
DESC lxw1234;  
cookieid                STRING   
createtime              STRING   
pv INT   
   
hive> select * from lxw1234;  
OK  
cookie1 2015-04-10      1  
cookie1 2015-04-11      5  
cookie1 2015-04-12      7  
cookie1 2015-04-13      3  
cookie1 2015-04-14      2  
cookie1 2015-04-15      4  
cookie1 2015-04-16      4  
cookie2 2015-04-10      2  
cookie2 2015-04-11      3  
cookie2 2015-04-12      5  
cookie2 2015-04-13      6  
cookie2 2015-04-14      3  
cookie2 2015-04-15      9  
cookie2 2015-04-16      7
1) NTILE
2) ROW_NUMBER
3) RANK
4) DENSE_RANK
5) CUME_DIST
6) PERCENT_RANK

3. 分析函数

测试数据：

CREATE EXTERNAL TABLE lxw1234 (  
cookieid string,  
createtime string,  --页面访问时间  
url STRING       --被访问页面  
) ROW FORMAT DELIMITED   
FIELDS TERMINATED BY ','   
stored as textfile location '/tmp/lxw11/';  
   
hive> select * from lxw1234;  
OK  
cookie1 2015-04-10 10:00:02     url2  
cookie1 2015-04-10 10:00:00     url1  
cookie1 2015-04-10 10:03:04     1url3  
cookie1 2015-04-10 10:50:05     url6  
cookie1 2015-04-10 11:00:00     url7  
cookie1 2015-04-10 10:10:00     url4  
cookie1 2015-04-10 10:50:01     url5  
cookie2 2015-04-10 10:00:02     url22  
cookie2 2015-04-10 10:00:00     url11  
cookie2 2015-04-10 10:03:04     1url33  
cookie2 2015-04-10 10:50:05     url66  
cookie2 2015-04-10 11:00:00     url77  
cookie2 2015-04-10 10:10:00     url44  
cookie2 2015-04-10 10:50:01     url55
1）LAG
LAG(col,n,DEFAULT) 用于统计窗口内往上第n行值
第一个参数为列名，第二个参数为往上第n行（可选，默认为1），第三个参数为默认值（当往上第n行为NULL时候，取默认值，如不指定，则为NULL）

SELECT cookieid,  
createtime,  
url,  
ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,  
LAG(createtime,1,'1970-01-01 00:00:00') OVER(PARTITION BY cookieid ORDER BY createtime) AS last_1_time,  
LAG(createtime,2) OVER(PARTITION BY cookieid ORDER BY createtime) AS last_2_time   
FROM lxw1234;  
   
   
last_1_time: 指定了往上第1行的值，default为'1970-01-01 00:00:00'    
             cookie1第一行，往上1行为NULL,因此取默认值 1970-01-01 00:00:00  
             cookie1第三行，往上1行值为第二行值，2015-04-10 10:00:02  
             cookie1第六行，往上1行值为第五行值，2015-04-10 10:50:01  
last_2_time: 指定了往上第2行的值，为指定默认值  
         cookie1第一行，往上2行为NULL  
         cookie1第二行，往上2行为NULL  
         cookie1第四行，往上2行为第二行值，2015-04-10 10:00:02  
         cookie1第七行，往上2行为第五行值，2015-04-10 10:50:01
2）LEAD
与LAG相反
LEAD(col,n,DEFAULT) 用于统计窗口内往下第n行值
第一个参数为列名，第二个参数为往下第n行（可选，默认为1），第三个参数为默认值（当往下第n行为NULL时候，取默认值，如不指定，则为NULL）

SELECT cookieid,  
createtime,  
url,  
ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,  
LEAD(createtime,1,'1970-01-01 00:00:00') OVER(PARTITION BY cookieid ORDER BY createtime) AS next_1_time,  
LEAD(createtime,2) OVER(PARTITION BY cookieid ORDER BY createtime) AS next_2_time   
FROM lxw1234;  

--逻辑与LAG一样，只不过LAG是往上，LEAD是往下
3）FIRST_VALUE
取分组内排序后，截止到当前行，第一个值

SELECT cookieid,  
createtime,  
url,  
ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,  
FIRST_VALUE(url) OVER(PARTITION BY cookieid ORDER BY createtime) AS first1   
FROM lxw1234;  

如果不指定ORDER BY，则默认按照记录在文件中的偏移量进行排序，会出现错误的结果

SELECT cookieid,  
createtime,  
url,  
FIRST_VALUE(url) OVER(PARTITION BY cookieid) AS first2    
FROM lxw1234;
3）LAST_VALUE
取分组内排序后，截止到当前行，最后一个值

SELECT cookieid,  
createtime,  
url,  
ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,  
LAST_VALUE(url) OVER(PARTITION BY cookieid ORDER BY createtime) AS last1   
FROM lxw1234;  


如果不指定ORDER BY，则默认按照记录在文件中的偏移量进行排序，会出现错误的结果

SELECT cookieid,  
createtime,  
url,  
LAST_VALUE(url) OVER(PARTITION BY cookieid) AS last2    
FROM lxw1234;  


如果想要取分组内排序后最后一个值，则需要变通一下：

SELECT cookieid,  
createtime,  
url,  
ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,  
LAST_VALUE(url) OVER(PARTITION BY cookieid ORDER BY createtime) AS last1,  
FIRST_VALUE(url) OVER(PARTITION BY cookieid ORDER BY createtime DESC) AS last2   
FROM lxw1234   
ORDER BY cookieid,createtime;

测试数据：

GROUPING SETS,GROUPING__ID,CUBE,ROLLUP

这几个分析函数通常用于OLAP中，不能累加，而且需要根据不同维度上钻和下钻的指标统计，比如，分小时、天、月的UV数。

CREATE EXTERNAL TABLE lxw1234 (  
month STRING,  
day STRING,   
cookieid STRING   
) ROW FORMAT DELIMITED   
FIELDS TERMINATED BY ','   
stored as textfile location '/tmp/lxw11/';  
   
   
hive> select * from lxw1234;  
OK  
2015-03 2015-03-10      cookie1  
2015-03 2015-03-10      cookie5  
2015-03 2015-03-12      cookie7  
2015-04 2015-04-12      cookie3  
2015-04 2015-04-13      cookie2  
2015-04 2015-04-13      cookie4  
2015-04 2015-04-16      cookie4  
2015-03 2015-03-10      cookie2  
2015-03 2015-03-10      cookie3  
2015-04 2015-04-12      cookie5  
2015-04 2015-04-13      cookie6  
2015-04 2015-04-15      cookie3  
2015-04 2015-04-15      cookie2  
2015-04 2015-04-16      cookie1
4) GROUPING SETS
在一个GROUP BY查询中，根据不同的维度组合进行聚合，等价于将不同维度的GROUP BY结果集进行UNION ALL

SELECT   
month,  
day,  
COUNT(DISTINCT cookieid) AS uv,  
GROUPING__ID   
FROM lxw1234   
GROUP BY month,day   
GROUPING SETS (month,day)   
ORDER BY GROUPING__ID;  

等价于   
SELECT month,NULL,COUNT(DISTINCT cookieid) AS uv,1 AS GROUPING__ID FROM lxw1234 GROUP BY month   
UNION ALL   
SELECT NULL,day,COUNT(DISTINCT cookieid) AS uv,2 AS GROUPING__ID FROM lxw1234 GROUP BY day  


SELECT   
month,  
day,  
COUNT(DISTINCT cookieid) AS uv,  
GROUPING__ID   
FROM lxw1234   
GROUP BY month,day   
GROUPING SETS (month,day,(month,day))   
ORDER BY GROUPING__ID;  


等价于  
SELECT month,NULL,COUNT(DISTINCT cookieid) AS uv,1 AS GROUPING__ID FROM lxw1234 GROUP BY month   
UNION ALL   
SELECT NULL,day,COUNT(DISTINCT cookieid) AS uv,2 AS GROUPING__ID FROM lxw1234 GROUP BY day  
UNION ALL   
SELECT month,day,COUNT(DISTINCT cookieid) AS uv,3 AS GROUPING__ID FROM lxw1234 GROUP BY month,day  

其中的 GROUPING__ID，表示结果属于哪一个分组集合。
5) CUBE
根据GROUP BY的维度的所有组合进行聚合。

SELECT   
month,  
day,  
COUNT(DISTINCT cookieid) AS uv,  
GROUPING__ID   
FROM lxw1234   
GROUP BY month,day   
WITH CUBE   
ORDER BY GROUPING__ID;  

等价于  
SELECT NULL,NULL,COUNT(DISTINCT cookieid) AS uv,0 AS GROUPING__ID FROM lxw1234  
UNION ALL   
SELECT month,NULL,COUNT(DISTINCT cookieid) AS uv,1 AS GROUPING__ID FROM lxw1234 GROUP BY month   
UNION ALL   
SELECT NULL,day,COUNT(DISTINCT cookieid) AS uv,2 AS GROUPING__ID FROM lxw1234 GROUP BY day  
UNION ALL   
SELECT month,day,COUNT(DISTINCT cookieid) AS uv,3 AS GROUPING__ID FROM lxw1234 GROUP BY month,day  
ROLLUP
6) ROLLUP
是CUBE的子集，以最左侧的维度为主，从该维度进行层级聚合。

比如，以month维度进行层级聚合：  
SELECT   
month,  
day,  
COUNT(DISTINCT cookieid) AS uv,  
GROUPING__ID    
FROM lxw1234   
GROUP BY month,day  
WITH ROLLUP   
ORDER BY GROUPING__ID;  
   
   
可以实现这样的上钻过程：  
月天的UV->月的UV->总UV  

--把month和day调换顺序，则以day维度进行层级聚合：  
   
SELECT   
day,  
month,  
COUNT(DISTINCT cookieid) AS uv,  
GROUPING__ID    
FROM lxw1234   
GROUP BY day,month   
WITH ROLLUP   
ORDER BY GROUPING__ID;  

可以实现这样的上钻过程：  
天月的UV->天的UV->总UV  
（这里，根据天和月进行聚合，和根据天聚合结果一样，因为有父子关系，如果是其他维度组合的话，就会不一样）

4. 混合函数

java_method(class,method[arg])

reflect

hash

5. UDTF

内置表生成函数（UDTF）
explode函数
返回类型 函数 说明
数组 explode(array<TYPE> a) 数组一条记录中有多个参数，将参数拆分，每个参数生成一列。
一行转多行：
例：

as 1,2,3

三、自定义函数

1. 创建函数

create temporary function 函数名 as '包名.类名'

该语句创建一个由类名实现的函数。在Hive中用户可以使用Hive类路径中的任何类，用户通过执行add files语句将函数类添加到类路径，并且可持续使用该函数进行操作。

2. 删除函数

注销用户定义函数的格式如下：

drop temporary function 函数名

2.UDF

一进一出
map阶段
用户自定义函数

3.UDAF

UDAF 多进一出 sum() avg() max() min()
reduce阶段

4. UDTF

一进多出
UDTF(User-Defined Table-Generating Functions) 用来解决输入一行输出多行(On-to-many maping) 的需求。
最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 194,491评论 5赞 459
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 81,856评论 2赞 371
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 141,745评论 0赞 319
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 52,196评论 1赞 263
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 61,073评论 4赞 355
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 46,112评论 1赞 272
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 36,531评论 3赞 381
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 35,215评论 0赞 253
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 39,485评论 1赞 290
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 34,578评论 2赞 309
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 36,356评论 1赞 326
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 32,215评论 3赞 312
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 37,583评论 3赞 299
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 28,898评论 0赞 17
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 30,174评论 1赞 250
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 41,497评论 2赞 341
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 40,697评论 2赞 335

推荐阅读更多精彩内容

  • Lua 5.1 参考手册
    Lua 5.1 参考手册 by Roberto Ierusalimschy, Luiz Henrique de F...
    苏黎九歌阅读 13,674评论 0赞 38
  • Oracle数据库学习一
    1.简介 数据存储有哪些方式？电子表格，纸质文件，数据库。 那么究竟什么是关系型数据库？ 目前对数据库的分类主要是...
    乔震阅读 1,681评论 0赞 2
  • 第5章 引用类型
    第5章 引用类型(返回首页) 本章内容 使用对象 创建并操作数组 理解基本的JavaScript类型 使用基本类型...
    大学一百阅读 3,196评论 0赞 4
  • PHP常用函数大全
    PHP常用函数大全 usleep() 函数延迟代码执行若干微秒。 unpack() 函数从二进制字符串对数据进行解...
    上街买菜丶迷倒老太阅读 1,344评论 0赞 20
  • HIVE 常用函数及实例
    本篇文章长更，欢迎大家收藏和喜欢 2018.12.25 字符串函数2 1.正则表达式解析函数：regexp_ext...
    清听阅读 4,122评论 0赞 1