数据分析面试真题 - 开窗函数

写入数据

create table staffTable( 
department varchar(20) not null, 
name varchar(20),
salary bigint)
ENGINE=InnoDB DEFAULT CHARSET=utf8;

# 插入数据
INSERT INTO staffTable 
(department,name,salary)
VALUES
('HR','jack',8000), 
('HR','peter',7000), 
('HR','merry',6000), 
('BD','harry',10000), 
('BD','lily',9000), 
('BD','Bella',30000), 
('IT','Max',10000), 
('IT','Liz',20000), 
('IT','jason',25000), 
('IT','Raymon',50000);

题目

有department部门，name员工姓名，salary工资三列数据，求出每个部门工资最高的员工，以及每个部门的平均工资

正确🙆

select department, name, salary,
max(salary) over (partition by department) as 'max_salary',
avg(salary) over (partition by department) as 'ave_salary'
from staffTable

错误🙅

select department, name, salary,
max(salary) over (partition by department order by salary) as 'max_salary',
avg(salary) over (partition by department order by salary) as 'ave_salary'
from staffTable

错误原因：
加了order_by之后，会在分组之内根据排序选取最大值和平均值！！！ 注意想要实现的东西，不要瞎排序。

正确🙆

如果只想取出工资最高的那个人，其他的信息删掉

select department, name, salary, ave_salary from
(select department, name, salary,
-- 排序一定要desc，才能在rank=1时取到工资最高的那个
rank() over (partition by department order by salary desc) as 'rank_salary',
avg(salary) over (partition by department) as 'ave_salary'
from staffTable) ttttable
-- 这个必须写在表外边
where rank_salary = 1

写入数据

create table DAUTable( 
userid varchar(20) not null, 
dt datetime,
score bigint)
ENGINE=InnoDB DEFAULT CHARSET=utf8;

# 插入数据
INSERT INTO DAUTable 
(userid,dt,score)
VALUES
('424',20200101,8000), 
('424',20200102,10000), 
('424',20200104,11000), 
('893ec',20200101,9000), 
('893ec',20200102,8000), 
('893ec',20200103,11000), 
('ccc023dm2',20200103,11000),
('ccc023dm2',20200106,20000);

题目

有userid，score，dt（datetime）三列数据，选出过去10天，有2天评分大于10000的人

普通的group by

select userid,count(dt) as counts from DAUTable
where dt>=20200101 and dt <=20200110
and score >= 10000
group by userid
having counts = 2

小缺点：
user_id对应的score不能被展示出来，除非再进行一次join

开窗函数 - count()

错误❌1

select userid,score,
count(dt) over (partition by userid order by score) as counts from DAUTable
where dt>=20200101 and dt <=20200110
and score >= 10000

问题！！！ 不要加order，不然会排序之后再count

错误❌2

会先进行where条件的筛选，然后开窗函数的运算;
所以不能在where条件中加counts = 2

select userid,score,
count(dt) over (partition by userid) as counts from DAUTable
where dt>=20200101 and dt <=20200110
and score >= 10000

错误❌3

select userid,score,
count(dt) over (partition by userid) as counts from DAUTable
where dt>=20200101 and dt <=20200110
and score >= 10000
and count(dt) over (partition by userid) = 2

正确

select userid,score from
(select userid,score,
count(dt) over (partition by userid) as counts from DAUTable
where dt>=20200101 and dt <=20200110
and score >= 10000) ttt
where counts = 2