力扣每日SQL刷题(题目 : 534, 569, 570, 1280)

力扣_534题

说明

力扣_534

分析

1.使用sum窗口函数,对id进行分组,根据日期排序即可,sum() over()可以实现按照行累加。

实现

准备工作

Create table If Not Exists Activity (player_id int, device_id int, event_date date, games_played int)
Truncate table Activity
insert into Activity (player_id, device_id, event_date, games_played) values ('1', '2', '2016-03-01', '5')
insert into Activity (player_id, device_id, event_date, games_played) values ('1', '2', '2016-03-02', '6')
insert into Activity (player_id, device_id, event_date, games_played) values ('1', '3', '2017-06-25', '1')
insert into Activity (player_id, device_id, event_date, games_played) values ('3', '1', '2016-03-02', '0')
insert into Activity (player_id, device_id, event_date, games_played) values ('3', '4', '2018-07-03', '5')

实现方法

select player_id,
       event_date,
       sum(games_played) over (partition by player_id order by event_date) as games_played_so_far
from Activity;

小结

窗口函数:sum() over()

力扣_569题

说明

力扣_569

分析

1.首先使用窗口函数根据company分组,salary排序得到工资有序的表,再使用count窗口函数统计每个部门分别有多少人;
2.若部门人数为偶数,比如为6,则排名为3,4的为中位数;若部门人数为奇数,比如5,则中位数排名为3;
即中位数的排名在 (总人数 / 2) 和 (总人数 / 2 + 1)之间。

实现

准备工作

Create table If Not Exists Employee (id int, company varchar(255), salary int)
Truncate table Employee
insert into Employee (id, company, salary) values ('1', 'A', '2341')
insert into Employee (id, company, salary) values ('2', 'A', '341')
insert into Employee (id, company, salary) values ('3', 'A', '15')
insert into Employee (id, company, salary) values ('4', 'A', '15314')
insert into Employee (id, company, salary) values ('5', 'A', '451')
insert into Employee (id, company, salary) values ('6', 'A', '513')
insert into Employee (id, company, salary) values ('7', 'B', '15')
insert into Employee (id, company, salary) values ('8', 'B', '13')
insert into Employee (id, company, salary) values ('9', 'B', '1154')
insert into Employee (id, company, salary) values ('10', 'B', '1345')
insert into Employee (id, company, salary) values ('11', 'B', '1221')
insert into Employee (id, company, salary) values ('12', 'B', '234')
insert into Employee (id, company, salary) values ('13', 'C', '2345')
insert into Employee (id, company, salary) values ('14', 'C', '2645')
insert into Employee (id, company, salary) values ('15', 'C', '2645')
insert into Employee (id, company, salary) values ('16', 'C', '2652')
insert into Employee (id, company, salary) values ('17', 'C', '65')

实现方法

select Id, Company, Salary
from (select Id,
             Company,
             Salary,
             row_number() over (partition by Company order by Salary) as ranking,
             count(Id) over (partition by Company)                    as cnt
      from Employee) a
where ranking >= cnt / 2
  and ranking <= cnt / 2 + 1;

小结

窗口函数

力扣_570题

说明

力扣_571

分析

按照managerID分组,然后统计每个组的人数,找到人数大于等于5的managerID,然后使用子查询找出那么name.

实现

准备工作

Create table If Not Exists Employee (id int, name varchar(255), department varchar(255), managerId int)
Truncate table Employee
insert into Employee (id, name, department, managerId) values ('101', 'John', 'A', 'None')
insert into Employee (id, name, department, managerId) values ('102', 'Dan', 'A', '101')
insert into Employee (id, name, department, managerId) values ('103', 'James', 'A', '101')
insert into Employee (id, name, department, managerId) values ('104', 'Amy', 'A', '101')
insert into Employee (id, name, department, managerId) values ('105', 'Anne', 'A', '101')
insert into Employee (id, name, department, managerId) values ('106', 'Ron', 'B', '101')

实现方法

select name
from employee
where id in (select managerId
            from employee
            group by managerId
            having count(id) >= 5);

小结

子查询
聚合函数

力扣_1280题

说明

力扣_1280

分析

1.首先subjecs表内连接student表,得到的新表就包含了学生信息和参加的考试科目;
2.从examinations表里,按照学生id和姓名分组,查出每个学生参与各科考试的次数;
3.然后用第一步的表左连接第二步的表(左连接让没有参加考试的学生也可以展示出来),然后加上判断条件,当参与考试次数为null时,返回0,否则返回考试次数即可。

实现

准备工作

Create table If Not Exists Students (student_id int, student_name varchar(20))
Create table If Not Exists Subjects (subject_name varchar(20))
Create table If Not Exists Examinations (student_id int, subject_name varchar(20))
Truncate table Students
insert into Students (student_id, student_name) values ('1', 'Alice')
insert into Students (student_id, student_name) values ('2', 'Bob')
insert into Students (student_id, student_name) values ('13', 'John')
insert into Students (student_id, student_name) values ('6', 'Alex')
Truncate table Subjects
insert into Subjects (subject_name) values ('Math')
insert into Subjects (subject_name) values ('Physics')
insert into Subjects (subject_name) values ('Programming')
Truncate table Examinations
insert into Examinations (student_id, subject_name) values ('1', 'Math')
insert into Examinations (student_id, subject_name) values ('1', 'Physics')
insert into Examinations (student_id, subject_name) values ('1', 'Programming')
insert into Examinations (student_id, subject_name) values ('2', 'Programming')
insert into Examinations (student_id, subject_name) values ('1', 'Physics')
insert into Examinations (student_id, subject_name) values ('1', 'Math')
insert into Examinations (student_id, subject_name) values ('13', 'Math')
insert into Examinations (student_id, subject_name) values ('13', 'Programming')
insert into Examinations (student_id, subject_name) values ('13', 'Physics')
insert into Examinations (student_id, subject_name) values ('2', 'Math')
insert into Examinations (student_id, subject_name) values ('1', 'Math')

实现方法

select t1.student_id,
       t1.student_name,
       t1.subject_name,
       if(num is null, 0, num) attended_exams
from (select *
      from students,
           subjects) t1
         left join (select student_id, subject_name, count(student_id) as num
                    from examinations
                    group by student_id, subject_name) t2
                   on t1.student_id = t2.student_id and t1.subject_name = t2.subject_name
order by t1.student_id, t1.subject_name;

小结

内连接
左连接
聚合函数