一文学完所有的 Hive Sql（两万字最全详解）

关注

发布于: 2021 年 04 月 01 日

Hive Sql 大全

本文基本涵盖了 Hive 日常使用的所有 SQL，因为 SQL 太多，所以将 SQL 进行了如下分类：一、DDL 语句（数据定义语句）：
对数据库的操作：包含创建、修改数据库
对数据表的操作：分为内部表及外部表，分区表和分桶表
二、DQL 语句（数据查询语句）：
单表查询、关联查询
hive 函数：包含聚合函数，条件函数，日期函数，字符串函数等
行转列及列转行：lateral view 与 explode 以及 reflect
窗口函数与分析函数
其他一些窗口函数

文章首发于公众号【五分钟学大数据】，大数据领域原创技术号，每周更新大数据技术文及面试真题解析，关注后可领取精心制作大数据面试宝典！

hive 的 DDL 语法

对数据库的操作

创建数据库:

create database if not exists myhive;说明：hive的表存放位置模式是由hive-site.xml当中的一个属性指定的 :hive.metastore.warehouse.dir
创建数据库并指定hdfs存储位置 :create database myhive2 location '/myhive2';

复制代码

修改数据库:

alter  database  myhive2  set  dbproperties('createtime'='20210329');

复制代码

说明：可以使用 alter database 命令来修改数据库的一些属性。但是数据库的元数据信息是不可更改的，包括数据库的名称以及数据库所在的位置

查看数据库详细信息

查看数据库基本信息hive (myhive)> desc  database  myhive2;
查看数据库更多详细信息hive (myhive)> desc database extended  myhive2;

复制代码

删除数据库

删除一个空数据库，如果数据库下面有数据表，那么就会报错drop  database  myhive2;
强制删除数据库，包含数据库下面的表一起删除drop  database  myhive  cascade;

复制代码

对数据表的操作

对管理表(内部表)的操作:

建内部表:

hive (myhive)> use myhive; -- 使用myhive数据库hive (myhive)> create table stu(id int,name string);hive (myhive)> insert into stu values (1,"zhangsan");hive (myhive)> insert into stu values (1,"zhangsan"),(2,"lisi");  -- 一次插入多条数据hive (myhive)> select * from stu;

复制代码

hive 建表时候的字段类型:

对 decimal 类型简单解释下：
用法：decimal(11,2) 代表最多有 11 位数字，其中后 2 位是小数，整数部分是 9 位；如果整数部分超过 9 位，则这个字段就会变成 null；如果小数部分不足 2 位，则后面用 0 补齐两位，如果小数部分超过两位，则超出部分四舍五入
也可直接写 decimal，后面不指定位数，默认是 decimal(10,0) 整数 10 位，没有小数

创建表并指定字段之间的分隔符

create  table if not exists stu2(id int ,name string) row format delimited fields terminated by '\t' stored as textfile location '/user/stu2';

复制代码

row format delimited fields terminated by '\t' 指定字段分隔符，默认分隔符为 '\001'
stored as 指定存储格式
location 指定存储位置

根据查询结果创建表

create table stu3 as select * from stu2;

复制代码

根据已经存在的表结构创建表

create table stu4 like stu2;

复制代码

查询表的结构

只查询表内字段及属性desc stu2;
详细查询desc formatted  stu2;

复制代码

查询创建表的语句

show create table stu2;

复制代码

对外部表操作

外部表因为是指定其他的 hdfs 路径的数据加载到表当中来，所以 hive 表会认为自己不完全独占这份数据，所以删除 hive 表的时候，数据仍然存放在 hdfs 当中，不会删掉，只会删除表的元数据

构建外部表

create external table student (s_id string,s_name string) row format delimited fields terminated by '\t';

复制代码

从本地文件系统向表中加载数据

追加操作load data local inpath '/export/servers/hivedatas/student.csv' into table student;
覆盖操作load data local inpath '/export/servers/hivedatas/student.csv' overwrite  into table student;

复制代码

从 hdfs 文件系统向表中加载数据

load data inpath '/hivedatas/techer.csv' into table techer;
加载数据到指定分区load data inpath '/hivedatas/techer.csv' into table techer partition(cur_date=20201210);

复制代码

注意：
1.使用 load data local 表示从本地文件系统加载，文件会拷贝到 hdfs 上
2.使用 load data 表示从 hdfs 文件系统加载，文件会直接移动到 hive 相关目录下，注意不是拷贝过去，因为 hive 认为 hdfs 文件已经有 3 副本了，没必要再次拷贝了
3.如果表是分区表，load 时不指定分区会报错
4.如果加载相同文件名的文件，会被自动重命名

对分区表的操作

创建分区表的语法

create table score(s_id string, s_score int) partitioned by (month string);

复制代码

创建一个表带多个分区

create table score2 (s_id string, s_score int) partitioned by (year string,month string,day string);

复制代码

注意：
hive 表创建的时候可以用 location 指定一个文件或者文件夹，当指定文件夹时，hive 会加载文件夹下的所有文件，当表中无分区时，这个文件夹下不能再有文件夹，否则报错
当表是分区表时，比如 partitioned by (day string)，则这个文件夹下的每一个文件夹就是一个分区，且文件夹名为 day=20201123 这种格式，然后使用：msck repair table score; 修复表结构，成功之后即可看到数据已经全部加载到表当中去了

加载数据到一个分区的表中

load data local inpath '/export/servers/hivedatas/score.csv' into table score partition (month='201806');

复制代码

加载数据到一个多分区的表中去

load data local inpath '/export/servers/hivedatas/score.csv' into table score2 partition(year='2018',month='06',day='01');

复制代码

查看分区

show  partitions  score;

复制代码

添加一个分区

alter table score add partition(month='201805');

复制代码

同时添加多个分区

 alter table score add partition(month='201804') partition(month = '201803');

复制代码

注意：添加分区之后就可以在 hdfs 文件系统当中看到表下面多了一个文件夹

删除分区

 alter table score drop partition(month = '201806');

复制代码

对分桶表操作

将数据按照指定的字段进行分成多个桶中去，就是按照分桶字段进行哈希划分到多个文件当中去
分区就是分文件夹，分桶就是分文件

分桶优点：
1. 提高 join 查询效率
2. 提高抽样效率

开启 hive 的捅表功能

set hive.enforce.bucketing=true;

复制代码

设置 reduce 的个数

set mapreduce.job.reduces=3;

复制代码

创建桶表

create table course (c_id string,c_name string) clustered by(c_id) into 3 buckets;

复制代码

桶表的数据加载：由于桶表的数据加载通过 hdfs dfs -put 文件或者通过 load data 均不可以，只能通过 insert overwrite 进行加载
所以把文件加载到桶表中，需要先创建普通表，并通过 insert overwrite 的方式将普通表的数据通过查询的方式加载到桶表当中去

通过 insert overwrite 给桶表中加载数据

insert overwrite table course select * from course_common cluster by(c_id);  -- 最后指定桶字段

复制代码

修改表和删除表

修改表名称

alter  table  old_table_name  rename  to  new_table_name;

复制代码

增加/修改列信息

查询表结构desc score5;
添加列alter table score5 add columns (mycol string, mysco string);
更新列alter table score5 change column mysco mysconew int;

复制代码

删除表操作

drop table score5;

复制代码

清空表操作

truncate table score6;
说明：只能清空管理表，也就是内部表；清空外部表，会产生错误

复制代码

注意：truncate 和 drop：
如果 hdfs 开启了回收站，drop 删除的表数据是可以从回收站恢复的，表结构恢复不了，需要自己重新创建；truncate 清空的表是不进回收站的，所以无法恢复 truncate 清空的表
所以 truncate 一定慎用，一旦清空将无力回天

向 hive 表中加载数据

直接向分区表中插入数据

insert into table score partition(month ='201807') values ('001','002','100');

复制代码

通过 load 方式加载数据

 load data local inpath '/export/servers/hivedatas/score.csv' overwrite into table score partition(month='201806');

复制代码

通过查询方式加载数据

insert overwrite table score2 partition(month = '201806') select s_id,c_id,s_score from score1;

复制代码

查询语句中创建表并加载数据

create table score2 as select * from score1;

复制代码

在创建表是通过 location 指定加载数据的路径

create external table score6 (s_id string,c_id string,s_score int) row format delimited fields terminated by ',' location '/myscore';

复制代码

export 导出与 import 导入 hive 表数据（内部表操作）

create table techer2 like techer; --依据已有表结构创建表
export table techer to  '/export/techer';
import table techer2 from '/export/techer';

复制代码

hive 表中数据导出

insert 导出

将查询的结果导出到本地insert overwrite local directory '/export/servers/exporthive' select * from score;
将查询的结果格式化导出到本地insert overwrite local directory '/export/servers/exporthive' row format delimited fields terminated by '\t' collection items terminated by '#' select * from student;
将查询的结果导出到HDFS上(没有local)insert overwrite directory '/export/servers/exporthive' row format delimited fields terminated by '\t' collection items terminated by '#' select * from score;

复制代码

Hadoop 命令导出到本地

dfs -get /export/servers/exporthive/000000_0 /export/servers/exporthive/local.txt;

复制代码

hive shell 命令导出

基本语法：（hive -f/-e 执行语句或者脚本 > file）
hive -e "select * from myhive.score;" > /export/servers/exporthive/score.txt
hive -f export.sh > /export/servers/exporthive/score.txt

复制代码

export 导出到 HDFS 上

export table score to '/export/exporthive/score';

复制代码

hive 的 DQL 查询语法

单表查询

SELECT [ALL | DISTINCT] select_expr, select_expr, ... FROM table_reference[WHERE where_condition] [GROUP BY col_list [HAVING condition]] [CLUSTER BY col_list   | [DISTRIBUTE BY col_list] [SORT BY| ORDER BY col_list] ] [LIMIT number]

复制代码

注意：
1、order by 会对输入做全局排序，因此只有一个 reducer，会导致当输入规模较大时，需要较长的计算时间。
2、sort by 不是全局排序，其在数据进入 reducer 前完成排序。因此，如果用 sort by 进行排序，并且设置 mapred.reduce.tasks>1，则 sort by 只保证每个 reducer 的输出有序，不保证全局有序。
3、distribute by(字段)根据指定的字段将数据分到不同的 reducer，且分发算法是 hash 散列。
4、Cluster by(字段) 除了具有 Distribute by 的功能外，还会对该字段进行排序。
因此，如果分桶和 sort 字段是同一个时，此时，cluster by = distribute by + sort by

WHERE 语句

select * from score where s_score < 60;

复制代码

注意：
小于某个值是不包含 null 的，如上查询结果是把 s_score 为 null 的行剔除的

GROUP BY 分组

select s_id ,avg(s_score) from score group by s_id;
分组后对数据进行筛选，使用having select s_id ,avg(s_score) avgscore from score group by s_id having avgscore > 85;

复制代码

注意：
如果使用 group by 分组，则 select 后面只能写分组的字段或者聚合函数
where 和 having 区别：
1 having 是在 group by 分完组之后再对数据进行筛选，所以 having 要筛选的字段只能是分组字段或者聚合函数
2 where 是从数据表中的字段直接进行的筛选的，所以不能跟在 gruopby 后面，也不能使用聚合函数

join 连接

INNER JOIN 内连接：只有进行连接的两个表中都存在与连接条件相匹配的数据才会被保留下来select * from techer t [inner] join course c on t.t_id = c.t_id; -- inner 可省略
LEFT OUTER JOIN 左外连接：左边所有数据会被返回，右边符合条件的被返回select * from techer t left join course c on t.t_id = c.t_id; -- outer可省略
RIGHT OUTER JOIN 右外连接：右边所有数据会被返回，左边符合条件的被返回、select * from techer t right join course c on t.t_id = c.t_id;
FULL OUTER JOIN 满外(全外)连接: 将会返回所有表中符合条件的所有记录。如果任一表的指定字段没有符合条件的值的话，那么就使用NULL值替代。SELECT * FROM techer t FULL JOIN course c ON t.t_id = c.t_id ;

复制代码

注：1. hive2 版本已经支持不等值连接，就是 join on 条件后面可以使用大于小于符号了;并且也支持 join on 条件后跟 or (早前版本 on 后只支持 = 和 and，不支持 > < 和 or)
2.如 hive 执行引擎使用 MapReduce，一个 join 就会启动一个 job，一条 sql 语句中如有多个 join，则会启动多个 job

注意：表之间用逗号(,)连接和 inner join 是一样的
select * from table_a,table_b where table_a.id=table_b.id;
它们的执行效率没有区别，只是书写方式不同，用逗号是 sql 89 标准，join 是 sql 92 标准。用逗号连接后面过滤条件用 where ，用 join 连接后面过滤条件是 on。

order by 排序

全局排序，只会有一个reduceASC（ascend）: 升序（默认） DESC（descend）: 降序SELECT * FROM student s LEFT JOIN score sco ON s.s_id = sco.s_id ORDER BY sco.s_score DESC;

复制代码

注意：order by 是全局排序，所以最后只有一个 reduce，也就是在一个节点执行，如果数据量太大，就会耗费较长时间

sort by 局部排序

每个MapReduce内部进行排序，对全局结果集来说不是排序。
设置reduce个数set mapreduce.job.reduces=3;
查看设置reduce个数set mapreduce.job.reduces;
查询成绩按照成绩降序排列select * from score sort by s_score; 将查询结果导入到文件中（按照成绩降序排列）insert overwrite local directory '/export/servers/hivedatas/sort' select * from score sort by s_score;

复制代码

distribute by 分区排序

distribute by：类似MR中partition，进行分区，结合sort by使用
设置reduce的个数，将我们对应的s_id划分到对应的reduce当中去set mapreduce.job.reduces=7;
通过distribute by  进行数据的分区select * from score distribute by s_id sort by s_score;

复制代码

注意：Hive 要求 distribute by 语句要写在 sort by 语句之前

cluster by

当distribute by和sort by字段相同时，可以使用cluster by方式.cluster by除了具有distribute by的功能外还兼具sort by的功能。但是排序只能是正序排序，不能指定排序规则为ASC或者DESC。
以下两种写法等价select * from score cluster by s_id;select * from score distribute by s_id sort by s_id;

复制代码

Hive 函数

聚合函数

hive支持 count(),max(),min(),sum(),avg() 等常用的聚合函数

复制代码

注意：
聚合操作时要注意 null 值
count(*) 包含 null 值，统计所有行数
count(id) 不包含 null 值
min 求最小值是不包含 null，除非所有值都是 null
avg 求平均值也是不包含 null

非空集合总体变量函数: var_pop

语法: var_pop(col)返回值: double说明: 统计结果集中col非空集合的总体变量（忽略null）

复制代码

非空集合样本变量函数: var_samp

语法: var_samp (col)返回值: double说明: 统计结果集中col非空集合的样本变量（忽略null）

复制代码

总体标准偏离函数: stddev_pop

语法: stddev_pop(col)返回值: double说明: 该函数计算总体标准偏离，并返回总体变量的平方根，其返回值与VAR_POP函数的平方根相同

复制代码

中位数函数: percentile

语法: percentile(BIGINT col, p)返回值: double说明: 求准确的第pth个百分位数，p必须介于0和1之间，但是col字段目前只支持整数，不支持浮点数类型

复制代码

关系运算

支持：等值(=)、不等值(!= 或 <>)、小于(<)、小于等于(<=)、大于(>)、大于等于(>=)
空值判断(is null)、非空判断(is not null)

复制代码

LIKE 比较: LIKE

语法: A LIKE B操作类型: strings描述: 如果字符串A或者字符串B为NULL，则返回NULL；如果字符串A符合表达式B 的正则语法，则为TRUE；否则为FALSE。B中字符”_”表示任意单个字符，而字符”%”表示任意数量的字符。

复制代码

JAVA 的 LIKE 操作: RLIKE

语法: A RLIKE B操作类型: strings描述: 如果字符串A或者字符串B为NULL，则返回NULL；如果字符串A符合JAVA正则表达式B的正则语法，则为TRUE；否则为FALSE。

复制代码

REGEXP 操作: REGEXP

语法: A REGEXP B操作类型: strings描述: 功能与RLIKE相同示例：select 1 from tableName where 'footbar' REGEXP '^f.*r$';结果：1

复制代码

数学运算

支持所有数值类型：加(+)、减(-)、乘(*)、除(/)、取余(%)、位与(&)、位或(|)、位异或(^)、位取反(~)

复制代码

逻辑运算

支持：逻辑与(and)、逻辑或(or)、逻辑非(not)

复制代码

数值运算

取整函数: round

语法: round(double a)返回值: BIGINT说明: 返回double类型的整数值部分 （遵循四舍五入）示例：select round(3.1415926) from tableName;结果：3

复制代码

指定精度取整函数: round

语法: round(double a, int d)返回值: DOUBLE说明: 返回指定精度d的double类型hive> select round(3.1415926,4) from tableName;3.1416

复制代码

向下取整函数: floor

语法: floor(double a)返回值: BIGINT说明: 返回等于或者小于该double变量的最大的整数hive> select floor(3.641) from tableName;3

复制代码

向上取整函数: ceil

语法: ceil(double a)返回值: BIGINT说明: 返回等于或者大于该double变量的最小的整数hive> select ceil(3.1415926) from tableName;4

复制代码

取随机数函数: rand

语法: rand(),rand(int seed)返回值: double说明: 返回一个0到1范围内的随机数。如果指定种子seed，则会等到一个稳定的随机数序列hive> select rand() from tableName; -- 每次执行此语句得到的结果都不同0.5577432776034763
hive> select rand(100) ;  -- 只要指定种子，每次执行此语句得到的结果一样的0.7220096548596434

复制代码

自然指数函数: exp

语法: exp(double a)返回值: double说明: 返回自然对数e的a次方hive> select exp(2) ;7.38905609893065

复制代码

以 10 为底对数函数: log10

语法: log10(double a)返回值: double说明: 返回以10为底的a的对数hive> select log10(100) ;2.0

复制代码

此外还有：以 2 为底对数函数: log2()、对数函数: log()

幂运算函数: pow

语法: pow(double a, double p)返回值: double说明: 返回a的p次幂hive> select pow(2,4) ;16.0

复制代码

开平方函数: sqrt

语法: sqrt(double a)返回值: double说明: 返回a的平方根hive> select sqrt(16) ;4.0

复制代码

二进制函数: bin

语法: bin(BIGINT a)返回值: string说明: 返回a的二进制代码表示hive> select bin(7) ;111

复制代码

十六进制函数: hex()、将十六进制转化为字符串函数: unhex()
进制转换函数: conv(bigint num, int from_base, int to_base) 说明: 将数值 num 从 from_base 进制转化到 to_base 进制

此外还有很多数学函数：绝对值函数: abs()、正取余函数: pmod()、正弦函数: sin()、反正弦函数: asin()、余弦函数: cos()、反余弦函数: acos()、positive 函数: positive()、negative 函数: negative()

条件函数

If 函数: if

语法: if(boolean testCondition, T valueTrue, T valueFalseOrNull)返回值: T说明: 当条件testCondition为TRUE时，返回valueTrue；否则返回valueFalseOrNullhive> select if(1=2,100,200) ;200hive> select if(1=1,100,200) ;100

复制代码

非空查找函数: coalesce

语法: coalesce(T v1, T v2, …)返回值: T说明: 返回参数中的第一个非空值；如果所有值都为NULL，那么返回NULLhive> select coalesce(null,'100','50') ;100

复制代码

条件判断函数：case when (两种写法，其一)

语法: case when a then b [when c then d]* [else e] end返回值: T说明：如果a为TRUE,则返回b；如果c为TRUE，则返回d；否则返回ehive> select case when 1=2 then 'tom' when 2=2 then 'mary' else 'tim' end from tableName;mary

复制代码

条件判断函数：case when (两种写法，其二)

语法: case a when b then c [when d then e]* [else f] end返回值: T说明：如果a等于b，那么返回c；如果a等于d，那么返回e；否则返回fhive> Select case 100 when 50 then 'tom' when 100 then 'mary' else 'tim' end from tableName;mary

复制代码

日期函数

注：以下 SQL 语句中的 from tableName 可去掉，不影响查询结果

获取当前 UNIX 时间戳函数: unix_timestamp

语法: unix_timestamp()返回值: bigint说明: 获得当前时区的UNIX时间戳hive> select unix_timestamp() from tableName;1616906976

复制代码

UNIX 时间戳转日期函数: from_unixtime

语法: from_unixtime(bigint unixtime[, string format])返回值: string说明: 转化UNIX时间戳（从1970-01-01 00:00:00 UTC到指定时间的秒数）到当前时区的时间格式hive> select from_unixtime(1616906976,'yyyyMMdd') from tableName;20210328

复制代码

日期转 UNIX 时间戳函数: unix_timestamp

语法: unix_timestamp(string date)返回值: bigint说明: 转换格式为"yyyy-MM-dd HH:mm:ss"的日期到UNIX时间戳。如果转化失败，则返回0。hive>  select unix_timestamp('2021-03-08 14:21:15') from tableName;1615184475

复制代码

指定格式日期转 UNIX 时间戳函数: unix_timestamp

语法: unix_timestamp(string date, string pattern)返回值: bigint说明: 转换pattern格式的日期到UNIX时间戳。如果转化失败，则返回0。hive>  select unix_timestamp('2021-03-08 14:21:15','yyyyMMdd HH:mm:ss') from tableName;1615184475

复制代码

日期时间转日期函数: to_date

语法: to_date(string timestamp)返回值: string说明: 返回日期时间字段中的日期部分。hive> select to_date('2021-03-28 14:03:01') from tableName;2021-03-28

复制代码

日期转年函数: year

语法: year(string date)返回值: int说明: 返回日期中的年。hive> select year('2021-03-28 10:03:01') from tableName;2021hive> select year('2021-03-28') from tableName;2021

复制代码

日期转月函数: month

语法: month (string date)返回值: int说明: 返回日期中的月份。hive> select month('2020-12-28 12:03:01') from tableName;12hive> select month('2021-03-08') from tableName;8

复制代码

日期转天函数: day

语法: day (string date)返回值: int说明: 返回日期中的天。hive> select day('2020-12-08 10:03:01') from tableName;8hive> select day('2020-12-24') from tableName;24

复制代码

日期转小时函数: hour

语法: hour (string date)返回值: int说明: 返回日期中的小时。hive> select hour('2020-12-08 10:03:01') from tableName;10

复制代码

日期转分钟函数: minute

语法: minute (string date)返回值: int说明: 返回日期中的分钟。hive> select minute('2020-12-08 10:03:01') from tableName;3

复制代码

日期转秒函数: second

语法: second (string date)返回值: int说明: 返回日期中的秒。hive> select second('2020-12-08 10:03:01') from tableName;1

复制代码

日期转周函数: weekofyear

语法: weekofyear (string date)返回值: int说明: 返回日期在当前的周数。hive> select weekofyear('2020-12-08 10:03:01') from tableName;49

复制代码

日期比较函数: datediff

语法: datediff(string enddate, string startdate)返回值: int说明: 返回结束日期减去开始日期的天数。hive> select datediff('2020-12-08','2012-05-09') from tableName;213

复制代码

日期增加函数: date_add

语法: date_add(string startdate, int days)返回值: string说明: 返回开始日期startdate增加days天后的日期。hive> select date_add('2020-12-08',10) from tableName;2020-12-18

复制代码

日期减少函数: date_sub

语法: date_sub (string startdate, int days)返回值: string说明: 返回开始日期startdate减少days天后的日期。hive> select date_sub('2020-12-08',10) from tableName;2020-11-28

复制代码

字符串函数

字符串长度函数：length

语法: length(string A)返回值: int说明：返回字符串A的长度hive> select length('abcedfg') from tableName;7

复制代码

字符串反转函数：reverse

语法: reverse(string A)返回值: string说明：返回字符串A的反转结果hive> select reverse('abcedfg') from tableName;gfdecba

复制代码

字符串连接函数：concat

语法: concat(string A, string B…)返回值: string说明：返回输入字符串连接后的结果，支持任意个输入字符串hive> select concat('abc','def’,'gh')from tableName;abcdefgh

复制代码

带分隔符字符串连接函数：concat_ws

语法: concat_ws(string SEP, string A, string B…)返回值: string说明：返回输入字符串连接后的结果，SEP表示各个字符串间的分隔符hive> select concat_ws(',','abc','def','gh')from tableName;abc,def,gh

复制代码

字符串截取函数：substr,substring

语法: substr(string A, int start),substring(string A, int start)返回值: string说明：返回字符串A从start位置到结尾的字符串hive> select substr('abcde',3) from tableName;cdehive> select substring('abcde',3) from tableName;cdehive> select substr('abcde',-1) from tableName; （和ORACLE相同）e

复制代码

字符串截取函数：substr,substring

语法: substr(string A, int start, int len),substring(string A, int start, int len)返回值: string说明：返回字符串A从start位置开始，长度为len的字符串hive> select substr('abcde',3,2) from tableName;cdhive> select substring('abcde',3,2) from tableName;cdhive>select substring('abcde',-2,2) from tableName;de

复制代码

字符串转大写函数：upper,ucase

语法: upper(string A) ucase(string A)返回值: string说明：返回字符串A的大写格式hive> select upper('abSEd') from tableName;ABSEDhive> select ucase('abSEd') from tableName;ABSED

复制代码

字符串转小写函数：lower,lcase

语法: lower(string A) lcase(string A)返回值: string说明：返回字符串A的小写格式hive> select lower('abSEd') from tableName;absedhive> select lcase('abSEd') from tableName;absed

复制代码

去空格函数：trim

语法: trim(string A)返回值: string说明：去除字符串两边的空格hive> select trim(' abc ') from tableName;abc

复制代码

左边去空格函数：ltrim

语法: ltrim(string A)返回值: string说明：去除字符串左边的空格hive> select ltrim(' abc ') from tableName;abc

复制代码

右边去空格函数：rtrim

语法: rtrim(string A)返回值: string说明：去除字符串右边的空格hive> select rtrim(' abc ') from tableName;abc

复制代码

正则表达式替换函数：regexp_replace

语法: regexp_replace(string A, string B, string C)返回值: string说明：将字符串A中的符合java正则表达式B的部分替换为C。注意，在有些情况下要使用转义字符,类似oracle中的regexp_replace函数。hive> select regexp_replace('foobar', 'oo|ar', '') from tableName;fb

复制代码

正则表达式解析函数：regexp_extract

语法: regexp_extract(string subject, string pattern, int index)返回值: string说明：将字符串subject按照pattern正则表达式的规则拆分，返回index指定的字符。hive> select regexp_extract('foothebar', 'foo(.*?)(bar)', 1) from tableName;thehive> select regexp_extract('foothebar', 'foo(.*?)(bar)', 2) from tableName;barhive> select regexp_extract('foothebar', 'foo(.*?)(bar)', 0) from tableName;foothebarstrong>注意，在有些情况下要使用转义字符，下面的等号要用双竖线转义，这是java正则表达式的规则。select data_field,regexp_extract(data_field,'.*?bgStart\\=([^&]+)',1) as aaa,regexp_extract(data_field,'.*?contentLoaded_headStart\\=([^&]+)',1) as bbb,regexp_extract(data_field,'.*?AppLoad2Req\\=([^&]+)',1) as ccc from pt_nginx_loginlog_st where pt = '2021-03-28' limit 2;

复制代码

URL 解析函数：parse_url

语法: parse_url(string urlString, string partToExtract [, string keyToExtract])返回值: string说明：返回URL中指定的部分。partToExtract的有效值为：HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, and USERINFO.hive> select parse_url('https://www.tableName.com/path1/p.php?k1=v1&k2=v2#Ref1', 'HOST') from tableName;www.tableName.com hive> select parse_url('https://www.tableName.com/path1/p.php?k1=v1&k2=v2#Ref1', 'QUERY', 'k1') from tableName;v1

复制代码

json 解析函数：get_json_object

语法: get_json_object(string json_string, string path)返回值: string说明：解析json的字符串json_string,返回path指定的内容。如果输入的json字符串无效，那么返回NULL。hive> select  get_json_object('{"store":{"fruit":\[{"weight":8,"type":"apple"},{"weight":9,"type":"pear"}], "bicycle":{"price":19.95,"color":"red"} },"email":"amy@only_for_json_udf_test.net","owner":"amy"}','$.owner') from tableName;

复制代码

空格字符串函数：space

语法: space(int n)返回值: string说明：返回长度为n的字符串hive> select space(10) from tableName;hive> select length(space(10)) from tableName;10

复制代码

重复字符串函数：repeat

语法: repeat(string str, int n)返回值: string说明：返回重复n次后的str字符串hive> select repeat('abc',5) from tableName;abcabcabcabcabc

复制代码

首字符 ascii 函数：ascii

语法: ascii(string str)返回值: int说明：返回字符串str第一个字符的ascii码hive> select ascii('abcde') from tableName;97

复制代码

左补足函数：lpad

语法: lpad(string str, int len, string pad)返回值: string说明：将str进行用pad进行左补足到len位hive> select lpad('abc',10,'td') from tableName;tdtdtdtabc注意：与GP，ORACLE不同，pad 不能默认

复制代码

右补足函数：rpad

语法: rpad(string str, int len, string pad)返回值: string说明：将str进行用pad进行右补足到len位hive> select rpad('abc',10,'td') from tableName;abctdtdtdt

复制代码

分割字符串函数: split

语法: split(string str, string pat)返回值: array说明: 按照pat字符串分割str，会返回分割后的字符串数组hive> select split('abtcdtef','t') from tableName;["ab","cd","ef"]

复制代码

集合查找函数: find_in_set

语法: find_in_set(string str, string strList)返回值: int说明: 返回str在strlist第一次出现的位置，strlist是用逗号分割的字符串。如果没有找该str字符，则返回0hive> select find_in_set('ab','ef,ab,de') from tableName;2hive> select find_in_set('at','ef,ab,de') from tableName;0

复制代码

复合类型构建操作

Map 类型构建: map

语法: map (key1, value1, key2, value2, …)说明：根据输入的key和value对构建map类型hive> Create table mapTable as select map('100','tom','200','mary') as t from tableName;hive> describe mapTable;t       map<string ,string>hive> select t from tableName;{"100":"tom","200":"mary"}

复制代码

Struct 类型构建: struct

语法: struct(val1, val2, val3, …)说明：根据输入的参数构建结构体struct类型hive> create table struct_table as select struct('tom','mary','tim') as t from tableName;hive> describe struct_table;t       struct<col1:string ,col2:string,col3:string>hive> select t from tableName;{"col1":"tom","col2":"mary","col3":"tim"}

复制代码

array 类型构建: array

语法: array(val1, val2, …)说明：根据输入的参数构建数组array类型hive> create table arr_table as select array("tom","mary","tim") as t from tableName;hive> describe tableName;t       array<string>hive> select t from tableName;["tom","mary","tim"]

复制代码

复杂类型访问操作

array 类型访问: A[n]

语法: A[n]操作类型: A为array类型，n为int类型说明：返回数组A中的第n个变量值。数组的起始下标为0。比如，A是个值为['foo', 'bar']的数组类型，那么A[0]将返回'foo',而A[1]将返回'bar'hive> create table arr_table2 as select array("tom","mary","tim") as t from tableName;hive> select t[0],t[1] from arr_table2;tom     mary    tim

复制代码

map 类型访问: M[key]

语法: M[key]操作类型: M为map类型，key为map中的key值说明：返回map类型M中，key值为指定值的value值。比如，M是值为{'f' -> 'foo', 'b' -> 'bar', 'all' -> 'foobar'}的map类型，那么M['all']将会返回'foobar'hive> Create table map_table2 as select map('100','tom','200','mary') as t from tableName;hive> select t['200'],t['100'] from map_table2;mary    tom

复制代码

struct 类型访问: S.x

语法: S.x操作类型: S为struct类型说明：返回结构体S中的x字段。比如，对于结构体struct foobar {int foo, int bar}，foobar.foo返回结构体中的foo字段hive> create table str_table2 as select struct('tom','mary','tim') as t from tableName;hive> describe tableName;t       struct<col1:string ,col2:string,col3:string>hive> select t.col1,t.col3 from str_table2;tom     tim

复制代码

复杂类型长度统计函数

Map 类型长度函数: size(Map<k .V>)

语法: size(Map<k .V>)返回值: int说明: 返回map类型的长度hive> select size(t) from map_table2;2

复制代码

array 类型长度函数: size(Array<T>)

语法: size(Array<T>)返回值: int说明: 返回array类型的长度hive> select size(t) from arr_table2;4

复制代码

类型转换函数 ***

类型转换函数: cast语法: cast(expr as <type>)返回值: Expected "=" to follow "type"说明: 返回转换后的数据类型hive> select cast('1' as bigint) from tableName;1

复制代码

hive 当中的 lateral view 与 explode 以及 reflect 和窗口函数

使用 explode 函数将 hive 表中的 Map 和 Array 字段数据进行拆分

lateral view 用于和 split、explode 等 UDTF 一起使用的，能将一行数据拆分成多行数据，在此基础上可以对拆分的数据进行聚合，lateral view 首先为原始表的每行调用 UDTF，UDTF 会把一行拆分成一行或者多行，lateral view 在把结果组合，产生一个支持别名表的虚拟表。

其中 explode 还可以用于将 hive 一列中复杂的 array 或者 map 结构拆分成多行

需求：现在有数据格式如下

zhangsan  child1,child2,child3,child4  k1:v1,k2:v2
lisi  child5,child6,child7,child8  k3:v3,k4:v4

复制代码

字段之间使用\t 分割，需求将所有的 child 进行拆开成为一列

+----------+--+| mychild  |+----------+--+| child1   || child2   || child3   || child4   || child5   || child6   || child7   || child8   |+----------+--+

复制代码

将 map 的 key 和 value 也进行拆开，成为如下结果

+-----------+-------------+--+| mymapkey  | mymapvalue  |+-----------+-------------+--+| k1        | v1          || k2        | v2          || k3        | v3          || k4        | v4          |+-----------+-------------+--+

复制代码

创建 hive 数据库

创建hive数据库hive (default)> create database hive_explode;hive (default)> use hive_explode;

复制代码

创建 hive 表，然后使用 explode 拆分 map 和 array

hive (hive_explode)> create  table t3(name string,children array<string>,address Map<string,string>) row format delimited fields terminated by '\t'  collection items terminated by ',' map keys terminated by ':' stored as textFile;

复制代码

加载数据

node03执行以下命令创建表数据文件  mkdir -p /export/servers/hivedatas/  cd /export/servers/hivedatas/  vim maparray内容如下:zhangsan  child1,child2,child3,child4  k1:v1,k2:v2lisi  child5,child6,child7,child8  k3:v3,k4:v4
hive表当中加载数据hive (hive_explode)> load data local inpath '/export/servers/hivedatas/maparray' into table t3;

复制代码

使用 explode 将 hive 当中数据拆开

将array当中的数据拆分开hive (hive_explode)> SELECT explode(children) AS myChild FROM t3;
将map当中的数据拆分开
hive (hive_explode)> SELECT explode(address) AS (myMapKey, myMapValue) FROM t3;

复制代码

使用 explode 拆分 json 字符串

需求: 需求：现在有一些数据格式如下：

a:shandong,b:beijing,c:hebei|1,2,3,4,5,6,7,8,9|[{"source":"7fresh","monthSales":4900,"userCount":1900,"score":"9.9"},{"source":"jd","monthSales":2090,"userCount":78981,"score":"9.8"},{"source":"jdmart","monthSales":6987,"userCount":1600,"score":"9.0"}]

复制代码

其中字段与字段之间的分隔符是 |

我们要解析得到所有的 monthSales 对应的值为以下这一列（行转列）

4900

2090

6987

创建 hive 表

hive (hive_explode)> create table explode_lateral_view                   > (`area` string,                   > `goods_id` string,                   > `sale_info` string)                   > ROW FORMAT DELIMITED                   > FIELDS TERMINATED BY '|'                   > STORED AS textfile;

复制代码

准备数据并加载数据

准备数据如下cd /export/servers/hivedatasvim explode_json
a:shandong,b:beijing,c:hebei|1,2,3,4,5,6,7,8,9|[{"source":"7fresh","monthSales":4900,"userCount":1900,"score":"9.9"},{"source":"jd","monthSales":2090,"userCount":78981,"score":"9.8"},{"source":"jdmart","monthSales":6987,"userCount":1600,"score":"9.0"}]
加载数据到hive表当中去hive (hive_explode)> load data local inpath '/export/servers/hivedatas/explode_json' overwrite into table explode_lateral_view;

复制代码

使用 explode 拆分 Array

hive (hive_explode)> select explode(split(goods_id,',')) as goods_id from explode_lateral_view;

复制代码

使用 explode 拆解 Map

hive (hive_explode)> select explode(split(area,',')) as area from explode_lateral_view;

复制代码

拆解 json 字段

hive (hive_explode)> select explode(split(regexp_replace(regexp_replace(sale_info,'\\[\\{',''),'}]',''),'},\\{')) as  sale_info from explode_lateral_view;
然后我们想用get_json_object来获取key为monthSales的数据：
hive (hive_explode)> select get_json_object(explode(split(regexp_replace(regexp_replace(sale_info,'\\[\\{',''),'}]',''),'},\\{')),'$.monthSales') as  sale_info from explode_lateral_view;

然后挂了FAILED: SemanticException [Error 10081]: UDTF's are not supported outside the SELECT clause, nor nested in expressionsUDTF explode不能写在别的函数内如果你这么写，想查两个字段，select explode(split(area,',')) as area,good_id from explode_lateral_view;会报错FAILED: SemanticException 1:40 Only a single expression in the SELECT clause is supported with UDTF's. Error encountered near token 'good_id'使用UDTF的时候，只支持一个字段，这时候就需要LATERAL VIEW出场了

复制代码

配合 LATERAL VIEW 使用

配合 lateral view 查询多个字段

hive (hive_explode)> select goods_id2,sale_info from explode_lateral_view LATERAL VIEW explode(split(goods_id,','))goods as goods_id2;
其中LATERAL VIEW explode(split(goods_id,','))goods相当于一个虚拟表，与原表explode_lateral_view笛卡尔积关联

复制代码

也可以多重使用

hive (hive_explode)> select goods_id2,sale_info,area2                    from explode_lateral_view                     LATERAL VIEW explode(split(goods_id,','))goods as goods_id2                     LATERAL VIEW explode(split(area,','))area as area2;也是三个表笛卡尔积的结果

复制代码

最终，我们可以通过下面的句子，把这个 json 格式的一行数据，完全转换成二维表的方式展现

hive (hive_explode)> select get_json_object(concat('{',sale_info_1,'}'),'$.source') as source,get_json_object(concat('{',sale_info_1,'}'),'$.monthSales') as monthSales,get_json_object(concat('{',sale_info_1,'}'),'$.userCount') as monthSales,get_json_object(concat('{',sale_info_1,'}'),'$.score') as monthSales from explode_lateral_view LATERAL VIEW explode(split(regexp_replace(regexp_replace(sale_info,'\\[\\{',''),'}]',''),'},\\{'))sale_info as sale_info_1;

复制代码

总结：

Lateral View 通常和 UDTF 一起出现，为了解决 UDTF 不允许在 select 字段的问题。Multiple Lateral View 可以实现类似笛卡尔乘积。Outer 关键字可以把不输出的 UDTF 的空结果，输出成 NULL，防止丢失数据。

行转列

列转行

所需函数:

EXPLODE(col)：将 hive 一列中复杂的 array 或者 map 结构拆分成多行。

LATERAL VIEW

用法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias

解释：用于和 split, explode 等 UDTF 一起使用，它能够将一列数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。

数据准备:

cd /export/servers/hivedatasvim movie.txt文件内容如下:  数据字段之间使用\t进行分割《疑犯追踪》  悬疑,动作,科幻,剧情《Lie to me》  悬疑,警匪,动作,心理,剧情《战狼2》  战争,动作,灾难

复制代码

需求: 将电影分类中的数组数据展开。结果如下：

《疑犯追踪》  悬疑《疑犯追踪》  动作《疑犯追踪》  科幻《疑犯追踪》  剧情《Lie to me》  悬疑《Lie to me》  警匪《Lie to me》  动作《Lie to me》  心理《Lie to me》  剧情《战狼2》  战争《战狼2》  动作《战狼2》  灾难

复制代码

实现步骤:

创建 hive 表

create table movie_info(    movie string,     category array<string>) row format delimited fields terminated by "\t"collection items terminated by ",";

复制代码

加载数据

load data local inpath "/export/servers/hivedatas/movie.txt" into table movie_info;

复制代码

按需求查询数据

select    movie,    category_namefrom     movie_info lateral view explode(category) table_tmp as category_name;

复制代码

reflect 函数

reflect 函数可以支持在 sql 中调用 java 中的自带函数，秒杀一切 udf 函数。

需求 1: 使用 java.lang.Math 当中的 Max 求两列中最大值

实现步骤:

创建 hive 表

create table test_udf(col1 int,col2 int) row format delimited fields terminated by ',';

复制代码

准备数据并加载数据

cd /export/servers/hivedatasvim test_udf 
文件内容如下:1,24,36,47,55,6

复制代码

加载数据

hive (hive_explode)> load data local inpath '/export/servers/hivedatas/test_udf' overwrite into table test_udf;

复制代码

使用 java.lang.Math 当中的 Max 求两列当中的最大值

hive (hive_explode)> select reflect("java.lang.Math","max",col1,col2) from test_udf;

复制代码

需求 2: 文件中不同的记录来执行不同的 java 的内置函数

实现步骤:

创建 hive 表

hive (hive_explode)> create table test_udf2(class_name string,method_name string,col1 int , col2 int) row format delimited fields terminated by ',';

复制代码

准备数据

cd /export/servers/hivedatasvim test_udf2
文件内容如下:java.lang.Math,min,1,2java.lang.Math,max,2,3

复制代码

加载数据

hive (hive_explode)> load data local inpath '/export/servers/hivedatas/test_udf2' overwrite into table test_udf2;

复制代码

执行查询

hive (hive_explode)> select reflect(class_name,method_name,col1,col2) from test_udf2;

复制代码

需求 3: 判断是否为数字

实现方式:

使用 apache commons 中的函数，commons 下的 jar 已经包含在 hadoop 的 classpath 中，所以可以直接使用。

select reflect("org.apache.commons.lang.math.NumberUtils","isNumber","123")

复制代码

窗口函数与分析函数

在 sql 中有一类函数叫做聚合函数,例如 sum()、avg()、max()等等,这类函数可以将多行数据按照规则聚集为一行,一般来讲聚集后的行数是要少于聚集前的行数的。但是有时我们想要既显示聚集前的数据,又要显示聚集后的数据,这时我们便引入了窗口函数。窗口函数又叫 OLAP 函数/分析函数，窗口函数兼具分组和排序功能。

窗口函数最重要的关键字是 partition by 和 order by。

具体语法如下：over (partition by xxx order by xxx)

sum、avg、min、max

准备数据

建表语句:create table test_t1(cookieid string,createtime string,   --day pv int) row format delimited fields terminated by ',';
加载数据：load data local inpath '/root/hivedata/test_t1.dat' into table test_t1;
cookie1,2020-04-10,1cookie1,2020-04-11,5cookie1,2020-04-12,7cookie1,2020-04-13,3cookie1,2020-04-14,2cookie1,2020-04-15,4cookie1,2020-04-16,4
开启智能本地模式SET hive.exec.mode.local.auto=true;

复制代码

SUM 函数和窗口函数的配合使用：结果和 ORDER BY 相关,默认为升序。

select cookieid,createtime,pv,sum(pv) over(partition by cookieid order by createtime) as pv1 from test_t1;
select cookieid,createtime,pv,sum(pv) over(partition by cookieid order by createtime rows between unbounded preceding and current row) as pv2from test_t1;
select cookieid,createtime,pv,sum(pv) over(partition by cookieid) as pv3from test_t1;
select cookieid,createtime,pv,sum(pv) over(partition by cookieid order by createtime rows between 3 preceding and current row) as pv4from test_t1;
select cookieid,createtime,pv,sum(pv) over(partition by cookieid order by createtime rows between 3 preceding and 1 following) as pv5from test_t1;
select cookieid,createtime,pv,sum(pv) over(partition by cookieid order by createtime rows between current row and unbounded following) as pv6from test_t1;

pv1: 分组内从起点到当前行的pv累积，如，11号的pv1=10号的pv+11号的pv, 12号=10号+11号+12号pv2: 同pv1pv3: 分组内(cookie1)所有的pv累加pv4: 分组内当前行+往前3行，如，11号=10号+11号， 12号=10号+11号+12号，                         13号=10号+11号+12号+13号， 14号=11号+12号+13号+14号pv5: 分组内当前行+往前3行+往后1行，如，14号=11号+12号+13号+14号+15号=5+7+3+2+4=21pv6: 分组内当前行+往后所有行，如，13号=13号+14号+15号+16号=3+2+4+4=13，               14号=14号+15号+16号=2+4+4=10

复制代码

如果不指定 rows between,默认为从起点到当前行;

如果不指定 order by，则将分组内所有值累加;

关键是理解 rows between 含义,也叫做 window 子句：

preceding：往前

following：往后

current row：当前行

unbounded：起点

unbounded preceding 表示从前面的起点

unbounded following：表示到后面的终点

AVG，MIN，MAX，和 SUM 用法一样。

row_number、rank、dense_rank、ntile

准备数据

cookie1,2020-04-10,1cookie1,2020-04-11,5cookie1,2020-04-12,7cookie1,2020-04-13,3cookie1,2020-04-14,2cookie1,2020-04-15,4cookie1,2020-04-16,4cookie2,2020-04-10,2cookie2,2020-04-11,3cookie2,2020-04-12,5cookie2,2020-04-13,6cookie2,2020-04-14,3cookie2,2020-04-15,9cookie2,2020-04-16,7 CREATE TABLE test_t2 (cookieid string,createtime string,   --day pv INT) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' stored as textfile;  加载数据：load data local inpath '/root/hivedata/test_t2.dat' into table test_t2;

复制代码

ROW_NUMBER()使用
ROW_NUMBER()从 1 开始，按照顺序，生成分组内记录的序列。

SELECT cookieid,createtime,pv,ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY pv desc) AS rn FROM test_t2;

复制代码

RANK 和 DENSE_RANK 使用
RANK() 生成数据项在分组中的排名，排名相等会在名次中留下空位。
DENSE_RANK()生成数据项在分组中的排名，排名相等会在名次中不会留下空位。

SELECT cookieid,createtime,pv,RANK() OVER(PARTITION BY cookieid ORDER BY pv desc) AS rn1,DENSE_RANK() OVER(PARTITION BY cookieid ORDER BY pv desc) AS rn2,ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY pv DESC) AS rn3 FROM test_t2 WHERE cookieid = 'cookie1';

复制代码

NTILE
有时会有这样的需求:如果数据排序后分为三部分，业务人员只关心其中的一部分，如何将这中间的三分之一数据拿出来呢?NTILE 函数即可以满足。
ntile 可以看成是：把有序的数据集合平均分配到指定的数量（num）个桶中, 将桶号分配给每一行。如果不能平均分配，则优先分配较小编号的桶，并且各个桶中能放的行数最多相差 1。
然后可以根据桶号，选取前或后 n 分之几的数据。数据会完整展示出来，只是给相应的数据打标签；具体要取几分之几的数据，需要再嵌套一层根据标签取出。

SELECT cookieid,createtime,pv,NTILE(2) OVER(PARTITION BY cookieid ORDER BY createtime) AS rn1,NTILE(3) OVER(PARTITION BY cookieid ORDER BY createtime) AS rn2,NTILE(4) OVER(ORDER BY createtime) AS rn3FROM test_t2 ORDER BY cookieid,createtime;

复制代码

其他一些窗口函数

lag,lead,first_value,last_value

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

SELECT cookieid,createtime,url,ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,LAG(createtime,1,'1970-01-01 00:00:00') OVER(PARTITION BY cookieid ORDER BY createtime) AS last_1_time,LAG(createtime,2) OVER(PARTITION BY cookieid ORDER BY createtime) AS last_2_time FROM test_t4;

last_1_time: 指定了往上第1行的值，default为'1970-01-01 00:00:00'                      cookie1第一行，往上1行为NULL,因此取默认值 1970-01-01 00:00:00                    cookie1第三行，往上1行值为第二行值，2015-04-10 10:00:02                    cookie1第六行，往上1行值为第五行值，2015-04-10 10:50:01last_2_time: 指定了往上第2行的值，为指定默认值               cookie1第一行，往上2行为NULL               cookie1第二行，往上2行为NULL               cookie1第四行，往上2行为第二行值，2015-04-10 10:00:02               cookie1第七行，往上2行为第五行值，2015-04-10 10:50:01

复制代码

LEAD

与 LAG 相反 LEAD(col,n,DEFAULT) 用于统计窗口内往下第 n 行值第一个参数为列名，第二个参数为往下第 n 行（可选，默认为 1），第三个参数为默认值（当往下第 n 行为 NULL 时候，取默认值，如不指定，则为 NULL）

SELECT cookieid,createtime,url,ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,LEAD(createtime,1,'1970-01-01 00:00:00') OVER(PARTITION BY cookieid ORDER BY createtime) AS next_1_time,LEAD(createtime,2) OVER(PARTITION BY cookieid ORDER BY createtime) AS next_2_time FROM test_t4;

复制代码

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

 SELECT cookieid, createtime, url, ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn, FIRST_VALUE(url) OVER(PARTITION BY cookieid ORDER BY createtime) AS first1  FROM test_t4;

复制代码

LAST_VALUE

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

SELECT cookieid,createtime,url,ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,LAST_VALUE(url) OVER(PARTITION BY cookieid ORDER BY createtime) AS last1 FROM test_t4;

复制代码

如果想要取分组内排序后最后一个值，则需要变通一下：

SELECT cookieid,createtime,url,ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,LAST_VALUE(url) OVER(PARTITION BY cookieid ORDER BY createtime) AS last1,FIRST_VALUE(url) OVER(PARTITION BY cookieid ORDER BY createtime DESC) AS last2 FROM test_t4 ORDER BY cookieid,createtime;

复制代码

特别注意 order by

如果不指定 ORDER BY，则进行排序混乱，会出现错误的结果

SELECT cookieid,createtime,url,FIRST_VALUE(url) OVER(PARTITION BY cookieid) AS first2  FROM test_t4;

复制代码

cume_dist,percent_rank

这两个序列分析函数不是很常用，注意：序列函数不支持 WINDOW 子句

数据准备

d1,user1,1000d1,user2,2000d1,user3,3000d2,user4,4000d2,user5,5000 CREATE EXTERNAL TABLE test_t3 (dept STRING,userid string,sal INT) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' stored as textfile;
加载数据：load data local inpath '/root/hivedata/test_t3.dat' into table test_t3;

复制代码

CUME_DIST 和 order byd 的排序顺序有关系
CUME_DIST 小于等于当前值的行数/分组内总行数 order 默认顺序正序升序比如，统计小于等于当前薪水的人数，所占总人数的比例

 SELECT  dept, userid, sal, CUME_DIST() OVER(ORDER BY sal) AS rn1, CUME_DIST() OVER(PARTITION BY dept ORDER BY sal) AS rn2  FROM test_t3;  rn1: 没有partition,所有数据均为1组，总行数为5，      第一行：小于等于1000的行数为1，因此，1/5=0.2      第三行：小于等于3000的行数为3，因此，3/5=0.6 rn2: 按照部门分组，dpet=d1的行数为3,      第二行：小于等于2000的行数为2，因此，2/3=0.6666666666666666

复制代码

PERCENT_RANK
PERCENT_RANK 分组内当前行的 RANK 值-1/分组内总行数-1
经调研该函数显示现实意义不明朗有待于继续考证

  SELECT   dept,  userid,  sal,  PERCENT_RANK() OVER(ORDER BY sal) AS rn1,   --分组内  RANK() OVER(ORDER BY sal) AS rn11,          --分组内RANK值  SUM(1) OVER(PARTITION BY NULL) AS rn12,     --分组内总行数  PERCENT_RANK() OVER(PARTITION BY dept ORDER BY sal) AS rn2   FROM test_t3;    rn1: rn1 = (rn11-1) / (rn12-1)        第一行,(1-1)/(5-1)=0/4=0       第二行,(2-1)/(5-1)=1/4=0.25       第四行,(4-1)/(5-1)=3/4=0.75  rn2: 按照dept分组，       dept=d1的总行数为3       第一行，(1-1)/(3-1)=0       第三行，(3-1)/(3-1)=1

复制代码

grouping sets,grouping__id,cube,rollup

这几个分析函数通常用于 OLAP 中，不能累加，而且需要根据不同维度上钻和下钻的指标统计，比如，分小时、天、月的 UV 数。

数据准备

2020-03,2020-03-10,cookie12020-03,2020-03-10,cookie52020-03,2020-03-12,cookie72020-04,2020-04-12,cookie32020-04,2020-04-13,cookie22020-04,2020-04-13,cookie42020-04,2020-04-16,cookie42020-03,2020-03-10,cookie22020-03,2020-03-10,cookie32020-04,2020-04-12,cookie52020-04,2020-04-13,cookie62020-04,2020-04-15,cookie32020-04,2020-04-15,cookie22020-04,2020-04-16,cookie1 CREATE TABLE test_t5 (month STRING,day STRING, cookieid STRING ) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' stored as textfile;
加载数据：load data local inpath '/root/hivedata/test_t5.dat' into table test_t5;

复制代码

GROUPING SETS

grouping sets 是一种将多个 group by 逻辑写在一个 sql 语句中的便利写法。

等价于将不同维度的 GROUP BY 结果集进行 UNION ALL。

GROUPING__ID，表示结果属于哪一个分组集合。

SELECT month,day,COUNT(DISTINCT cookieid) AS uv,GROUPING__ID FROM test_t5 GROUP BY month,day GROUPING SETS (month,day) ORDER BY GROUPING__ID;
grouping_id表示这一组结果属于哪个分组集合，根据grouping sets中的分组条件month，day，1是代表month，2是代表day
等价于 SELECT month,NULL,COUNT(DISTINCT cookieid) AS uv,1 AS GROUPING__ID FROM test_t5 GROUP BY month UNION ALL SELECT NULL as month,day,COUNT(DISTINCT cookieid) AS uv,2 AS GROUPING__ID FROM test_t5 GROUP BY day;

复制代码

再如：

SELECT month,day,COUNT(DISTINCT cookieid) AS uv,GROUPING__ID FROM test_t5 GROUP BY month,day GROUPING SETS (month,day,(month,day)) ORDER BY GROUPING__ID;
等价于SELECT month,NULL,COUNT(DISTINCT cookieid) AS uv,1 AS GROUPING__ID FROM test_t5 GROUP BY month UNION ALL SELECT NULL,day,COUNT(DISTINCT cookieid) AS uv,2 AS GROUPING__ID FROM test_t5 GROUP BY dayUNION ALL SELECT month,day,COUNT(DISTINCT cookieid) AS uv,3 AS GROUPING__ID FROM test_t5 GROUP BY month,day;

复制代码

CUBE

根据 GROUP BY 的维度的所有组合进行聚合。

SELECT month,day,COUNT(DISTINCT cookieid) AS uv,GROUPING__ID FROM test_t5 GROUP BY month,day WITH CUBE ORDER BY GROUPING__ID;
等价于SELECT NULL,NULL,COUNT(DISTINCT cookieid) AS uv,0 AS GROUPING__ID FROM test_t5UNION ALL SELECT month,NULL,COUNT(DISTINCT cookieid) AS uv,1 AS GROUPING__ID FROM test_t5 GROUP BY month UNION ALL SELECT NULL,day,COUNT(DISTINCT cookieid) AS uv,2 AS GROUPING__ID FROM test_t5 GROUP BY dayUNION ALL SELECT month,day,COUNT(DISTINCT cookieid) AS uv,3 AS GROUPING__ID FROM test_t5 GROUP BY month,day;

复制代码

ROLLUP

是 CUBE 的子集，以最左侧的维度为主，从该维度进行层级聚合。

比如，以month维度进行层级聚合：SELECT month,day,COUNT(DISTINCT cookieid) AS uv,GROUPING__ID  FROM test_t5 GROUP BY month,dayWITH ROLLUP ORDER BY GROUPING__ID;
--把month和day调换顺序，则以day维度进行层级聚合： SELECT day,month,COUNT(DISTINCT cookieid) AS uv,GROUPING__ID  FROM test_t5 GROUP BY day,month WITH ROLLUP ORDER BY GROUPING__ID;（这里，根据天和月进行聚合，和根据天聚合结果一样，因为有父子关系，如果是其他维度组合的话，就会不一样）

复制代码

微信搜索公众号【五分钟学大数据】，每周首发原创大数据技术文，深入框架原理，大厂面试真题等

发布于: 2021 年 04 月 01 日阅读数: 241

原文链接:【http://xie.infoq.cn/article/547ab082b526b471ade184296】。未经作者许可，禁止转载。

五分钟学大数据

关注

专注于大数据技术研究 2020.11.10 加入

运营公众号：五分钟学大数据。大数据领域原创技术号，深入大数据技术

发布

暂无评论

创作场景

一文学完所有的 Hive Sql（两万字最全详解）

Hive Sql 大全

hive 的 DDL 语法

对数据库的操作

对数据表的操作

对管理表(内部表)的操作:

对外部表操作

对分区表的操作

对分桶表操作

修改表和删除表

向 hive 表中加载数据

hive 表中数据导出

hive 的 DQL 查询语法

单表查询

Hive 函数

聚合函数

关系运算

数学运算

逻辑运算

数值运算

条件函数

日期函数

字符串函数

复合类型构建操作

复杂类型访问操作

复杂类型长度统计函数

hive 当中的 lateral view 与 explode 以及 reflect 和窗口函数

使用 explode 函数将 hive 表中的 Map 和 Array 字段数据进行拆分

使用 explode 拆分 json 字符串

配合 LATERAL VIEW 使用

行转列

列转行

reflect 函数

窗口函数与分析函数

sum、avg、min、max

row_number、rank、dense_rank、ntile

其他一些窗口函数

lag,lead,first_value,last_value

cume_dist,percent_rank

grouping sets,grouping__id,cube,rollup

五分钟学大数据

评论