HQL中常用的函数

以下是我最近写sql常用的一些函数（不全），后续会持续更新整理

1.时间函数

        01.to_date('2022-09-02 15:14:20')    '2022-09-02'  将日期转化为'年-月-日'的形式

        02.date_format('日期','日期格式')  将某个日期转化为指定日期格式

        03.date_sub('2022-09-02',10)  '2022-08-23'  将某个日期减少指定天数

        04.date_add('2022-09-02',10)  '2022-09-12' 将某个日期增加指定天数

        05.datediff('日期1','日期2')  计算'日期1-日期2'相差的天数

        06.unix_timestamp('2022-09-03 14:41:25') 1662187285  获取参数时间的时间戳

2.concat和concat_ws

        01.concat('abc','123')  'abc123'  拼接多个字符串，多个参数之间用逗号分隔，拼接之后是紧挨着的，中间没有分隔符，想有分隔符，使用concat_ws()

        02.concat_ws(',','abc','123')  'abc,123'  使用指定分隔符拼接字符串

3.collect_set()

        列转行专用函数（自动去重，符合set集合特征），有时为了字段拼接，可以与concat_ws()函数连用

        concat_set('a','b','a','c')  ['a','b','c']   

        经常与group by一起使用,使用group by进行第一层去重，使用collect_set()进行再次去重并列转行

4.array_contains()

        与 in 关键字类似

        array_contains(['a','b','c'],'a',1,0)  判断'a'是都在集合[['a','b','c']中，满足返回1，不满足返回0

5.over() 开窗函数

        over()函数在一些复杂SQL中经常用到，并且要与rank()、desc_rank()、row_number() 、min()、max()、sum()一起使用，不能单独使用

        over()函数中，经常会有partition by和order by出现

                over(partition by uid order by age)  当前窗口以uid进行分区，以age进行排序，可以对其做一些聚合和编号操作

6.rank()、dense_rank()、row_number()

        rank()、desc_rank()、row_number()，这些函数必须在开窗的情况下进行，与over(order by 字段)联合使用， 对窗口中的有序数据所在行进行编号

        rank() 排序为1 1 3 3 5的形式，相同数据编号相同，而整体编号是与数据量一致的，所以rank()函数的编号可能是不连续的

        dense_rank() 排序为  1 1 2 2 3的形式，相同数据编号相同，并且整体的编号是连续的

        row_number()排序为1 2 3 4 5的形式，不会有相同的编号，按照排好序的顺序从1开始进行依次+1编号，最后的编号与数据量是一致的

7.rows between  和 range between 

         rows between  和 range between 是在over()函数中使用的

        rows between ... and ... 是以当前行为基准，控制窗口大小

        unbounded 无界  preceding往前  following 往后  current row 当前行

                over(partition by uid order by age rows between unbounded preceding and 1 preceding))  表示在以uid分区，age排序窗口的情况下，取当前行的前一行到前所有行（首行）

        range between  以当前行指定字段值为基准，向上或者向下加减字段值，将窗口中这个范围内所有值取出

                sum(age) over(partition by uid order by age rangebetween 10 preceding and 20 following)）   以当前行的年龄的值（比如100）为基准，向上年龄-10，向下年龄+20 ，将90~120这个范围内所有的年龄进行求和计算

8.关于数字保留的函数  round()、floor()、ceil()

        round(a,b)函数 有两个参数,第一个参数表示要保留的数字，第二个参数是可选参数，不写默认保留整数（实行的是四舍五入的原则），第二个参数表示这个数字a要保留几位小数

        floor(a)函数  表示向下取整，比如floor(2.6) 结果是2  floor(2.3) 结果也是2

        ceil(a) 函数  表示向上取整，比如ceil(2.6) 结果是3  floor(2.3) 结果也是3