mysql练习
01-hiving对分组数据进行筛选
01-建表语句
Create table If Not Exists Employee (Id int, Name varchar(255), Department varchar(255), ManagerId int);
insert into Employee (Id, Name, Department, ManagerId) values ('101', 'John', 'A', null);
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');
02-题目
Employee 表包含所有员工和他们的经理。每个员工都有一个 Id,并且还有一列是经理的 Id。
+------+----------+-----------+----------+
|Id |Name |Department |ManagerId |
+------+----------+-----------+----------+
|101 |John |A |null |
|102 |Dan |A |101 |
|103 |James |A |101 |
|104 |Amy |A |101 |
|105 |Anne |A |101 |
|106 |Ron |B |101 |
+------+----------+-----------+----------+
给定 Employee 表,请编写一个SQL查询来查找至少有5名直接下属的经理。对于上表,您的SQL查询应该返回:
+-------+
| Name |
+-------+
| John |
+-------+
03-答案:
SELECT
Name
FROM
Employee AS t1 JOIN
(SELECT
ManagerId
FROM
Employee
GROUP BY ManagerId
HAVING COUNT(ManagerId) >= 5) AS t2
ON t1.Id = t2.ManagerId
;
02-中位数计算_sum窗口函数
01-建表语句
Create table If Not Exists Numbers (Number int, Frequency int);
insert into Numbers (Number, Frequency) values ('0', '7');
insert into Numbers (Number, Frequency) values ('1', '1');
insert into Numbers (Number, Frequency) values ('2', '3');
insert into Numbers (Number, Frequency) values ('3', '1');
02-题目
Numbers 表保存数字的值及其频率。
+----------+-------------+
| Number | Frequency |
+----------+-------------|
| 0 | 7 |
| 1 | 1 |
| 2 | 3 |
| 3 | 1 |
+----------+-------------+
在此表中,数字为 0, 0, 0, 0, 0, 0, 0, 1, 2, 2, 2, 3,所以中位数是 (0 + 0) / 2 = 0
+--------+
| median |
+--------|
| 0.0000 |
+--------+
请编写一个查询来查找所有数字的中位数并将结果命名为 median
03-答案
select avg(number) median
from
(select number,
sum(frequency) over(order by number) asc_accumu,
sum(frequency) over(order by number desc) desc_accumu,
sum(frequency) over() total
from Numbers) t1
where asc_accumu >= total / 2 and desc_accumu >= total / 2
03-查询累计薪资
01-建表语句
DROP TABLE IF EXISTS employee;
Create table If Not Exists Employee (Id int, Month int, Salary int);
insert into Employee(Id, Month, Salary) values ('1', '1', '20');
insert into Employee(Id, Month, Salary) values ('2', '1', '20');
insert into Employee(Id, Month, Salary) values ('1', '2', '30');
insert into Employee(Id, Month, Salary) values ('2', '2', '30');
insert into Employee(Id, Month, Salary) values ('3', '2', '40');
insert into Employee(Id, Month, Salary) values ('1', '3', '40');
insert into Employee(Id, Month, Salary) values ('3', '3', '60');
insert into Employee(Id, Month, Salary) values ('1', '4', '60');
insert into Employee(Id, Month, Salary) values ('3', '4', '70');
02-题目
Employee 表保存了一年内的薪水信息。
请你编写 SQL 语句,对于每个员工,查询他除最近一个月(即最大月)之外,剩下每个月的近三个月的累计薪水(不足三个月也要计算)。
结果请按 Id 升序,然后按 Month 降序显示。
示例:
输入:
| Id | Month | Salary |
|----|-------|--------|
| 1 | 1 | 20 |
| 2 | 1 | 20 |
| 1 | 2 | 30 |
| 2 | 2 | 30 |
| 3 | 2 | 40 |
| 1 | 3 | 40 |
| 3 | 3 | 60 |
| 1 | 4 | 60 |
| 3 | 4 | 70 |
输出:
| Id | Month | Salary |
|----|-------|--------|
| 1 | 3 | 90 |
| 1 | 2 | 50 |
| 1 | 1 | 20 |
| 2 | 1 | 20 |
| 3 | 3 | 100 |
| 3 | 2 | 40 |
解释:
员工 '1' 除去最近一个月(月份 '4'),有三个月的薪水记录:月份 '3' 薪水为 40,月份 '2' 薪水为 30,月份 '1' 薪水为 20。
所以近 3 个月的薪水累计分别为 (40 + 30 + 20) = 90,(30 + 20) = 50 和 20。
| Id | Month | Salary |
|----|-------|--------|
| 1 | 3 | 90 |
| 1 | 2 | 50 |
| 1 | 1 | 20 |
员工 '2' 除去最近的一个月(月份 '2')的话,只有月份 '1' 这一个月的薪水记录。
| Id | Month | Salary |
|----|-------|--------|
| 2 | 1 | 20 |
员工 '3' 除去最近一个月(月份 '4')后有两个月,分别为:月份 '3' 薪水为 60 和 月份 '2' 薪水为 40。所以各月的累计情况如下:
| Id | Month | Salary |
|----|-------|--------|
| 3 | 3 | 100 |
| 3 | 2 | 40 |
03-答案
SELECT t.id, t.`month`, t.salary FROM
(SELECT id, `month`,
SUM(salary) over(PARTITION BY id ORDER BY `month`) salary,
rank() over(PARTITION BY id ORDER BY `month` DESC) ranks
FROM Employee) t
WHERE t.ranks > 1;