美女做瑷网站/第一推广网

感谢该博主总结的思路，为hive学习和面试提供系统性的指导，本文是借鉴该楼主的文章进行练习，无眠：数据分析面试必备——SQL你准备好了吗？

一、最基本

1.选择某列

select a from table_1;

2.表连接：多张表中，想选取多个字段？

select a.id,a.age,b.sex

from (select id,age from table-1)a

join

(select id , sex from table_2) b

on a.id=b.id;

知识点：join

知识点：union和join的区别

二、最常用(多重组合)

1、如果有千万用户数据，想知道有多少去重的用户数？—— 去重 distinct

罗列不同的id

select distinct id from table_1;

统计不同的id个数

select count(distinct id) from table_1;

优化后

select count(*) from

(select distinct id from table_1) tb;

2、想分性别进行统计，看看男女各多少？—— 聚合函数和groupby

#统计不同性别(男、女)中，不同id的个数

select count(distinct id) from table_1

group by sex;

#统计最大最小平均年龄

select max(age),min(age),avg(age) from table_1

group by id;

易错点：直接写select count(*) from table_1 group by sex;没有对相同的id进行去重，非常重要！！！！！！！！！！！！

3、只想查看A公司的男女人数数据？—— 筛选 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、希望查询结果从高到低/从低到高排序？—— 排序 orderby

#按年龄全局倒序排序取最年迈的签10个人

select id, age from table_1

order by age desc

limit 10;将数值型的变量转化为分类型的变量？ —— case when 条件函数

5、将数值型的变量转化为分类型的变量？ —— casewhen 条件函数

此种情况常见于将某列进行分类或者分组

#收入分组

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;

6、字符串的相关常见函数concat(a,b.....)返回的是a和b的连在一起的字符串

select concat('www','ooo') from a;

wwwooosplit(str,regex)用于将string类型按regex提取，分割后转化成array序列

以regex为分隔符分隔str,并返回array序列

substr(str,0,len) 截取字符串从0位开始的长度为len个字符。

select substr('abcde',3,2) from a；

cd

三、基础进阶

1、不想全局排序，需要分组排序？—— row_number()

select *,

row_number()over(order by salary desc) as rn1 --全局按照salary下降排序row_number()over(partition by deptid order by salary desc) as rn2 --分组按照salary下降排序from table1;

2、想要获取top10%的值？—— percentile 百分位函数、ntile(n)函数也可以做到

3、想要对时间字段进行操作？—— 时间函数

to_date('datetime') ;--转化为日期函数date_format('datetime’,'YYYY-mm-dd')---日期格式化datediff('2016-12-30','2016-12-29')-----日期相差天数dateadddatesubfrom_unixtime()时间戳转化为日期unixtime()日期转化为时间戳

四、常见笔试/面试题:

学生表：SID，Sname,Sage,Ssex

课程表：CID，Cname，TID

教师表：TID,Tname

成绩表：SID， CID， score

#1.2查询同时存在01课程和02课程的学生姓名

select Sname from student a

inner join

(select SID from score where CID='01') b

on a.SID= b.SID

inner join

(select SID from score where CID='02') c

on a.SID=c.SID;

select Sname from student a

inner join score b

on a.SID=b.SID AND b.CID='01'

inner join score c

on a.SID=c.SID AND c.CID='02';

#8.查询没有学过张三老师授课的同学的信息

select * from student

where SID

not in

(select SID from score a

inner join course b

on a.CID=b.CID

INNER JOIN teacher c

on b.TID=c.TID

where Tname='张三');

#15查出两门及以上不及格课程的同学学号姓名及其平均成绩

select a.SID,a.Sname,avg(b.score) from student a

inner join score b

on a.SID=b.SID

where a.SID IN

(SELECT SID FROM SCORE where score<60 group by SID having count(*)>=2)

group by a.SID;