数据分析SQL必会语句
- 基本 select 语句
- 最常用
- 基础进阶
- 常见笔试/面试题
基本 select 语句
- 把数据从表中取出
--从table_1中选择a这一列
select a from table_1
- 双表连接查询
--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
- 合并两张表
--不去重,合并两张表的数据
select * from (select id from table_1 UNION ALL select id from table_2)t;
union
和union all
均基于列合并多张表的数据,所合并的列格式必须完全一致。union
的过程中会去重并降低效率,union all
直接追加数据。union
前后是两段select 语句而非结果集。
最常用
- 去重
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,而影响效率,因此适合改写为子查询。
- 聚合函数和
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
- 筛选
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;
- 排序
order by
-- 按年龄全局倒序排序取最年迈的10个人
select id,age from table_1 order by age DESC
limit 10
- 将数值型的变量转化为分类型的变量,
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.1concat( A, B...)
返回将A和B按顺序连接在一起的字符串,如:concat('foo', 'bar')
返回’foobar’
select concat('www','.baidu','.com') from iteblog;
--得到 www.baidu.com
6.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
6.3 substr(str,0,len)
截取字符串从0
位开始的长度为len
个字符。
select substr('abcde',3,2) from iteblog;
-- 得到cd
基础进阶
- 不想全局排序,需要分组排序——
row_number()
-- 按照字段salary倒序编号
select *, row_number() over(order by salary desc) as row_num from table_1;
-- 按照字段deptid分组后再按照salary倒序编号
select *, row_number() over(patition by depid 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()
则在排序相同时不重复,会根据顺序排序。
- 获取top10%的值——
percentile
百分位函数
-- 获取income字段的top10%的阈值
select percentile (CAST (salary as int),0.9) as income_top10p from table_1;
-- 获取income字段的10个百分位点
select percentile(CAST (salary as int),0.9), 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;
- 对时间字段进行操作—— 时间函数
-- 转换为时间数据的格式
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
天后的日期。
常见笔试/面试题
例:有3个表S,C,SC:
S(SNO,SNAME)代表(学号,姓名)
C(CNO,CNAME,CTEACHER)代表(课号,课名,教师)
SC(SNO,CNO,SCGRADE)代表(学号,课号,成绩)
问题:
- 找出没选过“黎明”老师的所有学生姓名。
- 列出2门以上(含2门)不及格学生姓名及平均成绩。
- 既学过1号课程又学过2号课所有学生的姓名。
1. -- 考察条件筛选
select sname from s where sno not in
( select sno from sc where cno in
(
select distinct cno from c where cteacher='黎明'
)
);
2. -- 考察聚合函数,条件筛选
select s.sname, avg_grade from s
join
(select sno from sc where scgrade < 60 group by sno having count(*) >= 2) t1
on s.sno = t1.sno
join
(select sno, avg(scgrade) as avg_grade from sc group by sno ) t2
on s.sno = t2.sno;
3. -- 考察筛选、连接
select sname from
( select sno from sc where cno = 1) a
join
(select sno from sc where cno = 2) b
on a.sno = b.sno