一.关于数据库内容的考察
sql
本文将从三大块介绍入门SQL需要掌握的语法和知识，分别是最基础的选择（select）和连接（join/union）；最常用的函数（distinct/group by/order by等）；一些小小的进阶技巧（组内排序、取前百分之多少的值、时间函数）。
一.最基本（选数据）
1. 怎么把数据从表里选出来？
-- 从table_1中选择a这一列 select a from table_1
2. 表连接
-- table_1中有id,age; table_2中有id，sex。想取出id,age,sex 三列信息
-- 将table_1,table_2 根据主键id连接起来
select a.id,a.age,b.sex from
(select id,age from table_1) a --将select之后的内容存为临时表a
join
(select id, sex from table_2) b --将select之后的内容存为临时表b
on a.id =b.id
在这里先介绍一下几种join: （敲重点，很容易问的哦）
join : hive的join默认是inner join，找出左右都可匹配的记录
left join: 左连接，以左表为准，逐条去右表找可匹配字段，如果有多条会逐次列出，如果没有找到则是NULL；
right join：右连接，以右表为准，逐条去左表找可匹配字段，如果有多条会逐次列出，如果没有找到则是NULL
full outer join: 全连接，包含两个表的连接结果，如果左表缺失或者右表缺失的数据会填充NULL
每种join 都有on , on的是左表和右表中都有的字段。join 之前要确保关联键是否去重，是不是刻意保留非去重结果。
3. 两张表数据的字段一样，想合并起来，怎么办？
-- 不去重，合并两张表的数据
select * from
(
select id from table_1
UNION ALL
select id from table_2
)t;
union和union all均基于列合并多张表的数据，所合并的列格式必须完全一致。union的过程中会去重并降低效率，union all直接追加数据。union前后是两段select 语句而非结果集。
二.最常用
为方便大家理解每个函数的作用，先建一个表，后面以这个为示例。
1. 去重 distinct
-- 罗列不同的id
select distinct id from table_1
-- 统计不同的id的个数
select count(distinct id) from table_1
-- 优化版本的count distinct
select count(*) from
(select distinct id from table_1) tb
distinct 会对结果集去重，对全部选择字段进行去重，并不能针对其中部分字段进行去重。使用count distinct进行去重统计会将reducer数量强制限定为1，而影响效率，因此适合改写为子查询
2. 聚合函数和group by
-- 统计不同性别（F、M）中，不同的id个数
select count(distinct id) from table_1
group by sex
-- 其它的聚合函数例如：max/min/avg/sum
-- 统计最大/最小/平均年龄
select max(age), min(age),avg(age) from
table_1
group by id
聚合函数帮助我们进行基本的数据统计，例如计算最大值、最小值、平均值、总数、求和
3. 筛选 where/having
-- 统计A公司的男女人数
select count(distinct id) from table_1
where company = 'A'
group by sex
-- 统计各公司的男性平均年龄，并且仅保留平均年龄30岁以上的公司
select company, avg(age) from table_1
where sex = 'M'
group by company
having avg(age)>30;
4. 排序 order by
-- 按年龄全局倒序排序取最年迈的10个人
select id,age from table_1 order by age DESC
limit 10
5. case when 条件函数
-- 收入区间分组
select id,
(case when CAST(salary as float)<50000 Then '0-5万'
when CAST(salary as float)>=50000 and CAST(salary as float)<100000 then '5-10万'
when CAST(salary as float) >=100000 and CAST(salary as float)<200000 then '10-20万'
when CAST(salary as float)>200000 then '20万以上'
else NULL end
from table_1;
case 函数的格式为（case when 条件1 then value1 else null end）, 其中else 可以省，但是end不可以省。
在这个例子里也穿插了一个CAST的用法，它常用于string/int/double型的转换。
6. 字符串
1)concat( A, B...)返回将A和B按顺序连接在一起的字符串，如：concat('foo', 'bar') 返回'foobar'。
select concat('www','.iteblog','.com') from
iteblog;
2)split(str, regex)用于将string类型数据按regex提取，分隔后转换为array。
-- 以","为分隔符分割字符串，并转化为array
Select split("1,2,3",",")as value_array from table_1;
-- 结合array index,将原始字符串分割为3列
select value_array[0],value_array[1],value_array[2] from
(select split("1,2,3",",")as value_array from table_1 )t
3)substr（str,0,len) 截取字符串从0位开始的长度为len个字符。
select substr('abcde',3,2) from
iteblog;
三.基础进阶
1.row_number(）
-- 按照字段salary倒序编号
select *, row_number() over (order by salary desc) as row_num from table_1;
-- 按照字段deptid分组后再按照salary倒序编号
select *, row_number() over (partition by deptid order by salary desc) as rank from table_1;
按照depid分组，对salary进行排序（倒序）
除了row_number函数之外，还有两个分组排序函数，分别是rank() 和dense_rank()。
rank()排序相同时会重复，总数不会变 ，意思是会出现1、1、3这样的排序结果；
dense_rank() 排序相同时会重复，总数会减少，意思是会出现1、1、2这样的排序结果。
row_number() 则在排序相同时不重复，会根据顺序排序。
2.percentile 百分位函数
-- 获取income字段的top10%的阈值
select percentile(CAST (salary AS int),0.9)) as income_top10p_threshold from table_1;
-- 获取income字段的10个百分位点
select percentile(CAST (salary AS int),array(0.0,0.1,0.2,0.3,0.4,0.5,0.6,0.7,0.8,0.9,1.0)) as income_percentiles
from table_1;
3.时间函数
-- 转换为时间数据的格式
select to_date("1970-01-01 00:00:00") as start_time from table_1;
-- 计算数据到当前时间的天数差
select datediff('2016-12-30','2016-12-29');
-- 得到 "1"
to_date函数可以把时间的字符串形式转化为时间类型，再进行后续的计算。
常用的日期提取函数包括：
-
year()/month()/day()/hour()/minute()/second()
-
日期运算函数包括datediff(enddate,stratdate) 计算两个时间的时间差（day)
-
date_sub(stratdate,days) 返回开始日期startdate减少days天后的日期
-
date_add(startdate,days) 返回开始日期startdate增加days天后的日期
sql基础45题
course
score
student
teacher
--1.查询课程编号为“01”的课程比“02”的课程成绩高的所有学生的学号（重点）
select s.*,sc1.s_score,sc2.s_score from
student s
inner join score sc1
on s.s_id=sc1.s_id
inner join score sc2
on sc1.s_id=sc2.s_id
where sc1.c_id='01' and sc2.c_id='02' and sc1.s_score>sc2.s_score
--2、查询平均成绩大于60分的学生的学号和平均成绩（简单，第二道重点）
select st.s_id,st.s_name,ifnull(avg(sc.s_score),0) as 平均成绩 from student st
left join score sc on st.s_id=sc.s_id
group by st.s_id
having 平均成绩>60
-- 2.1、所有成绩小于60分的学生信息
select * from student
where s_id not in ( select s_id from score where s_score>60)
-- 2.2查询平均成绩小于60分的学生的学号和平均成绩，考虑没参加考试的情况，没参加考试成绩即为0
select st.s_id,st.s_name,ifnull(avg(sc.s_score),0) as 平均成绩 from student st
left join score sc on st.s_id=sc.s_id
group by st.s_id
having 平均成绩<60
-- 3、查询所有学生的学号、姓名、选课数、总成绩（不重要）
select st.s_id,st.s_name,count(sc.c_id),sum(sc.s_score) from student st
inner join score sc on st.s_id=sc.s_id
group by st.s_id
-- 4、查询姓“李”的老师的个数（不重要）
select count(t_id) from teacher
where t_name like '李%'
-- 5、查询没学过“张三”老师课的学生的学号、姓名（重点）
select s_id,s_name from student
where s_id not in (
select sc.s_id from score sc
inner join course co on sc.c_id=co.c_id
inner join teacher te on co.t_id=te.t_id
where te.t_name='张三')
-- 6、查询学过“张三”老师所教的所有课的同学的学号、姓名（重点）
-- 备注有问题，当张三教多门课时，就不对了，这里时张三教的所有课，而不是只要上过张三课就行
-- 比如张三同时教01和02两门课，那么这里就等价于同时上过01和02连门的学生，就同7题
select s_id,s_name from student
where s_id in (
select sc.s_id from score sc
inner join course co on sc.c_id=co.c_id
inner join teacher te on co.t_id=te.t_id
where te.t_name='张三')
-- 7、查询学过编号为“01”的课程并且也学过编号为“02”的课程的学生的学号、姓名（重点）
select * from student
where s_id in (
select s_id from score where c_id='01' and s_id in (select s_id from score where c_id='02')
)
-- 7.1、查询学过编号为“01”的课程但没有学过编号为“02”的课程的学生的学号、姓名（重点）
select * from student
where s_id in (
select s_id from score where c_id='01'
and s_id not in (select s_id from score where c_id='02')
)
-- 8、查询课程编号为“02”的总成绩（不重点）
select sum(s_score) from score
where c_id ='01'
-- 9、查询所有课程成绩小于60分的学生的学号、姓名
select * from student
where s_id not in ( select s_id from score where s_score>60)
-- 10、查询没有学全所有课的学生的学号、姓名(重点)
select st.s_id,st.s_name,sc.c_id,count(sc.c_id) from student st
left join score sc on st.s_id=sc.s_id
group by st.s_id
having count(sc.c_id)<(select count(c_id) from course)
-- 11、查询至少有一门课与学号为“01”的学生所学课程相同的学生的学号和姓名（重点）
select distinct s.* from student s
inner join score sc on s.s_id=sc.s_id
where sc.c_id in (select c_id from score where s_id='01')
and s.s_id!='01'
-- 12、查询和“01”号同学所学课程完全相同的其他同学的学号(重点)
select s_id from score
where s_id <> '01'
group by s_id
having group_concat(c_id)=(
select group_concat(c_id)
from score
where s_id='01')
-- 13、查询没学过"张三"老师讲授的任一门课程的学生姓名 和47题一样（重点，能做出来）
select s_id from score
group by s_id
having group_concat(c_id) = (
select group_concat(co.c_id) from course co
inner join teacher te on co.t_id=te.t_id
where t_name='张三')
-- 有错误，不能用等号，in也不对，group_concat(c_id)是包含后面的
-- 15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩（重点）
select s.s_id,s.s_name,avg(sc.s_score) avg from student s
inner join score sc on s.s_id=sc.s_id
where sc.s_score<60
group by s.s_id,s.s_name
having count(c_id)>=2
-- 16、检索"01"课程分数小于60，按分数降序排列的学生信息（和34题重复，不重点）
SELECT st.s_id,st.s_name,sc.c_id,sc.s_score from student st
left join score sc on st.s_id = sc.s_id
where sc.c_id='01' and sc.s_score<=60
-- 这里要考虑8，没有成绩，方法二
SELECT s_id,s_name from student
where s_id in ( select s_id from score where c_id='01' and s_score<=60)
-- 17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩(重重点与35一样)
--备注：
--1.因为要选出需要的字段 用case when 当c_id='01' then 可以得到对应的 s_core
--2.因为GROUP UP 要与select 列一致，所以case when 加修饰max，min，sum均可
--3.因为最后要展现出每个同学的各科成绩为一行，所以用到case
select st.s_id,st.s_name,sc.c_id,
sum(case when sc.c_id='01' then sc.s_score else null end) '语文',
sum(case when sc.c_id='02' then sc.s_score else null end) '数学',
sum(case when sc.c_id='03' then sc.s_score else null end) '英语',
ifnull(avg(sc.s_score),0) from student st
left join score sc on st.s_id=sc.s_id
group by st.s_id
order by avg(sc.s_score) desc
-- 18、查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，
--最高分，最低分，平均分，及格率，中等率，优良率，优秀率（及格为>=60，中等为：70-80，
--优良为：80-90，优秀为：>=90） (超级重点)
-- 备注：各种率都可以用avg平均值
select sc.c_id,co.c_name,max(sc.s_score) 'max',min(sc.s_score) 'mim',avg(sc.s_score) 'avg',
avg( case when sc.s_score>=60 then 1 else 0 end) '及格率',
avg( case when sc.s_score>70 and sc.s_score<=80 then 1 else 0 end) '中等率',
avg( case when sc.s_score>80 and sc.s_score<=90 then 1 else 0 end) '优良率',
avg( case when sc.s_score>90 then 1 else 0 end) '优秀率'
from score sc
inner join course co on sc.c_id=co.c_id
group by sc.c_id
-- 19、按各科成绩进行排序，并显示排名(重点row_number)，row_number(）over （order by 列）
SELECT *,row_number() over(order by s_score desc) 'Rank'
from score
-- 20、查询学生的总成绩并进行排名（不重点）
SELECT st.s_id,st.s_name,ifnull(sum(s_score),0) 总成绩 from student st
left join score sc on st.s_id = sc.s_id
group by st.s_id
order by sum(s_score) desc
-- 21 、查询不同老师所教不同课程平均分从高到低显示(不重点)
select co.t_id,co.c_id,te.t_name,avg(sc.s_score) from score sc
inner join course co on sc.c_id=co.c_id
inner join teacher te on co.t_id=te.t_id
group by co.t_id,sc.c_id
order by avg(sc.s_score) desc
-- 22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩（重要 25类似）
select * from (
select s.*,sc.c_id,sc.s_score,row_number() over (partition by c_id order by s_score desc) 排名
from score sc inner join student s on sc.s_id=s.s_id) b
where 排名 in (2,3)
-- 23、使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩，分别统计各分数段人数：
--课程ID和课程名称(重点和18题类似)
select c_id,max(s_score) max,min(s_score) min,avg(s_score) avg,
avg(case when s_score>=60 then 1 else 0 end) '及格率',count(case when s_score>=60 then 1 else 0 end) '及格人数',
avg(case when s_score>=70 and s_score<80 then 1 else 0 end) '中等率',count(case when s_score>=60 then 1 else 0 end) '中等人数',
avg(case when s_score>=80 and s_score<90 then 1 else 0 end) '优良率',count(case when s_score>=60 then 1 else 0 end) '优良人数',
avg(case when s_score>=90 then 1 else 0 end) '优良率',count(case when s_score>=60 then 1 else 0 end) '优良人数'
from score
group by c_id
-- 24、查询学生平均成绩及其名次（同19题，重点）
select s_id,avg(s_score) avg,row_number() over (order by avg(s_score) desc) 'Rank'
from score
group by s_id
-- 25、查询各科成绩前三名的记录（不考虑成绩并列情况）（重点 与22题类似）
select * from (
select s.*,sc.c_id,sc.s_score,row_number() over (partition by c_id order by s_score desc) 排名
from score sc inner join student s on sc.s_id=s.s_id) b
where 排名 in (1,2,3)
-- 26、查询每门课程被选修的学生数(不重点)同39
select c_id,count(c_id) from score
group by c_id
-- 27、 查询出只有两门课程的全部学生的学号和姓名(不重点)同10题
select st.s_id,st.s_name,count(sc.c_id) from student st
left join score sc on st.s_id=sc.s_id
group by st.s_id
having count(sc.c_id)=2
-- 28、查询男生、女生人数(不重点)
select s_sex,count(s_id) from student
group by s_sex
-- 29 查询名字中含有"风"字的学生信息（不重点）
select * from student
where s_name like '%风'
-- 31、查询1990年出生的学生名单（重点year）
select * from student
where year(s_birth)=1990
-- 32、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩（不重要）
select st.s_id,st.s_name,avg(sc.s_score) from student st
inner join score sc on st.s_id=sc.s_id
group by st.s_id
having avg(sc.s_score)>=85
-- 33、查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列（不重要）
select st.s_id,st.s_name,avg(sc.s_score) from student st
inner join score sc on st.s_id=sc.s_id
group by st.s_id
order by avg(sc.s_score) desc ,sc.c_id desc
-- 34、查询课程名称为"数学"，且分数低于60的学生姓名和分数（不重点）
select st.s_id,st.s_name,sc.s_score,co.c_name from student st
inner join score sc on st.s_id=sc.s_id
inner join course co on sc.c_id=co.c_id
where co.c_name='数学' and sc.s_score<60
-- 35、查询所有学生的课程及分数情况（重点）
select s_id,
min(case when c_id='01' then s_score else null end) '语文',
min(case when c_id='02' then s_score else null end) '数学',
min(case when c_id='03' then s_score else null end) '英语'
from score
group by s_id
-- 36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数（重点） 注：不用group by
select s.s_id,s.s_name,co.c_name,sc.s_score from
student s inner join score sc on s.s_id=sc.s_id
inner join course co on sc.c_id=co.c_id
where sc.s_score>70
-- 37、查询不及格的课程并按课程号从大到小排列(不重点)
select sc.c_id,sc.s_score,co.c_name from score sc
inner join course co on sc.c_id=co.c_id
where sc.s_score<60
order by sc.c_id desc,sc.s_score desc
-- 38、查询课程编号为03且课程成绩在80分以上的学生的学号和姓名（不重要）
select st.s_id,st.s_name,sc.c_id,sc.s_score from student st
inner join score sc on st.s_id=sc.s_id
where sc.c_id='03' and sc.s_score > 80
-- 39、求每门课程的学生人数（不重要）同26
select c_id,count(c_id) from score
group by c_id
-- 40、查询选修“张三”老师所授课程的学生中成绩最高的学生姓名及其成绩（重要top）（同5，6题）
--（成绩最高学生可能有n个，应该用嵌套查到最高成绩再查成绩等于最高成绩的学生信息）
select * from (
select st.s_id,st.s_name,sc.c_id,max(sc.s_score) from student st
inner join score sc on st.s_id=sc.s_id
inner join course co on sc.c_id=co.c_id
inner join teacher te on co.t_id=te.t_id
where te.t_name='张三') b
-- 41.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩 （重点）
select distinct sc1.s_id,sc1.c_id,sc1.s_score
from score sc1 inner join score sc2
on sc1.s_id=sc2.s_id
where sc1.s_score=sc2.s_score and sc1.c_id!=sc2.c_id
-- 42、查询每门功成绩最好的前两名（同22和25题）
select * from (
select s.*,sc.c_id,sc.s_score,row_number() over (partition by c_id order by s_score desc) 排名
from score sc inner join student s on sc.s_id=s.s_id) b
where 排名 in (1,2)
-- 43、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列（不重要）
select c_id,count(c_id) 人数 from score sc
group by c_id
having 人数>5
order by 人数 desc,c_id asc
-- 44、检索至少选修两门课程的学生学号（不重要）
select s_id,count(c_id) from score
group by s_id
having count(c_id)>=2
-- 45、 查询选修了全部课程的学生信息（重点划红线地方）同10题，27题
select st.s_id,st.s_name,count(sc.c_id) from student st
inner join score sc on st.s_id=sc.s_id
group by st.s_id
having count(sc.c_id)=(select count(c_id) from course)
-- 46、查询各学生的年龄（精确到月份）
select s_id,s_birth,
round(datediff('2020-06-09',s_birth)/365,0)
from student
-- another one
select s_id,s_birth,
year(now())-year(s_birth)
from student
-- 注意本题的意思其实是按月计算，但是datediff在mysql中不能添加第三个参数depart
--sql serve
select s_id,s_birth,datediff(month,s_birth,date(now()))/12 as 年龄
from student
-- 47.查询本月过生日的学生
select * from student
where month(s_birth)=month(now())
-- 48.查询本周过生日的学生（使用week、date(now())，week(s_birth,1),1表示一周的第一天是周一
select * from student
where week(s_birth,1)=week(date(now()),1)
-- 49.查询下周过生日的学生
select * from student
where week(s_birth,1)=week(date(now()),1)+1
-- 50.查询下月过生日的学生
select * from student
where month(s_birth)=month(now())+1
letcode题库
--难题184 185 262 601 626
--175组合两个表 left join
############################################################################
'''
175组合两个表
表1: Person
+-------------+---------+
| 列名 | 类型 |
+-------------+---------+
| PersonId | int |
| FirstName | varchar |
| LastName | varchar |
+-------------+---------+
PersonId 是上表主键
表2: Address
+-------------+---------+
| 列名 | 类型 |
+-------------+---------+
| AddressId | int |
| PersonId | int |
| City | varchar |
| State | varchar |
+-------------+---------+
AddressId 是上表主键
'''
--编写一个 SQL 查询，满足条件：无论 person 是否有地址信息，都需要基于上述两表提供 person 的以下信息：
--FirstName, LastName, City, State'''
--这里用left join 保证person不为空值，读取左边数据表的全部数据
select p.FirstName, p.LastName, a.City, a.State from
Person as p left join Address as a
on p.PersonId = a.PersonId
#FirstName LastName City State
Allen Wang null null
############################################################################
--176第二高的薪水 ifnull(x,y) limit(x,y)
############################################################################
'''
176编写一个 SQL 查询，获取 Employee 表中第二高的薪水（Salary）
+----+--------+
| Id | Salary |
+----+--------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+----+--------+
'''
--ifnull(x，y)，若x不为空则返回x，否则返回y，这道题y=null
--limit x，y，limit中x从0开始的，y是娶几个记录。找到对应的记录就停止。
--limit 1,1就是200，如果salary很多，求第三高的薪资，就是limit 2，1
--distinct，过滤关键字
select
ifnull
(
(select distinct Salary
from Employee
order by Salary desc
limit 1,1),
null
)as 'SecondHighestSalary'
--或者
select max(salary) as SecondHighestSalary from Employee
where salary < (select max(salary) from Employee)
+---------------------+
| SecondHighestSalary |
+---------------------+
| 200 |
+---------------------+
############################################################################
--177第 n 高的薪水 limit(x,y)
############################################################################
'''177编写一个 SQL 查询，获取 Employee 表中第 n 高的薪水（Salary）
+----+--------+
| Id | Salary |
+----+--------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+----+--------+
例如上述 Employee 表，n = 2 时，应返回第二高的薪水 200。如果不存在第 n 高的薪水，那么查询应返回 null。
+------------------------+
| getNthHighestSalary(2) |
+------------------------+
| 200 |
+------------------------+
'''
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
set n = n-1;
RETURN (
select distinct Salary as getNthHighestSalary
from Employee
order by Salary DESC
limit N,1
);
END
############################################################################
--178分数排名 排名使用自连接(自己对比自己，2表)，count(dinstinct x)
############################################################################
'''
178编写一个 SQL 查询来实现分数排名。
如果两个分数相同，则两个分数排名（Rank）相同。请注意，平分后的下一个名次应该是下一个连续的整数值。换句话说，
名次之间不应该有“间隔”。
+----+-------+
| Id | Score |
+----+-------+
| 1 | 3.50 |
| 2 | 3.65 |
| 3 | 4.00 |
| 4 | 3.85 |
| 5 | 4.00 |
| 6 | 3.65 |
+----+-------+
例如，根据上述给定的 Scores 表，你的查询应该返回（按分数从高到低排列）：
+-------+------+
| Score | Rank |
+-------+------+
| 4.00 | 1 |
| 4.00 | 1 |
| 3.85 | 2 |
| 3.65 | 3 |
| 3.65 | 3 |
| 3.50 | 4 |
+-------+------+
'''
--1.从两张相同的表scores分别命名为s1，s2。
--2.s1中的score与s2中的score比较大小。意思是在输出s1.score的前提下，
--有多少个s2.score大于等于它。比如当s1.salary=3.65的时候，这里也可以计数有多少个s2.score小于等于它
--s2.salary中[4.00,4.00,3.85,3.65,3.65]有5个成绩大于等于他，
--但是利用count(distinct s2.score)去重可得s1.salary3.65的rank为3
--3.group by s1.id 不然的话只会有一条数据
--4.最后根据s1.score排序desc
select s1.score,count(distinct s2.score) as rank
from scores as s1,scores as s2
where s1.score<=s2.score
group by s1.id
order by s1.score desc;
--plan b
-- dense_rank() over(order by ... desc),不间隔排名
select score,dense_rank() over(order by score desc) as 'Rank' from scores
############################################################################
--180查找所有至少连续出现三次的数字 自连接(3表)
############################################################################
'''
180编写一个 SQL 查询，查找所有至少连续出现三次的数字。
+----+-----+
| Id | Num |
+----+-----+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 1 |
| 6 | 2 |
| 7 | 2 |
+----+-----+
例如，给定上面的 Logs 表， 1 是唯一连续出现至少三次的数字。
+-----------------+
| ConsecutiveNums |
+-----------------+
| 1 |
+-----------------+
'''
--自连接3表，用where限定，a.num=b.num=c.num，id依次增大保证连续
select distinct a.Num as ConsecutiveNums
from Logs as a,Logs as b,Logs as c
where a.Num=b.Num and b.Num=c.Num and a.id=b.id-1 and b.id=c.id-1;
############################################################################
--181收入超过他们经理的员工 自连接(2表)
############################################################################
'''
181. 超过经理收入的员工
SQL架构
Employee 表包含所有员工，他们的经理也属于员工。每个员工都有一个 Id，此外还有一列对应员工的经理的 Id。
+----+-------+--------+-----------+
| Id | Name | Salary | ManagerId |
+----+-------+--------+-----------+
| 1 | Joe | 70000 | 3 |
| 2 | Henry | 80000 | 4 |
| 3 | Sam | 60000 | NULL |
| 4 | Max | 90000 | NULL |
+----+-------+--------+-----------+
给定 Employee 表，编写一个 SQL 查询，该查询可以获取收入超过他们经理的员工的姓名。
在上面的表格中，Joe 是唯一一个收入超过他的经理的员工。
+----------+
| Employee |
+----------+
| Joe |
+----------+'''
--用where限定条件 e1.ManagerId=e2.Id但是e1.Salary > e2.Salary
select e1.Name as Employee from
Employee as e1,Employee as e2
where e1.ManagerId=e2.Id
and e1.Salary > e2.Salary
-- plan b 采用左连接
select e1.Name from Employee e1
left join Employee e2 on e1.MangerId=e2.Id
where e1.salary>e2.salary
############################################################################
--182查找重复的电子邮箱 1.group by + having 2.inner join on email相等但是id不相等
############################################################################
'''
182. 查找重复的电子邮箱
SQL架构
编写一个 SQL 查询，查找 Person 表中所有重复的电子邮箱。
示例：
+----+---------+
| Id | Email |
+----+---------+
| 1 | a@b.com |
| 2 | c@d.com |
| 3 | a@b.com |
+----+---------+
根据以上输入，你的查询应返回以下结果：
+---------+
| Email |
+---------+
| a@b.com |
+---------+
说明：所有电子邮箱都是小写字母。
'''
--plan a group by
--count(1)记录email出现的次数
select email,count(1) from person
group by email
having count(1)>1
--plan b
select email,t from (
select count(1) as t,
email from person
group by email) as r
where r.t>1
--plan c
--inner join on email = / id ！=
select distinct(p1.Email) from Person p1
join Person p2 on p1.Email = p2.Email AND p1.Id!=p2.Id
############################################################################
--183从不订购的客户 1.customers left join orders on c.id=o.cid and customerid is null
-- 2.子查询 where限定
############################################################################
'''
183. 从不订购的客户
SQL架构
某网站包含两个表，Customers 表和 Orders 表。编写一个 SQL 查询，找出所有从不订购任何东西的客户。
Customers 表：
+----+-------+
| Id | Name |
+----+-------+
| 1 | Joe |
| 2 | Henry |
| 3 | Sam |
| 4 | Max |
+----+-------+
Orders 表：
+----+------------+
| Id | CustomerId |
+----+------------+
| 1 | 3 |
| 2 | 1 |
+----+------------+
例如给定上述表格，你的查询应返回：
+-----------+
| Customers |
+-----------+
| Henry |
| Max |
+-----------+'''
--plan a
--left join注意左表是customers
select c.Name from Customers as c
left join Orders as o
on o.CustomerId=c.Id where o.Id is null
--plan b
--子查询 c.id not in orders中的customers.id
select c.Name as Customers from Customers c
where c.Id not in (select distinct o.CustomerId from Orders o);
############################################################################
--184部门工资最高的员工 互连接 + 子查询
############################################################################
'''
184. 部门工资最高的员工
SQL架构
Employee 表包含所有员工信息，每个员工有其对应的 Id, salary 和 department Id。
+----+-------+--------+--------------+
| Id | Name | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1 | Joe | 70000 | 1 |
| 2 | Henry | 80000 | 2 |
| 3 | Sam | 60000 | 2 |
| 4 | Max | 90000 | 1 |
+----+-------+--------+--------------+
Department 表包含公司所有部门的信息。
+----+----------+
| Id | Name |
+----+----------+
| 1 | IT |
| 2 | Sales |
+----+----------+
编写一个 SQL 查询，找出每个部门工资最高的员工。例如，根据上述给定的表格，Max 在 IT 部门有最高工资，
Henry 在 Sales 部门有最高工资。
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT | Max | 90000 |
| Sales | Henry | 80000 |
+------------+----------+--------+
'''
--互连接 + 子查询
--互连接 employees 和 department where employees.DepartmentId =department.Id
--子查询 where 条件中 and 创建一个表示部门最高工资的表，group by departmentId
select
d.name as Department,
e.name as Employee,
e.Salary
from
Department as d,Employee as e
where e.DepartmentId =d.Id
and (e.Salary,e.DepartmentId) in (select max(Salary),DepartmentId from Employee
group by DepartmentId)
--select max(Salary),DepartmentId from Employee
--group by DepartmentId
--得到了部门最高工资
--max(Salary) DepartmentId
--90000 1
--80000 2
############################################################################
--185部门工资前三高的所有员工 分组内取前几名的问题，可以先group by然后用having count()来筛选+子查询
--其中查询部门前三名工资的自连接(2表)方法与178相同
############################################################################
'''
185. 部门工资前三高的所有员工
Employee 表包含所有员工信息，每个员工有其对应的工号 Id，
姓名 Name，工资 Salary 和部门编号 DepartmentId 。
+----+-------+--------+--------------+
| Id | Name | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1 | Joe | 85000 | 1 |
| 2 | Henry | 80000 | 2 |
| 3 | Sam | 60000 | 2 |
| 4 | Max | 90000 | 1 |
| 5 | Janet | 69000 | 1 |
| 6 | Randy | 85000 | 1 |
| 7 | Will | 70000 | 1 |
+----+-------+--------+--------------+
Department 表包含公司所有部门的信息。
+----+----------+
| Id | Name |
+----+----------+
| 1 | IT |
| 2 | Sales |
+----+----------+
编写一个 SQL 查询，找出每个部门获得前三高工资的所有员工。例如，根据上述给定的表，
查询结果应返回：
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT | Max | 90000 |
| IT | Randy | 85000 |
| IT | Joe | 85000 |
| IT | Will | 70000 |
| Sales | Henry | 80000 |
| Sales | Sam | 60000 |
+------------+----------+--------+
解释：
IT 部门中，Max 获得了最高的工资，Randy 和 Joe 都拿到了第二高的工资，
Will 的工资排第三。销售部门（Sales）只有两名员工，Henry 的工资最高，Sam 的工资排第二。'''
--SQL架构:分组内取前几名的问题，可以先group by然后用having count()来筛选，
--比如这题，找每个部门的工资前三名，那么先在子查询中用Employee和自己做连接，
--连接条件是【部门相同但是工资比我高】，那么接下来按照having count(distinct Salary) <= 2来筛选的原理是：
--如果【跟我一个部门而且工资比我高的人数】不超过2个，那么我一定是部门工资前三，
--这样内层查询可以查询出所有符合要求的员工ID.
--plan a 在比较部门前三名工资的子查询中，使用left join 连接employee自身，
--注意e1.Salary < e2.Salary 且having count(distinct e2.Salary) <= 2
select d.Name as Department,e.Name as Employee,e.Salary as Salary
from Employee as e left join Department as d
on e.DepartmentId = d.Id
--剔除不是部门工资前三名的e.id
where e.Id in
(
select e1.Id
from Employee as e1 left join Employee as e2
on e1.DepartmentId = e2.DepartmentId and e1.Salary < e2.Salary
group by e1.Id
--前三名 2可以改为任意值
having count(distinct e2.Salary) <= 2
)
and e.DepartmentId in (select Id from Department)
order by d.Id asc,e.Salary desc
--plan b 在比较部门前三名工资的子查询中，使用自连接方式 连接employee自身，
--注意e1.Salary<=e2.Salary 且having count(distinct e2.Salary)<=3
select d.name as Department,
e.name as Employee,
e.Salary as Salary
from Employee e left join Department d
on e.DepartmentId=d.Id
where e.id in (
select e1.Id from Employee e1,Employee e2
where e1.DepartmentId=e2.DepartmentId and e1.Salary<=e2.Salary
group by e1.Id
having count(distinct e2.Salary)<=3
)
and e.DepartmentId in (select Id from Department)
order by d.Id asc,e.Salary desc
############################################################################
--196删除重复的电子邮箱 delete
-- 1.inner join 2. 子连接(2表)
############################################################################
'''
196. 删除重复的电子邮箱
编写一个 SQL 查询，来删除 Person 表中所有重复的电子邮箱，重复的邮箱里只保留 Id 最小 的那个。
+----+------------------+
| Id | Email |
+----+------------------+
| 1 | john@example.com |
| 2 | bob@example.com |
| 3 | john@example.com |
+----+------------------+
Id 是这个表的主键。
例如，在运行你的查询语句之后，上面的 Person 表应返回以下几行:
+----+------------------+
| Id | Email |
+----+------------------+
| 1 | john@example.com |
| 2 | bob@example.com |
+----+------------------+
'''
--执行 SQL 之后，输出是整个 Person 表。
--使用 delete 语句。
--plan a
--inner join
delete p1
from Person p1
join Person p2
on p1.email = p2.email and p1.id>p2.id
--plan b
--自连接
delete p1
from person p1,person p2
where p1.Email = p2.Email
and p1.Id > p2.Id
############################################################################
--197上升的温度 left join 自己两表 on datediff(w1,w2)=1
############################################################################
'''
197. 上升的温度
SQL架构
给定一个 Weather 表，编写一个 SQL 查询，来查找与之前（昨天的）日期相比温度更高的所有日期的 Id。
+---------+------------------+------------------+
| Id(INT) | RecordDate(DATE) | Temperature(INT) |
+---------+------------------+------------------+
| 1 | 2015-01-01 | 10 |
| 2 | 2015-01-02 | 25 |
| 3 | 2015-01-03 | 20 |
| 4 | 2015-01-04 | 30 |
+---------+------------------+------------------+
例如，根据上述给定的 Weather 表格，返回如下 Id:
+----+
| Id |
+----+
| 2 |
| 4 |
+----+
'''
--dateDiff(w1.RecordDate,W2.RecordDate)=1
--plan a left join
select w1.Id from
Weather as w1
left join Weather as w2
on dateDiff(w1.RecordDate,W2.RecordDate)=1
where w1.Temperature > w2.Temperature
and w2.RecordDate is not null
--plan b 自连接
select w1.id from weather w1,weather w2
where datediff(w1.RecordDate,w2.RecordDate)=1
and w1.Temperature>w2.Temperature
############################################################################
--262行程和用户 非禁止用户的取消率 round(sum(Status!='completed')/count(status),2)
############################################################################
'''
262. 行程和用户
SQL架构
Trips 表中存所有出租车的行程信息。每段行程有唯一键 Id，
Client_Id 和 Driver_Id 是 Users 表中 Users_Id 的外键。
Status 是枚举类型，枚举成员为 (‘completed’, ‘cancelled_by_driver’, ‘cancelled_by_client’)。
+----+-----------+-----------+---------+--------------------+----------+
| Id | Client_Id | Driver_Id | City_Id | Status |Request_at|
+----+-----------+-----------+---------+--------------------+----------+
| 1 | 1 | 10 | 1 | completed |2013-10-01|
| 2 | 2 | 11 | 1 | cancelled_by_driver|2013-10-01|
| 3 | 3 | 12 | 6 | completed |2013-10-01|
| 4 | 4 | 13 | 6 | cancelled_by_client|2013-10-01|
| 5 | 1 | 10 | 1 | completed |2013-10-02|
| 6 | 2 | 11 | 6 | completed |2013-10-02|
| 7 | 3 | 12 | 6 | completed |2013-10-02|
| 8 | 2 | 12 | 12 | completed |2013-10-03|
| 9 | 3 | 10 | 12 | completed |2013-10-03|
| 10 | 4 | 13 | 12 | cancelled_by_driver|2013-10-03|
+----+-----------+-----------+---------+--------------------+----------+
Users 表存所有用户。每个用户有唯一键 Users_Id。Banned 表示这个用户是否被禁止，
Role 则是一个表示（‘client’, ‘driver’, ‘partner’）的枚举类型。
+----------+--------+--------+
| Users_Id | Banned | Role |
+----------+--------+--------+
| 1 | No | client |
| 2 | Yes | client |
| 3 | No | client |
| 4 | No | client |
| 10 | No | driver |
| 11 | No | driver |
| 12 | No | driver |
| 13 | No | driver |
+----------+--------+--------+
写一段 SQL 语句查出 2013年10月1日 至 2013年10月3日 期间非禁止用户的取消率。
基于上表，你的 SQL 语句应返回如下结果，取消率（Cancellation Rate）保留两位小数。
取消率的计算方式如下：(被司机或乘客取消的非禁止用户生成的订单数量) / (非禁止用户生成的订单总数)
+------------+-------------------+
| Day | Cancellation Rate |
+------------+-------------------+
| 2013-10-01 | 0.33 |
| 2013-10-02 | 0.00 |
| 2013-10-03 | 0.50 |
+------------+-------------------+
'''
--非禁止用户的取消率保留2位小数 用round(x,y),y就表示小数位数
--trips和users用inner join 连接 关系为t.Client_Id = u.Users_Id，
--Client_Id 和 Driver_Id 是 Users 表中 Users_Id 的外键。
--最后用where来限定条件
--group by 按照日期分组
select Request_at as 'Day',
round(sum(Status!='completed')/count(status),2)
as 'Cancellation Rate'
from Trips t join Users u
on t.Client_Id = u.Users_Id
where Banned = 'No'
and Request_at between '2013-10-01' and '2013-10-03'
group by Request_at
############################################################################
--595大国 where
############################################################################
'''
大国-SQL架构
这里有张 World 表
+-----------------+------------+------------+--------------+---------------+
| name | continent | area | population | gdp |
+-----------------+------------+------------+--------------+---------------+
| Afghanistan | Asia | 652230 | 25500100 | 20343000 |
| Albania | Europe | 28748 | 2831741 | 12960000 |
| Algeria | Africa | 2381741 | 37100000 | 188681000 |
| Andorra | Europe | 468 | 78115 | 3712000 |
| Angola | Africa | 1246700 | 20609294 | 100990000 |
+-----------------+------------+------------+--------------+---------------+
如果一个国家的面积超过300万平方公里，或者人口超过2500万，那么这个国家就是大国家。
编写一个SQL查询，输出表中所有大国家的名称、人口和面积。
例如，根据上表，我们应该输出:
+--------------+-------------+--------------+
| name | population | area |
+--------------+-------------+--------------+
| Afghanistan | 25500100 | 652230 |
| Algeria | 37100000 | 2381741 |
+--------------+-------------+--------------+
'''
--where 限定
select name,population,area from World
where area > 3000000 or population > 25000000
############################################################################
--596超过5名学生的课 group by + having
############################################################################
'''
596. 超过5名学生的课
SQL架构
有一个courses 表 ，有: student (学生) 和 class (课程)。
请列出所有超过或等于5名学生的课。
例如,表:
+---------+------------+
| student | class |
+---------+------------+
| A | Math |
| B | English |
| C | Math |
| D | Biology |
| E | Math |
| F | Computer |
| G | Math |
| H | Math |
| I | Math |
+---------+------------+
应该输出:
+---------+
| class |
+---------+
| Math |
+---------+
Note:
学生在每个课中不应被重复计算。
'''
--group by + having
select class from courses
group by class
having count(distinct student)>=5
############################################################################
--601体育馆的人流量 自连接(3表)+where and
############################################################################
'''
601. 体育馆的人流量
SQL架构
X 市建了一个新的体育馆，每日人流量信息被记录在这三列信息中：序号 (id)、日期 (visit_date)、 人流量 (people)。
请编写一个查询语句，找出人流量的高峰期。高峰期时，至少连续三行记录中的人流量不少于100。
例如，表 stadium：
+------+------------+-----------+
| id | visit_date | people |
+------+------------+-----------+
| 1 | 2017-01-01 | 10 |
| 2 | 2017-01-02 | 109 |
| 3 | 2017-01-03 | 150 |
| 4 | 2017-01-04 | 99 |
| 5 | 2017-01-05 | 145 |
| 6 | 2017-01-06 | 1455 |
| 7 | 2017-01-07 | 199 |
| 8 | 2017-01-08 | 188 |
+------+------------+-----------+
对于上面的示例数据，输出为：
+------+------------+-----------+
| id | visit_date | people |
+------+------------+-----------+
| 5 | 2017-01-05 | 145 |
| 6 | 2017-01-06 | 1455 |
| 7 | 2017-01-07 | 199 |
| 8 | 2017-01-08 | 188 |
+------+------------+-----------+
提示：
每天只有一行记录，日期随着 id 的增加而增加。
'''
--自连接(3表) where people>=100
-- id (a b c)排序 012 123 234三种情况 如果id=2
-- order by
--distinct 本题中，id为，5，6，7，8的满足。我们讨论id 为6的这一天， id为6 满足是因为，5，6，7； 也可以是因为6，7，8。
--id=7也是同理，6，7，8满足，5，6，7也满足
--每种情况都记录一次，那是不是这两种情况就记录了两次，重复了。
select distinct a.* from stadium a,stadium b,stadium c
where a.people>=100 and b.people>=100 and c.people>=100
and (
(a.id = b.id-1 and b.id = c.id -1) or
(a.id = b.id-1 and a.id = c.id +1) or
(a.id = b.id+1 and b.id = c.id +1)
)
order by a.id
############################################################################
--620有趣的电影 where + order by
############################################################################
'''
620. 有趣的电影
SQL架构
某城市开了一家新的电影院，吸引了很多人过来看电影。该电影院特别注意用户体验，
专门有个 LED显示板做电影推荐，上面公布着影评和相关电影描述。
作为该电影院的信息部主管，您需要编写一个 SQL查询，
找出所有影片描述为非 boring (不无聊) 的并且 id 为奇数 的影片，结果请按等级 rating 排列。
例如，下表 cinema:
+---------+-----------+--------------+-----------+
| id | movie | description | rating |
+---------+-----------+--------------+-----------+
| 1 | War | great 3D | 8.9 |
| 2 | Science | fiction | 8.5 |
| 3 | irish | boring | 6.2 |
| 4 | Ice song | Fantacy | 8.6 |
| 5 | House card| Interesting| 9.1 |
+---------+-----------+--------------+-----------+
对于上面的例子，则正确的输出是为：
+---------+-----------+--------------+-----------+
| id | movie | description | rating |
+---------+-----------+--------------+-----------+
| 5 | House card| Interesting| 9.1 |
| 1 | War | great 3D | 8.9 |
+---------+-----------+--------------+-----------+
'''
--where + order by
select id,movie,description,rating from cinema
where description != 'boring' and id%2=1
order by rating desc
############################################################################
--626换座位 case (when)
############################################################################
'''
626. 换座位
SQL架构
小美是一所中学的信息科技老师，她有一张 seat 座位表，平时用来储存学生名字和与他们相对应的座位 id。
其中纵列的 id 是连续递增的
小美想改变相邻俩学生的座位。
你能不能帮她写一个 SQL query 来输出小美想要的结果呢？
示例：
+---------+---------+
| id | student |
+---------+---------+
| 1 | Abbot |
| 2 | Doris |
| 3 | Emerson |
| 4 | Green |
| 5 | Jeames |
+---------+---------+
假如数据输入的是上表，则输出结果如下：
+---------+---------+
| id | student |
+---------+---------+
| 1 | Doris |
| 2 | Abbot |
| 3 | Green |
| 4 | Emerson |
| 5 | Jeames |
+---------+---------+
注意：
如果学生人数是奇数，则不需要改变最后一个同学的座位。
'''
--case when
--奇数+1 偶数—1，学生人数是奇数，则不需要改变最后一个同学的座位
--order by
select
(case
--id !=(select count(*)from seat) id！=5(id数目)
when id%2=1 and id !=(select count(*)from seat) then id+1
when id%2=0 then id-1
else id
end ) as id,student
from seat
order by id asc
############################################################################
--627交换工资 update()+1.if()
-- 2.case when
############################################################################
'''
627. 交换工资,考察if和case的用法，以及update
SQL架构
给定一个 salary 表，如下所示，有 m = 男性 和 f = 女性 的值。交换所有的 f 和 m 值
（例如，将所有 f 值更改为 m，反之亦然）。要求只使用一个更新（Update）语句，并且没有中间的临时表。
注意，您必只能写一个 Update 语句，请不要编写任何 Select 语句。
例如：
| id | name | sex | salary |
|----|------|-----|--------|
| 1 | A | m | 2500 |
| 2 | B | f | 1500 |
| 3 | C | m | 5500 |
| 4 | D | f | 500 |
运行你所编写的更新语句之后，将会得到以下表:
| id | name | sex | salary |
|----|------|-----|--------|
| 1 | A | f | 2500 |
| 2 | B | m | 1500 |
| 3 | C | f | 5500 |
| 4 | D | m | 500 |'''
--plan a
--if(x,y,z) 实现性别交换
UPDATE salary
SET sex = if(sex = "m","f","m")
--plan b
--case when else end
UPDATE salary
SET
sex = CASE sex
WHEN "m" THEN "f"
ELSE "m"
END;
############################################################################
--1179重新格式化部门表 一个元素的聚合函数=此元素 group by + 聚合函数(SUM，AVG，MIN，MAX，COUNT)
############################################################################
'''
1179. 重新格式化部门表
通过使用GROUP BY ,sql引擎为GROUP BY子句中的每个元素创建一组结果行，
并且仅允许针对多个值（例如SUM，AVG，MIN，MAX，COUNT）进行聚合操作。
由于我们的集合包含一个元素，因此我们无法直接从GROUP BY中提取该元素，因此我们使用了一种技巧-单个元素的聚合函数。
一个元素的聚合函数=此元素。
SQL架构
部门表 Department：
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| id | int |
| revenue | int |
| month | varchar |
+---------------+---------+
(id, month) 是表的联合主键。
这个表格有关于每个部门每月收入的信息。
月份（month）可以取下列值 ["Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"]。
编写一个 SQL 查询来重新格式化表，使得新的表中有一个部门 id 列和一些对应 每个月 的收入（revenue）列。
查询结果格式如下面的示例所示：
Department 表：
+------+---------+-------+
| id | revenue | month |
+------+---------+-------+
| 1 | 8000 | Jan |
| 2 | 9000 | Jan |
| 3 | 10000 | Feb |
| 1 | 7000 | Feb |
| 1 | 6000 | Mar |
+------+---------+-------+
查询得到的结果表：
+------+-------------+-------------+-------------+-----+-------------+
| id | Jan_Revenue | Feb_Revenue | Mar_Revenue | ... | Dec_Revenue |
+------+-------------+-------------+-------------+-----+-------------+
| 1 | 8000 | 7000 | 6000 | ... | null |
| 2 | 9000 | null | null | ... | null |
| 3 | null | 10000 | null | ... | null |
+------+-------------+-------------+-------------+-----+-------------+
注意，结果表有 13 列 (1个部门 id 列 + 12个月份的收入列)。
'''
SELECT
id,
max(if(month = 'Jan',revenue,null)) Jan_Revenue,
max(if(month = 'Feb',revenue,null)) Feb_Revenue,
max(if(month = 'Mar',revenue,null)) Mar_Revenue,
max(if(month = 'Apr',revenue,null)) Apr_Revenue,
max(if(month = 'May',revenue,null)) May_Revenue,
max(if(month = 'Jun',revenue,null)) Jun_Revenue,
max(if(month = 'Jul',revenue,null)) Jul_Revenue,
max(if(month = 'Aug',revenue,null)) Aug_Revenue,
max(if(month = 'Sep',revenue,null)) Sep_Revenue,
max(if(month = 'Oct',revenue,null)) Oct_Revenue,
max(if(month = 'Nov',revenue,null)) Nov_Revenue,
max(if(month = 'Dec',revenue,null)) Dec_Revenue
FROM Department GROUP BY id
############################################################################
hive
一.Hive的基本使用
1、使用进入 hive 窗口
进入linux 服务器后在任意位置输入 hive ，即可进入 hive 窗口
2、hiveSql 的两种运行方式
a、直接在窗口中执行 sql 语句：
b、使用 shell 脚本执行 sql 语句
方法一：hive -e 直接执行sqlY语句， hive -v -e 的v表示展示log信息
适用于当想把sql语句自动化执行时，就可以写成shell 脚本的方式，然后通过调度平台调用shell 脚本即可，是常用的一种方式
方法二：hive -f 执行sql脚本
适用于执行一下大规模的sql语句，但是相比较 hive -e 的方式，hive -f 不能使用 shell 变量没那么灵活，因此不是很常用。
方法三：hive -i 执行配置参数，但是会进入hive 窗口
二.HiveSql的使用
1、创建数据库
create database frog_db;
drop database frog_db;
注意一定执行了语句才算创建了数据库，之前很多同学以为在 linux 创建了同名文件夹，就认为hive数据库也已经创建了。
2、创建数据表
create table student(
id int comment 'id',
name string comment '姓名',
score decimal(30,6) comment '成绩')
stored as textfile;
常用的表字段类型
3、关于hive的数据表
- 查看表结构：
- 查看建表语句：会有一些默认的格式
- 自己指定建表格式：方便导入数据，用逗号分隔符分隔数据
use frog_db; drop table student; create table student( id int comment '识别码', name string comment '姓名', score string comment '成绩') row format delimited fields terminated by ',' lines terminated by '\n' stored as textfile;
lines terminated by '\n' （默认就是以换行区分一条记录，可以省略）
stored as textfile; （默认文件格式）
数据内容：
1,zhangsan,56.7 2,lisi,78.9 3,wagnwu,90.8 4,赵六,100
导入数据：
load data local inpath '/home/froghd/student.txt' into table student;
问题：建表格式中的 location 是什么意思？
答：这个是表数据文件在hdfs 的存放路径
快速复制
# 每个人的路径不一定相同，要看表的 location 是什么 hadoop fs -ls hdfs://localhost:9000/user/hive/warehouse/frog_db.db/student
# 在hive中也有命令可以直接访问 hdfs 路径文件 dfs -ls hdfs://localhost:9000/user/hive/warehouse/frog_db.db/student;
4、内部表和外部表
我们说表格式是建表会有默认值，如果我们不指定 location 那么创建的表就是内部表，如果指定了 location呢，那就是外部表。上面我们分析 load data 命令可以知道，其实就是把数据从 linux 上放到了 hdfs 路径上。
那我们来看一下外部表要怎么创建
use frog_db; drop table student; create external table student( id int comment '识别码', name string comment '姓名') row format delimited fields terminated by ',' lines terminated by '\n' stored as textfile location '/tmp/student';
hadoop fs -mkdir /tmp/student
hadoop fs -put student.txt /tmp/student
hadoop fs -ls /tmp/student
删除表时，内部表中的数据和元数据将会被同时删除，而外部表只删除元数据，不删除数据。
5、分区表
在表目录中为数据文件创建分区子目录，以便于在查询时，MR 程序可以针对分区子目录中的数据进行处理，缩减读取数据的范围（不然就要全部读取）。
比如：网站每天产生的浏览记录，浏览记录应该建一个表来存放，但是，有时候，我们可能只需要对某一天的浏览记录进行分析，这时，就可以将这个表建为分区表，每天的数据导入其中的一个分区，当然，每日的分区目录，应该有一个目录名（分区字段）。
# partitioned by（day string）就是分区的依据 use frog_db; drop table pv_log; create table pv_log( ip string, url string, visit_time string) partitioned by(day string) row format delimited fields terminated by ',';
# 建表语句，只会建表目录，分区的目录是在放数据的时候建立，先建表再放数据
27.38.32.58,http://www.baidu.com.cn,2006-12-13 12:34:16 27.38.32.59,http://www.baidu.com.cn,2011-08-13 08:37:16 27.38.32.60,http://www.baidu.com.cn,2006-12-13 12:24:16 27.38.32.61,http://www.baidu.com.cn,2016-06-13 06:34:16 27.38.32.54,http://www.baidu.com.cn,2012-12-15 12:34:16 27.38.32.55,http://www.baidu.com.cn,2009-08-13 09:24:16 27.38.32.57,http://www.baidu.com.cn,2005-12-13 12:14:16 27.38.32.50,http://www.baidu.com.cn,2003-07-16 10:04:16 27.38.32.52,http://www.baidu.com.cn,2007-12-13 12:34:16
# hive 执行导入数据，需要指定分区是什么 load data local inpath '/home/froghd/pv.log' into table pv_log partition (day='20150620');
show partitions pv_log;
6、CTAS 建表语法
通过已存在的表来建表
# 创建一个和 table1 一样字段的 table2 表 create table table2 like table1
create table pv_log1 like pv_log;
# 创建一个表类似与已有表，不仅字段一样而且还带有数据（好像不会有分区），查出来是什么字段名新表就是什么字段名
create table pv_log2 as select * from pv_log where visit_time>'2006-12-13';
三.Hive的常用函数
1、case when（有点类似 switch case）
# 这里 case when 就是返回一个值，就看 score 是属于什么范围 select id, name, case when score<=60 then '不及格' when score>60 and score <=80 then '良好' else '优秀' end as grade from student;
2、if 语句，类似一个三元表达式
# 如果分数大于60，就返回 pass，不然就返回 fail
select id，if (score>=60,'pass','fail') from student;
3、nvl函数：空值转换函数
函数形式：nvl（expr1，expr2）
适用于数字型、字符型和日期型，但是expr1和expr2的数据类型必须为相同类型。
作用：将查询为Null值转换为指定值
# 查询插入一条 null 值数据 insert into student select 5, 'frog', null;
# 如果 score 字段为空，就返回 0 ，不为空就是 score 本身 select score,nvl(score,0) from student;
四.Hive的窗口函数
1、row_number() over()
eg：有如下数据，要查出每种性别中年龄最大的2条数据
1,18,a,male 2,19,a,male 3,22,a,female 4,16,b,female 5,30,b,male 6,26,b,female
use frog_db; drop table userinfo; create table userinfo( id string, age int, title string, sex string) row format delimited fields terminated by ',' lines terminated by '\n' stored as textfile;
# 导入数据
load data local inpath '/home/froghd/data.txt' into table userinfo;
答：这里先 partition by sex字段分组，然后根据每一组的 age 字段降序排序。得到的序号 1,2,3 等取名为 rn ，然后通过 where 判断 前两个就是结果
# rn 字段是一个分组标记 序号，如下图是中间（select ...）括号的结果
select id, age, title, sex, row_number() over(partition by sex order by age desc) as rn from userinfo;
select * from (select id, age, title, sex, row_number() over(partition by sex order by age desc) as rn from userinfo) a where a.rn<3;
应用：可以用来对数据去重，当想保留数据一条时，就可以让 rn=1 ，这样就只取了每组数据的一条记录，达到去重的目的。
2、sum() over()
例如有一份产品的消费记录数据，要求按时间累计统计到当月的总额
A,2012-01,1000 A,2012-02,2030 A,2012-03,3600 A,2012-04,6008 A,2012-05,3000 B,2012-01,2000 B,2012-02,2300 B,2012-03,1800 B,2012-04,2000 B,2012-05,1300 B,2012-06,1600 B,2012-07,5000 C,2012-01,1020 C,2012-02,2000 C,2012-03,3200 C,2012-04,6000 C,2012-05,5300 C,2012-06,8800 C,2012-07,9000
use frog_db; drop table saleinfo; create table saleinfo( product_name string, month string, money string) row format delimited fields terminated by ',' lines terminated by '\n' stored as textfile;
# 导入数据
load data local inpath '/home/froghd/data1.txt' into table saleinfo;
select product_name, month, money, sum(money) over(partition by product_name order by month) as all_money from saleinfo;
相关面试题
sql
1.如何取连续7天登录的用户
select id,count(*) from ( select *,date(日期)-cum as 结果 from ( select *,row_number() over(PARTITION by id order by 日期) as cum from( select DISTINCT date(date) as 日期,id from orde) a -- 用户登录日期去重 ) b -- row_number() 按照日期升序排列 ) c -- date(日期)-cum 作为‘结果’ GROUP BY id,结果 -- 只有当id和结果都一致时 having count(*)>=7 ; -- 去掉这句可以得到每个用户连续登录的天数
2.having和where的区别
having 子句的作用是筛选满足条件的组，即在分组之后过滤数据，条件中经常包含聚组函数，使用having 条件显示特定的组，也可以使用多个分组标准进行分组。
3.null与' '的区别
1）null表示空，用is null判断
2）''表示空字符串，用=''判断
4.sql的数据类型
1）字符串：char、varchar、text
2）二进制串：binary、varbinary
3）布尔类型：boolean
4）数值类型：integer、smallint、bigint、decimal、numeric、float、real、double
5）时间类型：date、time、timestamp、interval
5.sql内连接与外连接的区别
1）内连接：左右表取匹配行
2）外连接：分为左连接、右连接和全连接
6.笛卡尔积（连接）
笛卡尔积原本是代数的概念，他的意思是对于两个不同的集合A，B。对于A中的每一个元素，都有对于在B中的所有元素做连接运算 。可以见得对于两个元组分别为m，n的表。笛卡尔积后得到的元组个数为m x n个元组。而对于mysql来说，默认的连接就是笛卡尔积连接
7.数据库连接类型及区别
https://blog.csdn.net/y_dzaichirou/article/details/53804273
'''连接数据库的两种方法 1.pymysql 2.pandas''' #1.pymysql import pymysql #建立连接 conn=pymysql.connect( host='localhost',#or '127.0.0.1' user='root', password='19940811', db='data', port=3306, charset='utf8' ) #建立一个游标 cur=conn.cursor() cur.execute('select * from company')#cur.execute("select * from company where conpanySize='150-500人'") data = cur.fetchall() #print(data) #关闭cur和conn #cur.close() #conn.close() #2.pandas import pandas as pd from sqlalchemy import create_engine sql = "select * from company where companyShortName='爱贝云计费'" engine=create_engine('mysql+pymysql://root:19940811@localhost:3306/data?charset=utf8') company=pd.read_sql(sql,engine) # print(company) # companyId companyFullName companyLabelList companyShortName companySize businessZones # 0 138 爱贝信息技术有限公司 ['岗位晋升', '领导好', '移动互联网', '弹性工作'] 爱贝云计费 150-500人 ['科技园', '南山医院', '深圳湾']
回购率
-- 回购率：三月份购买的人数中，四月份依旧购买的人数占比 select t1.m,count(t1.m),count(t2.m)，count(t2.userId)/count(t1.userId) as percent from( SELECT userId,date_format(paidTime,'%Y-%m-01') as m FROM data.orderinfo where isPaid = '已支付' group by userId,date_format(paidTime,'%Y-%m-01')) as t1-- 分组查看userId按月消费情况 left join ( SELECT userId,date_format(paidTime,'%Y-%m-01') as m FROM data.orderinfo where isPaid = '已支付' group by userId,date_format(paidTime,'%Y-%m-01')) as t2 on t1.userId = t2.userId and t1.m = date_sub(t2.m,interval 1 month) -- 对于同一商品orderId,其购买月份统计 group by t1.m
复购率
-- 复购率：当月中所有消费人数中，有多少是消费一次以上占比 select count(ct) as total, count(if(ct>1 ,1,null)) as bigger_two, -- count( case when ct>1 then 1 else null end) as bigger_two count(if(ct>1 ,1,null))/count(ct) as percent from ( -- 统计不同userId的三月份购买次数 SELECT userId,count(userId) as ct FROM data.orderinfo where isPaid = '已支付' and month(paidTime) = 3 group by userId) as t
8.sql窗口函数
hive
1.hive和spark的区别
1）Hive允许使用类SQL语句在hadoop集群上进行读、写、管理等操作
2）Spark是一种与hadoop相似的开源集群计算环境，将数据集缓存在分布式内存中的计算平台，每轮迭代不需要读取磁盘的IO操作，从而答复降低了单轮迭代时间
2.hive和sql的区别
1.查询语言不同：hive是hql语言，mysql是sql语句；
2.数据存储位置不同：hive是把数据存储在hdfs上，而mysql数据是存储在自己的系统中；
3.数据格式：hive数据格式可以用户自定义，mysql有自己的系统定义格式；
4.数据更新：hive不支持数据更新，只可以读，不可以写，而sql支持数据更新；
5.索引：hive没有索引，因此查询数据的时候是通过mapreduce很暴力的把数据都查询一遍，也造成了hive查询数据速度很慢的原因，而mysql有索引；
6.延迟性：hive延迟性高，原因就是上边一点所说的，而mysql延迟性低；
7.数据规模：hive存储的数据量超级大，而mysql只是存储一些少量的业务数据；
8.底层执行原理：hive底层是用的mapreduce，而mysql是excutor执行器；
3.行储存和列储存的区别
1）行存储：传统数据库的存储方式，同一张表内的数据放在一起，插入更新很快。缺点是每次查询即使只涉及几列，也要把所有数据读取
2）列存储：OLAP等情况下，将数据按照列存储会更高效，每一列都可以成为索引，投影很高效。缺点是查询是选择完成时，需要对选择的列进行重新组装。
“当你的核心业务是 OLTP 时，一个行式数据库，再加上优化操作，可能是个最好的选择。
当你的核心业务是 OLAP 时，一个列式数据库，绝对是更好的选择”
4.map join的优化方法
Map Join 的计算步骤分两步，将小表的数据变成hashtable广播到所有的map 端，将大表的数据进行合理的切分，然后在map 阶段的时候用大表的数据一行一行的去探测(probe) 小表的hashtable. 如果join key 相等，就写入HDFS.
map join 之所以叫做map join 是因为它所有的工作都在map 端进行计算.
hive 在map join 上做了几个优化：
hive 0.6 的时候默认认为写在select 后面的是大表，前面的是小表， 或者使用 /*+mapjoin(map_table) */ 提示进行设定. hive 0.7 的时候这个计算是自动化的，它首先会自动判断哪个是小表，哪个是大表，这个参数由（hive.auto.convert.join=true）来控制. 然后控制小表的大小由（hive.smalltable.filesize=25000000L）参数控制（默认是25M），当小表超过这个大小，hive 会默认转化成common join. 你可以查看HIVE-1642. 首先小表的Map 阶段它会将自己转化成MapReduce Local Task ，然后从HDFS 取小表的所有数据，将自己转化成Hashtable file 并压缩打包放入DistributedCache 里面.
目前hive 的map join 有几个限制，一个是它打算用BloomFilter 来实现hashtable , BloomFilter 大概比hashtable 省8-10倍的内存, 但是BloomFilter 的大小比较难控制.
现在DistributedCache 里面hashtable默认的复制是3份，对于一个有1000个map 的大表来说，这个数字太小，大多数map 操作都等着DistributedCache 复制.
5.静态分区和动态分区
静态分区 SP（static partition）
1、静态分区是在编译期间指定的指定分区名
2、支持load和insert两种插入方式
2.1load方式
1）会将分区字段的值全部修改为指定的内容
2）一般是确定该分区内容是一致的时候才会使用
2.2insert方式
1）必须先将数据放在一个没有设置分区的普通表中
2）该方式可以在一个分区内存储一个范围的内容
3）从普通表中选出的字段不能包含分区字段
3、适用于分区数少，分区名可以明确的数据
动态分区 DP（dynamic partition）
1、根据分区字段的实际值，动态进行分区
2、是在sql执行的时候进行分区
3、需要先将动态分区设置打开（set hive.exec.dynamic.partition.mode=nonstrict ）
4、只能用insert方式
5、通过普通表选出的字段包含分区字段，分区字段放置在最后，多个分区字段按照分区顺序放置
6.hive的并行化操作，管道操作
7.内部表和外部表的区别
Hive 创建内部表时，会将数据移动到数据仓库指向的路径；若创建外部表，仅记录数据所在的路径，不对数据的位置做任何改变。在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据。这样外部表相对来说更加安全些，数据组织也更加灵活，方便共享源数据。
需要注意的是传统数据库对表数据验证是 schema on write（写时模式），而 Hive 在load时是不检查数据是否符合schema的，hive 遵循的是 schema on read（读时模式），只有在读的时候hive才检查、解析具体的数据字段、schema。
读时模式的优势是load data 非常迅速，因为它不需要读取数据进行解析，仅仅进行文件的复制或者移动。
写时模式的优势是提升了查询性能，因为预先解析之后可以对列建立索引，并压缩，但这样也会花费要多的加载时间。
8.hive字符串操作，窗口函数
Rank排名函数
RANK() 排序相同时会重复，总数不会变；
DENSE_RANK() 排序相同时会重复，总数会减少；
ROW_NUMBER() 根据顺序计算排名。
在实际开发中，以上三个rank函数通常是和开窗函数一起使用的。
窗口函数（开窗函数）
OVER()：用于指定分析函数工作时的数据窗口大小，这个数据窗口大小可能会随着行的变而变化；
CURRENT ROW：当前行；
n PRECEDING：往前n行数据；
n FOLLOWING：往后n行数据；
UNBOUNDED：起点，UNBOUNDED PRECEDING 表示从前面的起点， UNBOUNDED FOLLOWING表示到后面的终点；
LAG(col,n,default_val)：往前第n行数据；
LEAD(col,n, default_val)：往后第n行数据；
NTILE(n)：把有序分区中的行分发到指定数据的组中，各个组有编号，编号从1开始，对于每一行，NTILE返回此行所属的组的编号。这个函数需要注意：n必须为int类型。
某商店有如下一张用户订单表order_table，其中记录了用户名，订单时间及订单金额，以此表为例，可以提出多个用窗口函数解决的小问题。相关数据如下：
（1）查询本店2017年1月份有购买行为的顾客姓名及购买次数。
解题思路：
可以采用一般的聚合函数count()，也可以使用窗口函数count() over()；对于日期的限制可采用各种方式，如日期转换函数，也可以使用substr()。
select user_name,count(1) as cnt from order_table where year(order_time)=2017 and month(order_time)=1 group by user_name
（2）查询顾客的购买明细及每个月所有顾客的购买总额。
解题思路：
由于需要每位顾客明细后都要带一个当月所有顾客购买总额的字段，因此可以选择使用窗口函数中的sum() over()获得这个字段。
select user_name,order_time,order_amt, sum(order_amt) over(partition by month(order_time)) as month_sum_amt from order_table
查询顾客的购买明细及每个月该顾客的购买总额。
select user_name,order_time,order_amt, sum(order_amt) over(partition by month(order_time),user_name) as month_sum_amt from order_table
（3）查询整个订单信息中前20%时间的订单信息。
解题思路：
排序，取前20%。以前我有过一种操作是先使用窗口函数中的row_number()进行排序，搞一个rank，然后取最大rank的20%作为限制条件，取到前20%。后来发现别人都有顶好用的ntile！
-- 查询整个订单信息中前20%时间的订单信息。 select * from ( select user_name,order_time,order_amt,ntile(5) over(order by order_time) as 'RANK' from order_table) t where t.RANK=1 -- 分桶（分组排序），按照你的意愿进行设置，分为几个桶，比如5个， -- 每个桶占20%的记录，每个桶会有编号，取出想取的编号对应的数据即可。
（4）查询客户上一次购买的时间或者下一次购买
select user_name,order_time,order_amt, lag(order_time) over(partition by user_name order by order_time) as t from order_table
select user_name,order_time,order_amt, lead(order_time) over(partition by user_name order by order_time) as t from order_table
查询学生测试次数(包含次数为0)笛卡尔积
9.数据倾斜如何处理
1）倾斜原因：
map输出数据按key Hash的分配到reduce中，由于key分布不均匀、业务数据本身的特、建表时考虑不周、等原因造成的reduce 上的数据量差异过大。
（1）key分布不均匀;
（2）业务数据本身的特性;
（3）建表时考虑不周;
（4）某些SQL语句本身就有数据倾斜;
如何避免：对于key为空产生的数据倾斜，可以对其赋予一个随机值。
2）解决方案
（1）参数调节：
hive.map.aggr = true
hive.groupby.skewindata=true
有数据倾斜的时候进行负载均衡，当选项设定位true,生成的查询计划会有两个MR Job。第一个MR Job中，Map的输出结果集合会随机分布到Reduce中，每个Reduce做部分聚合操作，并输出结果，这样处理的结果是相同的Group By Key有可能被分发到不同的Reduce中，从而达到负载均衡的目的；第二个MR Job再根据预处理的数据结果按照Group By Key 分布到 Reduce 中（这个过程可以保证相同的 Group By Key 被分布到同一个Reduce中），最后完成最终的聚合操作。
（2）SQL 语句调节：
① 选用join key分布最均匀的表作为驱动表。做好列裁剪和filter操作，以达到两表做join 的时候，数据量相对变小的效果。
② 大小表Join：
使用map join让小的维度表（1000 条以下的记录条数）先进内存（小表在左，大表在右）。在map端完成reduce.
③ 大表Join大表：
把空值的key变成一个字符串加上随机数，把倾斜的数据分到不同的reduce上，由于null 值关联不上，处理后并不影响最终结果。
④ count distinct大量相同特殊值:
count distinct 时，将值为空的情况单独处理，如果是计算count distinct，可以不用处理，直接过滤，在最后结果中加1。如果还有其他计算，需要进行group by，可以先将值为空的记录单独处理，再和其他计算结果进行union。
linux系统
1.linux系统基本命令
1）目录操作：ls、cd、mkdir、find、locate、whereis等
2）文件操作：mv、cp、rm、touch、cat、more、less
3）权限操作：chmod+rwx421
4）账号操作：su、whoami、last、who、w、id、groups等
5）查看系统：history、top
6）关机重启：shutdown、reboot
7）vim操作：i、w、w!、q、q!、wq等
2.linux权限的问题
https://www.nowcoder.com/questionTerminal/2276e48a891f4ddfaee6bbacec1d5860?toCommentId=2522135
3.shell语句中可生成/test文件的写法
https://www.nowcoder.com/questionTerminal/9cc17bd0d6404e7e86369fc7d4c9d212?toCommentId=3648658
4.grep是什么
Linux grep 命令用于查找文件里符合条件的字符串
https://www.runoob.com/linux/linux-comm-grep.html
5.hadoop是什么
https://blog.csdn.net/hsl_1990_08_15/article/details/50470214