Hive数据查询

基础查询

select 字段列表|表达式|子查询
from [临时]表（子查询|视图）
where not 条件A and | or 条件B  -- 先：面向原始行进行筛选
group by 字段A[，字段B，...]
having 聚合条件（非原始字段      -- 后：针对聚合后的字段进行二次筛选（建议使用子查询代替 having）
order by 字段A[，字段B，...]
limit N[前N条记录]|M（行号偏移量）,N（记录数）

select

SELECT用于映射符合指定查询条件的行

Hive SELECT是数据库标准SQL的子集

使用方法类似于MySQL

关键字和MySQL一样，不区分大小写

where 子句

关系运算符

关系运算符：> >= < <= = <>
between (>=) SMALL_VALUE and (<=) BIG_VALUE;

逻辑运算符

逻辑运算符：not BOOL_EXPR , EXPR1 and | or EXPR2
if(BOOL_EXPR, VALUE_IF_TRUE, VALUE_IF_FALSE, VALUE_IF_FALSE_OR_NULL)
nvl(VAL, VALUE_DEFAULT)
isnull(VAL) 	<=> VAL is NULL
case EXPR when V1 then VAL1 when V2 then VAL2 ... else VALN end 	<=> switch case
case C1 when V1 then VAL1 when C2 then VAL2 ... else VALN end		<=> if ... else if ...

通配符

通配符：
like '% | _'			模糊查询
	% 表示零个字符或者多个任意字符
	_ 表示一个任意符号
rlike	'^\\d+$'		正则匹配

CTE

CTE（Common Table Expression）：重用，结构层次清晰（可读性好）

CTE通过 WITH 关键字引入，可以让你在 SQL 查询中创建和引用临时表，提高 SQL 查询的可读性和可维护性

with table_name1 as(
	-- 第一个子查询的定义，可以进行一些数据的处理和筛选
	select ...
),
table_name2 as(
	-- 第二个子查询的定义，可以引用之前定义的 CTE(table_name1)
	select ... from table_name1
)
-- 主查询，可以引用之前定义的 CTE(table_name1,table_name2)
select ...;

嵌套查询（子查询）

select * from (select * from employee)a;

关联查询：join

join常规下不可进行非等值关联

join 用于将两个或多个表中的行组合在一起查询（多表联合查询）

join 发生在 where 子句之前

隐式连接

select A*, B*
from A,B
where A.pk=B.fk

连接

select *
from TABLE_A as A
inner join TABLE_B as B					-- 内连接：交集
left [outer] join TABLE_B as B			-- 左外连接：取左全集，右交集
right [outer] join TABLE_B as B			-- 右外连接：取右全集，左交集
full [outer] join TABLE_B as B			-- 全外连接：取左右全集
cross join TABLE_B as B					-- 交叉连接：一张表的数据去强行匹配另一张表的数据，寻找逻辑关系
on A.pk=B.fk
...
where ...
group by ...
having ...
order by ...
limit ...

关联查询：MapJoin

MapJoin操作在Map端完成

小表关联大表，可进行不等值连接

两个属性来设置该优化的触发时机

默认值为true，自动开户MAPJOIN优化
hive.auto.convert.join

默认值为2500000(25M),通过配置该属性来确定使用该优化的表的大小，
如果表的大小小于此值就会被加载进内存中
hive.mapjoin.smalltable.filesize

MAPJOIN操作不支持：

在UNION ALL, LATERAL VIEW, GROUP BY/JOIN/SORT BY/CLUSTER BY/DISTRIBUTE BY等操作后面

在UNION, JOIN 以及其他 MAPJOIN之前

集合操作（UNION）

union 的使用场景

当 hive 使用连接查询时他有一个必要的限制，必须要两个结果集有关系才能进行连接，

当两个结果集没有任何关系时可以使用 union 关键字进行两个结果集的集合展示。

union的特性

union在查出结果集会对结果集进行排序，这是mapreduce的特性

union使用时候可以去重，如果不希望去重可以使用union all，但是union all不排序

union使用的前提是每个结果集使用的表头必须是相同的字段

union all ：合并后保留重复项

union ：合并后删除重复项

数据排序

order by

在 order by B 之前可以先对数据进行 distribute by A sort by B

order by (asc|desc) 类似于标准 SQL

order by 只使用一个 Reducer 执行全局数据排序，性能较差，速度较慢，应提前做好数据过滤

order by 支持字段（field）、函数（func）、表达式（expr）、case …when

select * from offers order by case when offerid = 1 then 1 else 0
end;
select * from offers order by 1;

NULL 值在排序时最小（在大多数SQL数据库中，包括Hive，数值较小的排在前面，数值较大的排在后面）

 -- 排序时如何将排序字段值为NULL的行后置
order by if(NAME is NULL,1,0)   

支持按字段顺序编号进行排序

set hive.groupby.orderby.position.alias=true;
select a,b,c from TABLE_N order by 2;

sort by / distribute by

sort by

sort by FIELD_N    

sort by 对每个Reducer中的数据进行排序

当 reducer 的数量为1时，等同于 order by

FIELD_N 必须是 select 字段列表中的一员

distribute by

distribute by 类似于标准SQL中的 group by（distribute by 通常在 sort by 之前使用）

根据相应列以及对应 reduce 的个数进行分发

默认是hash算法，根据分区字段的hash码与 reduce 的个数进行模除

SELECT department_id , name, employee_id, evaluation_score
FROM employee_hr 
DISTRIBUTE BY department_id SORT BY evaluation_score DESC; -- desc 为降序

cluster by（不支持 asc | desc）

cluster by = distribute by + sort by        -- distribute by FIELD_A sort by FIELD_A

排序列必须出现在 SELECT column 列表中

为了充分利用所有的 Reducer 来执行全局排序，可以先使用 cluster by，然后使用 order by

分组

为了聚合或去重而分组，否则类似 distinct
group by expr(field | func | case ... when)

grouping sets()需要和 group by 配合使用
group by A, B, C grouping sets(B, (A,C), (B,C))

全排列 A, B, C, (A,B), (A,C), (B,C), (A,B,C)
group by cube(A, B, C)

左排列 A, (A,B), (A,B,C)
group by rollup(A, B, C)

类似于 hadoop job 中的 Partitioner
distribute | cluster by (field | func | case ... when)

# 案例
select category, max(offervalue) from offers group by category;
-- group by使用表达式
select if(category > 4000, 'GOOD', 'BAD') as newcat,max(offervalue)
from offers
group by category if(category > 4000, 'GOOD', 'BAD')；

聚合运算：having

having 可以对 group by 聚合结果的条件进行过滤

having 的使用可以避免在 group by 后面使用子查询

# 案例对比
-- 使用子查询
select a.age
from
(
    select count(*) as cnt, sex_age.age
    from employee
    group by sex_age.age
) a
where a.cnt <= 1；
-- having 代替子查询
select sex_age.age
from employee
group by sex_age.age
having count(*) <= 1;

聚合运算：聚合函数

基础聚合函数

max，min，count，avg，sum

集合聚合函数

collect_set(FIELD | func | case ... when)					-- 自动去重后的列表
collect_list(FIELD | func | case ... when)					-- 列表

一般与GROUP BY一起使用

可应用于列或表达式

对NULL的count聚合为0

select count(null) = 0

窗口函数（作用：扩展列）

窗口函数是一组特殊函数

扫描多个输入行来计算每个输出值，为每行数据生成一行结果

可以通过窗口函数来实现复杂的计算和聚合，可以同时使用多个窗口函数

过滤窗口函数计算结果必须在最外面一层

function(arg1,..., arg n) over ([partition by <...>] [order by <....>] [<window_clause>])

over 从句

partition by 类似于 group by，未指定则按整个结果集

只有指定 order by 子句之后才能进行窗口函数

func(...) over(
    [ partition by F1 [ , F2, ... ] ]					-- 确定分区的边界（范围）
    [ order by Fa [ asc | desc ] ]						-- 确定分区内行的排列顺序
)

进一步缩小窗口粒度

partition by F1 order by F2 [rows between unbounded preceding and current row]

range between ... and ...  	  -- 一种定义窗口行范围的方式，基于数据的排序值
rows between ... and ...      -- 一种定义窗口行范围的方式，基于行的位置
unbounded preceding        	  -- 当前窗口的上边界（第一行）
N preceding                   -- 当前行的上 N 行
current row                	  -- 当前行
N following                	  -- 当前行的下 N 行
unbounded following           -- 当前窗口的下边界（最后一行）

窗口函数按功能可分为排序，聚合，分析

排序

row_number()        -- 输出不同行号（唯一且连续）
rank()              -- 排名（并列会跳号）        1,2,2,4
dense_rank()        -- 排名（并列不会跳号）		1,2,2,3
ntile(N)            -- 切片号(将有序的数据集合平均分配到n个桶中, 将桶号分配给每一行，根据桶号，选取前或后 n分之几的数据) 抽样或倾斜（热点数据）处理
percent_rank()      --百分比排名                 (当前排名-1)/(当前窗口内的总行数-1) 

聚合(Hive2.1.0开始在over子句中支持聚合函数)

聚合函数可以当做窗口函数用

count()、sum()、avg()、max()、min()

分析 ：不支持 window_clause

lag(F, N)			-- 当前窗口内当前行的上N行的字段F值
lead(F, N)			-- 当前窗口内当前行的下N行的字段F值
first_value(F)		-- 当前窗口内第一行的字段F值
last_value(F)		-- 当前窗口内最后一行的字段F值
cume_dist()			-- 小于等于当前值的行数/分组内总行数  <=> count(order by N) /count(1)
percentile(F, double<>)	-- 计算字段 F 的百分比，其中 double<> 是所需的百分比值
corr(F)			-- 皮尔逊相关系数（相似性）
stddev(F)			-- 标准差
var_pop(F)			-- 方差（离散特性）
covar_pop(F1, F2)		-- 协方差（相关性）

窗口函数 – 窗口定义

窗口定义由[<window_clause>]子句描述

• 用于进一步细分结果并应用分析函数

• 支持两类窗口定义

• 行类型窗口

• 范围类型窗口

• RANK、NTILE、DENSE_RANK、CUME_DIST、PERCENT_RANK、LEAD、LAG和ROW_NUMBER函数不支持与窗口子句一起使用

行窗口：根据当前行之前或之后的行号确定的窗口

ROWS BETWEEN <start_expr> AND <end_expr>

• <start_expr>可以为下列值
• UNBOUNDED PRECEDING : 窗口起始位置（分组第一行）
• CURRENT ROW：当前行
• N PRECEDING/FOLLOWING：当前行之前/之后n行

• <end_expr>可以为下列值
• UNBOUNDED FOLLOWING :  窗口结束位置（分组最后一行）
• CURRENT ROW：当前行
• N PRECEDING/FOLLOWING：当前行之前/之后n行

范围窗口：取分组内的值在指定范围区间内的行

该范围值/区间必须是数字或日期类型

目前只支持一个ORDER BY列

-- 假设当前close值为3000，语句将包含分区内范围从2500到4000的行
SUM(close) RANGE BETWEEN 500 PRECEDING AND 1000 FOLLOWING

示例

SELECT
    name, dept_num AS dept, salary AS sal,
    MAX(salary) OVER (PARTITION BY dept_num ORDER BY name ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) win1,
    salary - 1000 as sal_r_start,salary as sal_r_end,
    MAX(salary) OVER (PARTITION BY dept_num ORDER BY name RANGE BETWEEN 1000 PRECEDING AND CURRENT ROW) win13
FROM employee_contract
ORDER BY dept, name;