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;
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值