来源：https://mp.weixin.qq.com/s/PLWovsMDxO0wUrDTMaOh4w
Cube相关
GROUPING SETS：使用频率 ★
类似于kylin中的cube，将多种维度进行组合统计；在一个GROUP BY查询中,根据不同维度组合进行聚合，等价于将不同维度的GROUP BY结果集进行UNION ALL
1 --按照用户+访问日期统计统计次数 2 select 3 user_id, 4 visit_date, 5 sum(visit_cnt) as visit_cnt 6 from wedw_tmp.tmp_url_info 7 group by user_id,visit_date 8 grouping sets(user_id,visit_date) 9 10 --下图的结果类似于以下sql 11 select 12 user_id, 13 NULL as visit_date, 14 sum(visit_cnt) as visit_cnt 15 from wedw_tmp.tmp_url_info 16 union all 17 select 18 NULL as user_id, 19 visit_date, 20 sum(visit_cnt) as visit_cnt 21 from wedw_tmp.tmp_url_info 22 union all 23 select 24 user_id, 25 visit_date, 26 sum(visit_cnt) as visit_cnt 27 from wedw_tmp.tmp_url_info
字符相关
concat：使用频率 ★★★★★
字符拼接，concat(string|binary A, string|binary B…);该函数比较简单
1select concat(‘a‘,‘b‘,‘c‘) 2--最后结果就是abc
concat_ws：使用频率 ★★★★★
按照指定分隔符将字符或者数组进行拼接；concat_ws(string SEP, array)/concat_ws(string SEP, string A, string B…)
1 --还是concat使用的例子，这里可以写成 2 select concat_ws(‘‘,‘a‘,‘b‘,‘c‘) 3 4 --将数组列表元素按照指定分隔符拼接，类似于python中的join方法 5 select concat_ws(‘‘,array(‘a‘,‘b‘,‘c‘))
instr：使用频率 ★★★★
查找字符串str中子字符串substr出现的位置，如果查找失败将返回0，如果任一参数为Null将返回null，注意位置为从1开始的；通常笔者用这个函数作为模糊查询来查询
1 --查询vist_time包含10的记录 2 select 3 user_id, 4 visit_time, 5 visit_date, 6 visit_cnt 7 from wedw_tmp.tmp_url_info 8 where instr(visit_time,‘10‘)>0
length：使用频率 ★★★★★
统计字符串的长度
1select length(‘abc‘)
size:使用频率 ★★★★★
是用来统计数组或者map的元素，通常笔者用该函数用来统计去重数（一般都是通过distinct，然后count统计，但是这种方式效率较慢）
1--使用size 2 select 3 distinct size(collect_set(user_id) over(partition by year(visit_date))) 4 from wedw_tmp.tmp_url_info 5+-----------+--+ 6| user_cnt | 7+-----------+--+ 8| 4 | 9+-----------+--+ 101 row selected (0.268 seconds) 11 12--使用通过distinct，然后count统计的方式 13 select 14 count(1) 15 from 16 ( 17 select 18 distinct user_id 19 from wedw_tmp.tmp_url_info 20 )t 21+-----------+--+ 22| count(1) | 23+-----------+--+ 24| 4 | 25+-----------+--+ 261 row selected (0.661 seconds) 27 28--笔者这里只用到了19条记录数，就可以明显观察到耗时差异，这里涉及到shuffle问题，后续将会有单独的文章来讲
trim：使用频率 ★★★★★
将字符串前后的空格去掉，和java中的trim方法一样，这里还有ltrim和rtrim,不再讲述了
1 --最后会得到sfssf sdf sdfds 2 select trim(‘ sfssf sdf sdfds ‘)
regexp_replace：使用频率 ★★★★★
regexp_replace(string INITIAL_STRING, string PATTERN, string REPLACEMENT)
按照Java正则表达式PATTERN将字符串中符合条件的部分成REPLACEMENT所指定的字符串，如里REPLACEMENT空的话，抽符合正则的部分将被去掉
1--将url中？参数后面的内容全部剔除 2 select 3 distinct regexp_replace(visit_url,‘\\?(.*)‘,‘‘) as visit_url 4 from wedw_tmp.tmp_url_info
regexp_extract：使用频率 ★★★★
regexp_extract(string subject, string pattern, int index)
抽取字符串subject中符合正则表达式pattern的第index个部分的子字符串，注意些预定义字符的使用
类型于python爬虫中的xpath，用于提取指定的内容
1--提取csdn文章编号 2 select 3 distinct regexp_extract(visit_url,‘/details/([0-9]+)‘,1) as visit_url 4 from wedw_tmp.tmp_url_info
substring_index：使用频率 ★★
substring_index(string A, string delim, int count)
截取第count分隔符之前的字符串，如count为正则从左边开始截取，如果为负则从右边开始截取
1 --比如将2020年的用户组合获取前2个用户,下面的sql将上面讲解的函数都结合在一起使用了 2 select 3 user_set, 4 substring_index(user_set,‘,‘,2) as user_id 5 from 6( 7 select 8 distinct concat_ws(‘,‘,collect_set(user_id) over(partition by year(visit_date))) as user_set 9 from wedw_tmp.tmp_url_info 10 )t
条件判断
if：使用频率 ★★★★★
if(boolean testCondition, T valueTrue, T valueFalseOrNull):判断函数，很简单
如果testCondition 为true就返回valueTrue,否则返回valueFalseOrNull
1 --判断是否为user1用户 2 select 3 distinct user_id, 4 if(user_id=‘user1‘,true,false) as flag 5 from wedw_tmp.tmp_url_info
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
1 --仍然以if上面的列子 2 select 3 distinct user_id, 4 case when user_id=‘user1‘ then ‘true‘ 5 when user_id=‘user2‘ then ‘test‘ 6 else ‘false‘ end as flag 7 from wedw_tmp.tmp_url_info
coalesce：使用频率 ★★★★★
COALESCE(T v1, T v2, …)
返回第一非null的值，如果全部都为NULL就返回NULL
1--该函数结合lead或者lag更容易贴近实际业务需求,这里使用lead，并取后3行的值作为当前行值 2 select 3 user_id, 4 visit_time, 5 rank, 6 lead_time, 7 coalesce(visit_time,lead_time) as has_time 8 from 9( 10 select 11 user_id, 12 visit_time, 13 visit_cnt, 14 row_number() over(partition by user_id order by visit_date desc) as rank, 15 lead(visit_time,3) over(partition by user_id order by visit_date desc) as lead_time 16 from wedw_tmp.tmp_url_info 17 order by user_id 18 )t
数值相关
round：使用频率 ★★
round(DOUBLE a):返回对a四舍五入的BIGINT值,
round(DOUBLE a, INT d):返回DOUBLE型d的保留n位小数的DOUBLW型的近似值
该函数没什么可以讲解的
1select round(4/3),round(4/3,2); 2+------+-------+--+ 3| _c0 | _c1 | 4+------+-------+--+ 5| 1.0 | 1.33 | 6+------+-------+--+
ceil：使用频率 ★★★
ceil(DOUBLE a), ceiling(DOUBLE a)
求其不小于小给定实数的最小整数;向上取整
1 select ceil(4/3),ceiling(4/3)
floor：使用频率 ★★★
floor(DOUBLE a):向下取整‘‘
1 select floor(4/3);
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”等等）
1select 2 unix_timestamp() as current_timestamp,--获取当前时间戳 3 unix_timestamp(‘2020-09-01 12:03:22‘) as speical_timestamp,--指定时间对于的时间戳 4 from_unixtime(unix_timestamp(),‘yyyy-MM-dd‘) as current_date --获取当前日期
to_date：使用频率 ★★★★★
to_date(string timestamp)
返回时间字符串的日期部分
1--最后得到2020-09-10 2select to_date(‘2020-09-10 10:31:31‘)
year：使用频率 ★★★★★
year(string date)
返回时间字符串的年份部分
1--最后得到2020 2select year(‘2020-09-02‘)
month：使用频率 ★★★★★
month(string date)
返回时间字符串的月份部分
1 --最后得到09
2 select month(‘2020-09-10‘)
day：使用频率 ★★★★★
day(string date)
返回时间字符串的天
1 --最后得到10
2 select day(‘2002-09-10‘)
date_add：使用频率 ★★★★★
date_add(string startdate, int days)
从开始时间startdate加上days
1 --获取当前时间下未来一周的时间
2 select date_add(now(),7)
3 --获取上周的时间
4 select date_add(now(),-7)
date_sub：使用频率 ★★★★★
date_sub(string startdate, int days)
从开始时间startdate减去days
1 --获取当前时间下未来一周的时间
2 select date_sub(now(),-7)
3 --获取上周的时间
4 select date_sub(now(),7)