hive分位数 hive计算四分位

hive之表属性作

基本语法：

hive分位数 hive计算四分位

hive分位数 hive计算四分位

例：将表table_change_new中的字段name移到addr字段后面【name和addr都是string类型】

注：移动列位置的坑

上面的这种位置转移涉及到类型强制转换问题，

比如int不能被转换成string

尝试将字段【int类型】移动到addr后面，addr后面现在是name字段【string类型】

报当前列类型和现有位置的列name不相容的错误

例：将name放到列，现在name在第二列

例：创建表city，字段分隔符为#

将一份以tab作为字段分隔符的数据导入city，数据都被作为个字段的值

将字段分隔符改为tab

再次查看city表，每个字段值都被正常显示

例：创建分区表city，字段分隔符为#

将一份以tab作为字段分隔符的数据导入city，数据都被作为个字段的值， 分区表导入数据时要指定分区

查看此时的city表，数据都被赋给个字段

将字段分隔符改为tab

查看此时的字段分隔符参数， 发现还是#

修改分区表序列参数的坑：

注：如果修改serdeproperties参数的时候不加分区，那么字段分隔符参数field.delim会被改为t，但是还是没法把该分区的数据分隔开

分区等于201010的数据可以正常显示

再导入新的数据，设分区等于201110

发现新分区的数据无法按照分隔符tab显示

再导入新的数据，分区还是等于201010

此时的数据还是无法显示

再导入新数据，设分区等于201110

基本语法：

例：将内表city的location修改到/home/yy

此时/home/yy目录下有两个文件

此时city显示的/home/yy目录下的所有文件内容

内表转外表

外表转内表

2021-05-28-Hive-21(求取前10%的四种方法)

一般的求取前20%,就是使用两个窗口函数,如下获取

这种写法是一般的写法,当然也可以把count放在where那,来获取总的行数

hive中提供了一个非常有用的函数桶函数,把全部的数据分成5个桶获取个桶即可

percentile_approx / percentile -- spark

approx_percentile -- presto

kylin

percentile_approx(order_pr, array(0.1,0.5,0.75,0.95),9999) over(order by order_pr asc )

按照降序,获取中位数,和学过的窗口函数order by排序功能一样,局部的中位数

要想获取全局的中位数直接over()为空即可,在遇到数据倾斜可能失效,使用这个函数和前面的窗口写法获取的大不

这类小样本的问题是不能使用上面的三种方法的,可能有的分区只有几条数据,根本就不满足20%的条件,或者是由于样本太小,第20%不存在，还有一种就是特定需求

说一下这个需求的背景,销售排行榜上,我需要对四分位的销售者给予激励,这类销售者具备较大的潜力

有三种方法来解决这个问题,由于20%不存在,那么我们取20%-25%都是可以接受的,在窗口写法的基础上,排个序获取后几位即可,一种方法就是在窗口的写法上嵌套一层偏移量函数

HIVE常用正则表达式

一、校验数字的表达式

数字：^[0-9]$

n位的数字：^d{n}$

至少n位的数字：^d{n,}$

m-n位的数字：^d{m,n}$

零和非零开头的数字：^(0|[1-9][0-9])$

非零开头的多带两位小数的数字：^([1-9][0-9])+(.[0-9]{1,2})?$

带1-2位小数的正数或负数：^(-)?d+(.d{1,2})$

正数、负数、和小数：^(-|+)?d+(.d+)?$

有两位小数的正实数：^[0-9]+(.[0-9]{2})?$

有1~3位小数的正实数：^[0-9]+(.[0-9]{1,3})?$

非零的正整数：^[1-9]d$ 或 ^([1-9][0-9]){1,3}$ 或 ^+?[1-9][0-9]$

非零的负整数：^-[1-9][]0-9"$ 或 ^-[1-9]d$

非负整数：^d+$ 或 ^[1-9]d|0$

非正整数：^-[1-9]d|0$ 或 ^((-d+)|(0+))$

非负浮点数：^d+(.d+)?$ 或 ^[1-9]d.d|0.d[1-9]d|0?.0+|0$

非正浮点数：^((-d+(.d+)?)|(0+(.0+)?))$ 或 ^(-([1-9]d.d|0.d[1-9]d))|0?.0+|0$

正浮点数：^[1-9]d.d|0.d[1-9]d$ 或 ^(([0-9]+.[0-9][1-9][0-9])|([0-9][1-9][0-9].[0-9]+)|([0-9][1-9][0-9]))$

负浮点数：^-([1-9]d.d|0.d[1-9]d)$ 或 ^(-(([0-9]+.[0-9][1-9][0-9])|([0-9][1-9][0-9].[0-9]+)|([0-9][1-9][0-9])))$

浮点数：^(-?d+)(.d+)?$ 或 ^-?([1-9]d.d|0.d[1-9]d|0?.0+|0)$

校验字符的表达式

汉字：^[u4e00-u9fa5]{0,}$

英文和数字：^[A-Za-z0-9]+$ 或 ^[A-Za-z0-9]{4,40}$

长度为3-20的所有字符：^.{3,20}$

由26个英文字母组成的字符串：^[A-Za-z]+$

由26个大写英文字母组成的字符串：^[A-Z]+$

由26个小写英文字母组成的字符串：^[a-z]+$

由数字和26个英文字母组成的字符串：^[A-Za-z0-9]+$

由数字、26个英文字母或者下划线组成的字符串：^w+$ 或 ^w{3,20}$

中文、英文、数字包括下划线：^[u4E00-u9FA5A-Za-z0-9_]+$

中文、英文、数字但不包括下划线等符号：^[u4E00-u9FA5A-Za-z0-9]+$ 或 ^[u4E00-u9FA5A-Za-z0-9]{2,20}$

可以输入含有^%&',;=?$"等字符：[^%&',;=?$x22]+

禁止输入含有~的字符：[^~x22]+

三、特殊需求表达式

Email地址：^w+([-+.]w+)@w+([-.]w+).w+([-.]w+)$

域名：[a-zA-Z0-9][-a-zA-Z0-9]{0,62}(/.[a-zA-Z0-9][-a-zA-Z0-9]{0,62})+/.?

InternetURL：[a-zA-z]+://[^s] 或 ^

手机号码：^(13[0-9]|14[5|7]|15[0|1|2|3|5|6|7|8|9]|18[0|1|2|3|5|6|7|8|9])d{8}$

电话号码("XXX-XXXXXXX"、"XXXX-XXXXXXXX"、"XXX-XXXXXXX"、"XXX-XXXXXXXX"、"XXXXXXX"和"XXXXXXXX)：^((d{3,4}-)|d{3.4}-)?d{7,8}$

国内电话号码(0511-4405222、021-87888822)：d{3}-d{8}|d{4}-d{7}

电话号码正则表达式（支持手机号码，3-4位区号，7-8位直播号码，1－4位分机号）: ((d{11})|^((d{7,8})|(d{4}|d{3})-(d{7,8})|(d{4}|d{3})-(d{7,8})-(d{4}|d{3}|d{2}|d{1})|(d{7,8})-(d{4}|d{3}|d{2}|d{1}))$)

号(15位、18位数字)，一位是校验位，可能为数字或字符X：(^d{15}$)|(^d{18}$)|(^d{17}(d|X|x)$)

帐号是否合法(字母开头，允许5-16字节，允许字母数字下划线)：^[a-zA-Z][a-zA-Z0-9_]{4,15}$

密码(以字母开头，长度在6~18之间，只能包含字母、数字和下划线)：^[a-zA-Z]w{5,17}$

强密码(必须包含大小写字母和数字的组合，不能使用特殊字符，长度在 8-10 之间)：^(?=.d)(?=.[a-z])(?=.[A-Z])[a-zA-Z0-9]{8,10}$

强密码(必须包含大小写字母和数字的组合，可以使用特殊字符，长度在8-10之间)：^(?=.d)(?=.[a-z])(?=.[A-Z]).{8,10}$

日期格式：^d{4}-d{1,2}-d{1,2}

一年的12个月(01～09和1～12)：^(0?[1-9]|1[0-2])$

一个月的31天(01～09和1～31)：^((0?[1-9])|((1|2)[0-9])|30|31)$

钱的输入格式：

有四种钱的表示形式我们可以接受:"10000.00" 和 "10,000.00", 和没有 "分" 的 "10000" 和 "10,000"：^[1-9][0-9]$

这表示任意一个不以0开头的数字,但是,这也意味着一个字符"0"不通过,所以我们采用下面的形式：^(0|[1-9][0-9])$

一个0或者一个不以0开头的数字.我们还可以允许开头有一个负号：^(0|-?[1-9][0-9])$

这表示一个0或者一个可能为负的开头不为0的数字.让用户以0开头好了.把负号的也去掉,因为钱总不能是负的吧。下面我们要加的是说明可能的小数部分：^[0-9]+(.[0-9]+)?$

必须说明的是,小数点后面至少应该有1位数,所以"10."是不通过的,但是 "10" 和 "10.2" 是通过的：^[0-9]+(.[0-9]{2})?$

这样我们规定小数点后面必须有两位,如果你认为太苛刻了,可以这样：^[0-9]+(.[0-9]{1,2})?$

这样就允许用户只写一位小数.下面我们该考虑数字中的逗号了,我们可以这样：^[0-9]{1,3}(,[0-9]{3})(.[0-9]{1,2})?$

1到3个数字,后面跟着任意个 逗号+3个数字,逗号成为可选,而不是必须：^([0-9]+|[0-9]{1,3}(,[0-9]{3}))(.[0-9]{1,2})?$

备注：这就是终结果了,别忘了"+"可以用""替代如果你觉得空字符串也可以接受的话(奇怪,为什么?),别忘了在用函数时去掉去掉那个反斜杠,一般的错误都在这里

xml文件：^([a-zA-Z]+-?)+[a-zA-Z0-9]+.[x|X][m|M][l|L]$

中文字符的正则表达式：[u4e00-u9fa5]

双字节字符：[^x00-xff] (包括汉字在内，可以用来计算字符串的长度(一个双字节字符长度计2，ASCII字符计1))

空白行的正则表达式：nsr (可以用来删除空白行)

HTML标记的正则表达式：<(S?)[^>]>.?|<.? /> ( 首尾空白字符的正则表达式：^s|s$或(^s)|(s$) (可以用来删除行首行尾的空白字符(包括空格、制表符、换页符等等)，非常有用的表达式)

腾讯QQ号：[1-9][0-9]{4,} (腾讯QQ号从10000开始)

邮政编码：[1-9]d{5}(?!d) (邮政编码为6位数字)

IP地址：((?:(?:25[0-5]|2[0-4]d|[01]?d?d).){3}(?:25[0-5]|2[0-4]d|[01]?d?d))

Hive中常用的字符串作

创建虚表：

语法: length(string A)

返回值: int

说明：返回字符串A的长度

语法: rrse(string A)

返回值: string

说明：返回字符串A的反转结果

语法: concat(string A, string B…)

返回值: string

说明：返回输入字符串连接后的结果，支持任意个输入字符串

语法: concat_ws(string SEP, string A, string B…)

返回值: string

说明：返回输入字符串连接后的结果，SEP表示各个字符串间的分隔符

语法: substr(string A, int start),substring(string A, int start)

返回值: string

说明：返回字符串A从start位置到结尾的字符串

语法: substr(string A, int start, int len),substring(string A, int start, int len)

返回值: string

说明：返回字符串A从start位置开始，长度为len的字符串

语法: upper(string A) ucase(string A)

返回值: string

说明：返回字符串A的大写格式

语法: lower(string A) lcase(string A)

返回值: string

说明：返回字符串A的小写格式

语法: trim(string A)

返回值: string

说明：去除字符串两边的空格

语法: ltrim(string A)

返回值: string

说明：去除字符串左边的空格

语法: rtrim(string A)

返回值: string

说明：去除字符串右边的空格

语法: regexp_replace(string A, string B, string C)

返回值: string

说明：将字符串A中的符合ja正则表达式B的部分替换为C。注意，在有些情况下要使用转义字符,类似oracle中的regexp_replace函数。

语法: regexp_extract(string subject, string pattern, int index)

返回值: string

说明：将字符串subject按照pattern正则表达式的规则拆分，返回index指定的字符。

第三个参数:

0 是显示与之匹配的整个字符串

1 是显示个括号里面的

2 是显示第二个括号里面的字段

语法: parse_(string String, string partToExtract [, string keyToExtract])

返回值: string

说明：返回URL中指定的部分。partToExtract的有效值为：HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, and USERINFO.

语法: get_json_object(string json_string, string path)

返回值: string

说明：解析json的字符串json_string,返回path指定的内容。如果输入的json字符串无效，那么返回NULL。

语法: space(int n)

返回值: string

说明：返回长度为n的空字符串

语法: repeat(string str, int n)

返回值: string

说明：返回重复n次后的str字符串

语法: ascii(string str)

返回值: int

说明：返回字符串str个字符的ascii码

语法: lpad(string str, int len, string pad)

返回值: string

说明：将str进行用pad进行左补足到len位

语法: rpad(string str, int len, string pad)

返回值: string

说明：将str进行用pad进行右补足到len位

语法: split(string str, string pat)

返回值: array

说明: 按照pat字符串分割str，会返回分割后的字符串数组

语法: find_in_set(string str, string strList)

返回值: int

说明: 返回str在strlist次出现的位置，strlist是用逗号分割的字符串。如果没有找该str字符，则返回0

返回:int。substr在str中次出现的位置，若任何参数为null返回null，若substr不在str中返回0，Str中个字符的位置为1

说明：C1 被搜索的字符串

C2 希望搜索的字符串

I 搜索的开始位置,默认为1

J 出现的位置,默认为1

24、使用两个分隔符将文本拆分为键值对：str_to_map(text[, delimiter1, delimiter2])

返回：map

Delimiter1将文本分成K-V对，Delimiter2分割每个K-V对。对于delimiter1默认分隔符是','，对于delimiter2默认分隔符是'='

25、unix_timestamp() 返回当前时间戳。另外，current_timestamp() 也有同样作用。

unix_timestamp(string date) 返回 date 对应的时间戳，date 格式必须为 yyyy-MM-dd HH:mm:ss。

unix_timestamp(string date, string format) 返回 date 对应的时间戳，date 格式由 format 指定。

26、from_unixtime(int/bigint timestamp) 返回 timestamp 时间戳对应的日期，格式为 yyyy-MM-dd HH:mm:ss。

from_unixtime(int/bigint timestamp, string format) 返回 timestamp 时间戳对应的日期，格式由 format 指定。

如何解决hive同时计算多个分位数的问题

hive字符串函数 1. 字符串长度函数：length 语法: length(string A) 返回值: int 说明：返回字符串A的长度 举例： hive> select length('abcedfg') from lxw_dual; 7 2. 字符串反转函数：rrse 语法: rrse(string A) 返回值: string 说明：。

大数据之-HIVE入门（二十一）

percentile( BIGINT col,p) ：p为0-1之间的数，如0.75 代表，取在整个样本中75%分位的数据值。

percentile( BIGINT col,array(p1,p2))： array(p1,p2) 同时取整个样本中多个百分位的数据值。

percentile_approx（col,p,B) :B为精度，默认10000。其中p也可以为array类型，同中位数函数用法一样。