一、时间处理类
1、把固定日期转换成时间戳
select unix_timestamp('2020-05-29','yyyy-MM-dd') --返回结果 1590681600
select unix_timestamp('20200529','yyyyMMdd') --返回结果 1590681600
select unix_timestamp('2020-05-29T13:12:23Z', "yyyy-MM-dd'T'HH:mm:ss'Z'") --返回结果 1590729143
select unix_timestamp('2020-05-29 13:12:23', 'yyyy-MM-dd HH:mm:ss') --返回结果 15907291432、把 29/May/2020:11:30:03 +0800 转成正常格式（yyyy-MM-dd hh:mm:ss）
select from_unixtime(to_unix_timestamp('29/May/2020:11:30:03 +0800', 'dd/MMM/yyy:HH:mm:ss Z'))
--返回结果 2020-05-29 11:30:033、时间戳转换成固定日期
select from_timestamp(now(),'yyyyMM') --返回结果 202309
select from_unixtime(1590681600,'yyyy-MM-dd') --返回结果 2020-05-29
select from_unixtime(1590681600,'yyyyMMdd') --返回结果 20200529
select from_unixtime(1590729143,'yyyy-MM-dd HH:mm:ss') --返回结果 2020-05-29 13:12:23
select from_unixtime(1590729143) --返回结果 --2020-05-29 13:12:23 不加默认 yyyy-MM-dd HH:mm:ss 格式
select from_unixtime(unix_timestamp('2020-05-29','yyyy-MM-dd'),'Y年') --返回结果 2020年
select from_unixtime(unix_timestamp('2020-05-29','yyyy-MM-dd'),'yyyy-MM') --返回结果 2020-05
select from_unixtime(unix_timestamp('20200529','yyyyMMdd'),'yyyy-MM-dd') --返回结果 2020-05-29
select from_unixtime(unix_timestamp('2020-05-29','yyyy-MM-dd'),'EEEE') --返回结果 Friday
select from_unixtime(unix_timestamp('2020-05-29','yyyy-MM-dd'),'w') --返回第几周(22)
select from_unixtime(unix_timestamp('2020-05-29','yyyy-MM-dd'),'u') -- 返回本周的第几天
select from_unixtime(unix_timestamp('2020-05-29','yyyy-MM-dd'),'D') -- 本年的第几天(150)
select from_unixtime(unix_timestamp('2020-05-29','yyyy-MM-dd'),'d') -- 本月的第几天(29)4、对日期进行格式化转换
select date_format('2020-05-29','yyyyMMdd') --返回结果 202005295、计算得出星期几，date_code是日期参数
select pmod(datediff(date_code, '2012-01-01'), 7) = 0 --当等于0时，这天是'星期日'
select pmod(datediff(date_code, '2012-01-01'), 7) = 1 --当等于1时，这天是'星期一'
select pmod(datediff(date_code, '2012-01-01'), 7) = 2 --当等于2时，这天是'星期二'
select pmod(datediff(date_code, '2012-01-01'), 7) = 3 --当等于3时，这天是'星期三'
select pmod(datediff(date_code, '2012-01-01'), 7) = 4 --当等于4时，这天是'星期四'
select pmod(datediff(date_code, '2012-01-01'), 7) = 5 --当等于5时，这天是'星期五'
select pmod(datediff(date_code, '2012-01-01'), 7) = 6 --当等于6时，这天是'星期六'6、返回日期时间字段中的日期部分
select to_date('2020-05-29 13:12:23) -- 返回结果 2020-05-297、日期相减函数
datediff('2016-12-30','2016-12-29') --1
datediff(now(),from_unixtime(unix_timestamp('20230501','yyyyMMdd')) ) --3
date_sub('2016-12-29',10) -- 2016-12-19二、null值处理
nvl(a,0)函数： 如果为空，值为0；如果不为空，值为a
三、数组
select array("a", "b", "c" ) --生成数组
select array(ename, job) from emp --生成数组
split(category, ",") --切分字符串生成数组
select collect_set(ename) from emp group by deptno; --行转列
select collect_list(ename) from emp group by deptno; --行转列
select map("a",12,"b,3") --创建一个map
map_keys()： --遍历key
map_values()： --遍历valus四、字符串拼接
concat("a"+"-"+"b") ： a-b
concat_ws("-", collect_list(ename)) ： --第一个参数为拼接符，第二个参数为字符串 a-b五、类型转换
cast ("100" as int) --字符串转为int六、判断
case when判断
case
when 判断条件 then 值
when 判断条件 then 值
else 值
as aaaif 判断
sum(if(gender="男", 1, 0)) as sex;七、最大值
greatest() --求最大值八、行转列，列转行
行转列： 将多行数据集成到1行中
collect_list() --可重复
collect_set() --不可重复列传行： 将1行中数据拆成多行
explode() --将数据炸开
lateral view -- 特殊的join例如：
select
name,
cate
from
tb_move2
lateral view explode(category) tmp as cate九、窗口函数
.
1、 函数名(列) over (选项) ：开窗口函数，控制聚合范围
例如：
sum(cost) over(partition by name)
+----------------+---------------------+----------------+---------------+
| tb_order.name | tb_order.orderdate | tb_order.cost | sum_window_0 |
+----------------+---------------------+----------------+---------------+
| jack | 2017-01-05 | 46.0 | 176.0 |
| jack | 2017-01-08 | 55.0 | 176.0 |
| jack | 2017-01-01 | 10.0 | 176.0 |
| jack | 2017-04-06 | 42.0 | 176.0 |
| jack | 2017-02-03 | 23.0 | 176.0 |
| mart | 2017-04-13 | 94.0 | 299.0 |
| mart | 2017-04-11 | 75.0 | 299.0 |
| mart | 2017-04-09 | 68.0 | 299.0 |
| mart | 2017-04-08 | 62.0 | 299.0 |
| neil | 2017-05-10 | 12.0 | 92.0 |
| neil | 2017-06-12 | 80.0 | 92.0 |
| tony | 2017-01-04 | 29.0 | 94.0 |
| tony | 2017-01-02 | 15.0 | 94.0 |
| tony | 2017-01-07 | 50.0 | 94.0 |
+----------------+---------------------+----------------+-------------2、rows between 1 preceding and current row ：最近两行
例如：
partition by name order by orderdate rows between 1 preceding and current row
| tb_order.name | tb_order.orderdate | tb_order.cost | sum_window_0 |
+----------------+---------------------+----------------+---------------+
| jack | 2017-01-01 | 10.0 | 10.0 |
| jack | 2017-01-05 | 46.0 | 56.0 |
| jack | 2017-01-08 | 55.0 | 101.0 |
| jack | 2017-02-03 | 23.0 | 78.0 |
| jack | 2017-04-06 | 42.0 | 65.0 |
| mart | 2017-04-08 | 62.0 | 62.0 |
| mart | 2017-04-09 | 68.0 | 130.0 |
| mart | 2017-04-11 | 75.0 | 143.0 |
| mart | 2017-04-13 | 94.0 | 169.0 |
| neil | 2017-05-10 | 12.0 | 12.0 |
| neil | 2017-06-12 | 80.0 | 92.0 |
| tony | 2017-01-02 | 15.0 | 15.0 |
| tony | 2017-01-04 | 29.0 | 44.0 |
| tony | 2017-01-07 | 50.0 | 79.03、 rows between unbounded preceding and current row ：当前行和累计行
例如：
sum(cost) over(partition by name order by orderdate rows between unbounded preceding and current row)
等同于：sum(cost) over(partition by name order by orderdate)
+----------------+---------------------+----------------+---------------+
| tb_order.name | tb_order.orderdate | tb_order.cost | sum_window_0 |
+----------------+---------------------+----------------+---------------+
| jack | 2017-01-01 | 10.0 | 10.0 |
| jack | 2017-01-05 | 46.0 | 56.0 |
| jack | 2017-01-08 | 55.0 | 111.0 |
| jack | 2017-02-03 | 23.0 | 134.0 |
| jack | 2017-04-06 | 42.0 | 176.0 |
| mart | 2017-04-08 | 62.0 | 62.0 |
| mart | 2017-04-09 | 68.0 | 130.0 |
| mart | 2017-04-11 | 75.0 | 205.0 |
| mart | 2017-04-13 | 94.0 | 299.0 |
| neil | 2017-05-10 | 12.0 | 12.0 |
| neil | 2017-06-12 | 80.0 | 92.0 |
| tony | 2017-01-02 | 15.0 | 15.0 |
| tony | 2017-01-04 | 29.0 | 44.0 |
| tony | 2017-01-07 | 50.0 | 94.04、rows between 1 preceding and 1 following ：当前行，上一行，下一行
例如：
sum(cost) over(partition by name order by orderdate rows between 1 preceding and 1 following )
+----------------+---------------------+----------------+---------------+
| tb_order.name | tb_order.orderdate | tb_order.cost | sum_window_0 |
+----------------+---------------------+----------------+---------------+
| jack | 2017-01-01 | 10.0 | 56.0 |
| jack | 2017-01-05 | 46.0 | 111.0 |
| jack | 2017-01-08 | 55.0 | 124.0 |
| jack | 2017-02-03 | 23.0 | 120.0 |
| jack | 2017-04-06 | 42.0 | 65.0 |
| mart | 2017-04-08 | 62.0 | 130.0 |
| mart | 2017-04-09 | 68.0 | 205.0 |
| mart | 2017-04-11 | 75.0 | 237.0 |
| mart | 2017-04-13 | 94.0 | 169.0 |
| neil | 2017-05-10 | 12.0 | 92.0 |
| neil | 2017-06-12 | 80.0 | 92.0 |
| tony | 2017-01-02 | 15.0 | 44.0 |
| tony | 2017-01-04 | 29.0 | 94.0 |
| tony | 2017-01-07 | 50.0 | 79.05、lag(col,n,default) ：用于统计窗口内往上第n行值
例如：
lag(orderdate ,1,'第一次购买') over(partition by name order by orderdate)6、ntile（ ) : 将数据分成几份
例如:
select
*
from
(select
* ,
ntile(5) over(order by orderdate) as f
from
tb_order) t
where f = 1十、编号函数
1、rank() 排序相同时会重复，总数不会变
例如：
rank() over(partition by subject order by score desc
+--------------+-----------------+---------------+----------------+
| tb_sub.name | tb_sub.subject | tb_sub.score | rank_window_0 |
+--------------+-----------------+---------------+----------------+
| 孙悟空 | 数学 | 95.0 | 1 |
| 宋宋 | 数学 | 86.0 | 2 |
| 婷婷 | 数学 | 85.0 | 3 |
| 娜娜 | 数学 | 56.0 | 4 |
| 宋宋 | 英语 | 84.0 | 1 |
| 娜娜 | 英语 | 84.0 | 1 |
| 婷婷 | 英语 | 78.0 | 3 |
| 孙悟空 | 英语 | 68.0 | 4 |
| 娜娜 | 语文 | 94.0 | 1 |
| 孙悟空 | 语文 | 87.0 | 2 |
| 婷婷 | 语文 | 65.0 | 3 |
| 宋宋 | 语文 | 64.0 | 42、dense_rank() 排序相同时会重复，总数会减少
例如：
dense_rank() over(partition by subject order by score desc)
+--------------+-----------------+---------------+----------------------+
| tb_sub.name | tb_sub.subject | tb_sub.score | dense_rank_window_0 |
+--------------+-----------------+---------------+----------------------+
| 孙悟空 | 数学 | 95.0 | 1 |
| 宋宋 | 数学 | 86.0 | 2 |
| 婷婷 | 数学 | 85.0 | 3 |
| 娜娜 | 数学 | 56.0 | 4 |
| 宋宋 | 英语 | 84.0 | 1 |
| 娜娜 | 英语 | 84.0 | 1 |
| 婷婷 | 英语 | 78.0 | 2 |
| 孙悟空 | 英语 | 68.0 | 3 |
| 娜娜 | 语文 | 94.0 | 1 |
| 孙悟空 | 语文 | 87.0 | 2 |
| 婷婷 | 语文 | 65.0 | 3 |
| 宋宋 | 语文 | 64.0 | 43、row_number() 会根据顺序计算
例如：
row_number() over(partition by subject order by score desc)
+--------------+-----------------+---------------+----------------------+
| tb_sub.name | tb_sub.subject | tb_sub.score | row_number_window_0 |
+--------------+-----------------+---------------+----------------------+
| 孙悟空 | 数学 | 95.0 | 1 |
| 宋宋 | 数学 | 86.0 | 2 |
| 婷婷 | 数学 | 85.0 | 3 |
| 娜娜 | 数学 | 56.0 | 4 |
| 宋宋 | 英语 | 84.0 | 1 |
| 娜娜 | 英语 | 84.0 | 2 |
| 婷婷 | 英语 | 78.0 | 3 |
| 孙悟空 | 英语 | 68.0 | 4 |
| 娜娜 | 语文 | 94.0 | 1 |
| 孙悟空 | 语文 | 87.0 | 2 |
| 婷婷 | 语文 | 65.0 | 3 |
| 宋宋 | 语文 | 64.0 | 4十一、字符串正则表达式解析函数
语法：regexp_extract(string subject, string pattern, int index)
说明： 将字符串subject按照pattern正则表达式的规则拆分，返回index指定的字符。
第一参数： 要处理的字段
第二参数： 需要匹配的正则表达式
第三个参数:
0 是显示与之匹配的整个字符串
1 是显示第一个括号里面的
2 是显示第二个括号里面的字段...十二、自定义函数
1、UDF(一进一出)
2、UDAF（多进一出，类似于count、max、min）
3、UDTF（一进多出，类似于explore、split）
备注： 也可以使用reflect实现自定义函数功能
1 编写任意的类 任意的方法
2 打包
3 上传到linux
4 add jar demo2.jar
5 调用 select reflect("类名" , "方法名" , "参数") ;
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
