Hive之函数（一）

最新推荐文章于 2022-12-20 12:06:57 发布

悠然予夏

最新推荐文章于 2022-12-20 12:06:57 发布

阅读量301

点赞数

分类专栏： Hadoop生态圈技术文章标签： hive 大数据 hadoop

本文链接：https://blog.csdn.net/weixin_52851967/article/details/127327589

版权

Hadoop生态圈技术专栏收录该内容

48 篇文章 4 订阅

订阅专栏

Hive内置函数：LanguageManual UDF - Apache Hive - Apache Software Foundation

1、系统内置函数

1.1、查看系统函数

-- 查看系统自带函数
show functions;

-- 显示自带函数的用法
desc function upper;
desc function extended upper;

1.2、日期函数【重要】

-- 当前前日期
select current_date;

select unix_timestamp();
-- 建议使用current_timestamp，有没有括号都可以
select current_timestamp();

-- 时间戳转日期
select from_unixtime(1505456567);
select from_unixtime(1505456567, 'yyyyMMdd');
select from_unixtime(1505456567, 'yyyy-MM-dd HH:mm:ss');

-- 日期转时间戳
select unix_timestamp('2019-09-15 14:23:00');

-- 计算时间差
select datediff('2020-04-18','2019-11-21');
select datediff('2019-11-21', '2020-04-18');

-- 查询当月第几天
select dayofmonth(current_date);

-- 计算月末:
select last_day(current_date);

-- 当月第1天:
select date_sub(current_date, dayofmonth(current_date)-1)

-- 下个月第1天:
select add_months(date_sub(current_date,dayofmonth(current_date)-1), 1)

-- 字符串转时间（字符串必须为：yyyy-MM-dd格式）
select to_date('2020-01-01');
select to_date('2020-01-01 12:12:12');

-- 日期、时间戳、字符串类型格式化输出标准时间格式
select date_format(current_timestamp(), 'yyyy-MM-dd HH:mm:ss');
select date_format(current_date(), 'yyyyMMdd');
select date_format('2020-06-01', 'yyyy-MM-dd HH:mm:ss');

-- 计算emp表中，每个人的工龄
select *, round(datediff(current_date, hiredate)/365,1)
workingyears from emp;

1.3、字符串函数

-- 转小写。lower
select lower("HELLO WORLD");

-- 转大写。upper
select lower(ename), ename from emp;

-- 求字符串长度。length
select length(ename), ename from emp;

-- 字符串拼接。 concat / ||
select empno || " " ||ename idname from emp;
select concat(empno, " " ,ename) idname from emp;

-- 指定分隔符。concat_ws(separator, [string | array(string)]+)
SELECT concat_ws('.', 'www', array('lagou', 'com'));
select concat_ws(" ", ename, job) from emp;

-- 求子串。substr
SELECT substr('www.lagou.com', 5);
SELECT substr('www.lagou.com', -5);
SELECT substr('www.lagou.com', 5, 5);

-- 字符串切分。split，注意 '.' 要转义
select split("www.lagou.com", "\\.");

1.4、数学函数

-- 四舍五入。round
select round(314.15926);
select round(314.15926, 2);
select round(314.15926, -2);

-- 向上取整。ceil
select ceil(3.1415926);

-- 向下取整。floor
select floor(3.1415926);

-- 其他数学函数包括：绝对值、平方、开方、对数运算、三角运算等

1.5、条件函数【重要】

-- if (boolean testCondition, T valueTrue, T valueFalseOrNull)
select sal, if (sal<1500, 1, if (sal < 3000, 2, 3)) from emp;

-- CASE WHEN a THEN b [WHEN c THEN d]* [ELSE e] END
-- 将emp表的员工工资等级分类：0-1500、1500-3000、3000以上
select sal, if (sal<=1500, 1, if (sal <= 3000, 2, 3)) from emp;

-- CASE WHEN a THEN b [WHEN c THEN d]* [ELSE e] END
-- 复杂条件用 case when 更直观
select sal, case when sal<=1500 then 1
                 when sal<=3000 then 2
                 else 3 end sallevel
from emp;

-- 以下语句等价
select ename, deptno,
    case deptno when 10 then 'accounting'
                when 20 then 'research'
                when 30 then 'sales'
                else 'unknown' end deptname
from emp;

select ename, deptno,
    case when deptno=10 then 'accounting'
         when deptno=20 then 'research'
         when deptno=30 then 'sales'
         else 'unknown' end deptname
from emp;


-- COALESCE(T v1, T v2, ...)。返回参数中的第一个非空值；如果所有值都为NULL，那么返回NULL
select sal, coalesce(comm, 0) from emp;


-- isnull(a) isnotnull(a)
select * from emp where isnull(comm);
select * from emp where isnotnull(comm);


-- nvl(T value, T default_value)
select empno, ename, job, mgr, hiredate, deptno, sal + nvl(comm,0) sumsal from emp;


-- nullif(x, y) 相等为空，否则为a
SELECT nullif("b", "b"), nullif("b", "a");

1.6、UDTF函数【重要】

UDTF : User Defined Table-Generating Functions。用户定义表生成函数，一行输入，多行输出。

-- explode，炸裂函数
-- 就是将一行中复杂的 array 或者 map 结构拆分成多行
select explode(array('A','B','C')) as col;
select explode(map('a', 8, 'b', 88, 'c', 888));


-- UDTF's are not supported outside the SELECT clause, nor nested in expressions
-- SELECT pageid, explode(adid_list) AS myCol... is not supported
-- SELECT explode(explode(adid_list)) AS myCol... is not supported
-- lateral view 常与 表生成函数explode结合使用
-- lateral view 语法：
lateralView: LATERAL VIEW udtf(expression) tableAlias AS columnAlias (',' columnAlias)*
fromClause: FROM baseTable (lateralView)*

-- lateral view 的基本使用
with t1 as (
    select 'OK' cola, split('www.lagou.com', '\\.') colb
)
select cola, colc
    from t1
        lateral view explode(colb) t2 as colc;

1.7、UDTF 案例1：

-- 数据(id tags)：
1 1,2,3
2 2,3
3 1,2

--编写sql,实现如下结果：
1 1
1 2
1 3
2 2
2 3
3 1
3 2

-- 建表加载数据
create table tab1(id int, tags string)
row format delimited fields terminated by '\t';
load data local inpath '/home/hadoop/data/tab1.dat' into table tab1;

-- SQL
select id, split(tags, ',') from tab1;

select id, tag
    from tab1
        lateral view explode(split(tags, ",")) t1 as tag;

1.8、UDTF 案例2：

-- 数据准备
lisi|Chinese:90,Math:80,English:70
wangwu|Chinese:88,Math:90,English:96
maliu|Chinese:99,Math:65,English:60

-- 创建表
create table studscore(
 name string
  ,score map<String,string>)
row format delimited
fields terminated by '|'
collection items terminated by ','
map keys terminated by ':';

-- 加载数据
load data local inpath '/home/hadoop/data/score.dat' overwrite into table studscore;


-- 需求：找到每个学员的最好成绩
-- 第一步，使用 explode 函数将map结构拆分为多行
select explode(score) as (subject, socre) from studscore;
--但是这里缺少了学员姓名，加上学员姓名后出错。下面的语句有是错的
select name, explode(score) as (subject, socre) from studscore;

-- 第二步：explode常与 lateral view 函数联用，这两个函数结合在一起能关联其他字段
select name, subject, score1 as score from studscore
lateral view explode(score) t1 as subject, score1;

-- 第三步：找到每个学员的最好成绩
select name, max(mark) maxscore
    from (select name, subject, mark
            from studscore lateral view explode(score) t1 as subject, mark) t1
group by name;


with tmp as (
    select name, subject, mark
        from studscore lateral view explode(score) t1 as subject,mark
)
select name, max(mark) maxscore
from tmp
group by name;

小结：

将一行数据转换成多行数据，可以用于array和map类型的数据；
lateral view 与 explode 联用，解决 UDTF 不能添加额外列的问题

2、窗口函数【重要】

窗口函数又名开窗函数，属于分析函数的一种。用于解决复杂报表统计需求的功能强大的函数，很多场景都需要用到。窗口函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是：对于每个组返回多行，而聚合函数对于每个组只返回一行。窗口函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化。

2.1、over 关键字

使用窗口函数之前一般要要通过over()进行开窗

-- 查询emp表工资总和
select sum(sal) from emp;

-- 不使用窗口函数，有语法错误
select ename, sal, sum(sal) salsum from emp;

-- 使用窗口函数，查询员工姓名、薪水、薪水总和
select ename, sal, sum(sal) over() salsum,
    concat(round(sal / sum(sal) over()*100, 1) || '%')
ratiosal
    from emp;

注意：窗口函数是针对每一行数据的；如果over中没有参数，默认的是全部结果集；

2.2、partition by子句

在over窗口中进行分区，对某一列进行分区统计，窗口的大小就是分区的大小

-- 查询员工姓名、薪水、部门薪水总和
select ename, sal, sum(sal) over(partition by deptno) salsum from emp;

2.3、order by 子句

order by 子句对输入的数据进行排序

-- 增加了order by子句；sum：从分组的第一行到当前行求和
select ename, sal, deptno, sum(sal) 
    over(partition by deptno order by sal) salsum
from emp;

2.4、Window子句

rows between ... and ...

如果要对窗口的结果做更细粒度的划分，使用window子句，有如下的几个选项：

unbounded preceding。组内第一行数据
n preceding。组内当前行的前n行数据
current row。当前行数据
n following。组内当前行的后n行数据
unbounded following。组内最后一行数据

-- rows between ... and ... 子句
-- 等价。组内，第一行到当前行的和
select ename, sal, deptno, sum(sal) over(partition by deptno order by ename) from emp;

select ename, sal, deptno, sum(sal) 
    over(partition by deptno order by ename
rows between unbounded preceding and current row)
from emp;


-- 组内，第一行到最后一行的和
select ename, sal, deptno, sum(sal) 
    over(partition by deptno order by ename
rows between unbounded preceding and unbounded following)
from emp;


-- 组内，前一行、当前行、后一行的和
select ename, sal, deptno, sum(sal) 
    over(partition by deptno order by ename 
rows between 1 preceding and 1 following)
from emp;

2.5、排名函数

都是从1开始，生成数据项在分组中的排名。

row_number()。排名顺序增加不会重复；如1、2、3、4、... ...
RANK()。排名相等会在名次中留下空位；如1、2、2、4、5、... ...
DENSE_RANK()。排名相等会在名次中不会留下空位；如1、2、2、3、4、... ...

-- row_number / rank / dense_rank
100 1 1 1
100 2 1 1
100 3 1 1
99 4 4 2
98 5 5 3
98 6 5 3
97 7 7 4

-- 数据准备
class1 s01 100
class1 s03 100
class1 s05 100
class1 s07 99
class1 s09 98
class1 s02 98
class1 s04 97
class2 s21 100
class2 s24 99
class2 s27 99
class2 s22 98

class2 s25 98
class2 s28 97
class2 s26 96

-- 创建表加载数据
create table t2(
    cname string,
    sname string,
    score int
) row format delimited fields terminated by '\t';
load data local inpath '/home/hadoop/data/t2.dat' into table t2;


-- 按照班级，使用3种方式对成绩进行排名
select cname, sname, score,
    row_number() over (partition by cname order by score desc) rank1, 
    rank() over (partition by cname order by score desc) rank2, 
    dense_rank() over (partition by cname order by score desc) rank3
from t2;


-- 求每个班级前3名的学员--前3名的定义是什么--假设使用dense_rank
select cname, sname, score, rank
    from (select cname, sname, score,
        dense_rank() over (partition by cname order by score desc) rank
        from t2) tmp
where rank <= 3;

2.6、序列函数

lag：返回当前数据行的上一行数据
lead：返回当前数据行的下一行数据
first_value：取分组内排序后，截止到当前行，第一个值
last_value：分组内排序后，截止到当前行，最后一个值
ntile：将分组的数据按照顺序切分成n片，返回当前切片值

-- 测试数据 userpv.dat。cid ctime pv
cookie1,2019-04-10,1
cookie1,2019-04-11,5
cookie1,2019-04-12,7

cookie1,2019-04-13,3
cookie1,2019-04-14,2
cookie1,2019-04-15,4
cookie1,2019-04-16,4
cookie2,2019-04-10,2
cookie2,2019-04-11,3
cookie2,2019-04-12,5
cookie2,2019-04-13,6
cookie2,2019-04-14,3
cookie2,2019-04-15,9
cookie2,2019-04-16,7

-- 建表语句
create table userpv(
    cid string,
    ctime date,
    pv int
)
row format delimited fields terminated by ",";

-- 加载数据
Load data local inpath '/home/hadoop/data/userpv.dat' into table userpv;


-- lag。返回当前数据行的上一行数据
-- lead。功能上与lag类似
select cid, ctime, pv, 
    lag(pv) over(partition by cid order by ctime) lagpv,
    lead(pv) over(partition by cid order by ctime) leadpv
from userpv;


-- first_value / last_value
select cid, ctime, pv,
    first_value(pv) over (partition by cid order by ctime
        rows between unbounded preceding and unbounded following) as firstpv,
    last_value(pv) over (partition by cid order by ctime
        rows between unbounded preceding and unbounded following) as lastpv
from userpv;


-- ntile。按照cid进行分组，每组数据分成2份
select cid, ctime, pv,
    ntile(2) over(partition by cid order by ctime) ntile
from userpv;