Hive 常用函数

一、算术运算符：+ - * / % 
二、关系运算符： == <> > >= < <=
三、逻辑运算符：and or not
四、数学函数
        1.pmod(±n,m)
        -----------------------------------------------------------------
        select pmod(-3,5);
        (-3+n*5)%5    n为正整数取值为从1开始带入至表达式第一次为正值时的值
        -----------------------------------------------------------------
        2. rand(seed)                                   (伪)随机
        3. sign(num)                                    取表达式符号
        4. positive(num)                               返回数值本身
        5. nagative(num)                             返回数值的相反数
        6. ceil()
        7. floor()
        8. round()
        9. bin(十进制数)                               返回十进制参数的二进制值
        10.conv(num,from_base,to_base)   进制转换
         -----------------------
         conv(12,10,2) ->1100
         conv(12,10,8) ->14
         -----------------------
        11. greatest(T...ts)                           返回参数列表中的最大值
        12. least(T...ts)                                返回类型列表中的最小值
        13. bround(decimal)                        银行家舍入法：bround(2.5)=>2 bround(3.5)=>4 
        14. shiftleft(bigint|int,int)                 左移
        15. shiftright(bigint|int,int)               右移
    
    字符串函数
        1. concat(int|string|...)
        2. concat_ws(string sep,string...|array)
        -----------------------------------------------------------
        select concat_ws('_',array('aa','bb','cc')); -> aa_bb_cc
        -----------------------------------------------------------
        3. substr(cnt string,pos int[,len int])     pos从1开始
        4. locate(substr,str[,pos])                     从pos开始找到substr首次出现的首字母的位置，不存在则为0
        5. instr(str,substr)                                 找到substr在str首次出现的首字母出现的位置，不存在则为0
        6. replace(str,substr,rep)                      将str中的所有substr替换为rep
        7. regexp_replace(str,regex,rep)          将str中满足regex规则的内容替换为rep
            select regexp_replace('123abc456def789','[a-z]+','');
            +------------+
            |    _c0     |
            +------------+
            | 123456789  |
            +------------+
        8. sentences(str)                                   将str以标点符号和空格分别作为第一、第二维度分隔符，将str拆分成一个二维数组
        

        9. ngrams(array> arr,int n,int topk)
            针对arr中连续n个单词做词频统计并倒序排列，将topk个统计结果返回
       

       10. context_ngrams(array> arr,array cnt,int topk)
            针对arr中连续size(cnt)个单词的组合以cnt中非null内容匹配统计，按数量倒序排列，将topk个结果返回
        
        11. encode(string cnt,string encode)        将cnt转换为encode编码内容
        12. cast(exp as type)                        将exp转为type类型的值
        
        
        13. get_json_object(string json,string path)
            提取json格式中字符串中指定key的值，一次解析一项，但可多层 path  {key:value}: '$.key' '$.arraykey[index]' '$.objkey.subkey'
            select get_json_object('{"name":"henry","hobbies":["s","a","c"],"address":{"province":"js","city":"nj"}}','$.name');
            +--------+
            |  _c0   |
            +--------+
            | henry  |
            +--------+
    
        14. json_tuple(string json,string...ps)    
            提取json字符串中指定key列表的值，一次解析多个但限一层
            select json_tuple('{"name":"henry","hobbies":["s","a","c"],"address":{"province":"js","city":"nj"}}','name','hobbies');
            +--------+----------------+
            |   c0   |       c1       |
            +--------+----------------+
            | henry  | ["s","a","c"]  |
            +--------+----------------+
        15. in_file(string line,string filepath)    返回内容line是否存在于文件filepath中
        16. parse_url(string url,string part[,string key])
            解析url根据part提取内容，当part为QUERY时添加key进行单独键的值提取
            part : PROTOCOL,HOST,QUERY,REF,PATH,USERINFO

• select parse_url('http://facebook.com/path/p1.php?query=1', 'PROTOCOL') from dual;   --http
• select parse_url('http://facebook.com/path/p1.php?query=1', 'HOST') from dual;---facebook.com​
• select parse_url('http://facebook.com/path/p1.php?query=1', 'REF') from dual;---空​
• select parse_url('http://facebook.com/path/p1.php?query=1', 'PATH') from dual;---/path/p1.php​
• select parse_url('http://facebook.com/path/p1.php?query=1', 'QUERY') from dual;---空​
• ​select parse_url('http://facebook.com/path/p1.php?query=1', 'FILE') from dual;​---/path/p1.php?query=1​
• ​select parse_url('http://facebook.com/path/p1.php?query=1', 'AUTHORITY') from dual;​---facebook.com​
• ​select parse_url('http://facebook.com/path/p1.php?query=1', 'USERINFO') from dual;​---空

        17. printf(String format, Obj... args)
            select printf('%s,%d,%2f','henry',18,23456.234);
            +------------------------+
            |          _c0           |
            +------------------------+
            | henry,18,23456.234000  |
            +------------------------+
        18. str rlike regex 
            select '{"name":"henry","age":"18","gender":"male"}' rlike '^.*?"age":"\\d+",.*?$';
            +-------+
            |  _c0  |
            +-------+
            | true  |
            +-------+
            
        19. regexp_extract(str,group_regex,pos)        根据group_regex匹配str提取第pos个元素
            select regexp_extract('{"name":"henry","age":"18","gender":"male"}','\\{"name":"(.*?)","age":"(.*?)","gender":"(.*?)"\\}',1);
            +--------+
            |  _c0   |
            +--------+
            | henry  |
            +--------+
            
        20. split(str,regex)                            根据正则regex分割字符串，支持多字符串分割，返回类型为数组
            select split('aa,bb.cc',',|\\.');
            +-------------------+
            |        _c0        |
            +-------------------+
            | ["aa","bb","cc"]  |
            +-------------------+
            
        21. str_to_map(string cnt,string kvsSep,string kvSep)
            --将字符串cnt使用kvsSep作为键值对之间分隔符kvSep作为键和值的分隔符转化为map对象
            select str_to_map('name:henry,age:18,gender:male',',',':');
            +----------------------------------------------+
            |                     _c0                      |
            +----------------------------------------------+
            | {"name":"henry","age":"18","gender":"male"}  |
            +----------------------------------------------+

        22. translate(str,from,to)                     按照from同位置将str中的内容替换为to的内容
        23. initcap(str)                                首字母大写
        24. md5(concat('salt_prefix',field,'salt_suffix'))    非对称加密，不可逆
        25. base64(binary(str))                        将str字符串使用base64加密    简单的对称加密
        26. unbase64(str)                                将str字符串用base64解密
        27. base64(aes_encrypt(sring cnt,string secretKey))    secretKey的长度 16+(0~正整数)*8 复杂的对称加密
        select base64(aes_encrypt('I Love You','kb12202106160890'));
        +---------------------------+
        |            _c0            |
        +---------------------------+
        | P48HxSlhsjK8/QUrDFvTSg==  |
        +---------------------------+
        28. aes_decrypt(unbase64(),)
        select aes_decrypt(unbase64('P48HxSlhsjK8/QUrDFvTSg=='),'kb12202106160890');
        +-------------+
        |     _c0     |
        +-------------+
        | I Love You  |
        +-------------+
    
    集合函数
        1. size(array/map)                            返回集合元素的数量
        2. array_contains(array,item)                    返回数组中是否包含元素item
        3. array(item1,...itemn)                        返回多个元素的数组
        4. map(k1,v1,...kn,vn)                        返回多个元素的键值对
        5. struct(v1,...,vn)                            返回多个元素的结构体（自动追加列明col1,...,coln）
        6. map_keys(map)                                返回键值对的键集合
        7. map_values(map)                            返回键值对的值集合
        8. sort_array(array)                            返回数组升序排序
    
    日期函数
        1. current_date()                                返回系统当前日期
        2. current_timestamp()                        返回系统当前日期的完整格式
        3. unix_timestamp([string|date|datetime|timestamp[,date_format]])                            
                                                        默认返回系统当前时间戳(单位：秒)
                                                        如果只有参数1，返回参数1完整格式对应的时间戳
                                                        如果有两参数，返回参数1对应参数2格式的时间戳
        4. date_add(start_date,int days)                返回start_date之后第days日的日期
        5. add_months(string|date|datetime|timestamp,int months)
                                                        返回start_date之后第months月的日期
        6. datediff(big_date,small_date)                返回两个日期之间的天数差值
        7. from_unixtime(bigint[,date_format])        返回时间戳对应date_format格式字符串信息
        8. date_format(string|date|datetime|timestamp,date_format)
                                                        返回日期对应date_format格式字符串信息
        9. to_date(string|datetime|timestamp)            返回日期的年月日格式
        10.next_day(string|datetime|timestamp,weekOfDay)
                                                        返回距离当前日期最近的下一个weekOfDay
        
        11.last_day(string|datetime|timestamp)                    返回参数日期所属月份的最后一天
            concat(year(current_date()),'-12-31')                    年最后一天
            date_add(add_months(trunc(current_date(),'Q'),3),-1)    季度最后一天
            date_add(next_day(current_date(),'mo'),-1)            周最后一天
        
        12.trunc(string|date|datetime|timestamp,part)                返回参数1对应参数2单位的第一天日期         It currently only supports 'MONTH'/'MON'/'MM', 'QUARTER'/'Q' and 'YEAR'/'YYYY'/'YY'
            part : YY=>年 Q=>季 MM=>月
            date_add(next_day(date,'MO'),-7)                        返回参数日期所属周的第一天（星期一）日期
        13.months_between(big_date,small_date)                    返回两个日期之间的月数差(返回类型为小数)    
            select months_between('2021-6-11','2020-11-1');
            +-------------+
            |     _c0     |
            +-------------+
            | 7.32258065  |
            +-------------+

    条件函数
        1. if(condition,if_true_val,if_false_val);                双重分支：相当于三元运算符
        2. nvl(field,default_val)                                    field==null ? default_val : field
        3. case field when CONST_V1 then V1 ... else VN end;        等值判断多分支
        4. case when field>=CONST_V1 then V1 ... else VN end;        区间判断
        5. coalesce(T...vs)                                        返回参数列表中第一个非空值
        6. isnull(field)                                            返回field列值是否为空
        7. isnotnull(field)                                        返回field列值是否不为空

    侧视图
        select ... ,item_alias from TABLE_NAME lateral view func(field) LV_alias as item_alias;
    聚合函数
        1. sum([distinct] field)
        2. avg([distinct] field)
        3. count([distinct] field)
        4. max(field)
        5. min(field)
        6. collect_list(field)
        7. collect_set(filed)                    去重统计    
        8. var_pop(numField)                    方差
        9. stddev_pop(numField)                标准差
        10.covar_pop(numBaseField,numField)    协方差
    窗口函数(统计函数)
        row_number()
        rank()
        dense_rank()
        ntile(n)
        lag(field,nth,defaultVal)
        lead(field,nth,defaultVal)
        first_value(field)
        last_value(field)
        nth_value(field,nth)
    over重句
        over([partition by field [order by field]] rows between ... and ...)
            window 子句 rows between ... and ...
            unbounded_preceeding
            n preceding
            current_row
            n following
            unbounded_following