从哪里查询关键在from后面的语句,可以从表、视图、查询结果、join结果等
执行顺序: from -> Where(全表扫描) -> group(含聚合) -> having > order -> select(查询返回)
语法树
过滤(where + group by)
Where 后为布尔表达式,用于查询过滤。支持任何函数和运算符,聚合函数除外
select * from t_covid19 WHERE length(state) > 10
--州名长度>10select * from t_covid19 WHERE state="California"
--分区裁剪
Group by 维度
select的字段必须是group by字段,或聚合函数字段。
Group By后字段每个值的仅剩下一行,下图只能用开窗查询
Having
Group by小弟(只能对group by的结果操作,可以用聚合函数)
Where无法与聚合函数一起使用(Where是确定的过程),Having可对已确定的结果进行聚合
与where的区别
where是在分组前对数据进行过滤
having是在分组后对数据进行过滤
having可以使用聚合函数
where不可以使用聚合
排序(order by + Cluster by(Distribute by + sort by) )
Order by : 全局排序,默认为升序ASC(DESC降序),平时少用或进行limit等过滤后使用,因为只有一个reduceTask执行。
若列有NULL值,ASC默认NULL FIRST,DESC则NULL LAST。
若hive.mapred.mode为strict,不加limit会报错.
Cluster by : 根据指定字段分组,每组内根据字段正序排序。
根据同一个字段,分且排序。(分和排序的字段只能是同一个)
分组规则:哈希 字段 mod ReduceTask_Num
例:学号字段,并设置reduceTask=2,会将双数分并排序,单数分并排序
hive > set mapreduce.job.reduces =2;
不能与order by、sort by混用。因为order by为全局排序,一起排序冲突了
Distribute by + sort by
将cluster by一分为二。可分开字段操作,若字段相同和Cluster by效果一样
其他
Distinct 去重 select Distinct
Limit 限制 句尾limtit 5
·可用两个数字,比如 2,100。范围为(2,102],从i+1开始返回n条
with 临时结果集
·with [临时结果] + <select>
聚合函数
·Count 、 max 、 min 、 sum 、avg
Select count(name) from t_student;
聚合函数案例
Union 联合查询(默认去重)
多个Select,返回的列和数量必须相同。
select * from t_studetn_1
union [ALL]
select * from t_studetn_2
union [ALL]
select * from t_studetn_3
……
若要单个子语句limit,应当加括号
SELECT id,name from(select id,name t_student_1 limit 2) temp
UNION
select id,name from t_student_2;
查询当前数据库
select current_database();
子查询
from子查询
子查询 < 相关子查询
Where子查询 <
不相关子查询
where子查询(相关更长一些exists、不相关in)
子查询支持对父查询的引用,也就说先执行父查询,后执行子查询
select id
from (select id,name from t_student) [AS] temp;
Exists
相关子查询:可在子查询对父查询引用 < Not Exists
Where子查询 <
不相关子查询:只作为外部的条件 < IN
NOT IN
--不相关子查询
--作为外部条件in,not in使用
select * from student_hdfs where student.id in (select * from student_local);
--相关子查询
--支持对父查询的引用
--引用了外部T1作为判断
Select X from T1 where exists (Select Y from T2 Where T1.X = T2.Y);
where子查询演示
2 Join
可以join表,也可以join查询结果()。
5种
inner(内连接) 默认过滤空Key
left join(左连接) 因为左边全保留,不过滤空key
full join(全外连接)
left semi join(左半开)
cross jion(笛卡尔积)
--内连接 :t_XXX A [inner] join t_XXX B ON[Condition]
[example]
select A.id,A.name,B.city from table_1 A join table_2 B
on A.id=B.id
--外连接 :t_XXX A {Full|Left|Right} join t_XXX B join_condition
--左外连接:t_XXX A LEFT semi join t_XXX B join_condition
--笛卡尔积:t_XXX A cross join t_XXX B [join_condition]
Inner Join == join 内连接
--内连接 :t_XXX A join t_XXX B ON[Condition]
--例子
select A.id,A.name,B.city from table_1 A join table_2 B
on A.id=B.id
Left Join(同右连接)
一边返回全部值,一边根据条件返回,没Join的返回null
Select L.id,L.name,R.phone,R.email from t_1 right join t_2 on L.id = R.id
Full Join 全连接
分别进行左连接,右连接,再消去重复行
Left semi join 左半开连接
做内连接后只返回左表部分,相比inner join效率更高
Cross Join 笛卡尔积
少用,因为效率太低