空字段赋值
函数说明：
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; //也可以指定列替换
时间类
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');
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;
要求：求出不同部门男女各多少人
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;
行转列
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;
练习：把星座和血型一样的人归类到一起
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;
第一步转换：
白羊座,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;
列传行
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;
select explode(category) from movie_info;
select
movie,
category_name
from
movie_info lateral view explode(category) table_tmp as category_name;
窗口函数[究极重要]
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 类型。
数据：
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;
查询顾客的购买明细及购买总额
select *,sum(cost) over()
from business;
要将 cost 按照日期进行累加
先排序
select orderdate,cost
from business
order by orderdate;
再累加
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; //效果同上
查询顾客上次的购买时间
//1970-01-01为初始默认值
select
name,
orderdate,
cost,
lag(orderdate,1,'1970-01-01') over(distribute by name sort by orderdate)
from
business;
下次的购买时间
select
name,
orderdate,
cost,
lead(orderdate,1,'9999-01-01') over(distribute by name sort by orderdate)
from
business;
查询前 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;
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;
