大数据之Hive(二)

一、Hive的查询DQL

1. 查询的语法

关键字	作用
distinct	如果有多个字段，根据组合进行去重
all	默认情况就是查询所有，可以省略
select 1 from table	相当于给表增加了一列
where	分组前过滤
having	分组后过滤
order by	全局有序，按照关键字去重分组,reduce置为1
distribute by	结合sort by使用，指明按照什么进行分区
sort by	底层根据reduce个数随机分成对应个有序小组
cluster by	distribute by 和sort by 关键字一样时可以用它代替，没啥用，鸡肋
join	左右相连
union	上下相连
limit n offset m	限制查询个数, 从第m个开始数

2. 执行顺序

from > join on > where > group by > having > select > distinct > union > order by > limit
知道执行顺序的非常重要，建议书写SQL时也可以按照这个顺序来书写。不过select 建议在group by后即可书写了，因为必须先确定字段后才能进行分组后的排序。

3. 语法细节

1. 比较null是否等于null
   • null <=> null
   • null is null
2. limit n, m: 从n+1个元素开始数，数m个
3. 使用rlike代替like来进行字符串匹配，上限比较高,可以使用正则表达式。

4. 聚合函数

1. 所有聚合函数都是忽略null值的，count(1)来统计元素的个数时会统计null值
2. 聚合函数不能和普通列一起查询，需要普通列和聚合函数一起查询需要将普通列放在group by中

5. group by关键字

当select查询的字段和group by后的字段一样时，本质上就是去重。如果不想缺失数据时，建议不要使用group by 关键字来进行分组。

6. join关键字

如果不写on条件，join操作就是两个表进行逐行拼接，即笛卡尔积。join可以使用等值连接或者非等值连接。join默认是内连接，只会取符合on条件的交集。

满外连接： select * from emp e full join dept d on e.deptno = d.deptno;

7. union和union all的区别

union all 是上下两表连接不去重，union会默认去重。

8. join多表联查的底层原理

优化前：先启动一个MR将a表和b表进行连接，之后将将连接结果再用一个MR和c表进行连接。
优化后：默认是开启优化的，会使用map join，将b表和c表都作为小表读入内存后，直接一个map过程完成连接操作。

9.四个排序关键字

order by deptno, salary 基本上等价于distribute by deptno sort by salary ，不过distribute by + sort by 效率更高，并且设置多个reduce时可以将数据分到不同的文件中。

二、函数

1. 查询函数功能
   • show functions;
   • desc function 函数名
   • desc function extended 函数名
2. 单行函数
   • 单行函数指每行数据处理一次，一进一出。
   • 数值函数
     • round(,2)：四舍五入,保留2位小数
     • ceil: 数轴右边那个数
     • floor：数轴左边那个数
   • 字符串函数
     • substring(“atguigu”, -2):倒数后面2个都要
     • replace(): 一般用于日期格式替换
     • regexp_replace(str, regx, rep): 正则替换
     • nvl(A, B): 如果A为null, 则赋值为B
     • regexp() : 正则匹配
     • get_json_object():
       • $.属性名
       • $.[0].属性名
   • 日期函数：
     • unix_timestamp(): 转出时间戳
     • from_unixtime()：转成日期
     • current_date(): 打印当前时间
     • current_timestamp(): 打印时间，精确到毫秒
     • datediff(date1, date2) : 计算日期的差值
     • date_add(): 日期的加减
     • date_format():日期格式转换, 一般用于日期的截取
   • 流程控制函数
     • case语句
     • if语句 if(a,b,c),如果a为真，则执行b，否则执行c

case
	when sal >= 2500 then '小康'
	when sal >=1500 and sal <2500 then '温饱'
	when sal < 1500 then '贫穷'
end 

3. 聚合函数
   • 多行函数指多行数据处理一次，多进一出。
   • collect_list(job): 收集分组后多行数据，合并成一个数组, 结果不去重
   • collect_set(job): 收集分组后的去重数据集
   • array_contains(arr, num): 判断数组中是否包含num元素
4. 炸裂函数
   • 炸裂函数指一行数据返回多个结果，一进多出。
   • 适用于两个表都是多对多关系时，将数组分割为多行数据
   • udtf([a,b,c]) : 炸裂函数
     • explode(arr): 使用频率最高，用来炸数组
     • posexplode() : 带了的下标的炸裂函数
     • inline(): 用来炸裂结构体
   • Lateral View侧写: 一般搭配炸裂函数使用

select 
	*
from movie 
lateral view explode(movies) 
	temp as movie;

5. 窗口函数

• 概念：窗口函数是高阶函数，mysql8.0才有。窗口函数的执行时间在group by之后，having之前。
• 分类
  • 窗口： lead, lag, first_value,last_value
  • 聚合：count,sum,min,max,avg
  • 分析：rank,row_number,dense_rank,ntile
• 语法和含义
  • 函数()over([partition by][order by ][窗口子句])
  • 窗口就是你当前查询结果的所有数据，并且默认窗口最大从第一行到最后一行
  • 函数表示计算逻辑
  • over表示开窗，控制函数的计算范围
    (1) 窗口子句rows between表示 ： 显示限定范围,默认是上无边界，下无边界
    • unbounded preceding 上无边界
    • current row 当前行
    • unbounded following下无边界
    • 1 preceding 上一行
    • 1 following 下一行
    • 默认情况，如果没有order by，窗口子句默认是上无边界，下无边界；在聚合函数后，如果有order by且没有重复数据, 窗口子句默认是上无边界到当前行。如果有重复数据，是上无边界到小于等于当前行。

select 
	user_name,
	order_date,
	order_amount,
	count(*)over(rows between 
	unbounded preceding and 
	unbounded following) cnt
from 
	order

(2) partition by 属性名…， 开窗后再划分子窗口，每行再单独计算。可以有多个属性名来通过组合来划分的更细。
(3) order by 属性名…, 对细分子窗口进行排序。

select 
	user_name,
	order_date,
	order_amount,
	sum(order_amount)over(partition by user_name order by order_date)
	from order_info;

6. 常用窗口函数

6.1 跨行取数据

函数名	作用
lag(字段名, 偏移量, 默认值)	从当前位置向上偏移几行后取值，默认偏移量是1，默认值默认是null
lead(字段名, 偏移量, 默认值)	从当前位置向下偏移几行取值, 默认偏移量是1，默认值是默认是null

6.2 取第一个和最后一个数据

函数名	作用
first_value(字段名, false)	取第一个数据，第二个参数表示是否忽略null
last_value(字段名, false)	取最后一个数据，一般第二个参数均为false

6.3 排名函数

函数名	作用
rank	排名相同时会重复，有重复时下一个名次会消失，最后一个排名等于元素个数
dense_rank	排名相同时会重复，有重复时下一个名次不会消失
row_number	行号

6.4 分组分析

函数名	作用
ntile(组的个数)	将数据打上组名，可以通过组名取出对应组的数据

select 
	user_name,
	order_date,
	order_amount,
	ntile(5)over(order by order_date) nt
	from order_info;

注意：

①如果你需要结果有序，那一定要排序，否则会正进反出（MR的环形缓冲区的反向溢写导致的）
② 如果在累加求和时，排序有重复值，累加的不是一行，而是一段

三、子查询

子查询分类

1. 标量子查询，一个常量，单列值
2. 列子查询，一个竖列
3. 行子查询，一个子表

子查询出现的位置

位置	子查询类别
select	标量子查询
where	标量子查询，列子查询
from	行子查询, 列子查询