嘉兴做营销型网站设计,微博通 for wordpress,成都网站网页制作,天眼查询个人信息官网目录 一、数据类型
二、基础运算
三、字符串函数
1、字符串长度函数: length()
2、字符串反转函数#xff1a;reverse
3、字符串连接函数
4、字符串截取函数
5、字符串分割函数#xff1a;split
6、字符串查找函数
7、ascii
8、base64
9、character_length
10、c…目录 一、数据类型
二、基础运算
三、字符串函数
1、字符串长度函数: length()
2、字符串反转函数reverse
3、字符串连接函数
4、字符串截取函数
5、字符串分割函数split
6、字符串查找函数
7、ascii
8、base64
9、character_length
10、chr
11、decode
12、encode
13、sentences
14、ngrams
15、context_ngrams
16、elt
17、field
18、soundex
19、find_in_set
20、format_number
21、in_file
22、locate
23、lpad
24、rpad
25、ltrim
26、trim
27、rtrim
28、octet_length
29、parse_url
30、printf
31、repeat
32、replace
33、reverse
34、space
35、split
36、str_to_map
37、sustring_index
38、translate
39、unbase64
40、initcap
41、levenshtein
四、数学函数
1、round()
2、floor()
3、ceil()
4、rand()
5、exp()
6、pow()
7、sqrt()
8、abs()
9、acos()
10、asin()
11、atan()
12、bin()
13、ceiling
14、conv()
15、cbrt()
16、degrees()
17、radians()
18、factorial()
20、hex()
21、least()
22、ln()
23、bround()
24、log2()
25、log10()
26、log()
27、negative()
28、pi()
29、pmod()
30、positive()
31、rand()
32、round()
33、shiftleft()
34、shiftright()
35、shiftrightunsigned()
36、sign()
37、sin()
38、cos()
39、tan()
40、unhex()
41、width_bucket()
五、条件判断函数
1、if 函数
2、非空查找函数
3、条件判断函数
4、nvl(a,b)
5、nullif(a,b)
6、isnotnull
7、isnull
8、assert_true
六、正则表达式及解析函数
1、regexp_replace(str, regexp, rep)
2、regexp_extract(str, regexp[, idx])
3、parse_url。
4、get_json_object()。
5、rlike /regexp
6、like
七、日期函数
1.unix_timestamp()。
2、unix_timestamp(String date)
3、unix_timestamp(string date, string pattern)
4、from_unixtime
5、current_timestamp
6、to_date
7、日期转年/月/日/小时/分钟/秒/周函数
8、datediff
9、date_add
10、date_sub
11、extract
12、from_utc_timestamp
13、to_utc_timestamp
14、current_date
15、add_months
16、last_day
17、next_day
18、trunc
19、months_between
20、date_format
八、UDAF 函数(用户自定义聚合函数,user defined aggregation function.多对一的输入输出)
1、dense_rank()
2、rank()
3、row_number()
4、sum()
5、avg()
6、max()
7、min()
8、count()
9、ntile()
10、lag()
11、lead()
12、first_value()
13、last_value
14、variance
15、var_samp
16、stddev_pop
17、stddev_samp
18、covar_pop
19、covar_samp
20、corr
21、perccentile
22、percentile_approx
23、regr_avgx
24、regr_avgy
25、regr_count
26、regr_intercept
27、regr_r2
28、regr_slope
29、regr_sxy
30、regr_syy
31、regr_sxx
32、histogram_numeric
33、collect_set
34、collect_list
九、UDTF(User-Defined Table-Generating Functions)表生成函数
1、explode()
2、posexploed()
3、inline()
4、stack()
5、json_tuple()
6、parse_url()
十、集合类函数
1、grouping sets
2、grouping_id
3、cube
4、rollup
5、size
6、map_keys
7、map_values
8、array_contains
9、sort_array
十一、类型转换函数
1、cast()
2、binary() 一、数据类型
hive 的数据类型有原始数据类型和复杂类型原始类型包括 TINYINT,SMALLINT,INT,BIGINT,BOOLEAN,FLOAT,DOUBLE,STRING,BINARY,TIMESTAMP,DECIMAL,CHAR,VARCHAR,DATE。复杂类型包括 ARRAY,MAP,STRUCT,UNION这些复杂类型是由基础类型组成的。
二、基础运算
聚合函数 Hive 支持 count(),max(),min(),avg()等常用的聚合函数。 关系函数 , !, , , , , , 空值判断is null is not null. 数学运算支持所有的数值类型 , - , * , / % , | , ^, ~等。 逻辑运算逻辑与and逻辑或or逻辑非not。
三、字符串函数
1、字符串长度函数: length()
length(string a): 返回字符串 a 的长度 。
2、字符串反转函数reverse
reverse(string a) 返回字符串 a 的反转结果
3、字符串连接函数
1不带分隔符的字符串连接函数
concat() 语法:concat(string A, string B........)返回输入字符串连接后的结果支持任意个输入字符串 concat(string|binary A, string|binary B…) 将传入的字符串 A,B 合并为新的字符串concat(‘foo’, ‘bar’)结果为’foobar’
2带分隔符字符串连接函数
concat_ws() concat_ws(String SEP, string a, string b)返回输入字符串连接后的结果sep 表示各个字符串之间的分隔符。 concat_ws(string SEP, string A, string B…)或 concat_ws(string SEP, array) 与 concat 作用类似但是支持自定义的分隔符 SEP
4、字符串截取函数
1substr(string a, int start, int len) 返回字符串 a 从 start 位置开始长度为 len 的字符串。
2substring(string a, int start, int len) 返回字符串 a 从 start 位置开始长度为 len 的字符串。
5、字符串分割函数split
功能split(string str, string pat)
用法按照正则表达式 pat 拆分字符串 str。例如split(‘a,b,c,d’,’,’)返回[“a”,“b”,“c”,“d”]
6、字符串查找函数
功能instr(string str, string substr)
用法返回 str 中第一个 substr 的位置。如果其中 str 为 null则返回 null如果在 str 中找不到 substr则返回 0 。
7、ascii
用法ascii(string str)。
功能返回字符串的第一个字符的 ascii 数字。
8、base64
用法base64(binary bin)。
功能将二进制格式转成 base64 位的字符串返回值是字符串。
9、character_length
用法character_length(string str)。
功能返回 str 中包含的 UTF-8 字符数返回值为 int。
10、chr
用法chr(bigint 或 double num)。
功能返回指定数字对应的 ASCII 字符如果指定的数字大于 256将对该数字对 256 取模。
11、decode
用法decode(binary bin, string charset)
功能使用 charset 方式将二进制 bin 解码为字符串。支持的字符集有‘US-ASCII’, ‘ISO-8859-1’, ‘UTF-8’, ‘UTF-16BE’, ‘UTF-16LE’, ‘UTF-16’ 。
12、encode
用法:encode(str1,str2)
功能:将 str1 用 str2 设置的编码格式进行编码。
13、sentences
用法sentences(string str, string lang, string locale)
功能将 str 拆分成句子数组其中每个句子都是一个单词数组。‘lang’和’locale’参数是可选的如果省略则使用默认语言环境。
14、ngrams
用法ngrams(array arr,int n,int k)。
功能按 n 个单词出现频次倒序取 top k。
15、context_ngrams
用法context_ngrams(array, array, int K, int pf)
功能从一组标记化的句子中返回前 k 个文本。例如 select context_ngrams(sentences(‘hello word!hello hive,hi hive,hello hive’),array(‘hello’,null),4) from A会返回[{“ngram”:[“hive”],“estfrequency”:7141046.0},{“ngram”:[“word”],“estfrequency”:3570523.0}]即最经常在hello后出现的单词并统计其频次,有统计聚合作用。
16、elt
用法elt(N int,str1 string,str2 string,str3 string,…)
功能返回给定字符串集中的第 N 个若不存在则返回 null。例如 elt(2,‘hello’,‘world’) 返回’world’ 。
17、field
用法field(val T,val1 T,val2 T,val3 T,…)
功能返回 val 在 val1,val2…的位置。若不存在则返回 0。例如 field(‘world’,‘say’,‘hello’,‘world’)返回值为 3 。
18、soundex
用法soundex(string A)
功能返回字符串的 soundex 代码。例如soundex(‘Miller’)返回 M460
19、find_in_set
用法find_in_set(string str, string strList)
功能返回 str 在 strList 中的位置。若不存在或 str 中存在,则返回 0若 str 为 null则返回 null。
20、format_number
用法format_number(number x, int d)
功能将数字精确到 d 位小数例如 format_number(5.123456, 4)返回值为 5.1234。
21、in_file
用法in_file(string str, string filename)
功能如果 str 以整行的方式出现在 filename 中则返回 True。必须存在 filename 文件否则会报错 。
22、locate
用法locate(string substr, string str, int pos)
功能返回 substr 在 str 的 pos 位置后第一次出现的位置pos 非必传。例如 locate(‘a’, ‘abcabc’)返回 1locate(‘a’, ‘abcabc’,1)返回 1locate(‘a’, ‘abcabc’,2)返回 4。
23、lpad
用法lpad(string str, int len, string pad)
功能将 str 截取长度 len 位若 str 长度不足 len 则左侧用 pad 补充。例如 lpad(‘abcdef’, 5, ‘a’)返回’abcde’lpad(‘abc’, 5, ‘a’)返回’aaabc’。
24、rpad
用法rpad(string str, int len, string pad)
功能与 lpad 对应将 str 截取长度 len 位若 str 长度不足 len 则右用 pad 补充。例如 rpad(‘abcdef’, 5, ‘a’)返回’abcde’rpad(‘abc’, 5, ‘a’)返回’abcaa’。
25、ltrim
用法ltrim(string A)
功能删除字符串 A 左边的空格
26、trim
用法trim(string A)
功能返回出现在字符串 A 两端的空格。例如 trim(’ foo bar ‘)返回’foo bar’。
27、rtrim
用法rtrim(string A)
功能与 ltrim 对应删除字符串 A 右边的空格。
28、octet_length
用法octet_length(string str)
功能返回以 UTF-8 编码保存字符串 str 所需的八位字节数。
29、parse_url
用法parse_url(string urlString, string partToExtract [, string keyToExtract])
功能解析 url并返回需要抽取的部分可能抽取的部分 partToExtract 为 HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE。 例如parse_url(‘http://facebook.com/path1/p.php?k1v1k2v2#Ref1’, ‘HOST’) 返回 ‘http://facebook.com’
30、printf
用法printf(String format, Obj… args)
功能按照指定的 format 进行输出。例如 printf(“Hello World %d %s”, 100, “days”)返回值为Hello World 100 days”
31、repeat
用法repeat(string str, int n)
功能将字符串 str 重复 n 次。
32、replace
用法replace(string A, string OLD, string NEW) 。
功能将字符串 str 中的 OLD 子串替换为 NEW 。
33、reverse
用法reverse(string A)
功能对字符串 A 进行翻转
34、space
用法space(int n)
功能返回 n 个空格的字符串
35、split
用法split(string str, string pat)
功能按照正则表达式 pat 拆分字符串 str。例如split(‘a,b,c,d’,’,’)返回[“a”,“b”,“c”,“d”]
36、str_to_map
用法str_to_map(text, delimiter1, delimiter2)
功能将 text 分割为数个键值对。其中 delimiter1 和 delimiter2 为可选参数。delimiter1(默认为’:’)代表键 k 与值 v 的分隔符。delimiter2(默认为’,’)代表键值对 k-v 之间的分隔符。例如 str_to_map({“column1:1,column2:2”)返回值为{“column1”:“1”,“column2”:“2”}
37、sustring_index
用法substring_index(string A, string delim, int count)
功能delim 为分隔符返回 delim 将 A 分割后的前 count 部分。若 count 为负则返回分割的最后一部分。例如substring_index(‘http://www.apache.org’, ‘.’, 2) 返回 ‘www.apache’。
38、translate
用法translate(string|char|varchar input, string|char|varchar from, string|char|varchar to)
功能将 input 字符串中出现在 from 中的每个字符替换为 to 中的相应字符以后的字符串。 若 from 比 to 字符串长那么在 from 中比 to 中多出的字符将会被删除。与 PostgreSQL 中对应函数等价。例如 translate(‘abcdefga’,‘abc’,‘wo’)返回’wodefgw’。
39、unbase64
用法unbase64(string str)
功能将 64 位的字符串转换二进制值
40、initcap
用法initcap(string A)
功能返回字符串每个单词的第一个字母为大写所有其他字母为小写。单词由空格分隔。
41、levenshtein
用法levenshtein(string A, string B)
功能返回两个字符串之间的 Levenshtein 距离。例如 levenshtein(‘kitten’, ‘sitting’)返回值为 3。 (Levenshtein 距离又称编辑距离指的是两个字符串之间由一个转换成另一个所需的最少编辑操作次数。编辑操作包括将一个字符替换成另一个字符插入一个字符删除一个字符)
四、数学函数
1、round()
用法round(double a)。
功能返回值 bigint返回 double 类型的整数值部分(遵循四舍五入)。
用法round(double a, int d)。 功能返回值 double返回指定指定精度 d 的 double 类型。
2、floor()
用法floor(double a ),向下取整函数。
功能返回等于或者小于该 double 变量的最小的整数。
3、ceil()
用法ceil(double a)向上取整函数。
功能返回等于或者大于该 double 变量的最小的整数。同 ceiling。
4、rand()
用法取随机函数rand(int seed)。
功能返回一个 0 到 1 范围内的随机数。
5、exp()
用法自然指数函数exp(double a)。
功能返回自然对数 e 的 a 次方。
6、pow()
用法幂运算函数pow(double a, double p)。同 power()。
功能返回 a 的 p 次幂。
7、sqrt()
用法开平方函数sqrt(double a)。
功能返回 a 的平方根。
8、abs()
用法绝对值函数abs(x)。
功能返回 x 绝对值。
9、acos()
用法反余弦值acos(X)X 是数值型字符。
功能如果-1X1返回 X 的反余弦值。否则返回 NaN。
10、asin()
用法反余弦值asin(X)X 是数值型字符。
功能如果-1X1返回 X 的反正弦值。否则返回 NaN。
11、atan()
用法反正切值atan(X)X 是数值型字符。
功能返回 X 的反正切值。
12、bin()
用法bin(X)X 是整型。
功能返回 X 的二进制表示。
13、ceiling
用法ceiling(X)X 是数值型字符。
功能向上取整返回大于或等于 X 最小整数。
14、conv()
用法conv(X,from,to)X 是整型from、to 也是整型。
功能进制转换函数将 X 从 from 进制转换成 to 进制。
15、cbrt()
用法cbrt(DOUBLE a)。
功能返回类型 DOUBLE返回 a 的立方根。
16、degrees()
用法degrees(double/decimal a)。
功能将弧度 a 转化为角度。
17、radians()
用法radians(double/decimal a)
功能将角度 a 转化为弧度。
18、factorial()
用法factorial(INT a)。
功能返回类型是 BIGINT返回 a 的阶乘n!1×2×3×...×(n-1)×n 19、greatest() 语法greatest(T v1, T v2, …)。 功能返回 N 个数的最大值。
20、hex()
用法hex(X)X 可以是整型也可以是 string 型。
功能返回 X 的十六进制表示。
21、least()
用法least(T v1, T v2, …)。
功能返回 N 个数的最小值。
22、ln()
用法: ln(double a)。
功能: double 说明: 返回 a 的自然对数。
23、bround()
用法bround(double a)。
功能银行家舍入法四舍六入五成双。返回数字 a 高斯舍入后的值高斯舍入也称银行家舍入。简单的说就是四舍六入五考虑五后非空就进一五后为空看奇偶五前为偶应舍去五前为奇要进一。 银行家舍入法1~4舍6~9进5-前位数是偶舍5-前位数是奇进 bround(DOUBLE a)
24、log2()
用法log2(X)X 是数值型字符。
功能返回以 2 为底的 X 的对数。
25、log10()
用法log10(X)X 是数值型字符。
功能返回以 10 为底的 X 的对数。
26、log()
用法log(X,Y)X、Y 是数值型字符。
功能返回以 X 为底的 Y 的对数。
27、negative()
用法negative(X)X 是数值型字符。
功能返回 X 的相反数。
28、pi()
用法法: pi()数学常数 π。
功能返回值是 double 类型。
29、pmod()
用法pmod(X,Y)X,Y 是数值型字符。
功能返回 X 除以 Y 的余数。
30、positive()
用法positive(X)X 是任意字符。
功能返回 X 本身。
31、rand()
用法rand(X)X 是整型。
功能返回 0 到 1 之间的随机数。
32、round()
用法round(X,Y)X 是数值型字符Y 是整型。
功能对 X 进行四舍五入Y 是要保留的小数位数Y 如果省略则表示对 X 进行四舍五入取整。
33、shiftleft()
用法shiftleft(BIGINT a, INT b)。
功能返回类型INT or BIGINT按位左移。
34、shiftright()
用法shiftright(BIGINT a, INT b)。
功能返回类型 INT or BIGINT按位右移。
35、shiftrightunsigned()
用法shiftrightunsigned(BIGINT a, INT b)。
功能返回类型 DOUBLE按位无符号右移。
36、sign()
用法SIGN(number)。
功能SIGN 函数用于返回数字的符号。当数字大于 0 时返回 1等于 0 时返回 0小于 0 时返回 -1。
37、sin()
用法sin(X)X 是数值型字符。
功能返回 X 的正弦值X 是弧度制。
38、cos()
用法cos(X)X 是数值型字符。
功能返回 X 的余弦值X 是弧度制。
39、tan()
用法tan(X)X 是数值型字符。
功能返回 X 的正切值X 是弧度制。
40、unhex()
用法unhex(X)X 是 string 型。
功能X 代表十六进制的字串返回此十六进制的二进制表示。
41、width_bucket()
用法width_bucket(NUMERIC expr, NUMERIC min_value, NUMERIC max_value, INT num_buckets)。
功能分桶值按 min_value/max_value 创建 num_buckets1 个相同大小的桶返回当前值所在的桶编号。
五、条件判断函数
1、if 函数
if(boolean testCondition,T valueTrue, T valueFalseOrNull) 当条件 testCondition 为 True 时候返回 valueTrue,否则返回 valueFalseOrNull
2、非空查找函数
coalesce coalesce(T v1, T v2,.....)返回参数中第一个非空值如果所有值都为 null那么返回 Null。
3、条件判断函数
case when case when 写法一 case sex when ‘1’ then ‘男’ when ‘2’ then ‘女’ else ‘其他’ end
case when 写法二 case when sex ‘1’ then ‘男’ when sex ‘2’ then ‘女’ else ‘其他’ end
4、nvl(a,b)
判空函数若 a 为空则返回 b,否则为 a
5、nullif(a,b)
若 ab 时返回 null,否则为 a
6、isnotnull
isnull 判断输入参数是否不为空不为空返回 ture反之返回 false
7、isnull
isnull 判断输入参数是否为空为空返回 ture反之返回 false
8、assert_true
assert_true(BOOLEAN condition) 如果 condition 不为 true则抛出异常否则返回 null。
六、正则表达式及解析函数
1、regexp_replace(str, regexp, rep)
将字符串 str 中符合正则表达式 regexp 的部分替换成字符串 rep.
样例select regexp_replace(‘fooball’, ‘oo|al’ , ‘’) from table_Name; regexp_extract(string subject, string pattern, int index) 返回字符串 subject 与正则表达式 pattern 匹配后的 index 部分。
例如 regexp_extract(‘100-300’, ‘(d)-(d)’, 2) 的返回值为 300
2、regexp_extract(str, regexp[, idx])
将字符串 str 按照正则表达式 regexp 的规则拆分返回 idx 指定位置的字符。
样例select regexp_extract(‘foothebar’, ‘foo(.*?(bar)’, 1) from tableName; regexp_replace(string INITIAL_STRING, string PATTERN, string REPLACEMENT) 将字符串 subject 与正则表达式 pattern 匹配上部分用 REPLACEMENT 进行替换并返回。
例如 regexp_replace(‘100-200’, ‘(d)’, ‘num’)返回值为‘num-num’
3、parse_url。
url 解析函数返回 url 中指定的部分。
样例parse_url(string urlString, string partToExtract [, string keyToExtract] )
4、get_json_object()。
json 解析get_json_object(string json_string, string path) 样例select get_json_object(‘{......}’, ‘$.owner’) from tableName;
5、rlike /regexp
(A)str rlike (B)能否用 B 去正则匹配 A 的内容。
6、like
(A)str like (B)pattern能否用 B 去完全匹配 A 的内容。 7、regexp 功能语法同 rlike 一样只是名字不同。
七、日期函数
1.unix_timestamp()。
用法unix_timestamp(); 返回值bigint ,获得当前时区的 unix 时间戳
2、unix_timestamp(String date)
用法unix_timestamp(String date)
功能将格式为”yyyy-MM-dd HH:ss”的日期转换到 unix 时间戳返回 bigint 如果转换失败则返回 0.
3、unix_timestamp(string date, string pattern)
用法unix_timestamp(string date, string pattern)
功能转换 pattern 格式日期到 unix 时间戳。如果转化失败则返回 0. 样例select unix_timestamp(‘2021-03-08 14:21:11’,’yyyy-MM-dd HH:mm:ss’) from table
4、from_unixtime
用法from_unixtime(bigint unixtimestring pattern)
功能unix 时间戳转日期函数返回值 string把具体的秒转化为时间日期。
5、current_timestamp
用法current_timestamp()
功能获取当前的时间精确到毫秒样例:select current_timestamp() -- 2011-09-02 10:11:09.234212000
6、to_date
用法to_datestring timestamp
功能日期时间转日期函数年月日时分秒只取其中的年月日部分。
7、日期转年/月/日/小时/分钟/秒/周函数
1日期转年函数 year()
用法year(String date)
功能返回值为 int,返回日期中的年。样例select year(‘2021-03-21 10:11:02’) from table
2日期转月函数 month(STRING date)
用法unix 时间戳转日期函数
功能返回日期中的月
3日期转天函数 day(STRING date)
用法返回日期中的天。
功能返回类型 int。
4日期转小时函数:hour(STRING date)
用法返回日期中小时函数。
功能返回类型 int。
5日期转分钟函数 minute(STRING date)
用法返回日期中的分钟。
功能返回类型 int。
6日期转秒函数 second(STRING date)
用法返回日期中的秒。
功能返回类型 int。
7日期转周函数 weekofyear,weekofyear(string date)
用法返回日期在当前的周数。
功能返回值为 int。
8、datediff
用法datediff(string enddate, string startdate)
功能返回值 int返回结束日期减去开始日期。
9、date_add
用法date_add(string startdate, int days)
功能返回值 string日期增加函数返回开始日期 startdate 增加 days 天的日期。
10、date_sub
用法date_sub(string startdate, int days)
功能返回值 string. 日期减少函数返回开始日期 startdate 减少 days 天后的日期。
11、extract
用法extract(date,integer)
功能EXTRACT(year from 2015-01-15),抽取日期类型中的年/月/日。
12、from_utc_timestamp
用法from_utc_timestamp(timestamp, timezone)
功能把 UTC 时间转换成 timezone 时间。
13、to_utc_timestamp
用法to_utc_timestamp(timestamp, timezone)
功能将 timestamp 转换成 UTC 时间。
14、current_date
用法:current_date()
功能当前日期 。
15、add_months
用法add_months(date,integer)
功能返回 date 加上 integer 个月后的日期。
16、last_day
用法last_day(date)。
功能返回某个月的最后一天。
17、next_day
用法next_day(start_date,day_of_week)。
功能求当前日期的下一个周几。 next_day(current_date(),mo)。
18、trunc
用法trunc(date,fmt)
功能date:日期时间类型 fmt:MONTH/MON/MM OR YEAR/YYYY/YY 截断指定格式后补初始时间如果为 YEAR 则返回 year(date)-01-01,如果为 MM 则返回 year(date)month(date)-01
19、months_between
用法months_between(‘date1’,‘date2’)。
功能返回两个日期之间的月份差。
20、date_format
用法date_format(date, format)
功能date 参数是合法的日期format 参数是规定日期输出的格式。
八、UDAF 函数(用户自定义聚合函数,user defined aggregation function.多对一的输入输出)
分析函数名(参数) OVER (PARTITION BY 子句 ORDER BY 子句 ROWS/RANGE 子句)。 即由以下三部分组成
分析函数名 如 sum、max、min、count、avg 等聚集函数以及 lead、lag 行比较函数等
over 关键字表示前面的函数是分析函数不是普通的集合函数
分析子句 over 关键字后面挂号内的内容 分析子句又由下面三部分组成 partition by 分组子句表示分析函数的计算范围不同的组互不相干 ORDER BY 排序子句表示分组后组内的排序方式 ROWS/RANGE窗口子句是在分组(PARTITION BY)后组内的子分组也称窗口此时分析函数的计算范围窗口而不是 PARTITON。窗口有两种ROWS 和 RANGE
1、dense_rank()
用法dense_rank()。
功能有并列相同名次不空位总数会变。122345
2、rank()
用法rank()
功能有并列相同名次空位总数不会变。12245
3、row_number()
用法row_number()
功能没有并列相同名次按顺序排同分不同名
4、sum()
用法sum(column) 。
功能用于计算指定列的总和。
5、avg()
用法avg(column)
功能用于计算指定列的平均值。
6、max()
用法max(column)。
功能用于计算指定列的最大值。
7、min()
用法min(column) 。
功能用于计算指定列的最大值。
8、count()
用法count(column)
功能用于计算指定列的最大值。
9、ntile()
用法NTILE(n)。
功能用于将分组数据按照顺序切分成 n 片返回当前切片值如果切片不均匀默认增加第一个切片的分布
10、lag()
用法lag(exp_str,offset,defval) over(partion by ..order by …)
功能可以在 Hive 中轻松地查找前一行或前 N 行的数据如果没有行则返回 null。
11、lead()
用法lead(exp_str,offset,defval) over(partion by ..order by …)
功能可以在 Hive 中轻松地查找后一行或后 N 行的数据如果没有行则返回 null。
12、first_value()
用法first_value() over(partion by ..order by …)
功能取分组内排序后截止到当前行第一个值。
13、last_value
用法last_value() over(partion by ..order by …)
功能取分组内排序后截止到当前行最后一个值。
14、variance
用法variance(col)
功能:返回组内查询列的方差也可称为总体方差也可写成 var_pop(col)。
15、var_samp
用法var_samp(col)
功能返回组内查询列方差的无偏估计方差无偏估计中因为估计期望损失了一个自由度估计的分母为 n-1也可称为样本方差。
16、stddev_pop
用法stddev_pop(col)
功能返回组内查询列的标准差
17、stddev_samp
用法stddev_samp(col)
功能返回组内查询列标准差的无偏估计方差无偏估计中因为估计期望损失了一个自由度估计的分母为 n-1
18、covar_pop
用法covar_pop(col1, col2)
功能返回组内查询列 col1 和 col2 的总体协方差。
19、covar_samp
用法:covar_samp(col1, col2)
功能返回组内查询列 col1 和 col2 的样本协方差
20、corr
用法corr(col1, col2)
功能返回组内查询列 col1 和 col2 的相关系数 。
21、perccentile
用法percentile(BIGINT col, p)
功能返回组内查询整数列 col 所在的分位数p 可以为浮点数或数组且其中元素大小必须在 0-1 之间。若 col 不是整数需使用 percentile_approx 。
22、percentile_approx
用法percentile_approx(DOUBLE col, array(p1[, p2]…) [, B])
功能返回组内查询列 col 所在的分位数p 可以为浮点数或数组且其中元素大小必须在 0-1 之间。B 为可选参数为精度控制参数
23、regr_avgx
用法regr_avgx(independent, dependent)
功能:计算自变量的平均值。该函数将任意一对数字类型作为参数并返回一个 double。任何具有 null 的对都将被忽略。如果应用于空集返回 null。否则它计算以下内容avg(dependent)
24、regr_avgy
用法regr_avgy(independent, dependent) 。
功能计算因变量的平均值。该函数将任意一对数字类型作为参数并返回一个 double。任何具有 null 的对都将被忽略。如果应用于空集返回 null。否则它计算以下内容avg(independent)。
25、regr_count
用法regr_count(independent, dependent)
功能返回 independent 和 dependent 都非空的对数 。
26、regr_intercept
用法regr_intercept(independent, dependent)
功能返回线性回归的截距项 。
27、regr_r2
用法regr_r2(independent, dependent)
功能返回线性回归的判决系数R 方coefficient of determination
28、regr_slope
用法regr_slope(independent, dependent)
功能返回线性回归的斜率系数 。
29、regr_sxy
用法regr_sxy( [ALL | DISTINCT] yExpr, xExpr) [FILTER ( WHERE cond ) ]
参数说明yExpr一个数值表达式因变量。
xExpr一个数值表达式自变量。
cond一个可选的布尔表达式可筛选用于函数的行。
功能返回根据 xExpr 和 yExpr 不为 NULL 的组的值计算出的 yExpr 和 xExpr 乘积的和。
30、regr_syy
用法regr_syy( [ALL | DISTINCT] yExpr, xExpr) [FILTER ( WHERE cond ) ]
参数说明yExpr一个数值表达式因变量。
xExpr一个数值表达式自变量。
cond一个可选的布尔表达式可筛选用于函数的行。
功能返回 xExpr 和 yExpr 不为 NULL 的组中 yExpr 值的平方和。
31、regr_sxx
用法regr_sxx( [ALL | DISTINCT] yExpr, xExpr) [FILTER ( WHERE cond ) ]
参数说明yExpr一个数值表达式因变量。
xExpr一个数值表达式自变量。
cond一个可选的布尔表达式可筛选用于函数的行。
功能返回 xExpr 和 yExpr 不为 NULL 的组中 xExpr 值的平方和。
32、histogram_numeric
用法histogram_numeric(col, b)
功能用于画直方图。返回一个长度为 b 的数组数组中元素为(x,y)形式的键值对x 代表了直方图中该柱形的中心y 代表可其高度。
33、collect_set
用法collect_set(col)
功能返回查询列 col 去重后的集合与 distinct 不同distinct 查询结果为一列数据collect_set 查询后结果为一个集合形式的元素
34、collect_list
用法collect_list(col)
功能返回查询列 col 的列表
九、UDTF(User-Defined Table-Generating Functions)表生成函数
1、explode()
用法explode(col)。
功能explode()函数可以将数组array 类型的元素分隔成多行或将映射map 类型的元素分隔为多行和多列。
2、posexploed()
用法posexplode(col)。
功能posexplode 只能用于 array。
3、inline()
用法inline(ARRAY)
功能inline 一般结合 lateral view 使用
4、stack()
用法stack(int r,T1 V1,...,Tn/r Vn)
功能即分解 n 个值 V1…Vn 转化成 r 行。每一行将有 n/r 列向上取整。
5、json_tuple()
用法json_tuple(string jsonStr, string key1, string key2, …)
功能返回 key1,key2 键对应的值。
6、parse_url()
用法parse_url(URLparts[HOST/PATH/QUERY])
功能parse_url 函数是 Hive 中提供的最基本的 url 解析函数可以根据指定的参数从 url 解析出对应的参数值进行返回函数为普通的一对一函数类型。
十、集合类函数
1、grouping sets
用法group by k1,k2,k3 grouping sets((组合 1),(组合 2),(组合 3)) 对于 grouping sets 后的组合应该是 group by 后的子集没有使用到的 group by 字段会自动使用 null 值填充 功能根据不同的维度组合进行聚合等价于将不同维度的 GROUP BY 结果集进行 UNION ALL 对于多个维度聚合问题grouping sets 不用像 cube 方式将分组字段排列组合列出全部维度的结果能够实现更灵活的组合。
2、grouping_id
用法grouping sets 中的每一种粒度都对应唯一的 groupingid 值其计算公式与 group by 的顺序、当前粒度的字段有关。
功能它是根据 group by 后面声明的顺序字段是否存在于当前 group by 中的一个二进制位组合数据,若组合中出现即为 1反正则为 0group by 后字段先出现的放在最低位依次排开比如 group by class,sex,course,则二进制的顺序为course sex class ,grouping sets 字段出现则为 1反之则为 0 比如(class, course), 二进制为 101十进制则为 5则 groupingid 为 5同理 grouping__id 为 6则组合为sex,course二进制为 110。
3、cube
用法为指定表达式集的每个可能组合创建分组集。首先会对(A、B、C)进行 group by然后依次是(A、B)(A、C)(A)(B、C)(B)(C)最后对全表进行 group by 操作。
功能cube 简称数据魔方可以实现 hive 多个任意维度的查询cube(a,b,c)则首先会对(a,b,c)进行 group by然后依次是(a,b),(a,c),(a),(b,c),(b),(c)最后在对全表进行 group bycube 会统计所选列中值的所有组合的聚合。
4、rollup
用法rollup 的含义是卷曲的意思顾名思义就是会从右向左的组合字段得到聚合结果。 group by A,B,C with rollup 首先会对(A、B、C)进行 group by然后对(A、B)进行 group by然后是(A)进行 group by最后对全表进行 group by 操作。可以看出 group by A,B,C with rollup是上述几种 group by 的并集。 功能 rollup 可以实现从右到做递减多级的统计显示统计某一层次结构的聚合。
5、size
用法size(Map/ARRAY)
功能返回数组类型中的元素数。
6、map_keys
用法map_values(Map)
功能返回一个无序数组该数组包含输入映射的键。
7、map_values
用法:返回一个无序数组该数组包含输入映射的值。
功能map_values(Map)。
8、array_contains
用法array_contains(Array, value)。
功能如果数组包含值则返回 TRUE。
9、sort_array
用法sort_array(array, [asc|desc])
功能它可以根据指定的排序规则对数组进行排序并返回一个排好序的新数组。
十一、类型转换函数
1、cast()
用法cast (字段名 as 转换的类型)
功能基础类型之间强制转换。
2、binary()
用法binary(string|binary)。
功能binary将 string 类型转换为二进制。
