Hive函数基础，大数据，实施工程师

一、Hive基础
1、Hive作用
（1）可以将结构化的数据文件映射为一张数据库表，并提供完整的sql查询功能
（2）可以将sql语句转换为MapReduce任务进行运行

2、Hive数据类型
（1）列类型
整型、字符串类型、时间戳、日期、小数点、联合类型
（2）文字型
浮点类型、十进制类型
（3）Null 值型
Null 
（4）复杂类型
数组、映射、结构体

BDP字段类型：数值、文本、日期

3、SELECT基础语法
（1）使用ALL和DISTINCT选项区分对重复记录的处理。默认是ALL，表示查询所有记录DISTINCT表示去掉重复的记录
（2）Where 条件 类似我们传统SQL的where 条件
（3）ORDER BY 全局排序，只有一个Reduce任务
（4）SORT BY 只在本机做排序
（5）LIMIT限制输出的个数和输出起始位置

4、BDP中Hsql的基本语法
（1）OUTPUT\TEMP这两个关键字一定要大写；
（2）字段的别名需要用AS连接；
（3）表的别名不要用AS连接；
（4）函数使用的时候一定要将字段进行设置别名；
（5）OUTPUT在整个语句中只允许出现一次，在最后一条输出内容中使用；
（6）“#”用作语句的注释；
（7）不支持使用select * 的方式作为查询字段的内容，使用表中的插入所有字段；
（8）写SQL的时候尽量使用左侧表、字段列表，进行点选的方式。

二、关系运算
1、关系运算
主要包含=、<> 、is null、 like 等操作
2、数学运算
+、-、*、/、round等
3、逻辑运算
And 、or、not操作
4、日期函数运算
时间戳函数、to_date 函数、取日期细维度函数等
5、条件函数
if、case、COALESCE
6、字符串函数
Length、concat、trim、lapd等
7、汇总统计函数
count、sum、avg、min、max_date等

注：
（1）关系运算返回的结果为布尔型
（2）like函数中“%”代表任意数量字符、“_”代表任意单个字符
（3）字符串的比较要注意：如果是日期类型的话，可以先转化成date类型再进行比较

关系运算null
1、NULL 值代表遗漏的未知数据。默认地，表的列可以存放 NULL 值。
2、如果表中的某个列是可选的，那么我们可以在不向该列添加值的情况下插入新记录或更新已有
的记录。这意味着该字段将以 NULL 值保存。
3、NULL 值的处理方式与其他值不同。
4、NULL 用作未知的或不适用的值的占位符。
5、无法比较 NULL 和 0；它们是不等价的。
6、无法使用比较运算符来测试 NULL 值，比如 =、< 或 <>。只有IS NULL 和 IS NOT NULL 操作符。

sql之null、空字符串、0的区别
（1）'' 表示空字符串，判断'' 用 ='' 或 <>'' , 
（2）null表示空值，数值未知，没有两个相等的空值，判断用 is null 或 is not null 
（3）0表示值为‘0’
举例：查询姓名不是张三的数据
SELECT SID, SNAME, SCITY FROM Stu1 WHERE SNAME <> ‘张三’ OR SNAME IS NULL;
注：
（1）姓名不是张三有两种情况，第一是其他姓名，第二是姓名为空
（2）SQL的表达式，除了IS NULL和NOT NULL以外，只要出现NULL值结果都为FALSE

关系运算-Like
通配符可用于替代字符串中的任何其他字符。在 SQL 中，通配符与 SQL LIKE 操作符一起
使用。SQL 通配符用于搜索表中的数据。
（1）“_”表示任意单个字符
（2）字符”%”表示任意数量（0 个或多个）的字符。
Eg.
'%a' //以a结尾的数据
'a%' //以a开头的数据
'%a%' //含有a的数据
‘_a_’ //三位且中间字母是a的
'_a' //两位且结尾字母是a的
'a_' //两位且开头字母是a的

否定用法：
A NOT LIKE B 
NOT A LIKE B

三、条件运算
常用到的条件函数： If函数、非空查找函数: COALESCE、条件判断函数：CASE when

1、if函数标准格式：if(b>c,a1,a2)；第一个参数必须返回的布尔型，第二个参数为当第一个参数为true的时候该if函数的反馈结果，否则反馈结果为第三个参数。
2、case when 的格式：case when b>c then a1 when c>d then a2 else a3 end；when
关键字后面跟的必须是布尔型，then后面跟的内容为当当前布尔型反馈结果为ture的时候该case表达式返回的结果。如果所有的when后面的条件都不成立，则返回else后面的内容作为结果。
3、coalesce函数格式：coalesce(b,a,c,d)；按照bacd顺序，取第一个非空的值。如果所有参数都为NULL，COALESCE函数将返回NULL。

条件函数if
语法：IF( expr1 , expr2 , expr3 )
#expr1 的值为 TRUE，则返回值为 expr2 
#expr1 的值为FALSE，则返回值为 expr3

举例：查找出售价为 50 的书，如果是 java 书的话，就要标注为 已售完
select *, if(book_name='java','已卖完','有货') as product_status from book where price =50

非空查找函数-COALESCE
语法：COALESCE(expression_1, expression_2, ...,expression_n)
举例
select 姓名, COALESCE(电话1, 电话2, 电话3) as 联系方式 from 重点人信息
#依次参考各参数表达式，遇到非null值即停止并返回该值。如果所有的表达式都是空值，最终将返回
一个空值。

四、数值运算
1、常用到的数学运算函数：加法操作: +、减法操作: -、乘法操作: *、除法操作: /、求余操作：%
2、int + int 一般结果为int类型，而int + double 一般结果为double类型，即结果的数值类型等于A
的类型和B的类型的最小父类型
3、如果A乘以B的结果超过默认结果类型的数值范围，则需要通过cast将结果转换成范围更大的数值类型
4、hive中最高精度的数据类型是double,只精确到小数点后16位
5、精度在hive中是个很大的问题，类似这样的操作最好通过round指定精度，但是在BDP中前端展现
的时候可以对位数进行调整，所以我们在此不太需要关注。

数值计算函数
1、round（）
语法：round(DOUBLE d)返回DOUBLE型的d的BIGINT类型的近似值
round(DOUBLE d，INT) : 返回DOUBLE型的d的保留n位小数的DOUBLE类型的近似值四舍五入截取
2、cast（）
语法：cast(column_name as decimal(10,2)) cast函数四舍五入（没有精度损失）
举例select Cast(3.1953 as decimal(20,2)) from lxw_dual;   结果：3.20

3、floor（）
语法：floor(double a) 返回等于或者小于该double变量的最大的整数
举例select floor(3.1415926) from lxw_dual;  结果：3

4、ceil（）
语法：ceil(double a)返回等于或者大于该double变量的最小的整数
举例select ceil(3.1415926) from lxw_dual;  结果：4
注：以上函数实际使用时需要用as给结果设置别名
如：select ceil(3.1415926) as test from lxw_dual;

逻辑运算
1、常用到的逻辑运算函数：逻辑与操作: AND、逻辑或操作: OR、逻辑非操作: NOT
2、逻辑运算的的操作类型为布尔型
3、and和or不能并列使用，同一层级只能使用and或者or中的一个，不能同时使用。
4、尽管and、or、not存在优先级，但是在实际的使用过程中，最好用“括号”来表示优先
级。

五、常用字符串函数
1、字符串长度函数length
语法：length(string A) 返回字符串A的长度
举例：select length('abcedfg') as 别名 from lxw_dual;  结果：7

2、字符串连接函数 concat
语法：concat(string A, string B…)返回输入字符串连接后的结果，支持任意个输入字符串
举例select concat(‘abc’,'def’,'gh’) from lxw_dual; 结果：abcdefgh
注：使用CONCAT(str1,str2,...)函数拼接字符串的过程中，如果你拼接的字段当中有值为null，那么拼接的结果就为null。
解决方法：
1、把表字段值为null 的 update为空字符串 (不推荐且仅适用于字段类型为字符型)
2、用ifnull(字段1,'value')函数
Concat(ifnull(区名称, ''), ifnull(街道名称, ''), ifnull(社区名称,''))

3、字符串连接函数 concat_ws
语法：concat_ws(string SEP, string A, string B…) as
返回输入字符串连接后的结果，SEP表示各个字符串间的分隔符
举例：select concat_ws(',','abc','def','gh') from lxw_dual;
结果：abc,def,gh

4、字符串截取函数 substr,substring
4.1
语法：substr(string A, int start)
substring(string A, int start)
返回字符串A从start位置到结尾的字符串
举例：select substr('abcde',3) from lxw_dual;  结果：cde
select substring('abcde',3) from lxw_dual;  结果：cde
问题：字符串截取函数substr,substring开始截取位置大于字符串长度会如何，比如substr("张三",5)？
答：结果为空

4.2
语法：
substr(string A, int start, int len)
substring(string A, int start, int len)
返回字符串A从start位置开始，长度为len的字符串
举例：
select substr('abcde',3,2) from lxw_dual;  结果：cd
select substring('abcde',3,2) from lxw_dual;  结果：cd

日期转换
举例：
（1）20171205转成2017-12-05 
思路：用substr将日期截取为为年月日三部分，然后用concat将三部分用“-”连接起来。
select concat(substr('20171205',1,4),'-',substr('20171205',5,2),'-',substr('20171205',7,2)) from dual;

（2）2017-12-05转成20171205
select concat(substr('2017-12-05',1,4),substr('2017-12-05',6,2),substr('2017-12-05',9,2)) from dual;

5、字符串转大写函数 upper,ucase
语法：
upper(string A) 
ucase(string A)
返回字符串A的大写格式
举例：select upper('abSEd') from lxw_dual;  结果：ABSED
select ucase('abSEd') from lxw_dual;  结果：ABSED

6、去空格函数 trim，ltrim，rtrim
语法 ：
trim(string A) 去除字符串两边的空格
ltrim(string A) 去除字符串左边的空格
rtrim(string A) 去除字符串右边的空格
举例：
select trim(' abc ') from lxw_dual;  结果：Abc
select ltrim(' abc') from lxw_dual;  结果：abc
select rtrim('abc ') from lxw_dual;  结果：abc

7、查找函数 instring

语法：
instring(字符串1, 字符串2[, 起始位置[, 正序逆序, 匹配次数]])
返回字符串2在字符串1的起始位置后第一次出现的位置
其中起始位置可选，默认为1，起始位置为负时表示逆序第几个字符串开始搜索
正序逆序为0时从前往后查询，为1时从后往前查找
匹配次数如果为2，表示第二次匹配到的位置
举例：
Instring("湖北省武汉市", '武汉') 返回4
常用场景：配合substr进行字符串提取

8、字符串分割函数 split
语法：
split(string str, string pat)
按照pat字符串分割str，会返回分割后的字符串数组
举例：
select split('abcdef', 'c') from test;   结果：["ab", "def"]
select split('abcdef', 'c')[0] from test;  结果：ab
常用场景：分隔符相连的字符串提取

9、行列转置-单行拆分多行
语法 explode(split (string A, string SEP ))
返回字符串A按照分隔符拆分后的多行信息
语句explode(split(联系方式, ";"))

10、多行转单行
语法：
CONCAT_WS(string SEP ,COLLECT_SET(string A)) 
返回字符串A按照分隔符聚合后的数据
语句 SELECT 姓名, 
CONCAT_WS(';',COLLECT_SET(联系方式)) AS 联系方式

六、常用日期函数
1、UNIX时间戳转日期函数 from_unixtime
语法：
from_unixtime(bigint unixtime[, string format])
转化UNIX时间戳（从1970-01-01 00:00:00 UTC到指定时间的秒数）到当前时区的时间格式
举例：
select from_unixtime(1323308943,'yyyyMMdd') from lxw_dual;  结果：20111208

2、获取当前UNIX时间戳unix_timestamp
语法：
unix_timestamp()
获得当前时区的UNIX时间戳
举例：
select unix_timestamp() from lxw_dual;  结果：1323309615

日期转UNIX时间戳
语法：
unix_timestamp(string date)
转换格式为"yyyy-MM-dd HH:mm:ss"的日期到UNIX时间戳。如果转化失败，则返回0
举例：elect unix_timestamp('2011-12-07 13:01:03') from lxw_dual;  结果：1323234063

3、日期时间转日期函数
语法：
to_date(string timestamp)
返回日期时间字段中的日期部分
举例:
hive> select to_date('2011-12-08 10:03:01') from lxw_dual;  结果：2011-12-08

4、年月日提取函数
（1）语法：
year(string date)
返回日期中的年
举例：
hive> select year('2011-12-08 10:03:01') from lxw_dual;
结果：2011
hive> select year('2012-12-08') from lxw_dual;
结果：2012

（2）month (string date)
返回日期中的月份
select month('2011-12-08 10:03:01') from lxw_dual; 结果：12

（3）day (string date)
返回日期中的天
select day('2011-12-08 10:03:01') from lxw_dual;  结果：8

（4）hour(string date)
返回日期中的小时
select hour('2011-12-08 10:03:01') from lxw_dual  结果：10

5、日期加减函数
（1）日期比较函数: datediff
语法：
datediff(string enddate, string startdate)
返回结束日期减去开始日期的天数
举例：
select date_diff('2012-12-08','2012-05-09') from lxw_dual;  结果：213

（2）日期增加函数: date_add
语法：
date_add(string startdate, int days)
返回开始日期startdate增加days天后的日期
举例：
select date_add('2012-12-08',10) from lxw_dual; 结果：2012-12-18、

（3）日期减少函数: date_sub
语法：
date_sub (string startdate, int days)
返回开始日期startdate减少days天后的日期
举例：
select date_sub('2012-12-08',10) from lxw_dual;  结果：2012-11-28

七、聚合函数
1、个数统计函数
语法：
count(*), count(expr), count(DISTINCT expr[, expr_.])
count(*)统计检索出的行的个数，包括NULL值的行；
count(expr)返回指定字段的非空值的个数；
count(DISTINCT expr[, expr_.])返回指定字段的不同的非空值的个数
举例：
 select count(`姓名`) as 数量 from `民警数据`

2、总和统计函数
语法：
sum(col), sum(DISTINCT col)
sum(col)统计结果集中col的相加的结果；
sum(DISTINCT col)统计结果中col不同值相加的结果
举例：
select sum(t) from lxw_dual
select sum(distinct t) from lxw_dual

3、平均值统计函数
语法：
avg(col), avg(DISTINCT col)
avg(col)统计结果集中col的平均值；
avg(DISTINCT col)统计结果中col不同值相加的平均值
举例：select avg(t) from lxw_dual;

4、最小值/最大值统计函数
语法：
min(col)
统计结果集中col字段的最小值
max(col)
统计结果集中col字段的最大值
举例：
select min(t) from lxw_dual;
select max(t) from lxw_dual;

5、最大日期统计函数
语法：
max_date(col)
统计结果集中col字段的最大值
举例：hive> select max_date(t) from lxw_dual;

6、聚合函数对NULL值的处理
（1）AVG()
注意AVE()忽略NULL值，而不是将其作为“0”参与计算
（2）COUNT()
COUNT(*) 对表中行数进行计数不管是否有NULL

COUNT(字段名)对特定列有数据的行进行计数，忽略NULL值
（3）MAX()、MIN()
都忽略NULL
（4）SUM()
可以对单个列求和，也可以对多个列运算后求和
忽略NULL值，且当对多个列运算求和时，如果运算的列中任意一列的值为NULL，则忽略这行的记录。
例如： SUM(A+B+C)，A、B、C 为三列，如果某行记录中A列值为NULL，则不统计这行。

八、Join&union
1、SQL join
Join的定义：JOIN 子句用于把来自两个或多个表的行结合起来，基于这些表之间的共同字段。
（1）内连接
Inner join：只返回两张表中所有满足连接条件的行，即使用比较运算符根据每个表中共有的列的值匹配两个表中的行。
（2）左连接
Left join：返回左表中的所有行，如果左表中行在右表中没有匹配行，则在相关联的结果集中右表的所选择字段均为NULL。
（3）全连接
Full join: 返回两个连接中所有的记录数据，是左外连接和右外连接的并集

2、多表关联-内连接
内连接是从主表中删除与其他被连接表中没有匹配行的所有行，所以内连接可能会丢失信息。

3、多表关联-左连接
将左表所有的查询信息列出，而右表只列出关联后条件与左表满足的部分，可以能回到结果数据比左表数据多。

4、多表关联-全连接
存在匹配，匹配显示；同时，将各个表中不匹配的数据与空数据行匹配进行显示。可以看成是左外连接与右外连接的并集

九、窗口函数及正则简单介绍
1、窗口函数
目的：可以解决BDP中关于移动计算(移动平均，求和等)，滚动计算（滚动求和，平均等）结果再次进行二次计算问题，可以对数据按照某些固定维度求得顺序。
通常用法：
BDP的工作表中通过添加字段使用
作图界面添加计算字段使用
SQL创建合表中SQL语句中使用

常见语句：
（1）计算行顺序：row_number() over(partition by A,B order by C)
（2）求和(sum)/平均值(avg)/最大(max)/最小(min)值计算：sum(A) over(partition by B)
（3）排行第一（first_value）/最后(last_value)的值：first_value(A) over(partition by B ORDER BY C)
（4）根据偏移量计算值(LAG:向前，lead：向后)：lag(A,1,0) over(partition by B,C)

2、正则表达式
正则表达式，又称规则表达式。正则表达式通常被用来检索、替换那些符合某个模式(规则)的文本。