Hive 中常用的函数以及数据类型-易微帮

数据类型

1.基本数据类型:

数据类型	大小	范围	示例
TINYINT	1byte	-128 ~ 127	100Y
SMALLINT	2byte	-32768 ~ 32767	100S
INT	4byte	-2^32~ 2^32-1	100
BIGINT	8byte	-2^64~ 2^64-1	100L
FLOAT	4byte	单精度浮点数	5.21
DOUBLE	8byte	双精度浮点数	5.21
DECIMAL	-	高精度浮点数	DECIMAL(9,8)
BOOLEAN	-	布尔型	true/false
BINARY	-	字节数组	-

2.字符串类型:

数据类型	长度	示例
STRING	-	'abc'
VARCHAR	1-65535	'abc'
CHAR	1-255	'abc'

对于VARCHAR创建时需指定长度，如果插入的字符串超过了指定的长度，则会被截断，尾部的空格也会作为字符串的一部分，影响字符串的比较。
对于CHAR类型来说，它是固定长度的，如果插入的字符串长度不如指定的长度，则会用空格补齐。但是尾部的空格不影响字符串的比较。

3.日期与时间戳类型:(格式很重要,格式不对加载数据为空值)

数据类型	格式	示例
DATE	yyyy-MM-dd	2020-07-04
TIMESTAMPS	yyyy-MM-dd HH:mm:ss.fffffffff	2020-07-04 12:36:25.111

4.集合类型:

ARRAY：ARRAY 类型是由一系列相同数据类型的元素组成，这些元素可以通过下标来访问。比如有一个 ARRAY 类型的变量 fruits，它是由['apple','orange','mango']组成，可以由下标fruits[1]来访问元素orange。hive中经过split拆分后为ARRAY类型；
MAP：MAP 包含 key->value 键值对，可以通过 key 来访问元素。比如变量userlist是一个 map类型：username:password，需要通过userlist['username']来得到这个用户对应的 password。
STRUCT：STRUCT 可以包含不同数据类型的元素。这些元素可以通过点语法的方式来得到所需要的元素，比如 user 是一个 STRUCT 类型：15,北京。可以通过 user.address 得到这个用户的地址。

存储格式

Hive会为每个创建的数据库在HDFS上创建一个目录，该数据库的表会以子目录形式存储，表中的数据会以表目录下的文件形式存储。对于default数据库，默认的缺省数据库没有自己的目录，default数据库下的表默认存放在/user/hive/warehouse目录下。

textfile为默认格式，存储方式为行存储。数据不做压缩，磁盘开销大，数据解析开销大。
SequenceFile是Hadoop API提供的一种二进制文件支持，具有使用方便、可分割、可压缩的特点。

SequenceFile支持三种压缩选择：NONE, RECORD, BLOCK。 Record压缩率低，一般建议使用BLOCK压缩。

RCFile 一种行列存储相结合的存储方式。
ORCFile 数据按照行分块，每个块按照列存储，其中每个块都存储有一个索引。hive给出的新格式，属于RCFILE的升级版,性能有大幅度提升,而且数据可以压缩存储,压缩快快速列存取。

Parquet Parquet也是一种行式存储，同时具有很好的压缩性能；同时可以减少大量的表扫描和反序列化的时间。

数据格式

当数据存储在文本文件中，必须按照一定格式区别行和列，并且在Hive中指明这些区分符。Hive默认使用了几个平时很少出现的字符，这些字符一般不会作为内容出现在记录中。
\n   对于文本文件来说，每行是一条记录，所以\n 来分割记录
^A (Ctrl+A)   分割字段，也可以用\001 来表示
^B (Ctrl+B)   用于分割 Arrary 或者 Struct 中的元素，或者用于 map 中键值之间的分割，也可以用\002 分割。
^C   用于 map 中键和值自己分割，也可以用\003 表示。

常见函数内置函数

1.数值函数

指定精度的取整函数 round(a,b) 返回值: DOUBLE b指定精度

ceil 向上取整函数 floor 向下取整函数

select round(12.34567,2),ceil(12.3456),`floor`(12.3456) ;

随机数函数 round 返回0~1的随机数

--获取1-100
select ceil(rand()*100);

取余函数 mod

--取余
select mod(10,3),10%3;

幂函数 pow 开方函数 sqrt


--计算次幂
select pow(2,3),power(2,3);
--开平方
select sqrt(9);

2.日期函数

to_date(string timestamp):返回时间字符串中的日期部分,

如to_date('1970-01-01 00:00:00')='1970-01-01'

current_date:返回当前日期
current_timestamp:返回当前日期和时间
year(date)：返回日期date的年,类型为int

month(date)：返回日期date的月,类型为int,

day(date): 返回日期date的天,类型为int,

hour(date):返回日期date的时，类型为int
weekofyear(date1)：返回日期date1位于该年第几周。

datediff(date1,date2):返回日期date1与date2相差的天数

date_add(date1,int1):返回日期date1加上int1的日期

date_sub(date1,int1):返回日期date1减去int1的日期

months_between(date1,date2):返回date1与date2相差月份

add_months(date1,int1):返回date1加上int1个月的日期，int1可为负数

last_day(date1):返回date1所在月份最后一天

trunc(date1,string1):返回日期最开始年份或月份。string1可为年(YYYY/YY/YEAR)或月 (MONTH/MON/MM)。

unix_timestamp():返回当前时间的unix时间戳，可指定日期格式。

from_unixtime():返回unix时间戳的日期，可指定格式。

3.条件函数

if(boolean,t1,t2):若布尔值成立，则返回t1,反正返回t2。

select content,if(count_iphone>7,count_iphone,0) from (
select content,  (length(content)-length(regexp_replace(content,'(iphone)|(iPhone)','')))/length('iphone') count_iphone
from dw_weibo where locate('iPhone',content)>0 or locate('iphone',content)>0) h;

case when boolean then t1 else t2 end:若布尔值成立，则t1,否则t2,可加多重判断非空查找函数

select content,case when count_iphone>10 then count_iphone end from(
select content,  (length(content)-length(regexp_replace(content,'(iphone)|(iPhone)','')))/length('iphone') count_iphone
from dw_weibo where locate('iPhone',content)>0 or locate('iphone',content)>0) k;

coalesce(v0,v1,v2):返回参数中的第一个非空值,若所有值均为null,则返回null。coalesce(null,1,2)返回1

4.字符串函数

length(string1):返回字符串长度
concat(s1,s2,s3,...):返回拼接string1及string2后的字符串,如果拼接的数据有null则结果为null
concat_ws(sep,s1,s2,....):返回按指定分隔符拼接的字符串,只能拼接字符串,支持空值拼接
lower(string1):返回小写字符串，同lcase(string1)。upper()/ucase()：返回大写字符串
trim(string1):去字符串左右空格，ltrim(string1):去字符串左空格。rtrim(string1):去字符串右空 repeat(string1，int1)：返回重复string1字符串int1次后的字符串
reverse(string1):返回string1反转后的字符串。

rpad(string1,len1,pad1):以pad1字符右填充string1字符串，至len1长度。

split(string1,pat1):以pat1正则分隔字符串string1,返回数组。

substr(string1,index1,int1):以index位置起截取int1个字符。

get_json_object:json解析函数

with tmp as (
    select '[{"beCommentWeiboId":"","beForwardWeiboId":"","catchTime":"1387158842","commentCount":"1288","content":"秋天要走了嗎？捨不得你耶再留一回回兒吧！@最美和声","createTime":"1382041054","info1":"","info2":"","info3":"","mlevel":"","musicurl":[],"pic_list":["http://ww4.sinaimg.cn/square/687489f8jw1e9ottklbauj20vk0l1whj.jpg","http://ww3.sinaimg.cn/square/687489f8jw1e9ottmdchlj20vk0l1mzx.jpg","http://ww3.sinaimg.cn/square/687489f8jw1e9ottoedd1j20et0m8acf.jpg","http://ww4.sinaimg.cn/square/687489f8jw1e9ottrjaszj20hs0npjvg.jpg","http://ww1.sinaimg.cn/square/687489f8jw1e9ottts4fqj20hs0qp41r.jpg","http://ww1.sinaimg.cn/square/687489f8jw1e9ottwp24xj20hs0q9771.jpg","http://ww4.sinaimg.cn/square/687489f8jw1e9ottyt3d7j20hs0buwg0.jpg","http://ww2.sinaimg.cn/square/687489f8jw1e9otu0urofj20hs0dcdhw.jpg","http://ww3.sinaimg.cn/square/687489f8jw1e9otu9znc8j218g0xc17y.jpg"],"praiseCount":"6860","reportCount":"1303","source":"iPhone客户端","userId":"1752467960","videourl":[],"weiboId":"3634605976485130","weiboUrl":"http://weibo.com/1752467960/Aewg5rd4S"}]
' str
)
select get_json_object(str,'$.[0].content') ,
        split(regexp_replace(get_json_object(str,'$.[0].pic_list'),'\\[|\\]|"',''),',') from tmp;

parse_url :url解析函数

select parse_url('https://mp.csdn.net/mp_blog/creation/editor/137919192?spm=1011.2124.3001.9778','HOST')
--https://mp.csdn.net

space:空格字符串函数返回指定空格个数

ascii :首字符asc码函数

find_in_set:集合查找函数

select find_in_set('aaa','qqqq,rrrr,yyyy,aaa,gggg');

regexp_extract:正则表达式解析函数

select regexp_extract('100-200','(\\d+)-(\\d+)',2)
--200

regexp_replace:正则替代函数

select regexp_replace('100-200','(\\d+)','num')

5.集合函数

size:获取map或者arry的个数 size(map<K,V>) or size(ARRY)

map_keys:获取map中key的列表

map_values:获取map中value的列表

select map_keys(scores) from st_u;
-- ["语文","数学"]
select map_values(st_u.scores) from st_u;
-- [90,88]

arry_contains:判断数组是否包含某元素

select * from goods where array_contains(goods.goods,"衣服");
-- 1,2022-03-04,"[""衣服"",""鞋子"",""电脑""]",102,"{""name"":""zhangsan"",""phone"":"" 12123213""}"
-- 3,2022-03-04,"[""衣服"",""鞋子"",""电脑""]",102,"{""name"":""zhangsan"",""phone"":"" 12123213""}"
-- 5,2022-03-04,"[""衣服"",""鞋子"",""电脑""]",102,"{""name"":""zhangsan"",""phone"":"" 12123213""}"

sort_arry:数组排序函数


select sort_array(`array`(2,54,23,1,32423))
-- [1,2,23,54,32423]

6.类型转换函数

任意之间的数据类型转换:cast

select concat_ws('-',ename,job,hiredate,cast(comm as string)) from emp;

7.数据脱敏函数

mask 将查询数据结果大写字母变为X 小写字母变为x 数字变为n

select mask('12312ASUSUUchcudhid');
-- nnnnnXXXXXXxxxxxxxx

mask_frist_n 前n个变换

select mask_first_n('12312ASUSUUchcudhid',7);
-- nnnnnXXUSUUchcudhid

mask_last_n 后n个脱敏

select mask_last_n('12312ASUSUUchcudhid',7);
-- 12312ASUSUUcxxxxxxx

mask_show_frist_n :除了前n个其余脱敏

select mask_show_first_n('12312ASUSUUchcudhid',7);
-- 12312ASXXXXxxxxxxxx

mask_show_last_n :除了后n个其余脱敏

select mask_show_last_n('12312ASUSUUchcudhid',7);
-- nnnnnXXXXXXxhcudhid

mask_hash:返回字符串的hash编码

select mask_hash('12312ASUSUUchcudhid',7);
-- ca609eafd58f6d5bf4deae8680592ce7

8.其他杂项函数

调用Java自带的函数:java_method

自定义函数

1.一进一出函数 UDF普通函数

2.多进一出函数 UDAF聚合函数 Aggregation

3.UDTF 表生成函数 explode一进多出

select explode(`array`(1,2,2,6,3,6,334,5656,3));
-- 1
-- 2
-- 2
-- 6
-- 3
-- 6
-- 334
-- 5656
-- 3

explode函数:属于UDTF类型接受arry和map类型的数据作为输入,然后输出把每个元素变成一行

一般可以单独使用,多半是结合业务lateral view一起使用

输出生成一张虚表,其数据源于原表,在操作中,不能查询原表数据又想explode返回数据

select explode(map('name','张三','age','18','sex','male'))
-- name,张三
-- age,18
-- sex,male

Hive Lateal View 侧视图

Lateral view 是一种特殊语法主要搭配UDTF类型一起使用,解决一些查询限制的问题

一般使用UDTF ,就会固定搭配 Lateral VIew使用

Lateral View 主要功能是将原本汇总在一条（行）的数据拆分成多条（行）成虚拟表，再与原表进行笛卡尔积，从而得到明细表。配合UDTF函数使用，一般情况下经常与explode函数搭配，explode的操作对象（列值）是 ARRAY 或者 MAP ,可以通过 split 函数将 String 类型的列值转成 ARRAY 来处理。

语法: Select ..... from tableA lateral view UDTF(****) 别名 as col1 ,col2.....

create table test_01 (
    DEPT_NO    string    comment'部门编号',
    DEPT_TREE  string    comment'部门层级树',
    BENIFIT    int       comment'利润（万元）'
)
comment '测试-部门利润表'
partitioned by (deal_date string comment '日期分区' )
stored as orc;
alter table test_01 drop if exists partition (DEAL_DATE='20220516');
insert into table test_01 partition (DEAL_DATE='20220516')
select '101','A.A1.101',50;
insert into table test_01 partition (DEAL_DATE='20220516')
select '102','A.A1.102',20;
insert into table test_01 partition (DEAL_DATE='20220516')
select '201','A.A2.201',80;
select * from test_01;
-- 101,A.A1.101,50,20220516
-- 102,A.A1.102,20,20220516
-- 201,A.A2.201,80,20220516
select tmp_dept_no as DEPT_NO, sum(BENIFIT) as BENIFIT
from test_01
LATERAL VIEW explode (split(DEPT_TREE, '\\.')) tmp as tmp_dept_no
where DEAL_DATE='20220516'
group by tmp_dept_no;
-- 101,50
-- 102,20
-- 201,80
-- A,150
-- A1,70
-- A2,80

聚合函数

聚合函数属于典型多行输入一行输出也就是UDAF ,属于UDAF类型函数

通常搭配Group by一起使用 ,对分组后进行聚合操作

基础聚合

内置的UDAF函数例如 max ,min,avg,sum,通常搭配Group By一起适用

drop table if exists student;
create table student(
    num int,
    name string,
    sex string,
    age int,
    dept string)
row format delimited
fields terminated by ',';

insert into student values (95001,'lisi','M',20,'CS');
insert into student values (95002,'zhangsan','F',19,'IS');
insert into student values (95003,'wangwu','M',18,'MA');
insert into student values (95004,'zhaoliu','F',22,'IS');
insert into student values (95005,'xiaoba','M',21,'MA');
-- 统计男女人数
select sex,count(*) from student group by sex;
-- 统计平均年龄,人数
select count(*),avg(age) from student;

--搭配条件函数一起使用
select
    sum(case when sex='M' then 1 else 0 end)
from student;
select
    sum(if(sex='M',1,0))
from student;

聚合针对null处理

create table tmp1(col1 int,col2 int);
insert into table tmp1 values(1,2),(null,2),(2,3);
select * from  tmp1;
-- 空值列被忽略
select sum(tmp1.col1),sum(col1+tmp1.col2) from tmp1;
select
  sum(coalesce(col1,0)),
  sum(coalesce(col1,0)+col2)
from tmp1;

配合distinct去重

在此场景下,自动设置只会启动一个MapReduce处理结果

select count(distinct sex) from student;

-- 性能优化
select count(*) from
             (select distinct sex from student) k;

利用struct构造数据针对应用max找出最大元素

select sex,
    max(struct(age,name)).col1 as age,
    max(struct(age,name)).col2 as name
from student
group by sex;

增强聚合

常用的增强聚合函数包括:Grouping set,cube,rollup,主要适用于OLAP多为数据分析,多维指的是

问题的角度

数据:
create table if not exists cookie_info(
    month string,day string,cookies string
)row format delimited
fields terminated by ',';
2018-03,2018-03-10,cookie1
2018-03,2018-03-10,cookie5
2018-03,2018-03-12,cookie7
2018-04,2018-04-12,cookie3
2018-04,2018-04-13,cookie2
2018-04,2018-04-13,cookie4
2018-04,2018-04-16,cookie4
2018-03,2018-03-10,cookie2
2018-03,2018-03-10,cookie3
2018-04,2018-04-12,cookie5
2018-04,2018-04-13,cookie6
2018-04,2018-04-15,cookie6
2018-04,2018-04-15,cookie3
2018-04,2018-04-16,cookie1

-- Grouping set

select
    month,
    day,
    count(distinct cookies) as nums
from cookie_info
group by month,day
grouping sets (month,day);
--grouping set 把两种聚合结果做了union操作
SELECT month,NULL,COUNT(DISTINCT cookies) AS nums,1 AS GROUPING__ID FROM cookie_info GROUP BY month
UNION ALL
SELECT NULL as month,day,COUNT(DISTINCT cookies) AS nums,2 AS GROUPING__ID FROM cookie_info GROUP BY day;

SELECT
    month,
    day,
    COUNT(DISTINCT cookies) AS nums
FROM cookie_info
GROUP BY month,day
GROUPING SETS (month,day,(month,day));   --1 month   2 day    3 (month,day)
-- ,2018-03-10,4
-- ,2018-03-12,1
-- ,2018-04-12,2
-- ,2018-04-13,3
-- ,2018-04-15,2
-- ,2018-04-16,2
-- 2018-03,,5
-- 2018-03,2018-03-10,4
-- 2018-03,2018-03-12,1
-- 2018-04,,6
-- 2018-04,2018-04-12,2
-- 2018-04,2018-04-13,3
-- 2018-04,2018-04-15,2
-- 2018-04,2018-04-16,2

cube:表示根据Group by的维度所有组合进行聚合所有组合的总个数 2^n

SELECT
    month,
    day,
    COUNT(DISTINCT cookies) AS nums
FROM cookie_info
GROUP BY month,day
WITH CUBE;

相当于多种维度的聚合

SELECT NULL,NULL,COUNT(DISTINCT cookies) AS nums,0 AS GROUPING__ID FROM cookie_info
UNION ALL
SELECT month,NULL,COUNT(DISTINCT cookies) AS nums,1 AS GROUPING__ID FROM cookie_info GROUP BY month
UNION ALL
SELECT NULL,day,COUNT(DISTINCT cookies) AS nums,2 AS GROUPING__ID FROM cookie_info GROUP BY day
UNION ALL
SELECT month,day,COUNT(DISTINCT cookies) AS nums,3 AS GROUPING__ID FROM cookie_info GROUP BY month,day;

WITH ROLLUP 以month维度进行层级聚合

SELECT
    nvl(month,'总计'),
    nvl(day,'月份总计'),
    COUNT(DISTINCT cookies) AS nums
FROM cookie_info
GROUP BY month,day
WITH ROLLUP;

WITH ROLLUP 以day维度进行层级聚合

SELECT
    nvl(month,'总计'),
    nvl(day,'总计'),
    COUNT(DISTINCT cookies) AS nums
FROM cookie_info
GROUP BY day,month
WITH ROLLUP;

排名分析函数

1.基本语法

Function (arg1,..., argn) OVER ([PARTITION BY <...>] [ORDER BY <....>]
[<window_expression>])

Function (arg1,..., argn) 可以是下面的四类函数：

Aggregate Functions: 聚合函数,比如：sum(...)、 max(...)、min(...)、avg(...)等
Sort Functions: 数据排序函数, 比如：rank(...)、row_number(...)等
Analytics Functions: 统计和比较函数, 比如：lead(...)、lag(...)、 first_value(...)等

CREATE TABLE IF NOT EXISTS employee (
name string,
dept_num int,
employee_id int,
salary int,
type string,
start_date date
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '|'
STORED as TEXTFILE;
Michael|1000|100|5000|full|2014-01-29
Will|1000|101|4000|full|2013-10-02
Wendy|1000|101|4000|part|2014-10-02
Steven|1000|102|6400|part|2012-11-03
Lucy|1000|103|5500|full|2010-01-03
Lily|1001|104|5000|part|2014-11-29
Jess|1001|105|6000|part|2014-12-02
Mike|1001|106|6400|part|2013-11-03
Wei|1002|107|7000|part|2010-04-03
Yun|1002|108|5500|full|2014-01-29
Richard|1002|109|8000|full|2013-09-01

-- (1)查询姓名、部门编号、工资以及部门人数

select
    name,
    dept_num as deptno ,
    salary,
    count(*) over (partition by dept_num) as cnt
from employee ;

（2）查询姓名、部门编号、工资以及每个部门的总工资，部门总工资按照降序输出

select 
    name ,
    dept_num as deptno,
    salary,
    sum(salary) over (partition by dept_num order by dept_num) as sum_dept_salary 
from employee 
order by sum_dept_salary desc;

2.窗口排序函数

row_number：根据具体的分组和排序，为每行数据生成一个起始值等于1的唯一序列数

rank：对组中的数据进行排名，如果名次相同，则排名也相同，但是下一个名次的排名序号会出现不连续。比如查找具体条件的topN行

dense_rank：dense_rank函数的功能与rank函数类似，dense_rank函数在生成序号时是连续的，而rank函数生成的序号有可能不连续。当出现名次相同时，则排名序号也相同。而下一个排名的序号与上一个排名序号是连续的。

percent_rank：排名计算公式为：(current rank - 1)/(total number of rows - 1)

ntile：将一个有序的数据集划分为多个桶(bucket)，并为每行分配一个适当的桶数。它可用于将数据划分为相等的小切片，为每一行分配该小切片的数字序号。

(1)查询姓名、部门编号、工资、排名编号(按工资的多少排名)

select 
   name ,
   dept_num as dept_no ,
   salary,
   row_number() over (order by salary desc ) rnum 
from employee;

(2)查询每个部门工资最高的两个人的信息(姓名、部门、薪水)

select
   name,
   dept_num,
   salary
from
(
 select name ,
   dept_num ,
   salary,
   row_number() over (partition by dept_num order by salary desc ) rnum
 from employee) t1
 where rnum <= 2;

(3)查询每个部门的员工工资排名信息

select
 name ,
 dept_num as dept_no ,
 salary,row_number() over (partition by dept_num order by salary desc ) rnum
from employee;

(4)使用rank函数进行排名

select
  name,
  dept_num,
  salary,
  rank() over (order by salary desc) rank
from employee;

(5）使用dense_rank进行排名

select
  name,
  dept_num,
  salary,
  dense_rank() over (order by salary desc) rank
from employee;

(6)使用percent_rank()进行排名

select
  name,
  dept_num,
  salary,
  percent_rank() over (order by salary desc) rank
from employee;

(7)使用ntile进行数据分片排名

SELECT
name,
dept_num as deptno,
salary,
ntile(4) OVER(ORDER BY salary desc) as ntile
FROM employee;

从 Hive v2.1.0开始, 支持在OVER语句里使用聚集函数，比如

SELECT
  dept_num,
  row_number() OVER (PARTITION BY dept_num ORDER BY sum(salary)) as rk
FROM employee
GROUP BY dept_num;

窗口分析函数

常用的分析函数主要包括：

cume_dist

如果按升序排列，则统计：小于等于当前值的行数/总行数(number of rows ≤ current row)/(total number of rows）。如果是降序排列，则统计：大于等于当前值的行数/总行数。比如，统计小于等于当前工资的人数占总人数的比例，用于累计统计。

lead(value_expr[,offset[,default]])

用于统计窗口内往下第n行值。第一个参数为列名，第二个参数为往下第n行（可选，默认为1），第三个参数为默认值（当往下第n行为NULL时候，取默认值，如不指定，则为NULL

lag(value_expr[,offset[,default]]):

与lead相反，用于统计窗口内往上第n行值。第一个参数为列名，第二个参数为往上第n行（可选，默认为1），第三个参数为默认值（当往上第n行为NULL时候，取默认值，如不指定，则为NULL）

first_value: 取分组内排序后，截止到当前行，第一个值
last_value

取分组内排序后，截止到当前行，最后一个值

(1)统计小于等于当前工资的人数占总人数的比例

SELECT
 name,
 dept_num as deptno,
 salary,
 cume_dist() OVER (ORDER BY salary) as cume
FROM employee;

（2）统计大于等于当前工资的人数占总人数的比例

SELECT
 name,
 dept_num as deptno,
 salary,
 cume_dist() OVER (ORDER BY salary desc) as cume
FROM employee;

按照部门统计小于等于当前工资的人数占部门总人数的比例

SELECT
 name,
 dept_num as deptno,
 salary,
 cume_dist() OVER (PARTITION BY dept_num ORDER BY salary) as cume
FROM employee;

（4）按部门分组，统计每个部门员工的工资以及大于等于该员工工资的下一个员工的工资

SELECT
 name,
 dept_num as deptno,
 salary,
 lead(salary,1) OVER (PARTITION BY dept_num ORDER BY salary) as lead
FROM employee;

（5）按部门分组，统计每个部门员工的工资以及小于等于该员工工资的上一个员工的工资

SELECT
 name,
 dept_num as deptno,
 salary,
 lag(salary,1) OVER (PARTITION BY dept_num ORDER BY salary) as lead
FROM employee;

（6）按部门分组，统计每个部门员工工资以及该部门最低的员工工资

SELECT
 name,
 dept_num as deptno,
 salary,
 first_value(salary) OVER (PARTITION BY dept_num ORDER BY salary) as fval
FROM employee;

（7）按部门分组，统计每个部门员工工资以及该部门最高的员工工资

SELECT
 name,
 dept_num as deptno,
 salary,
 last_value(salary) OVER (PARTITION BY dept_num ORDER BY salary RANGE
BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as lval
FROM employee;

注意：last_value默认的窗口是RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，表示当前行永远是最后一个值，需改成RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING。

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

为默认值，即当指定了ORDER BY从句，而省略了window从句，表示从开始到当前行。

RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING

表示从当前行到最后一行

RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

表示所有行

n PRECEDING m FOLLOWING

表示窗口的范围是：[（当前行的行数）- n, （当前行的行数）+m]

Hive 中常用的函数以及数据类型

数据类型

1.基本数据类型:

2.字符串类型:

3.日期与时间戳类型:(格式很重要,格式不对加载数据为空值)

4.集合类型:

存储格式

数据格式

常见函数内置函数

1.数值函数

2.日期函数

3.条件函数

4.字符串函数

5.集合函数

6.类型转换函数

7.数据脱敏函数

8.其他杂项函数

自定义函数

Hive Lateal View 侧视图

聚合函数

基础聚合

增强聚合

排名分析函数

1.基本语法

2.窗口排序函数

窗口分析函数

网站公告

今日签到

热门文章

最新发布

Hive 中常用的函数以及数据类型

数据类型

1.基本数据类型:

2.字符串类型:

3.日期与时间戳类型:(格式很重要,格式不对加载数据为空值)

4.集合类型:

存储格式

数据格式

常见函数 内置函数

1.数值函数​​​​​​​​​​​​​​​​​​​​​

2.日期函数

3.条件函数

4.字符串函数

5.集合函数

6.类型转换函数

7.数据脱敏函数

8.其他杂项函数

自定义函数

Hive Lateal View 侧视图

聚合函数

基础聚合

增强聚合

排名分析函数

1.基本语法

2.窗口排序函数

窗口分析函数

网站公告

今日签到

热门文章

最新发布

常见函数内置函数

1.数值函数