扫码加入数据分析学习群

一、最基础（选数据）

1.怎么把数据从表里选出来？

-- 从table_1中选择a这一列
select a from table_1

2.表连接

-- table_1中有id,age；table_2中有id,sex。想取出id,age,sex 三列信息
select table_1.id,age,sex
from table_1 
left join table_2
on table_1.id = table_2.id; 

在这里先介绍一下几种join: （敲重点，很容易问的哦）

join : join默认是inner join，找出左右都可匹配的记录

eft join: 左连接，以左表为准，逐条去右表找可匹配字段，如果有多条会逐次列出，如果没有找到则是NULL；

right join：右连接，以右表为准，逐条去左表找可匹配字段，如果有多条会逐次列出，如果没有找到则是NULL

full outer join: 全连接，包含两个表的连接结果，如果左表缺失或者右表缺失的数据会填充NULL

每种join 都有on , on的是左表和右表中都有的字段。join 之前要确保关联键是否去重，是不是刻意保留非去重结果。

3. 两张表数据的字段一样，想合并起来，怎么办？

-- 不去重，合并两张表的数据
select * from table_1 UNION ALL select * from table_2;

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
-- 统计最大/最小/平均年龄s
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');

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);

-- 得到cd

三.基础进阶

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天后的日期