hive基础

数据类型转换

强转

cast(‘4’ as INT)

如果强制类型转换失败，如执行CAST(‘X’ AS INT)，表达式返回空值 NULL.

DDL

之后在复习,先过

DML

之后在复习,先过

DSL

EDIT: sql里的基本语法就不再做赘述了

where语句

比较运算符

• <=>安全等于
  • example如果A和B都为NULL，则返回TRUE，其他的和等号（=）操作符的结果一致，如果任一为NULL则结果为NULL
• IN在此之中
  • example

    -- 查询姓名叫小明或小花的员工
    select * from emp where name in ("小明","小花")

RLIKE和LIKE

• 区别RLIKE支持java正则表达式LIKE不支持

分组

GROUP BY

GROUP BY语句通常会和聚合函数一起使用，按照一个或者多个列队结果进行分组，然后对每个组执行聚合操作。

案例实操

-- 计算emp表每个部门的平均工资
select t.deptno, avg(t.sal) avg_sal from emp t group by t.deptno;
-- 计算emp每个部门中每个岗位的最高薪水
select t.deptno, t.job, max(t.sal) max_sal from emp t group by
 t.deptno, t.job;

HAVING

HAVING 针对的是查询后的结果集进行条件过滤.

WHERE 针对的是表本身

案例实操

-- 求每个部门的平均薪水大于2000的部门
hive (default)> select deptno, avg(sal) avg_sal from emp group by deptno having
 avg_sal > 2000;

join

• join内连接,左右表格都满足HAVING条件的数据,才会输出到结果即
• left join以join左边的表为基准(左边表满足WHERE条件数据全输出),右边表满足HAVING条件的数据才会输出.没有的用空值替代
• right join与left join同理
• full join左右满足WHERE条件的表全输出,任意一方没有满足HAVING条件则用空值替代
  

排序

order by

全局排序,一个reducer

sort by

reducer内部排序

常与distribute by 连用

distribute by

• 案例

  select * from emp
  distribute by deptno
  sort by name

• 案例sql解释:按照部门分区,每个分区内按照员工姓名排序(默认asc)

cluster by

等同于使用统一标准的distribute by sort by(sort by 按照升序 asc)

• 案例

  -- 两种等价写法
  hive (default)> select * from emp cluster by deptno;
  hive (default)> select * from emp distribute by deptno sort by deptno;

  需要注意的是,分区不一定就是一个固定死的值,也可是1到10号部门分到一个区.

常用的查询函数

NVL

NVL(a,replace_1)

如果a为NULL,则用replcae_1替换a

如果a和replace_1都为NULL,那么返回NULL

• 案例

  select nvl(salary,-1) from emp
  -- 如果salary为空,则返回-1

CASE WHEN

条件判断

• 案例:查询不通部门员工男女各多少人
  • 数据:
    悟空 A 男
    大海 A 男
    宋宋 B 男
    凤姐 A 女
    婷姐 B 女
    婷婷 B 女

select d.deptno,d.name
sum(case e.sex when '男' then 1 else 0 end) as male_number
sum(case e.sex when '女' then 1 else 0 end) as female_number
from emp e
right join dept d
having e.deptno=d.deptno
group by d.deptno
order by d.deptno desc

行转列

讲白了就是合并

这里设计到三个函数

• concatconcat(a,’,’,b)返回的就是a,b
• concat_ws(separator,str1,str2,….)第一个参数为分隔符,之后可以传递一个集合也是可以的concat_ws(separator,array)
• collect_set(col)将该字段的值去重汇总返回一个array类型的字段

直接上案例

• 原始数据

name	constellation	blood_type
孙悟空	白羊座	A
大海无疆	射手座	A
小宋	白羊座	B
猪八戒	白羊座	A
凤姐	射手座	A

• 需求:
  得到星座与血型都相同的人物分类表
  射手座,A  大海无疆|凤姐
  白羊座,A  孙悟空|猪八戒
  白羊座,B  小宋

• 解题

  -- 思路:先求出星座和血型合并的表t1
  select concat(constellation,',',blood_type) base_type,name
  from emp
  -- 再根据t1按照base_type分组处理
  select t1.base_type,concat_ws('|',collect_set(t1.name))
  from(
      select concat(constellation,',',blood_type) base_type,name
  from emp
  )t1
  group by t1.base_type

列转行

讲白了就是爆破.拆分列

• explode()
  这是自带的exploed()爆破函数,但是只能对map或者array类型进行操作
  操作格式:
  lateral view UDTF函数()
  e.g
  lateral view explode()

  直接上实例

• 原始数据

movie	category
《疑犯追踪》	悬疑,动作,科幻,剧情
《Lie to me》	悬疑,警匪,动作,心理,剧情
《战狼2》	战争,动作,灾难

• 需求
  将电影分类中的数组数据展开
  《疑犯追踪》 悬疑
  《疑犯追踪》 动作
  《疑犯追踪》 科幻
  《疑犯追踪》 剧情
  《Lie to me》 悬疑
  《Lie to me》 警匪
  《Lie to me》 动作
  《Lie to me》 心理
  《Lie to me》 剧情
  《战狼2》 战争
  《战狼2》 动作
  《战狼2》 灾难

• 解决

  -- 把category炸开
  select moive,ex_category
  from moives lateral view explode(category) 侧表 as ex_category

  开天窗

  有几点总结一下吧:

1. 更具最后查询结果进行开天窗
2. 天窗逐步扩大(步数为:1行)
3. 开窗最终大小以分区大小决定

• 以一下这组数据为例
  name,日期
  zs,20210801
  zs,20210802
  zs,20210803
  zs,20210804
  zs,20210806
  zs,20210807
  zs,20210808
  zs,20210811
  zs,20210812
  ls,20210730
  ls,20210731
  ls,20210801
  ls,20210804
  ls,20210806

若是over(partition by name),那么有逐步开窗示意图如下:

之后就来几组经典例题帮组理解吧.