2021-05-21 SQL数据清洗+牛客（11-20）

PART A

数据清洗一些函数，内容来自公众号：俊红的数据分析之路

数据清洗是数据分析必不可少的环节，常见的数据清洗方法主要有缺失值填充、数值替换、数据类型转换、数据分列、重复值处理等。

一、缺失值处理。
可以使用均值、线性插值等方法进行填充，这里使用均值来作为填充值填充，首先计算分数的平均值，然后筛选分数为空的行，使用UPDATE函数将空值填充为平均值。

#计算score列的均值
SELECT AVG(score) AS avg_score 
FROM score;
#使用均值填充空值
UPDATE score SET score=74 
WHERE score is null;
select * from score;

二、数值替换
REPLACE函数可以对字符进行替换，类似于EXCEL的替换功能。

#修改和替换
UPDATE course
SET Cname=REPLACE(Cname,'语文','语文-A');
select * from course;

三、大小写转换
小写转换可以用LOWER函数，大写转换可以用UPER函数，这里使用LOWER函数将大写A转换为小写a。

#大小写转换
UPDATE course
SET Cname=LOWER(Cname);
select * from course;

四、数据类型转换
数据类型转换用CONVERT函数，括号中第一个写要转换类型的值，第二个写要转换成的数据类型，此处将出生日期转换为日期类型。

SELECT Sname,CONVERT(Sage,date) as '出生日期' 
FROM student;

五、数据分列
数据分列与EXCEL的功能相似，这里使用到的函数是SUBSTRING_INDEX，这里将"语文-A"，分列成科目 语文 与评级为 A 两部分。

#数据分列
SELECT
SUBSTRING_INDEX(cname,'-',1) AS '科目',
SUBSTRING_INDEX(cname,'-',-1) AS '评级'
FROM course;

分列后的数据可以通过更新增加在原数据表中，下面是具体的代码。

#数据分列（改表），添加两个空字段
ALTER TABLE course  
ADD (col1 VARCHAR(255),col2 VARCHAR(255));
SELECT * FROM course;

#更新分列后的字段内容
UPDATE course SET col1 = SUBSTRING_INDEX(cname,'-',1),
col2 =SUBSTRING_INDEX(cname,'-',-1);
SELECT * FROM course;

六、重复值处理（distinct）

#重复值
SELECT DISTINCT CId,Cname 
FROM course;

补充：where子句中不能有聚合函数
sql语句的执行过程是：from–>where–>group by -->having — >order by --> select;

聚合函数是针对结果集进行的，但是where条件并不是在查询出结果集之后运行，所以主函数放在where语句中，会出现错误，

而having不一样，having是针对结果集做筛选的，所以我们一般把组函数放在having中，用having来代替where，having一般跟在group by后

PART B 牛客11-20题

11.查找employees表所有emp_no为奇数，且last_name不为Mary的员工信息，并按照hire_date逆序排列
考察点：奇数偶数判断：取余% / 整除&； 不等于的三种表达方式<>、！=、is not

select *
from employees
where emp_no % 2 != 0 and last_name != 'Mary'
order by hire_date desc;

12.统计出各个title类型对应的员工薪水对应的平均工资avg。结果给出title以及平均工资avg，并且以avg升序排序
考察点：各个→group by；当前→to_date时间

select t.title,avg(s.salary) 
from titles as t inner join salaries as s
on t.emp_no = s.emp_no
WHERE t.to_date = '9999-01-01'
AND s.to_date = '9999-01-01'
GROUP BY t.title;

13.获取薪水第二多的员工的emp_no以及其对应的薪水salary
考察点:limit用法+提前对数据去重（#牛客答案里提到了开窗与row_number），做完这十道题之后再去回顾开窗函数

select emp_no, salary
from salaries
where salary =
    (select distinct salary
        from salaries
        order by salary desc
        limit 1,1
    );

14.查找薪水排名第二多的员工编号emp_no、薪水salary、last_name以及first_name，不能使用order by完成
考察：不用order by排序，只能用精确到要求值，然后针对性的查询

select s.emp_no, s.salary, e.last_name, e.first_name
from salaries s join employees e
on s.emp_no = e.emp_no
where s.salary =              -- 第三步: 将第二高工资作为查询条件
    (
    select max(salary)        -- 第二步: 查出除了原表最高工资以外的最高工资(第二高工资)
    from salaries
    where salary <    
        (
        select max(salary)    -- 第一步: 查出原表最高工资
        from salaries
        where to_date = '9999-01-01'   
        )
    and to_date = '9999-01-01'
    )
and s.to_date = '9999-01-01'

*PS.
开窗函数：
有三种排序的窗口函数：
1）RANK()
在计算排序时，若存在相同位次，会跳过之后的位次。
例如，有3条排在第1位时，排序为：1，1，1，4······
2）、DENSE_RANK()
这就是题目中所用到的函数，在计算排序时，若存在相同位次，不会跳过之后的位次。
例如，有3条排在第1位时，排序为：1，1，1，2······
3）、ROW_NUMBER()
这个函数赋予唯一的连续位次。
例如，有3条排在第1位时，排序为：1，2，3，4······
用法：
<窗口函数> OVER ( [PARTITION BY <用于分组的字段> ]
ORDER BY <排序的字段> ）
*其中[ ]中的内容可以忽略*

15. 查找所有员工的last_name和first_name以及对应的dept_name，也包括暂时没有分配部门的员工
考察：三个表的联接

select a.last_name,a.first_name,c.dept_name
from employees as a left join dept_emp as b
on a.emp_no = b.emp_no
left join departments as c
on b.dept_no = c.dept_no;

16. 查找在职员工自入职以来的薪水涨幅情况，给出在职员工编号emp_no以及其对应的薪水涨幅growth，并按照growth进行升序，以上例子输出为
（注: to_date为薪资调整某个结束日期，或者为离职日期，to_date='9999-01-01’时，表示依然在职，无后续调整记录）
考察：根据离职及在职两种情况去带筛选条件匹配条件。

select e.emp_no,(a.salary-b.salary) as growth
from employees e
inner join
salaries a
on e.emp_no=a.emp_no and a.to_date='9999-01-01'
inner join
salaries b
on e.emp_no=b.emp_no and b.from_date=e.hire_date
order by growth asc;
```sql

17.统计各个部门的工资记录数，给出部门编码dept_no、部门名称dept_name以及部门在salaries表里面有多少条记录sum，按照dept_no升序排序**
考察：三表查询，按照条件进行分组group by

```sql
select d.dept_no,d.dept_name,count(s.salary) as sum 
from departments as d join dept_emp as de on d.dept_no = de.dept_no
join salaries as s on de.emp_no = s.emp_no
group by d.dept_no
order by dept_no asc;

18.对所有员工的薪水按照salary进行按照1-N的排名，相同salary并列且按照emp_no升序排列
考察：开窗函数

SELECT emp_no, salary, dense_rank () over (
ORDER BY salary DESC) AS ‘rank’ 
FROM salaries 
WHERE to_date = '9999-01-01' ;

19.获取所有非manager员工薪水情况，给出dept_no、emp_no以及salary
考察:多表连接，排除其他选项，
思路：先找到所有非manager员工emp_no,再内连接工资表和部门表即可

select de.dept_no,a.emp_no,s.salary
from 
(select emp_no
from employees 
where emp_no not in (select emp_no
from dept_manager)
) as a 
inner join dept_emp de on a.emp_no=de.emp_no
inner join salaries s on a.emp_no=s.emp_no
where s.to_date='9999-01-01'

20.获取员工其当前的薪水比其manager当前薪水还高的相关信息，
第一列给出员工的emp_no，
第二列给出其manager的manager_no，
第三列给出该员工当前的薪水emp_salary,
第四列给该员工对应的manager当前的薪水manager_salary
考察：多次比较，这种代码不提倡写事因为运算次数太多。

select de.emp_no,dm.emp_no as manager_no,
s1.salary as emp_salary,s2.salary as manager_salary
from dept_emp de,dept_manager dm,salaries s1,salaries s2
where de.dept_no=dm.dept_no
and de.emp_no=s1.emp_no
and dm.emp_no=s2.emp_no
and s1.salary>s2.salary
and s2.to_date='9999-01-01'
and s1.to_date='9999-01-01';

牛客答案：
查询员工当前工资表 emp_sal

select de.emp_no,de.dept_no,s1.salary as emp_salary
    from dept_emp de,salaries s1
    where de.emp_no=s1.emp_no
    and s1.to_date='9999-01-01'
    and de.to_date='9999-01-01'
**查询经理当前工资表mag_sal**
 select dm.emp_no as manager_no,dm.dept_no,s2.salary as manager_salary
    from dept_manager dm,salaries s2
    where dm.emp_no=s2.emp_no
    and s2.to_date='9999-01-01'
    and dm.to_date='9999-01-01'
**联结表emp_sal和表mag_sal，连接条件部门编号相等，要求：员工工资>经理工资**
select emp_sal.emp_no,mag_sal.manager_no,
emp_sal.emp_salary,mag_sal.manager_salary
from (
    select de.emp_no,de.dept_no,s1.salary as emp_salary
    from dept_emp de,salaries s1
    where de.emp_no=s1.emp_no
    and s1.to_date='9999-01-01'
    and de.to_date='9999-01-01'
)as emp_sal
inner join(
    select dm.emp_no as manager_no,dm.dept_no,s2.salary as manager_salary
    from dept_manager dm,salaries s2
    where dm.emp_no=s2.emp_no
    and s2.to_date='9999-01-01'
    and dm.to_date='9999-01-01'
)as mag_sal
on emp_sal.dept_no=mag_sal.dept_no
where mag_sal.manager_salary<emp_sal.emp_salary;