hive内置函数详解(分析函数、窗口函数)-白红宇

hive内置函数详解(分析函数、窗口函数)

阅读量：6428 次

发布时间：2019-06-23

本文共 3768 字，大约阅读时间需要 12 分钟。

cli命令

show functions;

desc function concat;

desc function extended concat;查看某个函数怎么使用的例子

nvl函数

coalesce(v1,v2,...)返回参数中第一个非空值，如果所有值都为null返回null；

set.cli.print.header=true;

winfunc

员工工资标识

id money type

关系型运算符优先级高到低为：not and or

and or 优先级

select id ,money from winfunc where id='1001' or id='1002'

and money ='100';

结果

1001 100

1001 150

1001 200

1001 150

1002 100

正确的sql应该是

select id ,money from winfunc where (id='1001' or id='1002') and money ='100';

结果

1001 100

1002 100

if(con,v1,v2)

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

case when

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

get_json_object

select get_json_object('{"name":"jack","age":"20"}','$.name') from dual;

jack

parse_url

select parse_url('http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1', 'HOST') from

lxw_dual;

facebook.com

select parse_url('http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1', 'QUERY', 'k1')

from lxw_dual;

concat_ws比concat多了个拼接字符串之间的分隔符

concat_ws(string SEP,array<string>)对数组里的值处理

collect_set(id)去重返回数组

select collect_set(id) from winfunc;

["1001","1002","1003","1004"]

collect_list(id)不去重返回数组

select collect_list(id) from winfunc;

partition by关键字是oracle中分析性函数的一部分，它和聚合函数不同的地方在于它能返回一个分组中的多条记录，而聚合函数一般只有一条反映统计值的记录

sum() over (PARTITION BY ...) 是一个分析函数。他执行的效果跟普通的sum ...group by ...不一样，它计算组中表达式的累积和，而不是简单的和。

Group By 和 Having, Where ,Order by这些关键字是按照如下顺序进行执行的：Where, Group By, Having, Order by。

在这四个关键字中，只有在Order By语句中才可以使用最终视图的列名，如：

SELECT FruitName, ProductPlace, Price, ID AS IDE, Discount

FROM T_TEST_FRUITINFO

WHERE (ProductPlace = N'china')

ORDER BY IDE

这里只有在ORDER BY语句中才可以使用IDE，其他条件语句中如果需要引用列名则只能使用ID，而不能使用IDE。

ORDER BY 子句中的列必须包含在聚合函数或 GROUP BY 子句中。

GROUP BY 和 ORDER BY一起使用时，ORDER BY要在GROUP BY的后面。

一、窗口函数

first_value(求组的第一个值)

select id,money,

first_value(money) over (partition by id order by money

rows between 1 preceding and 1 following)

from winfunc

每行对应的数据窗口是从第一行到最后一行

rows between unbounded preceding and unbounded following

lead(money,2) 取后面距离为2的记录值，没有就取null

select id,money,lead(money,2) over(order by money) from winfunc

lag(money,2)于lead相反

rank()排序函数与row_number()

select id,money, rank() over (partition by id order by money) from winfunc

结果

1001 100 1

1001 150 2

1001 200 4

dense_rank()

select id,money, dense_rank() over (partition by id order by money) from winfunc

结果

1001 100 1

1001 150 2

1001 200 3

cume_dist()
计算公式：CUME_DIST 小于等于当前值的行数/分组内总行数–比如，统计小于等于当前薪水的人数，所占总人数的比例
    select id,money, cume_dist() over (partition by id order by money) from winfunc
结果
    1001 100 0.25
    1001 150 0.75
    1001 150 0.75
    1001 200 1
percent_rank()，第一个总是从零开始
PERCENT_RANK() = (RANK() – 1) / (Total Rows – 1)
计算公式：(相同值最小行号-1)/(总行数-1)
结果
    1001 100 0
    1001 150 0.33
    1001 150 0.33
    1001 200 1

ntile(2) 分片

asc时， nulls last为默认

desc时， nulls first为默认

select id,money, ntile(2) over (order by money desc nulls last) from winfunc;

混合函数（使用java里面的方法）

java_method和reflect是一样的

select java_method("java.lang.Math","sqrt",cast(id as double)) from winfunc;

UDTF表函数explode()配合lateral view关键字

select id ,adid from winfunc lateral view explode(split(type,'B')) tt as adid

1001 ABC

列转行

1001 A