Hive DQL 查询

 从哪里查询关键在from后面的语句，可以从表、视图、查询结果、join结果等

执行顺序： from -> Where（全表扫描） -> group(含聚合) -> having > order -> select（查询返回）

语法树

---

过滤（where + group by）

Where 后为布尔表达式，用于查询过滤。支持任何函数和运算符，聚合函数除外

select * from t_covid19 WHERE length(state) > 10
--州名长度>10

select * 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 笛卡尔积

少用，因为效率太低