Hive常用查询函数

作者: 码戈 | 来源:发表于2020-06-10 20:43 被阅读0次

Hive常用查询函数
Hive其他常用查询函数
spark sql快速入门
Hive sql常见操作
PySpark操作Hive的常用语句函数封装包
hive进阶学习
SQL-1
Hive常用函数
Hive常用函数
hive 常用函数

空字段赋值

函数说明：
NVL：给值为 NULL 的数据赋值，
它的格式是
NVL( string1, replace_with)。
它的功能是如果string1 为 NULL，则 NVL 函数返回 replace_with 的值，否则返回 string1 的值，如果两个参数都为 NULL ，则返回NULL。

select comm from emp;
select nvl(comm,100) from emp;

select nvl(comm,empno) from emp; //也可以指定列替换

nvl.png

Snipaste_2020-06-09_15-44-15.png

时间类

date_format:格式化时间

 select date_format('2019-06-29','yyyy-MM-dd'); 
 select date_format('2019/06/29','yyyy-MM-dd');  //改格式

select regexp_replace('2019/07/03','/','-');  //使用replace函数将数据格式化为yyyy-MM-dd

date_add:时间跟天数相加

select date_add('2019-06-29',5); 
select date_add('2019-06-29',-5);

date_sub:时间跟天数相减，类似与add，使用一个就ok
select date_sub('2019-06-29',5);

datediff:两个时间相减
select datediff('2019-06-29','2019-06-24');

Snipaste_2020-06-09_21-03-12.png

case when

create table emp_sex( 
name string,dept_id string, sex string)
row format delimited fields terminated by "\t";

load data local inpath '/home/data/human.txt' into table emp_sex;

Snipaste_2020-06-10_08-38-44.png
要求：求出不同部门男女各多少人

select 
  dept_id,
  sum(case sex when '男' then 1 else 0 end) male_count, 
  sum(case sex when '女' then 1 else 0 end) female_count
from emp_sex 
group by dept_id;
//两个分支情况下可替换
select 
  dept_id,
  sum(if(sex='男',1,0)) male_count, 
  sum(if(sex='女',1,0)) female_count
from emp_sex 
group by dept_id;

Snipaste_2020-06-10_08-44-25.png

行转列

CONCAT(string A/col, string B/col…)：返回输入字符串连接后的结果，支持任意个输入字符串;

select concat(deptno,'-',dname) from dept; //拼接列
select concat(deptno,'-',dname,'-',loc) from dept;

CONCAT_WS(separator, str1, str2,...)：它是一个特殊形式的 CONCAT()。第一个参数剩余参数间的分隔符。
分隔符可以是与剩余参数一样的字符串。如果分隔符是 NULL，返回值也将为 NULL。
这个函数会跳过分隔符参数后的任何 NULL 和空字符串。分隔符将被加到被连接的字符串之间;

select concat_ws('-',str1,str2) from XX; //str为string类型的列名

COLLECT_SET(col)：函数只接受基本数据类型，它的主要作用是将某字段的值进行去重汇总，
产生 array 类型字段。

select collect_set(deptno) from dept;
select concat_ws('-',collect_set(dname)) from dept;

Snipaste_2020-06-10_09-02-13.png

练习：把星座和血型一样的人归类到一起

create table person_info( 
name string, 
constellation string, 
blood_type string)
row format delimited fields terminated by "\t";

load data local inpath "/home/data/cons.txt" into table person_info;

image.png

第一步转换：
白羊座,A 孙悟空
射手座,A 大海
白羊座,B 宋宋
白羊座,A 猪八戒
射手座,A 凤姐

select 
  concat(constellation,',',blood_type) constellation_blood,
  name
from person_info;  //t1

合并

select 
  constellation_blood,
  concat_ws('|',collect_set(name))
from 
  ()t1
group by constellation_blood;

Snipaste_2020-06-10_09-16-47.png

列传行

EXPLODE(col)：将hive 一列中复杂的 array 或者 map 结构拆分成多行。
LATERAL VIEW //侧写
用法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias
解释：用于和 split, explode 等UDTF 一起使用，它能够将一列数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。
练习：将电影分类中的数组数据展开

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

load data local inpath "/home/data/movie.txt" into table movie_info;

image.png

select explode(category) from movie_info;

炸裂函数explode

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

Snipaste_2020-06-10_09-35-12.png

窗口函数[究极重要]

OVER()：指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化；

/在over(里面使用)/
CURRENT ROW：当前行；
n PRECEDING：往前 n 行数据；
n FOLLOWING：往后 n 行数据；
UNBOUNDED：起点，
UNBOUNDED PRECEDING 表示从前面的起点，UNBOUNDEDFOLLOWING 表示到后面的终点；

/* 在over外面使用*/
LAG(col,n)：往前第n 行数据；
LEAD(col,n)：往后第 n 行数据；
NTILE(n)：把有序分区中的行分发到指定数据的组中，各个组有编号，编号从 1 开始，
对于每一行，NTILE 返回此行所属的组的编号。注意：n 必须为 int 类型。

数据：

image.png

create table business( 
name  string, 
orderdate  string, 
cost string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
lines terminated by '\n';
load data local inpath "/home/data/bus.txt" into table business;

一、查询在 2017 年 4 月份购买过的顾客及总人数
group分组一组一个值，over给每一条数据独立开窗

//count(*)开窗，窗口为数据集，数据集提供给聚合函数使用
//over后面括号空，代表对整个数据集进行开窗
//substring截取 orderdate 1-7个字符  
select name,count(*) over () 
from business
where substring(orderdate,1,7) = '2017-04' 
group by name;

Snipaste_2020-06-10_10-22-07.png

查询顾客的购买明细及购买总额

select *,sum(cost) over()
from business;

Snipaste_2020-06-10_10-23-38.png

要将 cost 按照日期进行累加
先排序

select orderdate,cost
from business
order by orderdate;

Snipaste_2020-06-10_10-25-42.png

再累加

select 
  name,
  orderdate,
  cost,
  sum(cost) over(distribute by name sort by orderdate)
from business;  //区内排序orderby

select 
  name,
  orderdate,
  cost,
  sum(cost) over(partition by name order by orderdate)
from business; //效果同上

Snipaste_2020-06-10_10-40-54.png

查询顾客上次的购买时间

//1970-01-01为初始默认值
select 
  name,
  orderdate,
  cost,
  lag(orderdate,1,'1970-01-01') over(distribute by name sort by orderdate)
from
  business;

Snipaste_2020-06-10_11-09-57.png

下次的购买时间

select 
  name,
  orderdate,
  cost,
  lead(orderdate,1,'9999-01-01') over(distribute by name sort by orderdate)
from
  business;

Snipaste_2020-06-10_11-11-49.png

查询前 20%时间的订单信息

select 
  name,
  orderdate,
  cost,
  ntile(5) over(sort by orderdate) ntile_5
from
  business; t1

select
  name,
  orderdate,
  cost
from
  ()t1
where
  ntile_5=1;

Snipaste_2020-06-10_14-11-18.png

Rank

RANK() 排序相同时会重复，总数不会变，可以并列
DENSE_RANK() 排序相同时会重复，总数会减少
ROW_NUMBER() 会根据顺序计算
练习：计算每门学科成绩排名

create table score( 
name string, 
subject string, 
score string)
row format delimited fields terminated by "\t";
load data local inpath '/home/data/sco.txt' into table score;

计算每门学科成绩排名

select
  name,
  subject,
  score,
  rank() over(partition by subject order by score desc) rank1,
  dense_rank() over(partition by subject order by score desc) rank2,
  row_number() over(partition by subject order by score desc) rank3
from
  score;

Snipaste_2020-06-10_14-30-48.png