字符串函数 对字符串进行拼接:concat(str1, str2, ..., strN) ,参数:str1、str2...是要进行拼接的字符串。 , (, ); 2. concat_ws 在拼接的字符串中间添加某种分隔符:concat_ws(sep, [str | array(str)]+)。 参数1:分隔符,如 - ;参数2:要拼接的字符串(可多个) (, , ); 3. encode 设置编码格式:encode(str, charset)。 参数1:要进行编码的字符串 ;参数2:使用的编码格式,如UTF-8 (, ); 4. decode 转码:decode(bin, charset)。 参数1:进行转码的binary ;参数2:使用的转码格式,如UTF-8 ((, ), ); 5. format_string / printf 格式化字符串:format_string(strfmt, obj, ...) (, , ); 6. initcap / lower / upper initcap:将每个单词的首字母转为大写,其他字母小写。单词之间以空白分隔。 upper:全部转为大写。 lower:全部转为小写。 (); (); (); 7. length 返回字符串的长度。 (); 8. lpad / rpad 返回固定长度的字符串,如果长度不够,用某种字符进行补全。 lpad(str, len, pad):左补全 rpad(str, len, pad):右补全 注意:如果参数str的长度大于参数len,则返回的结果长度会被截取为长度为len的字符串 (, , ); (, , ); (, , ); 9. trim / ltrim / rtrim 去除空格或者某种字符。 trim(str) / trim(trimStr, str):首尾去除。 ltrim(str) / ltrim(trimStr, str):左去除。 rtrim(str) / rtrim(trimStr, str):右去除。 (); (, ) ; 10. regexp_extract 正则提取某些字符串 (, , ); 11. regexp_replace 正则替换 (, , ); 12. repeat repeat(str, n):复制给定的字符串n次 (, ); 13. instr / locate 返回截取字符串的位置。如果匹配的字符串不存在,则返回0 () . (, ); (, ); 14. space 在字符串前面加n个空格 select concat(space(2), "A"); 15. split split(str, regex):以某字符拆分字符串 split(str, regex) [,] (, ); 16. substr / substring_index (, ); (, ); (, , ); . (, , ); 17. translate 替换某些字符为指定字符 (, , ); JSON函数 (, ); 2. from_json . ( (,, (,)) ); 这个方法可以给json定义一个Schema,这样在使用时,就可以直接使用a.k这种方式了,会简化很多。 3. to_json (()) ; 时间函数 获取当前时间 ; ; 2. 从日期时间中提取字段/格式化时间 (); 2)dayofweek(1 = Sunday, 2 = Monday, ..., 7 = Saturday)、dayofyear (); 3)weekofyear(date) (: ): { (.) } (); 4)trunc 截取某部分的日期,其他部分默认为01。第二个参数: YEAR、YYYY、YY、MON、MONTH、MM (, ); (, ); 5)date_trunc 参数:YEAR、YYYY、YY、MON、MONTH、MM、DAY、DD、HOUR、MINUTE、SECOND、WEEK、QUARTER :: ( ,); 6)date_format 按照某种格式格式化时间 (, ); 3. 日期时间转换 1)unix_timestamp 返回当前时间的unix时间戳。 (); (, ); 2)from_unixtime 将unix epoch(1970-01-01 00:00:00 UTC)中的秒数转换为以给定格式表示当前系统时区中该时刻的时间戳的字符串。 select from_unixtime(1609257600, "yyyy-MM-dd HH:mm:ss"); 3)to_unix_timestamp 将时间转化为时间戳。 (, ); 4)to_date / date 将时间字符串转化为date。 (); (); 5)to_timestamp 将时间字符串转化为timestamp。 select to_timestamp("2020-12-30 12:30:00"); 6)quarter 从给定的日期/时间戳/字符串中提取季度。 (); 4. 日期、时间计算 1)months_between(end, start) 返回两个日期之间的月数。参数1为截止时间,参数2为开始时间 (, ); 2)add_months 返回某日期后n个月后的日期。 (, ); 3)last_day(date) 返回某个时间的当月最后一天 (); 4)next_day(start_date, day_of_week) 返回某时间后the first date基于specified day of the week。 参数1:开始时间。 参数2:Mon、Tue、Wed、Thu、Fri、Sat、Sun。 (, ); 5)date_add(start_date, num_days) 返回指定时间增加num_days天后的时间 (, ); 6)datediff(endDate, startDate) 两个日期相差的天数 (, ); 7)关于UTC时间 (, ) , , . , . (, ) ; (, ) , , . , . (, ); 常用的开窗函数 开窗函数格式通常满足: function_name([argument_list]) function_name: 函数名称,比如SUM()、AVG() partition_expression:分区列 sort_expression:排序列 注意:以下举例涉及的表employee中字段含义:name(员工姓名)、dept_no(部门编号)、salary(工资) 1. cume_dist 如果按升序排列,则统计:小于等于当前值的行数/总行数(number of rows ≤ current row)/(total number of rows)。如果是降序排列,则统计:大于等于当前值的行数/总行数。用于累计统计。 举例: 1)统计小于等于当前工资的人数占总人数的比例 ,用于累计统计 , , , () ( ) ; 2)按照部门统计小于等于当前工资的人数占部门总人数的比例 , , , () ( ) ; 2. lead(value_expr[,offset[,default]]) 用于统计窗口内往下第n行值。第一个参数为列名,第二个参数为往下第n行(可选,默认为1),第三个参数为默认值(当往下第n行为NULL时候,取默认值,如不指定,则为NULL)。 举例:按照部门统计每个部门员工的工资以及大于等于该员工工资的下一个员工的工资 , , , (, ) ( ) ; 3. lag(value_expr[,offset[,default]]) 与lead相反,用于统计窗口内往上第n行值。第一个参数为列名,第二个参数为往上第n行(可选,默认为1),第三个参数为默认值(当往上第n行为NULL时候,取默认值,如不指定,则为NULL)。 举例:按照部门统计每个部门员工的工资以及小于等于该员工工资的上一个员工的工资 , , , (, ) ( ) ; 4. first_value 取分组内排序后,截止到当前行,第一个值。 举例:按照部门统计每个部门员工工资以及该部门最低的员工工资 , , , () ( ) ; 5. last_value 取分组内排序后,截止到当前行,最后一个值。 举例:按部门分组,统计每个部门员工工资以及该部门最高的员工工资 , , , () ( ) ; 注意: last_value默认的窗口是RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW,表示当前行永远是最后一个值,需改成RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING。 此外: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW:为默认值,即当指定了ORDER BY从句,而省略了window从句 ,表示从开始到当前行(当前行永远是最后一个值)。 RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING:表示从当前行到最后一行。 RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING:表示所有行。 n PRECEDING m FOLLOWING:表示窗口的范围是[(当前行的行数)- n, (当前行的行数)+ m] row。 6. rank 对组中的数据进行排名,如果名次相同,则排名也相同,但是下一个名次的排名序号会出现不连续。比如查找具体条件的topN行。RANK() 排序为 (1,2,2,4)。 7. dense_rank dense_rank函数的功能与rank函数类似,dense_rank函数在生成序号时是连续的,而rank函数生成的序号有可能不连续。大数据培训当出现名次相同时,则排名序号也相同。而下一个排名的序号与上一个排名序号是连续的。 8. SUM/AVG/MIN/MAX 数据: id time pv 结果: , , , () ( ) , () ( ) , () ( ) , () ( ) , () ( ) , () ( ) ; 9. NTILE NTILE(n),用于将分组数据按照顺序切分成n片,返回当前切片值。 NTILE不支持ROWS BETWEEN,比如 NTILE(2) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)。 如果切片不均匀,默认增加第一个切片的分布。 10. ROW_NUMBER 从1开始,按照顺序,生成分组内记录的序列。 比如,按照pv降序排列,生成分组内每天的pv名次 SparkSQL函数算子以上函数都是可以直接在SQL中应用的。 那么如果是在Spark SQL的DataFrame/DataSet的算子中调用,可以参考DataFrame/DataSet的算子以及org.apache.spark.sql.functions._下的函数: |
|