Hive Sql

hive sql 技巧总结

by yuzaer

---

目录&链接

一些注意事项以及tips
取数
连接
排序
时间相关
行列变换
空值的替换(nvl()函数)

---

一些注意事项以及tips：

1、count( )
设数据表 table1为

a	b
1	NULL
2	1
3	2

select
    count(*) as count_1,
    count(b) as count_2 -- count(expr)中 expr 可以为一个字段，也可以为一个表达式
from
    table1

count(*) 会计算表格中所有的数据行数
count(b) 只会计算b不为NULL的数量
返回的结果：

count_1	count_2
3	2

2、distinct
在hive中，distinct 与 group by 达到的效果类似.
但是：

• distinct 必须跟在 select 语句后，
• 对后面所有的字段组合进行去重.

假设数据表 table1为

a	b	c
1	qw	3
3	we	4
1	qw	3
2	ww	5
3	we	4

--单个字段
select
    distinct b
from
    table1

--多个字段
select
    distinct a,b,c
from
    table1

--group by
select
    a,b,c
from
    table1
group by
    a,b,c

得到的结果是
单个字段

b

qw

we

ww

多个字段

a	b	c
1	qw	3
3	we	4
2	ww	5

group by

a	b	c
1	qw	3
3	we	4
2	ww	5

3、UDF
以json_tuple ( )为例子
json_tuple( )的结果不能和其余字段一起选出，不过可以通过 lateral view 达到想要的结果

下面为例子：
假设数据table1为：

a	b
12	{‘gender’:‘male’}
13	{‘gender’:‘female’}
14	{‘gender’:‘male’}

select
    json_tuple(b,'gender') as b
from
    table1

返回结果

b

male

female

male

如果想同时把a也选出来
下面是报错的例子：

select
    a, -- UDF不支持与其与字段同时选出
    json_tuple(b,'gender') as b
from
    table1

若要达到想要的效果，可以使用 lateral view 来帮助达到

select
    a,
    b_gender
from
    table1 lateral view json_tuple(b,'gender') t as b_gender
    -- 使用了lateral view函数帮助解析和提取

其中，t时必不可少的
返回结果

a	b
12	male
13	female
14	male

---

取数

select 
    name1,
    name2,
    ...
from 
    DB.TABLE
where 
    ···
group by 
    ···

---

连接

1、left join (left outer join)

select 
    t1.XX,
    t2.XX
from 
    (
        table1 --这是一张表，可以是现有的，也可以是select出来的
    ) t1 
    left outer join 
    (
        table2
    ) t2 on t1.XX = t2.XX

会以左表为基准从右表中根据条件去寻找符合条件的数据，如果没有，就为NULL值.

2、join (inner join)

select
    t1.XX,
    t2.XX
from
    (
        table1 --这是一张表，可以是现有的，也可以是select出来的
    ) t1
    join
    (
        table2
    ) t2 on t1.XX = t2.XX

这个语句会返回两个表中同时满足条件的数据，等价于，剔除左连接中得到的NULL值所在行的数据。

---

排序

一共三种排序方式
dense_rank( ) 、rank( ) 、row_number( )

使用方式：

-- dense_rank()
select 
    dense_rank() over(
        partition by 
            name_rank1,
            name_rank2,
            ...
        order by
            name_order
        ) name1
from 
    DB.TABLE


-- rank()
select  
    rank() over(
        partition by 
            name_rank1,
            name_rank2,
            ...
        order by
            name_order
        ) name1
from 
    DB.TABLE


-- row_number() 
select 
    row_number() over(
        partition by 
            name_rank1,
            name_rank2,
            ...
        order by
            name_order
        ) name1
from 
    DB.TABLE

三者的区别：

假设有一张表格，数据库名称为DB，表格名称为rank_test，表格里面的数据为：

a	b
A	1
B	3
C	2
D	3
E	4
F	5
G	6

下面进行测试

select 
    a,
    row_number() over(
        order by b
        ) row_number,
    rank() over(
        order by b 
        ) rank,
    dense_rank() over(
        order by b
        ) dense_rank 
from 
    DB.rank_test

结果为

a	row_number	rank	dense_rank
A	1	1	1
C	2	2	2
D	3	3	3
B	4	3	3
E	5	5	4
F	6	6	5
G	7	7	6

也就是说,
row_number( ) 单纯的根据排完序后的数据加递增的排序值.
rank( ) 综合了排序字段和在表格中出现的位置得到的排序值.
dense_rank( ) 单纯考虑了被排序字段在所有可能值中的排序值.

---

时间相关

1、20180801转换为2018-08-01
date 的格式为 YYYYMMDD
p_date 的格式为 YYYY-MM-DD

concat(
    substring(date, 1, 4),
    '-',
    substring(date, 5, 2),
    '-',
    substring(date, 7, 2)
) as p_date

2、unix时间与UTC时间的转换：

--unixtime to UTC
from_unixtime(install_time, 'yyyyMMdd')--只转换到日期层面
from_unixtime(install_time, 'yyyy-MM-dd HH:mm:ss')--也可以转为确切时间

--UTC to unixtime
unix_timestamp('yyyy-MM-dd HH:mm:ss')--确切时间转换为unixtime

3、时间的差值计算

datediff(date1, date2)

返回的是 date1- date2的天数

---

行列变换

1、collect_list( )

数据记为 table1

id	name1
1001	A
1001	B
1001	C

下面为测试

select 
    id,
    concat_ws(',',collect_list(cast (name1 as string))) 
    --concat_ws(',',·····)表示用逗号连接
from 
    table1 
group by 
    id

结果记为 table2

id	name1
1001	A,B,C

NOTE :
collect_list 返回的是 array< ? > , ?是该列的类型；那如何将其统一转为string类型？
这就需要 concat_ws( ) 函数,但是concat_ws( ) 仅支持 string 或者 array< string > ,所以要利用 cast 先将类型转换为 string

2、lateral view explode( ) t1 as t2
测试数据使用上面的 table2

select 
    id, 
    name1
from 
    table2 lateral view explode(table2.name1) t as name1

返回的结果就是table1的结果：

id	name1
1001	A
1001	B
1001	C

NOTE :
sql语句中t1和t2都是必不可少的　
对应到测试语句中就是t和name1

---

空值的替换(nvl()函数)

1、nvl(expr1,expr2)

nvl(expr1,expr2) 的作用是:

当 expr1 为 NULL 时，返回 expr2 ;否则返回 expr1

下面进行测试

设数据表 table1为

a	b
1	NULL
2	1
3	3

select
    a,
    nvl(b,-1) as b_new -- 当b为NULL值时将其替换为-1
from
    table1

得到的结果为

a	b_new
1	-1
2	1
3	3

可以发现字段 b中的NULL值被替换为 -1,同时其余非NULL值并没有发生变化.

2、nvl2(expr1,expr2,expr3)

nvl2(expr1,expr2,expr3) 的作用是:

当 expr1 为 NULL 时，返回 expr2 ,否则返回 expr3

下面使用上面的table1作为测试数据

select
    a,
    nvl1(b,'i am NULL','i am not NULL') as b_new 
    --当b为NULL值时，替换为'i am NULL'，否则替换为'i am not NULL'
from
    table1

得到的结果为

a	b_new
1	i am NULL
2	i am not NULL
3	i am not NULL

3、nullif(expr1,expr2)

nullif(expr1,expr2) 的作用是:
当 expr1 与 expr2 相等时返回NULL;否则返回 expr1

下面继续使用table1作为测试数据

select
    a,
    nullif(a,b) as b_new
from
    table1

得到的结果是：

a	b_new
1	1
2	2
3	NULL

4、coalesce(expr1,expr2, … ,exprn)

coalesce(expr1,expr2, … ,exprn) 的作用是
返回 expr1,expr2, … ,exprn 中第一个非空表达式,若全为NULL，则返回NULL

下面是例子：

SELECT 
    coalesce(NULL,NULL,3,4,5) 
FROM 
    dual -- 某个table

返回的结果是 3

NOTE :
coalesce 是多个 nvl( ) 函数利用 case…when…then 进行复合而得