HQL函数大全
HQL函数大全
一、关系运算
- 等值比较: =
- 等值比较: <=>
- 不等值比较: <>和!=
- 小于比较: <
- 小于等于比较: <=
- 大于比较: >
- 大于等于比较: >=
- 区间比较: between and
- 空值判断: IS NULL
- 非空判断: IS NOT NULL
- like比较: 使用通配符 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）查询数据 列名.name - array结构
（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
- 反余弦函数: acos
- positive函数: positive
- negative函数(取反): 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:mm:ss’)
- 获取当前UNIX时间戳函数: unix_timestamp()
- 日期转UNIX时间戳函数: to_unix_timestamp(‘2017-01-01 12:12:12’,‘yyyy-MM-dd HH:mm: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码函数：ascii
- base64字符串：
- 字符串连接函数：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_url
- json解析函数：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;