sql 累计占比"/>
sql 累计占比
除了sqlzoo的练习题外,为了加大练习强度,我还不断搜集一些其他的SQL题,特别是大小厂面试题的类型。我把比较难的题挑出来做了一个合集,才有了这篇文章。我会把题目和答案分开放,方便先自己练习,不懂再查答案。
【1】腾讯面试题
table_A ( 用户userid和登录时间time)求连续登录3天的用户数
【2】滴滴面试题
学生表:tb_student(name:学生姓名,id:学号,class:班级,in_time:入学时间,age:年龄,sex:性别,major:专业)
学生成绩表:tb_score(id:学号,course:课程,score:分数)
(1)筛选出2017年入学的“计算机”专业年龄最小的10位同学名单(姓名、学号、班级、年龄)
(2)统计每个班同学各科成绩平均分大于80分的人数和人数占比
【3】滴滴面试题
table1(id:自增id,money:费用)问题:按id顺序累加money,取出累计值与1000相差最小差值的id。
【4】滴滴面试题
Employee 表包含所有员工信息,每个员工有其对应的 Id, Name,Salary 和 DepartmentId。
+----+-------+--------+--------------+
| Id | Name | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1 | Joe | 70000 | 1 |
| 2 | Henry | 80000 | 2 |
| 3 | Sam | 60000 | 2 |
| 4 | Max | 90000 | 1 |
Department 表包含公司所有部门的信息。
+----+----------+
| Id | Name |
+----+----------+
| 1 | IT |
| 2 | Sales |
+----+----------+
编写一个 SQL 查询,找出每个部门工资第二高的员工。
【5】这个题来源于(佰初:面试数据分析会遇到的SQL题)我筛选了一些有点难度的。
(1)查询“北京佰初数据有限公司”各个职级(post_grade)上的最高薪水、最低薪水、平均薪水.
(2)将“北京佰初数据有限公司”的员工两两组队,取出所有可能的组合。
(3)查询每个公司薪资排名前三的岗位id、薪资,按公司id升序,按薪资降序排序
(4)假设4张表存放在 MySQL 数据库中,查询“北京佰初数据有限公司”过去7天每天的新入职员工数量,按入职日期(date_of_entry)倒序排列.
(5)假设4张表存放在 MySQL 数据库中,查询“北京佰初数据有限公司”2019年5月每天的新入职员工数量,按入职日期(date_of_entry)倒序排列.
(6)查询“北京佰初数据有限公司”每个部门里薪资高于部门平均薪资的员工姓名和部门名字(department_name)
牛客网题(筛选)
【1】请取出 BORROW表中日期(RDATE字段)为当天的所有记录?(RDATE字段为datetime型,包含日期与时间)。
【2】有一张学生成绩表sc(sno 学号,class 课程,score 成绩),请查询出每个学生的英语、数学的成绩(行转列,一个学生只有一行记录)。
【3】有一个名为app的MySQL数据库表,app_id(应用ID), version_code(应用的版本号),download_count (当前版本的下载量)。
查询每个应用中总下载量最大的版本号和次数
+--------+--------------+----------------+
| app_id | version_code | download_count |
+--------+--------------+----------------+
| 1 | 10 | 90 |
| 1 | 11 | 100 |
| 1 | 10 | 20 |
| 2 | 15 | 10 |
| 2 | 16 | 15 |
| 2 | 17 | 30 |
| 2 | 16 | 5 |
| 3 | 2 | 50 |
+--------+--------------+----------------+
力扣网(筛选)
【1】
(1)编写一个 SQL 查询,获取 Employee 表中第二高的薪水(Salary) 。
+----+--------+
| Id | Salary |
+----+--------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+----+--------+
例如上述 Employee 表,SQL查询应该返回 200 作为第二高的薪水。如果不存在第二高的薪水,那么查询应返回 null。
+---------------------+
| SecondHighestSalary |
+---------------------+
| 200 |
+---------------------+
(2)原题不变,查询第n高薪水(这道题不用做,难度比较大)
【2】
【3】
————————————————————
参考答案
【1】
SELECT userid,time,
DATE_SUB(left(time,10),INTERVAL t.rn DAY) as flag_date,count(distinct left(time,10))
from (SELECT userid,time,dense_rank ()over(PARTITION by userid ORDER BY time) as rn
from table_A) as t
GROUP BY user_id,flag_date
HAVING count(distinct left(time,10))>=3;
解析:用窗口函数dense_rank 进行排序,日期函数DTESUB,将(日期-排序数)得到一个相等的日期flag_date,以其进行分组,将连续的日期分为一组。考虑到一天内多次登录,需要用left函数截取时间的日期部分,最后count进行去重操作。
【2】
(1)
select name,id,class,age
from tb_student
where year(in_time)=2017
and major = '计算机'
ORDER BY age ASC
limit 10;
(2)
SELECT a.class,
sum(case when aaa.x > 80 then 1 else 0 end)as num_80 ,
(sum(case when aaa.x > 80 then 1 else 0 end)/count(*))as proportion
from tb_student a
INNER JOIN (SELECT id,avg(score)as x from tb_score GROUP BY id)as aaa
on a.id = aaa.id
GROUP BY class;
解析:写一个子查询,从score表中得到以学号分组的学生各科平均分。命名为aaa,将其与学生表内联结。再以class分组,得到以class分组的各班学生的平均分,最后通过case语句,sum聚合函数得到平均分>80分的计数,和所占各班总人数比例。
case语句也可以用if语句来代替
写法2:
SELECT a.class,count(if(aaa.avg>80,true,null)) as numover80,
count(if(aaa.avg>80,true,null))/count(a.id) as total
from tb_student a
INNER JOIN (SELECT id,avg(score)as x from tb_score GROUP BY id)as aaa
on a.id = aaa.id
GROUP BY class;
【3】
写法1:
select id,
abs((sum(money)over(order by id))-1000) as x
from table1
order by x asc
limit 1;
写法2:
select id,min(abs(x-1000))
FROM(SELECT id,
sum(money)over(ORDER BY id)as x
from table1)as aaa
【4】
select Departmentid,Name,Salary,rn
from(select *,
row_number()over(PARTITION by Departmentid
ORDER BY Salary desc) as rn
from Employee)as a
WHERE rn=2
【5】
(1)
select.post_grade,max(salary),min(salary),avg(salary)
from table_staff a
inner join table_post b
on a.post_id =b.post_id
where company_id in
(select company id from table_company where company_name ='北京佰初数据有限公司')
group by post_grade;
(2) 这个题考察的是自连接查询
select a.staff name as team_a,b.staff name as team_b
from table_staff a, table_staff b
where company_id in
(select company id from table_company where company_name ='北京佰初数据有限公司')
and a.staff_id <b.staff_id;
(3)这个题考察窗口函数
select post_id ,salary
from(select * , row_number()over(partition by company_id order by salary desc) as rn
from table staff) as x
where rn<=3;
(4)这个题需要用到日期函数 dateadd()和getdate()
select date_of_entry,count(staff_id) as num
from table_staff
where date_of_entry>=dateadd(day,-7,getdate())
and company_id in
(select company id from table_company where company_name ='北京佰初数据有限公司')
group by date of entry
order by date of entry desc;
(5)
select date_of_entry,count(staff_id)as num
from table_staff
where date_of_entry>='2019-05-01'
and date_of_entry<'2019-06-01'
and company_id in
(select company id from table_company where company_name ='北京佰初数据有限公司')
group by date of entry
order by date of entry desc;
(6)这个题考察了窗口函数、聚合函数、联结表、子查询的使用
select x.staff_name,x.department_name
from(select*,avg(salary)over(PARTITION by department_id) as salary_avg
from table_staff a
join table_post b on a.post_id =b.post_id
join table_post c on a.company_id = c.company_id
where company_id in
(select company id from table_company where company_name ='北京佰初数据有限公司')
) as x
where x.salary>x.salary_avg;
牛客网(筛选)
【1】考察日期函数
select *
from BORROW
where datediff(dd,RDATE,getdate())=0
DATEDIFF() 函数返回两个日期之间的时间。
语法:datediff(datepart,startdate,enddate)
注意:mysql中datediff只有2个参数datediff(startdate,enddate)
startdate 和 enddate 参数是合法的日期表达式。
datepart 参数可以是下列的值:
datediff(dd,RDDATE,getdate())==0含义,即返回以日为单位(dd),和当前日期(getdate)相差为0日的RDDATE
这里用这个函数的意义在于,RDDATE包括日期和时间,这里因为有时间,和getdate不能直接比较,所以用datediff转换为范围。
【2】
select sno,
sum(if(class='english',score,0)) as english,
sum( if(class='math',score,0) ) as math
from sc
where class in('english','math')
group by sno
【3】
select app_id,version_code,sum_donload
from(select *,sum(download_count)as sum_donload,
dense_rank()over(partition by app_id order by sum(download_count) desc) as dr
from app
GROUP BY app_id,version_code)as x
WHERE dr =1;
力扣网(筛选)
【1】
(1)
仅满足查询第二高薪水
select distinct Salary as SecondHighestSalary
from Employee
order by Salary desc limit 1,1;
limit N # 返回 N 条记录
offset M # 跳过 M 条记录,M 默认为 0
limit M,N # 相当于 limit N offset M,从第 M 条记录开始,返回 N 条记录
同时满足查询第二高薪水,并在不存在第二高薪水时返回null。
写法一:
select(select distinct Salary
from Employee
order by Salary desc limit 1,1) as SecondHighestSalary;
写法二:
select ifnull(
(select distinct Salary
from Employee
order by Salary desc limit 1,1),null) as SecondHighestSalary;
(2)
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
set n = N-1;
RETURN (select ifnull(
(select distinct Salary
from Employee
order by Salary desc limit n,1),null) as SecondHighestSalary
);
END
【2】
DATEDIFF 函数,可以计算两者的日期差
DATEDIFF('2007-12-31','2007-12-30'); # 1
DATEDIFF('2010-12-30','2010-12-31'); # -1
select a.Id
from weather a, weather b
where a.Temperature > b.Temperature
and datediff(a.RecordDate,b.RecordDate) = 1
【3】
写法一:
update salary set
sex =if (sex='f','m','f');
写法二:
update salary set
sex = case sex when "m" then "f" else "m" end;
更多推荐
sql 累计占比
发布评论