如何写SQL（HIVE）？

以下内容用hive举例：

简单的SQL

• 数据

 student表
#字段名：s_id s_name s_birth s_sex
01 赵雷 1990-01-01 男
02 钱电 1990-12-21 男
03 孙风 1990-05-20 男
04 李云 1990-08-06 男
05 周梅 1991-12-01 女
06 吴兰 1992-03-01 女
07 郑竹 1989-07-01 女
08 王菊 1990-01-20 女

course表
#字段名：c_id c_course t_id
01 语文 02
02 数学 01
03 英语 03

teacher表
#字段名：t_id t_name
01 张三
02 李四
03 王五


score表
#字段名：s_id c_id s_score
01 01 80
01 02 90
01 03 99
02 01 70
02 02 60
02 03 80
03 01 80
03 02 80
03 03 80
04 01 50
04 02 30
04 03 20
05 01 76
05 02 87
06 01 31
06 03 34
07 02 89
07 03 98

• 建表

create table if not exists student(
s_id string comment 'this is primary key',
s_name String,
s_birth String,
s_sex String
)
comment 'this is a table'
row format delimited
fields terminated  by ' '
lines terminated by '\n'
stored as textfile
;

create table if not exists course(
c_id string comment 'this is primary key',
c_course String,
t_id String
)
comment 'this is a table'
row format delimited
fields terminated  by ' '
lines terminated by '\n'
stored as textfile
;

create table if not exists teacher(
t_id string comment 'this is primary key',
t_name string
)
comment 'this is a table'
row format delimited
fields terminated  by ' '
lines terminated by '\n'
stored as textfile
;

create table if not exists score(
s_id string comment 'this is primary key',
c_id string comment 'this is primary key',
s_score string
)
comment 'this is a table'
row format delimited
fields terminated  by ' '
lines terminated by '\n'
stored as textfile
;

• 加载数据

load data local inpath '/data/course.txt' overwrite into table course;
load data local inpath '/data/student.txt' overwrite into table student;
load data local inpath '/data/teacher.txt' overwrite into table teacher;
load data local inpath '/data/score.txt' overwrite into table score;

主键
可以唯一确定一行数据，虽然hive中主键的概念没那么主要，但是读写数据的时候还是依赖主键。
在执行SQL的时候，你要时刻知道自己这一步的数据是怎么样的。
只要你知道了主键的字段就可以知道所有主键为这个字段的表的所有信息。
执行顺序
在自己写一个sql的时候一定要确定执行顺序，很多人写SQL错误是因为执行顺序错误。

from 加载，进行表的查找和加载
on 关联字段
join 关联表
where 过滤
group by 分组
having 过滤
select 查询
distinct 去重
order by 排序
limit 限制输出数量

写SQL

1. 查询"李"姓老师的数量

select count(1) from teacher where t_name like '李%';

对于这种没有连接其他表的SQL，难的话就主要是聚合函数或者自定义函数，了解hive中的内置函数的具体内容是很必要的，在这里就不一一赘述了。

2. 查询没学过"张三"老师授课的同学的信息
   思路
   1. 确定我想要的结果，我需要学生信息，就必须要有学生表的主键s_id
   2. 从里往外拆分问题，我需要知道张三老师教的什么课，才可以知道谁没学过他的课

   select cid from course join (select tid from teacher where tname='张三') a on course.tid=a.tid
   

   3. 继续，没学过这种课的同学编号

   select distinct sid from score join 我第二步已经确定的临时虚拟表（视图）b on score.cid=b.cid
   

   4. 最后一步，我有学生表的主键之后就可以去结合第一步

   select student.* from student left join 我第三步已经确定的临时虚拟表（视图） c on student.sid=c.sid where c.sid is null；
   

   5. 完成

   select student.* from  student left join
    (select distinct sid from score join
    	 (select cid from course join 
    	 	(select tid from teacher where tname='张三') a on course.tid=a.tid) b 
    on score.cid=b.cid) c 
    on student.sid=c.sid where c.sid is null；
   

3. 查询和"01"号的同学学习的课程完全相同的其他同学的信息
   思路：
   • 确定我想要的结果，我需要学生信息，就必须要有学生表的主键s_id
   • 01号学生学了那些课？（我知道课程编号就可以）
     select score.c_id c1 from score where score.s_id = '01'
   • 学01号学生课程的人有哪些？（这个时候我要知道哪些人的课程和他完全相同，我可以让学这些课的人的课程数与01课程学的数量相同，这样我就可以知道和他完全相同的人的s_id了）
     仅仅靠连接算数量相等的话，会出现数量相等但是其他同学可能比01多学了其他课程，所以要再比较课程总数是否相等。判断相等采用简单的连接方式。
     选用比较好理解的方法去解决这个问题，每个问题的解决方法可能有很多种，我们可以做出很多尝试。
     -和01相同课程学生选课情况

     select score.s_id,count(1) co from score left join 
     (select score.c_id c1 from score where score.s_id = '01') b 
     on score.c_id=b.c1 
     group by score.s_id
     

     正确的：

     select f.s_id,f.co1 co from  
     (select score.s_id,count(1) co1 from score 
     join (select score.c_id c1 from score where score.s_id = '01') b 
     on score.c_id=b.c1 group by score.s_id) f 
     join (select count(c_id) co2 from score where score.s_id = '01' group by score.s_id) g 
     on f.co1=g.co2
     

     -01学生选课数量

     select count(score.c_id) num from score where score.s_id = '01' group by score.s_id
     

     -与01相同的只需要连接两表

     select c.s_id from 
     (select count(score.c_id) num from score where score.s_id = '01' group by score.s_id) d join 
     (select f.s_id,f.co1 co from 
     (select score.s_id,count(1) co1 from score join 
     (select score.c_id c1 from score where score.s_id = '01') b 
     on score.c_id=b.c1 group by score.s_id) f join 
     (select count(c_id) co2 from score where score.s_id = '01' group by score.s_id) g on f.co1=g.co2) c 
     on d.num=c.co
     

   • 完成（不想要等于01学生信息的话再去掉即可）

select student.* from student left semi join
(select c.s_id 
from (select count(score.c_id) num from score where score.s_id = '01' group by score.s_id) d
join 
( select f.s_id,f.co1 co from 
(select score.s_id,count(1) co1 from
score
join
(select score.c_id c1 from score where score.s_id = '01') b
on score.c_id=b.c1
group by score.s_id) f
join (select count(c_id) co2 from score where score.s_id = '01' group by score.s_id) g
on f.co1=g.co2) c on d.num=c.co) e
on student.s_id=e.s_id

最后，优化
https://blog.csdn.net/qq_43755771/article/details/90725461

总结

写一些连接比较多的SQL的时候，就可以采用这种从里到外的方式去写SQL语句，SQL语句并不可怕，可怕的是你没有思路，学会拆分，将复杂问题变成简单的问题。
例如：视图就是利用这种方式来简化SQL语句，让我们的思路变得清晰，在查询过程中创建虚拟表
这也类似我们学习过程中很重要的分层思想。
扩展
MVC分层（model=>view=>controller=>service=>dao）
数仓的建模和分层（在完成某项指标时会进行分层）