Hive的hql总结（后期会添加）

1、get_json_object

函数的作用：用来解析json字符串的一个字段：

格式get_json_object(json_txt, path)： 从一个JSON字符串中取出指定路径对应的数据！
说明：第一个参数填写json对象变量，第二个参数使用$表示json变量标识，然后用 . 或 [] 读取对象或数组；如果输入的json字符串无效，那么返回NULL。每次只能返回一个数据项。
$: 代表根对象
. : 获取子元素的操作符
[] : 获取一个数组中子元素的操作符

实例：

data =
{
 "store":
        {
         "fruit":[{"weight":8,"type":"apple"}, {"weight":9,"type":"pear"}],  
         "bicycle":{"price":19.95,"color":"red"}
         }, 
 "id":"123456" 
}

1.get单层值

hive> select  get_json_object(data, '$.id') from test;
结果：123456

2.get多层值.

hive> select  get_json_object(data, '$.store.bicycle.price') from test;
结果：19.95

3.get数组值[]

hive> select  get_json_object(data, '$.store.fruit[0]') from test;
结果：{"weight":8,"type":"apple"}

2、concat(string s1, string s2, string s3)

这个函数能够把字符串类型的数据连接起来，连接的某个元素可以是列值。
如 concat( aa, ‘:’, bb) 就相当于把aa列和bb列用冒号连接起来了，aa:bb。
例子：

hive> select concat('1','2','3');
结果123

3、collect_list、collect_set

collect_list():根据某个字段分组后，把分在一组的数据合并在一起
collect_set():则是在collect_list()的基础上去重，同时当想将查询后的数据进行去重操作，可以利用该函数对分组后的数据进行去重。

4、cast

用法：cast(value as type)
功能：将某个列的值显示的转化为某个类型
如：cast(age as string ) 将int类型的数据转化为了String类型
例子：这里利用日期函数进行举例

hive> select from_unixtime(cast('1505456567' as int),'yyyy-MM-dd');
结果：2017-09-15

5、concat_ws(seperator, string s1, string s2…)

功能：制定分隔符将多个字符串连接起来，实现“列转行”
例子：常常结合group by与collect_set使用

表名example
表结构a string , b string , c int
数据为
c d 1
c d 2
c d 3
e f 4
e f 5
e f 6

select a, b, concat_ws(',' , collect_set(cast(c as string)))
from example 
group by a,b;
结果为
c d 1,2,3
e f 4,5,6

6、over partition by 与 group by的区别理解

• group by是对检索结果的保留行进行单纯分组，一般和聚合函数一起使用例如max、min、sum、avg、count等一块用
• partition by虽然也具有分组功能，但同时也具有其他的高级功能。

这里对二者都做一个实例进行对比：
一份数据
01 01 80
01 02 90
01 03 99
02 01 70
02 02 60
02 03 80
03 01 80
03 02 80
03 03 80
04 01 50
04 02 30
04 03 20
group by实例：

这里对每个学生id进行分组，求其成绩总分
select s_id,sum(s_score)
from workdb.score
group by s_id
结果如下
01      269
02      210
03      240
04      100

over partition by实例

一、
select s_id,sum(s_score) over (partition by s_id)
from workdb.score
显示结果：
01      269
01      269
01      269
02      210
02      210
02      210
03      240
03      240
03      240
04      100
04      100
04      100
解释：这里是先对组内数据进行相加，最后显示相加后的数据
二、
select 
s_id,
sum(s_score) over (partition by s_id order by s_score)
from workdb.score
结果显示：
01      80
01      170
01      269
02      60
02      130
02      210
03      240
03      240
03      240
04      20
04      50
04      100
解释：之所以跟上面显示不同，是因为通过设置order by对s_score输入的数据强制排序，默认升序，因此组内数据的相加方式是累加。

因此两者之间最主要的区别：

• group by 更加强调的是一个整体，也就是组，通过与函数的结合使用，对组内的数据进行操作，最终显示每个组计算后的值。
• 使用over开窗函数进行分组的话，是在整体后更强调个体，能显示组中所有个体的记录，用于给结果集分组。

7、if函数和case函数的用法

7.1if语句

语法: if(boolean testCondition, T valueTrue, T valueFalseOrNull)

select if(1>2,1,2)
结果显示：
2
即当testCondition条件成立时，会返回valueTrue，
否则返回valueFalseOrNull

7.2case语句

两种用法：
一：

case 
when tb1.os = 'android' then 'android'
when tb1.os = 'ios' then 'iPhone'
else 'PC'
end as os

二：

case tb1.os
when 'android' then 'android'
when 'ios' then 'iPhone'
else 'PC'
end as os

8、lag和lead函数

lag 和lead 可以 获取结果集中，按一定排序所排列的当前行的上下相邻若干offset 的某个行的某个列(不用结果集的自关联）；
lag ，lead 分别是向前，向后；
lag 和lead 有三个参数，第一个参数是列名，第二个参数是偏移的offset，第三个参数是 超出记录窗口时的默认值）

举例如下：

select *  from yeardb;                                                                                                 
ID 			year
1 			2001                                                 
2 			2002                                                  
3 			2003
4			2004
5 			2005

select 
id,
year,
lag(year,1,0) over ( order by id )  yearago
from yeardb; 
                                                                  
ID 		year 		yearago     
1 		2001		0                                 
2 		2002       	2001
3 		2003       	2002
4 		2004       	2003
5 		2005       	2004

select 
id,
year,
lead(year,1,0) over ( order by id )  nextyear
from yeardb;
                                                                  
ID 		year 		nextyear     
1 		2001		2002                                 
2 		2002       	2003
3 		2003       	2004
4 		2004       	2005
5 		2005       	0

select 
id,
year,
lead(year,2,0) over ( order by id )  
from yeardb;                                                            


ID 		year 		nextyear     
1 		2001		2003                                 
2 		2002       	2004
3 		2003       	2005
4 		2004       	0
5 		2005       	0