数仓利器-Hive高频函数合集
2020-09-12 本文已影响0人
Bloo_m
前言
Hive是数仓建设使用频率最高的一项技术，基于各种业务需求，使用功能函数会为我们的开发提高了很多效率。本篇是基于笔者在日常开发中使用频率较高的函数做一次总结(同时也会给出一些业务场景帮助读者理解)，同时也是面试中经常会被问到的函数。如有遗漏，欢迎各位读者一起交流沟通并补充进来～；
另关注公众号"初学大数据"
后台回复“大数据”可获取更多关于大数据资料
数据准备
数据集
user1,https://blog.csdn.net/qq_28680977/article/details/108161655?k1=v1&k2=v2#Ref1,10,2020-09-12 02:20:02,2020-09-12
user1,https://blog.csdn.net/qq_28680977/article/details/108298276?k1=v1&k2=v2#Ref1,2,2020-09-11 11:20:12,2020-09-11
user1,https://blog.csdn.net/qq_28680977/article/details/108295053?k1=v1&k2=v2#Ref1,4,2020-09-10 08:19:22,2020-09-10
user1,https://blog.csdn.net/qq_28680977/article/details/108460523?k1=v1&k2=v2#Ref1,5,2020-08-12 19:20:22,2020-08-12
user2,https://blog.csdn.net/qq_28680977/article/details/108161655?k1=v1&k2=v2#Ref1,29,2020-04-04 12:23:22,2020-04-04
user2,https://blog.csdn.net/qq_28680977/article/details/108161655?k1=v1&k2=v2#Ref1,30,2020-05-15 12:34:23,2020-05-15
user2,https://blog.csdn.net/qq_28680977/article/details/108161655?k1=v1&k2=v2#Ref1,30,2020-05-15 13:34:23,2020-05-15
user2,https://blog.csdn.net/qq_28680977/article/details/108161655?k1=v1&k2=v2#Ref1,19,2020-05-16 19:03:32,2020-05-16
user2,https://blog.csdn.net/qq_28680977/article/details/108161655?k1=v1&k2=v2#Ref1,10,2020-05-17 06:20:22,2020-05-17
user3,https://blog.csdn.net/qq_28680977/article/details/108161655?k1=v1&k2=v2#Ref1,43,2020-04-12 08:02:22,2020-04-12
user3,https://blog.csdn.net/qq_28680977/article/details/108161655?k1=v1&k2=v2#Ref1,5,2020-08-02 08:10:22,2020-08-02
user3,https://blog.csdn.net/qq_28680977/article/details/108161655?k1=v1&k2=v2#Ref1,6,2020-08-02 10:10:22,2020-08-02
user3,https://blog.csdn.net/qq_28680977/article/details/108161655?k1=v1&k2=v2#Ref1,50,2020-08-12 12:23:22,2020-08-12
user4,https://blog.csdn.net/qq_28680977/article/details/108161655?k1=v1&k2=v2#Ref1,10,2020-04-12 11:20:22,2020-04-12
user4,https://blog.csdn.net/qq_28680977/article/details/108161655?k1=v1&k2=v2#Ref1,30,2020-03-12 10:20:22,2020-03-12
user4,https://blog.csdn.net/qq_28680977/article/details/108161655?k1=v1&k2=v2#Ref1,20,2020-02-12 20:26:43,2020-02-12
user2,https://blog.csdn.net/qq_28680977/article/details/108161655?k1=v1&k2=v2#Ref1,10,2020-04-12 19:12:36,2020-04-12
user2,https://blog.csdn.net/qq_28680977/article/details/108161655?k1=v1&k2=v2#Ref1,40,2020-05-12 18:24:31,2020-05-12
建表语句
create table wedw_tmp.tmp_url_info(
user_id string comment "用户id",
visit_url string comment "访问url",
visit_cnt int comment "浏览次数/pv",
visit_time timestamp comment "浏览时间",
visit_date string comment "浏览日期"
)
row format delimited
fields terminated by ','
stored as textfile;
窗口函数
row_number：使用频率 ★★★★★
row_number函数通常用于分组统计组内的排名，然后进行后续的逻辑处理。
注意：当遇到相同排名的时候，不会生成同样的序号，且中间不会空位
-- 统计每个用户每天最近一次访问记录
select
user_id,
visit_time,
visit_cnt
from
(
select
*,
row_number() over(partition by user_id,visit_date order by visit_time desc) as rank
from wedw_tmp.tmp_url_info
)t
where rank=1
order by user_id,visit_time
+----------+------------------------+------------+--+
| user_id | visit_time | visit_cnt |
+----------+------------------------+------------+--+
| user1 | 2020-08-12 19:20:22.0 | 5 |
| user1 | 2020-09-10 08:19:22.0 | 4 |
| user1 | 2020-09-11 11:20:12.0 | 2 |
| user1 | 2020-09-12 02:20:02.0 | 10 |
| user2 | 2020-04-04 12:23:22.0 | 29 |
| user2 | 2020-04-12 19:12:36.0 | 10 |
| user2 | 2020-05-12 18:24:31.0 | 40 |
| user2 | 2020-05-15 13:34:23.0 | 30 | --该用户同一天访问了多次,但只取了最新一次访问记录
| user2 | 2020-05-16 19:03:32.0 | 19 |
| user2 | 2020-05-17 06:20:22.0 | 10 |
| user3 | 2020-04-12 08:02:22.0 | 43 |
| user3 | 2020-08-02 10:10:22.0 | 6 |
| user3 | 2020-08-12 12:23:22.0 | 50 |
| user4 | 2020-02-12 20:26:43.0 | 20 |
| user4 | 2020-03-12 10:20:22.0 | 30 |
| user4 | 2020-04-12 11:20:22.0 | 10 |
+----------+------------------------+------------+--+
rank ：使用频率 ★★★★
和row_number功能一样，都是分组内统计排名，但是当出现同样排名的时候，中间会出现空位。这里给一个例子就可以很容易理解了
select
user_id,
visit_time,
visit_date,
rank() over(partition by user_id order by visit_date desc) as rank --每个用户按照访问时间倒排，通常用于统计用户最近一天的访问记录
from wedw_tmp.tmp_url_info
order by user_id,rank
+----------+------------------------+-------------+-------+--+
| user_id | visit_time | visit_date | rank |
+----------+------------------------+-------------+-------+--+
| user1 | 2020-09-12 02:20:02.0 | 2020-09-12 | 1 |
| user1 | 2020-09-12 02:20:02.0 | 2020-09-12 | 1 | --同一天访问了两次，9月11号访问排名第三
| user1 | 2020-09-11 11:20:12.0 | 2020-09-11 | 3 |
| user1 | 2020-09-10 08:19:22.0 | 2020-09-10 | 4 |
| user1 | 2020-08-12 19:20:22.0 | 2020-08-12 | 5 |
| user2 | 2020-05-17 06:20:22.0 | 2020-05-17 | 1 |
| user2 | 2020-05-16 19:03:32.0 | 2020-05-16 | 2 |
| user2 | 2020-05-15 12:34:23.0 | 2020-05-15 | 3 |
| user2 | 2020-05-15 13:34:23.0 | 2020-05-15 | 3 |
| user2 | 2020-05-12 18:24:31.0 | 2020-05-12 | 5 |
| user2 | 2020-04-12 19:12:36.0 | 2020-04-12 | 6 |
| user2 | 2020-04-04 12:23:22.0 | 2020-04-04 | 7 |
| user3 | 2020-08-12 12:23:22.0 | 2020-08-12 | 1 |
| user3 | 2020-08-02 08:10:22.0 | 2020-08-02 | 2 |
| user3 | 2020-08-02 10:10:22.0 | 2020-08-02 | 2 |
| user3 | 2020-04-12 08:02:22.0 | 2020-04-12 | 4 |
| user4 | 2020-04-12 11:20:22.0 | 2020-04-12 | 1 |
| user4 | 2020-03-12 10:20:22.0 | 2020-03-12 | 2 |
| user4 | 2020-02-12 20:26:43.0 | 2020-02-12 | 3 |
+----------+------------------------+-------------+-------+--+
dense_rank：使用频率 ★★★★
和row_number以及rank功能一样，都是分组排名，但是该排名如果出现同次序的话，中间不会留下空位
--还是以rank的sql为例子
select
user_id,
visit_time,
visit_date,
dense_rank() over(partition by user_id order by visit_date desc) as rank
from wedw_tmp.tmp_url_info
order by user_id,rank
+----------+------------------------+-------------+-------+--+
| user_id | visit_time | visit_date | rank |
+----------+------------------------+-------------+-------+--+
| user1 | 2020-09-12 02:20:02.0 | 2020-09-12 | 1 |
| user1 | 2020-09-12 02:20:02.0 | 2020-09-12 | 1 |
| user1 | 2020-09-11 11:20:12.0 | 2020-09-11 | 2 |--中间不会留下空缺
| user1 | 2020-09-10 08:19:22.0 | 2020-09-10 | 3 |
| user1 | 2020-08-12 19:20:22.0 | 2020-08-12 | 4 |
| user2 | 2020-05-17 06:20:22.0 | 2020-05-17 | 1 |
| user2 | 2020-05-16 19:03:32.0 | 2020-05-16 | 2 |
| user2 | 2020-05-15 12:34:23.0 | 2020-05-15 | 3 |
| user2 | 2020-05-15 13:34:23.0 | 2020-05-15 | 3 |
| user2 | 2020-05-12 18:24:31.0 | 2020-05-12 | 4 |
| user2 | 2020-04-12 19:12:36.0 | 2020-04-12 | 5 |
| user2 | 2020-04-04 12:23:22.0 | 2020-04-04 | 6 |
| user3 | 2020-08-12 12:23:22.0 | 2020-08-12 | 1 |
| user3 | 2020-08-02 08:10:22.0 | 2020-08-02 | 2 |
| user3 | 2020-08-02 10:10:22.0 | 2020-08-02 | 2 |
| user3 | 2020-04-12 08:02:22.0 | 2020-04-12 | 3 |
| user4 | 2020-04-12 11:20:22.0 | 2020-04-12 | 1 |
| user4 | 2020-03-12 10:20:22.0 | 2020-03-12 | 2 |
| user4 | 2020-02-12 20:26:43.0 | 2020-02-12 | 3 |
+----------+------------------------+-------------+-------+--+
rank/dense_rank/row_number对比
相同点：都是分组排序
不同点：
- Row_number:即便出现相同的排序，排名也不会一致，只会进行累加；即排序次序连续，但不会出现同一排名
- rank:当出现相同的排序时，中间会出现一个空缺，即分组内会出现同一个排名，但是排名次序是不连续的
- Dense_rank:当出现相同排序时，中间不会出现空缺，即分组内可能会出现同样的次序，且排序名次是连续的
first_value：使用频率 ★★★
按照分组排序取截止到当前行的第一个值;通常用于取最新记录或者最早的记录(根据排序字段进行变通即可)
--仍然使用row_number的例子;方便读者理解
select
user_id,
visit_time,
visit_cnt,
first_value(visit_time) over(partition by user_id order by visit_date desc) as first_value_time,
row_number() over(partition by user_id order by visit_date desc) as rank
from wedw_tmp.tmp_url_info
order by user_id,rank
1.png
last_value：使用频率 ★
按照分组排序取当前行的最后一个值；这个函数好像没啥卵用
--仍然使用row_number的例子;方便读者理解
select
user_id,
visit_time,
visit_cnt,
last_value(visit_time) over(partition by user_id order by visit_date desc) as first_value_time,
row_number() over(partition by user_id order by visit_date desc) as rank
from wedw_tmp.tmp_url_info
order by user_id,rank
2.png
lead：使用频率 ★★
LEAD(col,n,DEFAULT)用于取窗口内往下第n行值；通常用于行值填充；或者和指定行进行差值比较
第一个参数为列名
第二个参数为往下第n行(可选),
第三个参数为默认值(当往下第n行为NULL时候,取默认值,如不指定,则为NULL)
select
user_id,
visit_time,
visit_cnt,
row_number() over(partition by user_id order by visit_date desc) as rank,
lead(visit_time,1,'1700-01-01') over(partition by user_id order by visit_date desc) as lead_time
from wedw_tmp.tmp_url_info
order by user_id
+----------+------------------------+------------+-------+------------------------+--+
| user_id | visit_time | visit_cnt | rank | lead_time |
+----------+------------------------+------------+-------+------------------------+--+
| user1 | 2020-09-12 02:20:02.0 | 10 | 1 | 2020-09-12 02:20:02.0 | --取下一行的值作为当前值
| user1 | 2020-09-12 02:20:02.0 | 10 | 2 | 2020-09-11 11:20:12.0 |
| user1 | 2020-09-11 11:20:12.0 | 2 | 3 | 2020-09-10 08:19:22.0 |
| user1 | 2020-09-10 08:19:22.0 | 4 | 4 | 2020-08-12 19:20:22.0 |
| user1 | 2020-08-12 19:20:22.0 | 5 | 5 | 1700-01-01 00:00:00.0 | --这里是最后一条记录，则取默认值
| user2 | 2020-05-17 06:20:22.0 | 10 | 1 | 2020-05-16 19:03:32.0 |
| user2 | 2020-05-16 19:03:32.0 | 19 | 2 | 2020-05-15 12:34:23.0 |
| user2 | 2020-05-15 12:34:23.0 | 30 | 3 | 2020-05-15 13:34:23.0 |
| user2 | 2020-05-15 13:34:23.0 | 30 | 4 | 2020-05-12 18:24:31.0 |
| user2 | 2020-05-12 18:24:31.0 | 40 | 5 | 2020-04-12 19:12:36.0 |
| user2 | 2020-04-12 19:12:36.0 | 10 | 6 | 2020-04-04 12:23:22.0 |
| user2 | 2020-04-04 12:23:22.0 | 29 | 7 | 1700-01-01 00:00:00.0 |
| user3 | 2020-08-12 12:23:22.0 | 50 | 1 | 2020-08-02 08:10:22.0 |
| user3 | 2020-08-02 08:10:22.0 | 5 | 2 | 2020-08-02 10:10:22.0 |
| user3 | 2020-08-02 10:10:22.0 | 6 | 3 | 2020-04-12 08:02:22.0 |
| user3 | 2020-04-12 08:02:22.0 | 43 | 4 | 1700-01-01 00:00:00.0 |
| user4 | 2020-04-12 11:20:22.0 | 10 | 1 | 2020-03-12 10:20:22.0 |
| user4 | 2020-03-12 10:20:22.0 | 30 | 2 | 2020-02-12 20:26:43.0 |
| user4 | 2020-02-12 20:26:43.0 | 20 | 3 | 1700-01-01 00:00:00.0 |
+----------+------------------------+------------+-------+------------------------+--+
lag：使用频率 ★★
和lead功能一样，但是是取上n行的值作为当前行值
select
user_id,
visit_time,
visit_cnt,
row_number() over(partition by user_id order by visit_date desc) as rank,
lag(visit_time,1,'1700-01-01') over(partition by user_id order by visit_date desc) as lead_time
from wedw_tmp.tmp_url_info
order by user_id
3.png
集合相关
collect_set：使用频率 ★★★★★
将分组内的数据放入到一个集合中，具有去重的功能；
--统计每个用户具体哪些天访问过
select
user_id,
collect_set(visit_date) over(partition by user_id) as visit_date_set
from wedw_tmp.tmp_url_info
4.png
collect_list：使用频率 ★★★★★
和collect_set一样，但是没有去重功能
select
user_id,
collect_set(visit_date) over(partition by user_id) as visit_date_set
from wedw_tmp.tmp_url_info
--如下图可见，user2在2020-05-15号多次访问，这里也算进去了
6.png
sort_array：使用频率 ★★★
数组内排序；通常结合collect_set或者collect_list使用；
如collect_list为例子，可以发现日期并不是按照顺序组合的，这里有需求需要按照时间升序的方式来组合
--按照时间升序来组合
select
user_id,
sort_array(collect_list(visit_date) over(partition by user_id)) as visit_date_set
from wedw_tmp.tmp_url_info
--结果如下图所示；
1.png
如果突然业务方改需求了，想要按照时间降序来组合，那基于上面的sql该如何变通呢？哈哈哈哈，其实没那么复杂，这里根据没必要按照sort_array来实现，在collect_list中的分组函数内直接按照visit_date降序即可，这里只是为了演示sort_array如何使用
--按照时间降序排序
select
user_id,
collect_list(visit_date) over(partition by user_id order by visit_date desc) as visit_date_set
from wedw_tmp.tmp_url_info
2.png
这里还有一个小技巧，对于数值类型统计多列或者数组内的最大值，可以使用sort_array来实现
--具体思路就是先把数值变成负数，然后升序排序即可
select -sort_array(array(-a,-b,-c))[0] as max_value
from (
select 1 as a, 3 as b, 2 as c
) as data
+------------+--+
| max_value |
+------------+--+
| 3 |
+------------+--+
URL相关
parse_url：使用频率 ★★★★
用于解析url相关的参数,直接上sql
select
visit_url,
parse_url(visit_url, 'HOST') as url_host, --解析host
parse_url(visit_url, 'PATH') as url_path, --解析path
parse_url(visit_url, 'QUERY') as url_query,--解析请求参数
parse_url(visit_url, 'REF') as url_ref, --解析ref
parse_url(visit_url, 'PROTOCOL') as url_protocol, --解析协议
parse_url(visit_url, 'AUTHORITY') as url_authority,--解析author
parse_url(visit_url, 'FILE') as url_file, --解析filepath
parse_url(visit_url, 'USERINFO') as url_user_info --解析userinfo
from wedw_tmp.tmp_url_info
3.png
reflect：使用频率 ★★
该函数是利用java的反射来实现一些功能，目前笔者只用到了关于url编解码
--url编码
select
visit_url,
reflect("java.net.URLEncoder", "encode", visit_url, "UTF-8") as visit_url_encode
from wedw_tmp.tmp_url_info
4.png
--url解码
select
visit_url,
reflect("java.net.URLDecoder", "decode", visit_url_encode, "UTF-8") as visit_url_decode
from
(
select
visit_url,
reflect("java.net.URLEncoder", "encode", visit_url, "UTF-8") as visit_url_encode
from wedw_tmp.tmp_url_info
)t
5.png
JSON相关
get_json_object：使用频率 ★★★★★
通常用于获取json字符串中的key,如果不存在则返回null
select
get_json_object(json_data,'$.user_id') as user_id,
get_json_object(json_data,'$.age') as age --不存在age，则返回null
from
(
select
concat('{"user_id":"',user_id,'"}') as json_data
from wedw_tmp.tmp_url_info
)t
6.png
列转行相关
explode：使用频率 ★★★★★
列转行，通常是将一个数组内的元素打开，拆成多行
--简单例子
select explode(array(1,2,3,4,5))
+------+--+
| col |
+------+--+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+------+-
--结合lateral view 使用
select
get_json_object(user,'$.user_id')
from
(
select
distinct collect_set(concat('{"user_id":"',user_id,'"}')) over(partition by year(visit_date)) as user_list
from wedw_tmp.tmp_url_info
)t
lateral view explode(user_list) user_list as user
7.png
Cube相关
GROUPING SETS：使用频率 ★
类似于kylin中的cube，将多种维度进行组合统计；在一个GROUP BY查询中,根据不同维度组合进行聚合，等价于将不同维度的GROUP BY结果集进行UNION ALL
--按照用户+访问日期统计统计次数
select
user_id,
visit_date,
sum(visit_cnt) as visit_cnt
from wedw_tmp.tmp_url_info
group by user_id,visit_date
grouping sets(user_id,visit_date)
--下图的结果类似于以下sql
select
user_id,
NULL as visit_date,
sum(visit_cnt) as visit_cnt
from wedw_tmp.tmp_url_info
union all
select
NULL as user_id,
visit_date,
sum(visit_cnt) as visit_cnt
from wedw_tmp.tmp_url_info
union all
select
user_id,
visit_date,
sum(visit_cnt) as visit_cnt
from wedw_tmp.tmp_url_info
8.png
字符相关
concat：使用频率 ★★★★★
字符拼接，concat(string|binary A, string|binary B...);该函数比较简单
select concat('a','b','c')
--最后结果就是abc
concat_ws：使用频率 ★★★★★
按照指定分隔符将字符或者数组进行拼接；concat_ws(string SEP, array<string>)/concat_ws(string SEP, string A, string B...)
--还是concat使用的例子，这里可以写成
select concat_ws('','a','b','c')
--将数组列表元素按照指定分隔符拼接，类似于python中的join方法
select concat_ws('',array('a','b','c'))
instr：使用频率 ★★★★
查找字符串str中子字符串substr出现的位置，如果查找失败将返回0，如果任一参数为Null将返回null，注意位置为从1开始的；通常笔者用这个函数作为模糊查询来查询
--查询vist_time包含10的记录
select
user_id,
visit_time,
visit_date,
visit_cnt
from wedw_tmp.tmp_url_info
where instr(visit_time,'10')>0
9.png
length：使用频率 ★★★★★
统计字符串的长度
select length('abc')
size:使用频率 ★★★★★
是用来统计数组或者map的元素，通常笔者用该函数用来统计去重数（一般都是通过distinct，然后count统计，但是这种方式效率较慢）
--使用size
select
distinct size(collect_set(user_id) over(partition by year(visit_date)))
from wedw_tmp.tmp_url_info
+-----------+--+
| user_cnt |
+-----------+--+
| 4 |
+-----------+--+
1 row selected (0.268 seconds)
--使用通过distinct，然后count统计的方式
select
count(1)
from
(
select
distinct user_id
from wedw_tmp.tmp_url_info
)t
+-----------+--+
| count(1) |
+-----------+--+
| 4 |
+-----------+--+
1 row selected (0.661 seconds)
--笔者这里只用到了19条记录数，就可以明显观察到耗时差异，这里涉及到shuffle问题，后续将会有单独的文章来讲解hive的数据倾斜问题
trim：使用频率 ★★★★★
将字符串前后的空格去掉，和java中的trim方法一样，这里还有ltrim和rtrim,不再讲述了
--最后会得到sfssf sdf sdfds
select trim(' sfssf sdf sdfds ')
regexp_replace：使用频率 ★★★★★
regexp_replace(string INITIAL_STRING, string PATTERN, string REPLACEMENT)
按照Java正则表达式PATTERN将字符串中符合条件的部分成REPLACEMENT所指定的字符串，如里REPLACEMENT空的话，抽符合正则的部分将被去掉
--将url中？参数后面的内容全部剔除
select
distinct regexp_replace(visit_url,'\\?(.*)','') as visit_url
from wedw_tmp.tmp_url_info
10.png
regexp_extract：使用频率 ★★★★
regexp_extract(string subject, string pattern, int index)
抽取字符串subject中符合正则表达式pattern的第index个部分的子字符串，注意些预定义字符的使用
类型于python爬虫中的xpath，用于提取指定的内容
--提取csdn文章编号
select
distinct regexp_extract(visit_url,'/details/([0-9]+)',1) as visit_url
from wedw_tmp.tmp_url_info
11.png
substring_index：使用频率 ★★
substring_index(string A, string delim, int count)
截取第count分隔符之前的字符串，如count为正则从左边开始截取，如果为负则从右边开始截取
--比如将2020年的用户组合获取前2个用户,下面的sql将上面讲解的函数都结合在一起使用了
select
user_set,
substring_index(user_set,',',2) as user_id
from
(
select
distinct concat_ws(',',collect_set(user_id) over(partition by year(visit_date))) as user_set
from wedw_tmp.tmp_url_info
)t
12.png
条件判断
if：使用频率 ★★★★★
if(boolean testCondition, T valueTrue, T valueFalseOrNull):判断函数，很简单
如果testCondition 为true就返回valueTrue,否则返回valueFalseOrNull
--判断是否为user1用户
select
distinct user_id,
if(user_id='user1',true,false) as flag
from wedw_tmp.tmp_url_info
13.png
case when ：使用频率 ★★★★★
CASE a WHEN b THEN c [WHEN d THEN e] [ELSE f] END
如果a=b就返回c,a=d就返回e，否则返回f 如CASE 4 WHEN 5 THEN 5 WHEN 4 THEN 4 ELSE 3 END 将返回4
相比if，个人更倾向于使用case when
--仍然以if上面的列子
select
distinct user_id,
case when user_id='user1' then 'true'
when user_id='user2' then 'test'
else 'false' end as flag
from wedw_tmp.tmp_url_info
14.png
coalesce：使用频率 ★★★★★
COALESCE(T v1, T v2, ...)
返回第一非null的值，如果全部都为NULL就返回NULL
--该函数结合lead或者lag更容易贴近实际业务需求,这里使用lead，并取后3行的值作为当前行值
select
user_id,
visit_time,
rank,
lead_time,
coalesce(visit_time,lead_time) as has_time
from
(
select
user_id,
visit_time,
visit_cnt,
row_number() over(partition by user_id order by visit_date desc) as rank,
lead(visit_time,3) over(partition by user_id order by visit_date desc) as lead_time
from wedw_tmp.tmp_url_info
order by user_id
)t
15.png
数值相关
round：使用频率 ★★
round(DOUBLE a):返回对a四舍五入的BIGINT值,
round(DOUBLE a, INT d):返回DOUBLE型d的保留n位小数的DOUBLW型的近似值
该函数没什么可以讲解的
select round(4/3),round(4/3,2);
+------+-------+--+
| _c0 | _c1 |
+------+-------+--+
| 1.0 | 1.33 |
+------+-------+--+
ceil：使用频率 ★★★
ceil(DOUBLE a), ceiling(DOUBLE a)
求其不小于小给定实数的最小整数;向上取整
select ceil(4/3),ceiling(4/3)
16.png
floor：使用频率 ★★★
floor(DOUBLE a):向下取整''
select floor(4/3);
17.png
hex：使用频率 ★
hex(BIGINT a)/ hex(STRING a)/ hex(BINARY a)
计算十六进制a的STRING类型，如果a为STRING类型就转换成字符相对应的十六进制
该函数很少使用，主要是因为曾经遇到过关于emoj表情符脏数据，故使用该函数进行处理
时间相关(比较简单)
from_unxitime：使用频率 ★★★★★
from_unixtime(bigint unixtime[, string format])
将时间的秒值转换成format格式（format可为“yyyy-MM-dd hh:mm:ss”,“yyyy-MM-dd hh”,“yyyy-MM-dd hh:mm”等等）
select from_unixtime(1599898989,'yyyy-MM-dd') as current_time
+---------------+--+
| current_time |
+---------------+--+
| 2020-09-12 |
+---------------+--+
unix_timestamp：使用频率 ★★★★★
unix_timestamp():获取当前时间戳
unix_timestamp(string date)：获取指定时间对应的时间戳
通过该函数结合from_unixtime使用，或者可计算两个时间差等
select
unix_timestamp() as current_timestamp,--获取当前时间戳
unix_timestamp('2020-09-01 12:03:22') as speical_timestamp,--指定时间对于的时间戳
from_unixtime(unix_timestamp(),'yyyy-MM-dd') as current_date --获取当前日期
18.png
to_date：使用频率 ★★★★★
to_date(string timestamp)
返回时间字符串的日期部分
--最后得到2020-09-10
select to_date('2020-09-10 10:31:31')
year：使用频率 ★★★★★
year(string date)
返回时间字符串的年份部分
--最后得到2020
select year('2020-09-02')
month：使用频率 ★★★★★
month(string date)
返回时间字符串的月份部分
--最后得到09
select month('2020-09-10')
day：使用频率 ★★★★★
day(string date)
返回时间字符串的天
--最后得到10
select day('2002-09-10')
date_add：使用频率 ★★★★★
date_add(string startdate, int days)
从开始时间startdate加上days
--获取当前时间下未来一周的时间
select date_add(now(),7)
--获取上周的时间
select date_add(now(),-7)
date_sub：使用频率 ★★★★★
date_sub(string startdate, int days)
从开始时间startdate减去days
--获取当前时间下未来一周的时间
select date_sub(now(),-7)
--获取上周的时间
select date_sub(now(),7)
