hql函数

一.日期函数

1.to_date()：作用将字符类型按一定格式转化为日期类型

        用法：to_date(‘2004-11-27’,’yyyy-mm-dd’)

2.datediff(date1, date2)：返回date1和date2之间的时间间隔

3.add_months(start_date, num_months)：返回开始日期后num_months的日期

4.date_add(start_date, num_days)：返回开始日期后的num_days的日期

5.date_sub(start_date, num_days)：返回开始日期前num_days的日期

6.date_format(date/timestamp/string, fmt)：将日期/时间戳/string转换为日期格式fmt指定格式的字符串值

7.to_unix_timestamp(date[, pattern])：返回UNIX时间戳

8.from_unixtime(unix_time, format)：使用指定格式返回UNIX时间

9.dayofmonth(date)：返回date日期在当月的第几天

10.weekofyear(date)：返回date日期在当年的第几周

11.current_date()：返回当前日期

12.current_timestamp()：返回当前时间

13.next_day(start_date, day_of_week)：以开始日期为起点，返回下一个周几的日期

二.正则函数

1.like：模糊查询，%表示任意数量的字符，_表示任意单个字符

        用法：select * from table1 where app_name like '%wallet%';

2.regexp_replace(string A, string B, string C)：将字符串A中的符合java正则表达式B的部分替换为C

        用法：select regexp_replace('2021-06-29','-','') from table1;--20210629

3.regexp_extract(string A, string pattern, int index)：将字符串A按照pattern正则表达式的规则拆分，返回index指定的字符，index从1开始计

        用法：select regexp_extract(factor_value_string,'^\\[(.+)\\]$',1) from table1;

正则匹配字符：

"^"匹配开头

"$"匹配结尾

"." 任意单个字符

"*" 匹配前面的字符0次或多次

"+" 匹配前面的字符1次或多次

"?" 匹配前面的字符0次或1次

"\d" 等于 [0-9]，使用的时候写成'\d'

"\D" 等于 [^0-9]，使用的时候写成'\D'

三.类型转换函数

1.cast(expr as <type>)

        用法：select cast('1' as bigint) from table1;

四.条件函数

1.nvl(value, default_value)：如果value值为null就返回default_value

        用法：select nvl(app_name,'') from table1;

2.coalesce(value1,value2,value3,...)：返回第一个为null的值

        用法：select coalesce(app_name,s_name,'') from table1;

五.字符函数

1.split(string str, string pat)：按照pat字符串分割str，会返回分割后的字符串数组

        用法：select split('abcdef', 'c') from table1;--ab def

2.concat(string, string...)：对字符串按次序进行拼接

        用法：select concat('2021','-06-29') from table1;--2021-06-29

3.get_json_object(string json_string, string path)：从json字符串抽取出json对象，并返回这个对象的json格式

        用法：factor_value_string的值为

[{"unionInstituteType":0,"unionInstituteCode":"0193","unionInstituteFundRate":"100","isMaster":false}]

                  select get_json_object(factor_value_string,'$.unionInstituteType') fron table1;--0

4.length(string A)：返回字符串的长度  

5.ltrim(string A)：去掉字符串A前面的空格，rtrim(string A)：去掉字符串A后面的空格

6.trim(string A)：去掉字符串A前后的空格

7.round(double a, int d)：指定精度取整函数

        用法：select round(3.1415926,4) from table1;--3.1416

8.行转列

select  loan_id

           ,get_json_object(factor_value_string_list, '$.unionInstituteType') as unionInstituteType

from (select loan_id 

                   ,factor_value_string

                   ,split(regexp_replace(regexp_extract(factor_value_string ,'^\\[(.+)\\]$',1),'\\}\\,\\{',

'\\}\\|\\|\\{'),'\\|\\|')  factor_value_string_name
                   
         from mdw_ods.ods_fcore_t_loan_factor_df ) t 

 LATERAL VIEW explode(factor_value_string_name) tmp_string_name AS

factor_value_string_list

) c

9.列转行

name          constellation      blood_type   

孙悟空        白羊座                A

唐僧            射手座                A

猪八戒        白羊座                A

沙悟净        白羊座                B   

白羊座,A 孙悟空|猪八戒    

select t1.base,concat_ws('|', collect_set(t1.name)) name

from (select name,concat(constellation, "," , blood_type) base

        from person_info) t1

group by t1.base

六.窗口函数与分析函数

select user_id,user_type,sales,

--分组内所有行

sum(sales) over(partition by user_type) AS sales_1 ,

sum(sales) over(order by user_type) AS sales_2 ,

--默认为从起点到当前行，如果sales相同，累加结果相同

sum(sales) over(partition by user_type order by sales asc) AS sales_3,

--从起点到当前行，结果与sales_3不同。 根据排序先后不同，可能结果累加不同

sum(sales) over(partition by user_type order by sales asc rows between unbounded preceding and current row) AS sales_4,

--当前行+往前3行

sum(sales) over(partition by user_type order by sales asc rows between 3 preceding and current row) AS sales_5,

--当前行+往前3行+往后1行

sum(sales) over(partition by user_type order by sales asc rows between 3 preceding and 1 following) AS sales_6,

--当前行+往后所有行

sum(sales) over(partition by user_type order by sales asc rows between current row and unbounded following) AS sales_7

from order_detail

order by user_type,sales,user_id;

分析函数：

1.row_number()：1 2 3 4 5

2.rank()：1 2 3 3 5

3.dense_rank()：1 2 2 3 4