一 SQL训练资料
牛客SQL题目
猴子数据分析题目
关注的公众号
猴子数据分析
二 SQL知识点总结
1.SQL语句的执行顺序
每一个子句产生的中间结果供接下来的子句使用(阶段性)
开始->FROM子句->WHERE子句->GROUP BY子句->HAVING子句->SELECT子句->ORDER BY子句->LIMIT子句->最终结果
select e.number,count(e.name) as num
from employees e --(先从表格拿数据)
where e.number>100--(然后筛选出number大于100的数据)
group by e.gender--(group是在where筛选出来的数据之后进行操作)
having num>100--(having对group by 产生的数据进行筛选)
order by num desc --(对group筛选出的数据进行排序)
limit 0,1;--(limit对最后)
2.窗口函数
将聚合的数据放到原数据的后方
查询每个部门的当前的最高薪水情况
薪水可以聚类max,但是员工号不可以,所以得用窗口函数
--部门、员工号、薪水
select a.dept_no,a.emp_no,a.salary
from
(
select d.dept_no,d.emp_no,s.salary,
rank() over(
partition by d.dept_no
order by s.salary desc
) as rk
from dept_emp d inner join salaries s
on d.emp_no=s.emp_no
where d.to_date='9999-01-01' and s.to_date='9999-01-01'
) a
where rk=1
order by a.dept_no;
排序函数
总共有3种,形式为
rank() over(
partition by
order by desc
) as
序号函数名 | 组内排序后例子(1,2,3) |
---|---|
rank | 1,1,3 |
row_number | 1,2,3 |
dense_rank | 1,1,2 |
取值函数
①要查询的每一个数据,根据当前数据的位置确定
之前 | 之后 |
---|---|
lag | lead |
lag/lead() over()的使用
lag(col,n,default):用于统计窗口往上第n行值:
第一个参数为列名;
第二个参数为往上第n行(默认为1);
第三个参数为默认值(当往上第n行为null的时候,取默认值,如果不指定,则取null)。
同理:
lead(col,n,default):用于统计窗口往下第n行值:
例:找到车辆上一次的锁车记录
那么首先根据锁车的时间排序(降序),然后在这次锁车的时间的前一个
select
fence,
bike_id,
unlock_time, -- 开锁
lock_time, -- 锁车
lag(lock_time,1,null) over(partition by fence,hour(unlock_time),bike_id) as last_lock_time
from bike_hour_inc
②根据在over()窗口中的位置确定
第一个 | 最后一个 | 第n个 |
---|---|---|
first_value | last_value | nth_value |
以下语句统计了不同产品最低销售额、最高销售额以及第三高销售额所在的月份:
不同产品最高销售额所在月份
不同产品(分组),最高销售额(排序),所在月份(第一行)
SELECT product AS "产品", ym AS "年月",amount AS "销售额",
FIRST_VALUE(m.ym) OVER (
PARTITION BY m.product ORDER BY m.amount DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS "最高销售额月份",
LAST_VALUE(m.ym) OVER (
PARTITION BY m.product ORDER BY m.amount DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS "最低销售额月份",
NTH_VALUE(m.ym,3) OVER (
PARTITION BY m.product ORDER BY m.amount DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS "第三高销售额月份"
FROM sales_monthly m
ORDER BY product, ym;
3.字符串处理函数
字符串拼接,截取函数
将A的前两个字符与B的前3个字符拼接起来组成D,最后全部变为大写
upper(concat(substring(cust_contact, 1, 2), substring(cust_city, 1, 3))) as user_login
upper()
substring(A,1,2):对A从第一个字母开始,选择2个字母
contract(A,B):将A,B连接起来
读取日期字符串的年份、月份
year()
month()
模糊查询
占位符
% | _ |
---|---|
任意(0~∞)个字符 | 任意1个字符 |
查找包含toy的名字
select name from A where name like "%toy%"
查找以toy作为第二个字符的名字
select name from A where name like "_toy%"
查找以toy作为结尾、或者开头的名字
select name from A where (name like "%toy") | (name like "toy%")
4 拼接表
union函数
将产生的两张表进行上下拼接
select name from A
union
select name from B
三 SQL题目的总结
①205所有员工当前的manager
题目拆解,当前(where时间上进行筛选),所有员工(左外连接)
---所有员工(左外连接),当前(筛选to_date='9999-01-01')
select d.emp_no,m.emp_no as manager
from dept_emp d inner join dept_manager m
on d.dept_no=m.dept_no
where d.emp_no != m.emp_no and d.to_date='9999-01-01' and m.to_date='9999-01-01';
②206题获取每个部门当前员工最大薪水信息
需要用到group by (分组函数)
错误实例
select d.dept_no,d.emp_no,max(s.salary) as maxSalary
from dept_emp d inner join salaries s
on d.emp_no=s.emp_no
where d.to_date='9999-01-01' and s.to_date='9999-01-01'
group by d.dept_no
order by d.dept_no asc;
--这里,对于分组得到的每一列数据都需要进行聚合,
--首先按部门分组,那么部门号是聚合了的
--然后最大薪水是对组内的薪水进行了聚合
--而员工号不能进行聚合,所以结果错误
正确实例
select a.dept_no,a.emp_no,a.salary
from
(
select d.dept_no,d.emp_no,s.salary,
rank() over(
partition by d.dept_no
order by s.salary desc
) as rk
from dept_emp d inner join salaries s
on d.emp_no=s.emp_no
where d.to_date='9999-01-01' and s.to_date='9999-01-01'
) a
where rk=1
order by a.dept_no;
③211获取当前薪水第二多的员工的emp_no以及其对应的薪水salary
薪水第二多的员工可能有多个,所以先找出第二的薪水的值。
先查一遍唯一值薪水,然后找出薪水为此值的员工
select emp_no,salary
from salaries
where
salary=
(
select distinct salary
from salaries
where to_date='9999-01-01'
order by salary desc
limit 1,1
)
④212获取当前薪水第二多的员工的emp_no以及其对应的薪水salary
不能用order by 语句
找出当前薪水第二多的薪水,先找出最大的,然后排除最大的再找一次
select e.emp_no,s.salary,e.last_name,e.first_name
from employees e ,salaries s
where e.emp_no=s.emp_no
and s.salary=(
select max(salary)
from salaries
where to_date='9999-01-01'and salary !=(
select max(salary)
from salaries
where to_date='9999-01-01'
)
)and s.to_date ='9999-01-01';
215查找在职员工自入职以来的薪水涨幅情况
在职员工薪水涨幅情况,涉及两个极端情况
分别查极端,然后内连接构建新表,最后计算得出结果
入职的薪水:入职日期等于雇佣日期的薪水
现在的薪水:雇佣日期为现在
SELECT s1.emp_no AS "emp_no", s2.salary - s1.salary AS "growth"
FROM (
SELECT salaries.emp_no, salary
FROM salaries inner join employees
on salaries.emp_no=employees.emp_no
where salaries.from_date=employees.hire_date
) s1
INNER JOIN (
SELECT emp_no, salary
FROM salaries
WHERE to_date = '9999-01-01'
) s2 ON s1.emp_no = s2.emp_no -- 因为INNER JOIN只会连接匹配行,所以s2中筛除的已离职员工则不会被显示
ORDER BY growth ASC
;
216统计各个部门的工资记录数
首先
对于对各个部门的工资记录数进行聚类运算,注意对于所有工资进行统计,没有部门的排除(左外连接),对部门号进行分组统计。
然后
对于部门名称进行表连接
select a.dept_no,a.dept_name,b.sum
from departments a
inner join
(
select d.dept_no,count(*) as sum
from dept_emp d right join salaries s
on d.emp_no =s.emp_no
where d.dept_no is not null
group by d.dept_no
) b
on a.dept_no=b.dept_no
order by a.dept_no asc;
217对所有员工的薪水按照salary降序进行1-N的排名
利用dense_rank() over ()窗口函数
首先得到每一行的排名,然后最后输出数据时要进行降序排序
SELECT
emp_no,
salary,
dense_rank () over (
ORDER BY salary DESC) AS `rank`
FROM
salaries
WHERE to_date = '9999-01-01' ;