HQL函数大全

一、关系运算

等值比较: =等值比较: <=>不等值比较: <>和!=小于比较: <小于等于比较: <=大于比较: >大于等于比较: >=区间比较: between and空值判断: IS NULL非空判断: IS NOT NULLlike比较: 使用通配符 like ‘%a%’| ‘_a_’|concat(’%’,’’,’%’)rlike操作: 使用正则表达式 A rlike 正则表达’[……]’regexp操作: regexp_replace(‘7311W中文’,’[[^\\\u4e00-\\\u9fa5]]’,’’)替换符合条件的字符串； regexp_extract(string,需要匹配的正则表达式,0是显示与之匹配的整个字符串|1,2 是显示第一,二个括号里面的)取出符合条件的对应序号的字符串

二、数学运算：

加法操作: +减法操作: –乘法操作: *除法操作: /取余操作: %位与操作: &位或操作: |位异或操作: ^位取反操作: ~

三、逻辑运算：

逻辑与操作: AND 、&&逻辑或操作: OR 、||逻辑非操作: NOT、!

四、复合类型构造函数

map结构：{“张三”:23，“性别“:“male”} （1）建定义字段 ：          smap map<string,string> comment ‘string型map’          imap map<string,int> comment ‘sring型map’ create table test3(field2 map<string,string>) row format delimited fields terminated by ',' collection items terminated by "|" map keys terminated by ":";

（2）插入数据 map(‘地址’,‘安徽’) （3）查询数据 ‘列名’[‘key’] （4）select name, explode(deductions) from employees;          注意，explode单独使用只能单个字段，如果要和别的字段一起使用必须使           select name,dekey,devalue from employees           lateral view explode(deductions) dedView as dekey,devalue; （5）map_keys( )，map_values( )，size( ) （6）从Hive中的collect_list结果构造映射map

select id1, id2, map(collect_list(col)) as measurements from( select id1, id2, "height" as col union all select id1, id2, count(*) as col from table1 union all select id1, id2, "weight" as col union all select id1, id2, count(*) as col from table2) struct结构：{“张三”:23，“性别“:“male”} （1）定义字段 info struct<age:int, sex:string, addr:string> （2）插入数据 named_struct(“name”,‘zhangsan’,“age”,25) （3）查询数据 列名.namearray结构 （1）定义字段 sarray array comment ‘元组型’ （2）插入数据 array( , , ) （2）查询数据 select arr[num]，下标从0开始 （3）array_contains(year_arr,‘1990’)返回布尔型 （4）lateral view explode 拆成多条记录 （5）size( )数组排序 （1）sort_array() SELECT memberid, regexp_replace(concat_ws('-',sort_array (collect_list(concat_ws(':',cast(legcount as string),airways)))),'\\d\:','')hs from ( select 1 as memberid,'A' as airways,2 as legcount union ALL select 1 as memberid,'B' as airways,3 as legcount union ALL select 1 as memberid,'C' as airways,4 as legcount union ALL select 1 as memberid,'D' as airways,1 as legcount union ALL select 1 as memberid,'E' as airways,8 as legcount ) as t group by memberid

五、数值计算函数

取整函数: round(num,int)int保留至几位银行家舍入法：bround(num,int)指定精度取整函数: round向下取整函数: floor向上取整函数: ceil向上取整函数: ceiling取随机数函数: rand自然指数函数: exp以10为底对数函数: log10以2为底对数函数: log2对数函数: log幂运算函数: pow幂运算函数: power开平方函数: sqrt二进制函数: bin十六进制函数: hex反转十六进制函数: unhex进制转换函数: conv绝对值函数: abs正取余（模）函数: pmod(INT a, INT b)正弦函数: sin反正弦函数: asin余弦函数: cos反余弦函数: acospositive函数: positivenegative函数(取反): negative弧度值转换角度值 degrees(DOUBLE a), degrees(DECIMAL a)角度值转换成弧度值 radians(DOUBLE a), radians(DECIMAL a)如果a是正数则返回1.0，是负数则返回-1.0，否则返回0.0 sign(DOUBLE a), sign(DECIMAL a)数学常数e e()数学常数pi pi()a的阶乘 factorial(INT a)求a的立方根 cbrt(DOUBLE a)求最小值 least(T v1, T v2, …)按位左移 shiftleft(TINYINT|SMALLINT|INT a, INT b)无符号按位右移（<<<）shiftrightunsigned(TINYINT|SMALLINT|INTa, INT b)rank函数： select rank(1500) within group (order by salary desc) "rank of 1500" from employees

判断1500 在salary中排序是第几

六、类型转换函数

二进制转换：binary基础类型之间强制转换：cast

七、日期函数

UNIX时间戳转日期函数: from_unixtime(unix_timestamp(),‘yyyy-MM-dd HH:dd:ss’)获取当前UNIX时间戳函数: unix_timestamp()日期转UNIX时间戳函数: to_unix_timestamp(‘2017-01-01 12:12:12’,‘yyyy-MM-dd HH:dd:ss’)指定格式日期转UNIX时间戳函数: unix_timestamp()获得当前日期|日期时间: current_date()|current_timestamp()日期时间转日期函数: to_date日期转年函数: year日期转月函数: month日期转天函数: day日期转小时函数: hour日期转分钟函数: minute日期转秒函数: second日期转周函数: weekofyear日期比较函数: datediff(d1,d2) d1>d2返回正值日期增加函数: date_add|adddate月份加减: add_months(date,m)日期减少函数: date_sub返回周几: date_format(‘2020-01-19’ ,‘u’)utc时间转换: from_utc_timestamp(current_timestamp(),8)|to_utc_timestamp(current_timestamp(),8)下一个单位日期: next_day(to_date(),‘MO’)下周一每个月最后一天: last_day(add_months(‘2019-04-15’,-1))trunc截取函数: 1.select trunc(date1) from dual --2011-3-18 今天的日期为2011-3-18 2.select trunc(date1, 'mm') from dual --2011-3-1 返回当月第一天. 3.select trunc(date1, 'yy') from dual --2011-1-1 返回当年第一天 4.select trunc(date1, 'dd') from dual --2011-3-18 返回当前年月日 5.select trunc(date1, 'yyyy') from dual --2011-1-1 返回当年第一天 6.select trunc(date1, 'd') from dual --2011-3-13 (星期天)返回当前星期的第一天 7.select trunc(date1, 'hh') from dual --2011-3-18 14:00:00 当前时间为14:41 8.select trunc(date1, 'mi') from dual --2011-3-18 14:41:00 TRUNC()函数没有秒

八、字符串函数

字符ascii码函数：asciibase64字符串：字符串连接函数：concat带分隔符字符串连接函数：concat_ws(string SEP, string A, string B…)数组转换成字符串的函数：concat_ws(string SEP, array)group_concat函数：concat_ws(string SEP,collect_set() ) GROUP_CONCAT([DISTINCT] expr [,expr ...] [ORDER BY {unsigned_integer | col_name | formula} [ASC | DESC] [,col ...]] [SEPARATOR str_val]) 小数位格式化成字符串函数：format_number(number x, int d)字符串截取到结尾函数：substr(string A, int start),substring(string A, intstart)字符串截取函数：substr(string A, int start, int len),substring(string A, intstart, int len)字符串查找函数首次出现的位置：instr(string str, string substr)字符串长度函数：length字符串查找函数：locate(string substr, string str[, int pos])返回字符串 substr 在 str 中从 pos 后查找字符串格式化函数：printf字符串转换成map函数：str_to_map(text[, delimiter1, delimiter2])将字符串按照给定的分隔符转换成 map 结构base64解码函数：unbase64(string str)字符串转大写函数：upper,ucase字符串转小写函数：lower,lcase去空格函数：trim左边去空格函数：ltrim右边去空格函数：rtrim正则表达式替换函数：regexp_replace(string A, string pattern, string C)正则表达式解析函数：regexp_extract(string subject, string pattern, intindex)intindex表示返回第几个，0表示全部URL解析函数：parse_urljson解析函数：get_json_object空格字符串函数：space(int n)返回长度为 n 的字符串重复字符串函数：repeat(string str, int n)返回重复 n 次后的str 字符串左补足函数：lpad(string str, int len, string pad)将 str 进行用pad 进行左补足到 len 位右补足函数：rpad分割字符串函数: split(string str, string pat)按照 pat 字符串分割str,会返回分割后的字符串数组集合查找函数: find_in_set(string str, string strList)返回 str 在 strlist第一次出现的位置,strlist 是用逗号分割的字符串。如果没有找到该 str 字符,则返回 0分词函数：将字符串中内容按语句分组，每个单词间以逗号分隔，最后返回数组。 例如sentences(‘Hello there! How are you?’) 返回：( (“Hello”, “there”), (“How”, “are”, “you”) )分词后统计一起出现频次最高的：ngrams(array<array>, int N, intK, int pf)返回值: array<struct<string,double>>说明:与 sentences()函数一起使用,分词后,统计分词结果中一起出现频次最高的TOP-K 结果分词后统计与指定单词一起出现频次最高的：context_ngrams(array<array>,array, int K, int pf)返回值: array<struct<string,double>>说明:与 sentences()函数一起使用,分词后,统计分词结果中与数组中指定的单词一起出现(包括顺序)频次最高的 TOP-K 结果

九、条件函数

If函数: if()非空查找函数: coalesce|nvl()条件判断函数：case when then else end

十、汇总统计函数（UDAF）

个数统计函数: count(*) , count(1) 会计算null, count(expr), count(DISTINCT expr[, expr_., expr_.])不会计算空值总和统计函数: sum平均值统计函数: avg最小值统计函数: min最大值统计函数: max非空集合总体变量函数: var_pop 返回指定列的方差非空集合样本变量函数: var_samp 返回指定列的样本方差总体标准偏离函数: stddev_pop 返回指定列的偏差样本标准偏离函数: stddev_samp 返回指定列的样本偏差covar_pop(col1, col2) 两列数值协方差covar_samp(col1, col2) 两列数值样本协方差corr(col1, col2) 返回两列数值的相关系数中位数函数: percentile(col, p)返回数值区域的百分比数值点。0<=P<=1,否则返回NULL,不支持浮点型数值。中位数函数: percentile(col, array(p~1,\ [, p,2,]…))返回数值区域的一组百分比值分别对应的数值点。0<=P<=1,否则返回NULL,不支持浮点型数值。近似中位数函数: percentile_approx(col, p[, B]) -- 对频率数据取中位数 create table tmp_dm_op.temp_med_01399035 ( num string comment '数字' ,fre string comment '频率') insert into temp_med_01399035 (num,fre) values(1,2),(2,4)(4,4) -- 求中位数 select avg(num) as med from( select num ,sum(fre) over(order by num desc) as desc_sum_fre ,sum(fre) over(order by num asc) as asc_sum_fre from tmp_dm_op.temp_med_01399035)a join (select sum(fre) as sum_fre from tmp_dm_op.temp_med_01399035)b where a.desc_sum_fre >= b.sum_fre/2 and a.asc_sum_fre >= b.sum_fre/2 -- 使用分位数计算中位数，percentile只能传入int percentile_approx(变量，array(),B(精度)) percentile(变量,array()) 近似中位数函数: percentile_approx(col, array(p~1,\ [, p,2,]…[, B])直方图: histogram_numeric(col, b)一组数分为n组集合去重数：collect_set集合不去重函数：collect_list

十一、开窗函数

参考：https://docs.amazonaws.cn/redshift/latest/dg/r_WF_PERCENTILE_CONT.html

SUM — 注意,结果和ORDER BY相关,默认为升序 SELECT cookieid, createtime, pv, SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime) AS pv1, -- 默认为从起点到当前行 SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS pv2, --从起点到当前行，结果同pv1 SUM(pv) OVER(PARTITION BY cookieid) AS pv3, --分组内所有行 SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS pv4, --当前行+往前3行 SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING) AS pv5, --当前行+往前3行+往后1行 SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS pv6 ---当前行+往后所有行 FROM x1234; cookieid createtime pv pv1 pv2 pv3 pv4 pv5 pv6 ----------------------------------------------------------------------------- cookie1 2015-04-10 1 1 1 26 1 6 26 cookie1 2015-04-11 5 6 6 26 6 13 25 cookie1 2015-04-12 7 13 13 26 13 16 20 cookie1 2015-04-13 3 16 16 26 16 18 13 cookie1 2015-04-14 2 18 18 26 17 21 10 cookie1 2015-04-15 4 22 22 26 16 20 8 cookie1 2015-04-16 4 26 26 26 13 13 4 --------------------------------- 如果不指定ROWS BETWEEN,默认为从起点到当前行; 如果不指定ORDER BY，则将分组内所有值累加; 关键是理解ROWS BETWEEN含义,也叫做WINDOW子句： PRECEDING：往前 FOLLOWING：往后 CURRENT ROW：当前行 UNBOUNDED：起点， UNBOUNDED PRECEDING 表示从前面的起点， UNBOUNDED FOLLOWING：表示到后面的终点 –其他AVG，MIN，MAX，和SUM用法一样。 ntile() over() NTILE NTILE(n)，用于将分组数据按照顺序切分成n片，返回当前切片值 NTILE不支持ROWS BETWEEN，比如 NTILE(2) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) 如果切片不均匀，默认增加第一个切片的分布 select rank(1500) within group (order by salary desc) "rank of 1500" from employees 判断1500 在salary中排序是第几

十二、删除数据

保留表结构但删除表中所有数据：truncate table 表名；删除表：drop table if exists 表名删除分区：alter tab1 drop partition(partition_name = )

十三、正则表达式相关

---查询字符串中是否存在中文 select regexp_replace('7311W中文','[[^\\\u4e00-\\\u9fa5]]',''); ---查询字符串中是否存在字母 select regexp_replace('7311W', '[[^a-zA-Z]]', ''); ----查询字符串中是否存在数字 select regexp_replace('7311W','[[^0-9]]+',''); ---替换部分字符 select regexp_replace('2-755W1200',concat('[[^a-zA-Z]]','-'),'') limit 1; select regexp_replace('a2-755W1200','([a-zA-Z0-9]+)-','') select regexp_replace('a2-755W1200','([a-z]+|[A-Z]+|[0-9]+)-','') 替换特定符号之前的所有字母和数字 '([a-z]+|[0-9]+)$'表示所有小写字母或数字组合成的字符串 '([a-z]+|[A-Z]+|[0-9]+)' 这个加号表示一个或多个 括号表示是形成一个整体 -----这里的上角标是表示非， 字符 释义 转义 $ 匹配输入字符串结尾的位置。如果设置了 RegExp 对象的 Multiline 属性，那么 $ 还匹配 \n 或 \r 前面的位置。 若要匹配 $ 字符本身，请使用 \\$ ( ) 标记子表达式的开始和结束。可以捕获子表达式以供以后使用。 若要匹配这两个字符，请使用 \\( 和 \\) * 零次或多次匹配前面的字符或子表达式。 若要匹配 * 字符，请使用 \\* + 一次或多次匹配前面的字符或子表达式。 若要匹配 + 字符，请使用 \\+ . 匹配除换行符 \n 之外的任何单个字符。 若要匹配 .，请使用 \\. [ ] 标记中括号表达式的开始。 若要匹配这些字符，请使用 \\[ 和 \\] ? 零次或一次匹配前面的字符或子表达式，或指示“非贪心”限定符。 若要匹配 ? 字符，请使用 \\? \ 将下一字符标记为特殊字符、文本、反向引用或八进制转义符。 若要匹配 \ 字符，请使用 \\\ / 表示文本正则表达式的开始或结束。 若要匹配 / 字符，请使用 \\/ ^ 匹配输入字符串开始处的位置，但在中括号表达式中使用的情况除外，在那种情况下它对字符集求反。 若要匹配 ^ 字符本身，请使用 \\^ { } 标记限定符表达式的开始。 若要匹配这些字符，请使用 \\{ 和 \\} | 指出在两个项之间进行选择。 若要匹配 | ，请使用 \\|

十四、建表语句

drop table if exists tmp_dm_op.tmp_time_range; create table tmp_dm_op.tmp_time_range ( start_tm string comment '开始时间', end_tm string comment '结束时间', range_type string comment '归段类型（30或60分钟）' ) row format delimited fields terminated by '|'; --导入数据 load data inpath '/user/01379862/upload/time_range.txt' into table tmp_dm_op.tmp_time_range;

十五、调优设置

----跑数常用参数 set hive.auto.convert.join=true; set hive.mapjoin.smalltable.filesize=50000000; set mapred.queue.name=OPS; set mapred.job.queue.name=OPS; set hive.execution.engine=tez; set tez.queue.name=OPS; set hive.tez.container.size=10240; set hive.tez.java.opts=-Xmx8192m; set hive.fetch.task.conversion=more; --对简单查询不适用mapreduce,如*,直接的字段以及字段简单运算more;none是是会执行mapreduce set hive.cli.print.header=true; --是否显示表头 set hive.exec.reducers.max=400; --这个参数控制最大的reducer的数量，如果 input / bytes per reduce > max --则会启动这个参数所指定的reduce个数。这个并不会影响mapre.reduce.tasks参数的设置。默认的max是999 set hive.exec.compress.output=false; --输出结果不压缩 set hive.exec.compress.intermediate=true; --中间压缩 set hive.input.format =org.apache.hadoop.hive.ql.io.CombineHiveInputFormat; --在开启了org.apache.hadoop.hive.ql.io.CombineHiveInputFormat后， --一个data node节点上多个小文件会进行合并，合并文件数由mapred.max.split.size限制的大小决定 set mapred.max.split.size=100000000; --一个节点的合并文件数最大值 set mapred.min.split.size.per.node=100000000; --决定了多个data node上的文件是否需要合并~ set mapred.min.split.size.per.rack=100000000; --决定了多个交换机上的文件是否需要合并~ set hive.exec.parallel=true; --在同一个sql中的不同的job是否可以同时运行,默认为false set hive.ignore.mapjoin.hint=true; --默认值为true，是否忽略mapjoin hint即mapjoin标记 set hive.auto.convert.join = true; --;该参数为ture时，Hive自动对左边的表统计量，如果是小表就加入内存，即对小表用mapjoin set hive.groupby.skewindata = true; --决定 group by 操作是否支持倾斜数据，只能单个字段 set hive.support.concurrency=false; --关闭锁机制 set hive.exec.dynamic.partition.mode=nostrict; ---它的默认值是strick，即不允许分区列全部是动态的 set mapred.job.name=01399035; set mapreduce.job.queuename=root.OPS; --设置任务执行队列 set hive.auto.convert.join = true; --这样设置，hive就会自动的识别比较小的表，继而用mapJoin来实现两个表的联合 set hive.map.aggr=true --map端是否聚合 hive.mapred.mode=strict--where语句中含有分区字段来过滤条件限制数据范围； --要是使用order by，一定要使用LIMIT SET v_start_dt=case when '${v_start_dt}'='null' then add_months('$[time(yyyy-MM,-7d)]-01',-3) else '${v_start_dt}' end; SET v_end_dt=case when '${v_end_dt}'='null' then last_day(${hiveconf:v_start_dt}) else '${v_end_dt}' end; $[time(yyyyMMdd)] $[time(yyyyMMdd,-2d)] $[time(yyyy-MM-dd,-2d)] ----动态插入分区 set hive.exec.dynamic.partition.mode=nonstrict; set hive.exec.dynamic.partition=true; --开启动态分区 ----动态分区 set hive.exec.dynamic.partition.mode=nonstrict; set hive.exec.max.dynamic.partitions=100000; set hive.exec.max.dynamic.partitions.pernode=100000;