目录
- 系统内置函数
- 查看系统自带的函数
- 显示自带的函数的用法
- 详细显示自带的函数的用法
- 常用内置函数
- 空字段赋值
- 拼接字符串
- 行转列
- 列转行
- 窗口函数
- Rank
- 补充
- Grouping set
系统内置函数
查看系统自带的函数
show functions;显示自带的函数的用法
desc function upper;详细显示自带的函数的用法
desc function extended upper;常用内置函数
空字段赋值
NVL( value，default_value)。
NVL：给值为 NULL 的数据赋值，它的功能是如果 value 为 NULL，则 NVL 函数返回 default_value 的值，否则返回 value 的值。
如果两个参数都为 NULL ，则返回 NULL。
#用-1代替
select comm,nvl(comm, -1) from emp;
#用另一个字段代替
select comm, nvl(comm,mgr) from emp;拼接字符串
CONCAT(string A/col, string B/col…)：返回输入字符串连接后的结果，支持任意个输入字符串;
CONCAT_WS(separator, str1, str2,…)：第一个参数是剩余参数间的分隔符。分隔符将被加到被连接的字符串之间;
COLLECT_SET(col)：函数只接受基本数据类型，它的主要作用是将某字段的值进行去重汇总，产生 Array 类型字段。
行转列
- 假设有以下数据，要求行转列
姓名 星座 血型
小空 白羊座 A
小海 射手座 A
小宋 白羊座 B
小胡 白羊座 A
小凤 射手座 A
小艾 白羊座 B
2.HQL代码
SELECT
t1.c_b,
CONCAT_WS("|",collect_set(t1.name))
FROM (
SELECT
NAME,
CONCAT_WS(',',constellation,blood_type) c_b
FROM person_info
)t1
GROUP BY t1.c_b列转行
EXPLODE(col)：将 hive 一列中复杂的 Array 或者 Map 结构拆分成多行。
LATERAL VIEW：用于和 split, explode 等 UDTF 一起使用，它能够将一列数据拆成多行数据，在此基础上可以保留拆分后的数据和表内其它列的关联。
- 假设有以下数据，要求列转行
movie category
《疑犯追踪》 悬疑,动作,科幻,剧情
《Lie to me》 悬疑,警匪,动作,心理,剧情
《战狼 2》 战争,动作,灾难
转为
《疑犯追踪》 悬疑
《疑犯追踪》 动作
《疑犯追踪》 科幻
《疑犯追踪》 剧情
《Lie to me》 悬疑
《Lie to me》 警匪
《Lie to me》 动作
《Lie to me》 心理
《Lie to me》 剧情
《战狼 2》 战争
《战狼 2》 动作
《战狼 2》 灾难
- HQL代码
SELECT
movie,
category_name
FROM
movie_info
lateral VIEW
explode(split(category,",")) movie_info_tmp AS category_name;窗口函数
CURRENT ROW：当前行
n PRECEDING：往前 n 行数据
n FOLLOWING：往后 n 行数据
UNBOUNDED：起点，
UNBOUNDED PRECEDING 表示从前面的起点，
UNBOUNDED FOLLOWING 表示到后面的终点
LAG(col,n,default_val)：往前第 n 行数据
LEAD(col,n, default_val)：往后第 n 行数据
假设有以下数据
name，orderdate，cost
jack,2017-01-01,10
tony,2017-01-02,15
jack,2017-02-03,23
tony,2017-01-04,29
jack,2017-01-05,46
jack,2017-04-06,42
tony,2017-01-07,50
jack,2017-01-08,55
mart,2017-04-08,62
mart,2017-04-09,68
neil,2017-05-10,12
mart,2017-04-11,75
neil,2017-06-12,80
mart,2017-04-13,94
- 查询在 2017 年 4 月份购买过的顾客及总人数
select name,count(*) over ()
from business
where substring(orderdate,1,7) = '2017-04'
group by name;- 查询顾客的购买明细及月购买总额
select name,orderdate,cost,sum(cost) over(partition by month(orderdate))
from business;- 将每个顾客的 cost 按照日期进行累加，即新建一列为包括该日的cost的总和
select name,orderdate,cost,
sum(cost) over() as sample1,--所有行相加
sum(cost) over(partition by name) as sample2,--按 name 分组，组内数据相加
sum(cost) over(partition by name order by orderdate) as sample3,--按 name分组，组内数据累加 因为默认是从开始到当前行
sum(cost) over(partition by name order by orderdate rows between
UNBOUNDED PRECEDING and current row ) as sample4 ,--和 sample3 一样,由起点到当前行的聚合
sum(cost) over(partition by name order by orderdate rows between 1
PRECEDING and current row) as sample5, --当前行和前面一行做聚合
sum(cost) over(partition by name order by orderdate rows between 1
PRECEDING AND 1 FOLLOWING ) as sample6,--当前行和前边一行及后面一行
sum(cost) over(partition by name order by orderdate rows between current
row and UNBOUNDED FOLLOWING ) as sample7 --当前行及后面所有行
from business;rows 必须跟在 order by 子句之后，对排序的结果进行限制，使用固定的行数来限制分区中的数据行数量。
- 查看顾客上次的购买时间
select
name,orderdate,cost,
lag(orderdate,1,'1900-01-01') over(partition by name order by orderdate ) as time1,
lag(orderdate,2) over (partition by name order by orderdate) as time2
from business;- 查询前 20%时间的订单信息
NTILE(n)：对数据进行分组，分出n组，对于每一行，NTILE 返回此行所属的组的编号。注意：n 必须为 int 类型。
select * from (
select name,orderdate,cost, ntile(5) over(order by orderdate) sorted
from business
) t
where sorted = 1;Rank
RANK() 排序相同时会重复，总数不会变
DENSE_RANK() 排序相同时会重复，总数会减少
ROW_NUMBER() 会根据顺序计算
补充
Grouping set
对字段给出不同聚合方式并且将结果展示在一张表里的方法。
