数据分析——跟随秦路的第9、10篇，SQL

https://zhuanlan.zhihu.com/p/25203710

 

下面是各链接：

MySQL下载: https://www.mysql.com/downloads/

WorkBench下载: https://dev.mysql.com/downloads/workbench/ （win）

在线SQL练习：http://sqlzoo.net/wiki/SELECT_basics/zh

SQL简易教程：http://www.w3school.com.cn/sql/index.asp

练习：https://leetcode.com/problemset/database/

 

一、

数据库是逻辑上的概念，它是一堆互相关联的数据，放在物理实体。是一堆写在磁盘上的文件，文件中有数据。

1、group by 的功能类似数据透视表（后面有几个字段就相当于几维分析，所以group by的字段一定要select出来的），它常与聚合函数搭配使用来计算每组的特征。若不与聚合函数搭配，则group by 相当于distinct的功能。

2、count(1)、count(2)分别是以第一列为计数标准、以第二列，还有count(*)，其实大家结果应该都是一样的，毕竟每列行数一样么。若不想重复计数，可以与distinct搭配使用：count(distinct 字段)。count计算时0和1都会被计数，但null不会

3、if，达到有条件的select，类似where的作用。但if可直接接在select后面，当需要select别的的时候，很方便。

4、having 对聚合后的结果进行过滤

5、时间，可以直接select出来，无需from表

select now()、select week(now(),0) .......etc，等价于weekofyear、dayofyear........etc

时间加减法、计算时间间隔：

date_add(date(now()),interval 1 day) (1可正可负)、date_diff(date1,date2)、time_diff

6、数据清洗：left、right、mid、locate(类似excel的find)、substr(字段，从哪里开始截，截几个)(截取，类似excel的mid)

 

二、下面是三道思考题：

• 查询出哪家公司招聘的岗位数最多；
• 查询出O2O、电子商务、互联网金融这三个行业，哪个行业的平均薪资最高；
• 查询出各城市的最高薪水Top3是哪家公司哪个岗位。

第一题：

select companyfullname, count(distinct positionid) as counts
from da_7.da
group by companyfullname
order by counts desc
limit 1

bug在于若最大值不止一个，便会少计算掉；下面的怎么跑出来都不对，max(counts)倒是对的，只不过nam不是max对应的额，是子循环表里的第一项，不懂。。。。。。

select nam,max(counts) from (
select companyfullname as nam, count(distinct positionid) as counts
from da_7.da
group by companyfullname) as t1

第二题：

select industryfield, avg((bottomsalary+topsalary)/2) as avgsalary
from(select industryfield,
            left(salary,locate("k",salary)-1) as bottomsalary, 
            substr(salary,locate("-",salary)+1,length(salary)-locate("-",salary)-1) as topsalary
    from da_7.da) as t1
where industryfield in ("O2O","电子商务","移动互联网")
group by industryfield

第三题：我不会。。。。这是评论里的答案。。。

 

（针对第3道参考题，我的答案如下，仅供参考：

 

我的思路：

1、首先创建一个视图，主要是加上topSalary一列用于查询；

2、逐条取每一条记录，判断同一个城市中，大于当前记录最高薪水的条数是不是小于3条，再加上它本身正好是3条，这样就取出每个城市中3条最高薪水的记录（城市、公司、岗位、最高薪水）。

 

注意：

1、视图中的topSalary需要把字符转换为数字，用于下面的查询语句计算和排序，转换使用了cast函数；

2、查询结果topSalary可能会有重复，因为不同公司发布的岗位最高薪水可能会有相同数值，出现这种情况将会导致查询结果同一城市中的记录数超过3条；

 

CREATE view v1 as select city,companyShortName,positionName,cast(substr(salary,locate("-",salary)+1,length(salary)- locate("-",salary)-1) as signed) as topSalary
				from DataAnalyst
				where salary not like '%以上%'

SELECT * FROM v1 aa WHERE 3>(SELECT COUNT(*) FROM v1
WHERE city = aa.city and topSalary > aa.topSalary)
ORDER BY aa.city,aa.topSalary DESC 

）
2是父查询了吧》