欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

Hive Sql

程序员文章站 2022-05-18 17:15:56
...

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中,distinctgroup 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) 的作用是:

expr1NULL 时,返回 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) 的作用是:

expr1NULL 时,返回 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) 的作用是:
expr1expr2 相等时返回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 进行复合而得

相关标签: 数据分析