美文网首页
数仓利器-Hive高频函数合集

数仓利器-Hive高频函数合集

作者: Bloo_m | 来源:发表于2020-09-12 18:29 被阅读0次

    前言

    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对比

    相同点:都是分组排序

    不同点:

    1. Row_number:即便出现相同的排序,排名也不会一致,只会进行累加;即排序次序连续,但不会出现同一排名
    2. rank:当出现相同的排序时,中间会出现一个空缺,即分组内会出现同一个排名,但是排名次序是不连续的
    3. 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)
    

    相关文章

      网友评论

          本文标题:数仓利器-Hive高频函数合集

          本文链接:https://www.haomeiwen.com/subject/bhowektx.html