SQL - SQL Day5 - 《Leetcode 刷题》

#574. 当选者
#577. 员工奖金
#578. 查询回答率最高的问题
#579. 查询员工的累计薪水
- 窗口函数中 Preceding 的使用
- #580. 统计各专业学生人数
  - IFNULL() 函数
- 今日知识点

#574. 当选者

https://leetcode-cn.com/problems/winning-candidate/

+-------------+----------+
| Column Name | Type     |
+-------------+----------+
| id          | int      |
| name        | varchar  |
+-------------+----------+
Id是该表的主键列。
该表的每一行都包含关于候选对象的id和名称的信息。

+-------------+------+
| Column Name | Type |
+-------------+------+
| id          | int  |
| candidateId | int  |
+-------------+------+
Id是自动递增的主键。
candidateId是id来自Candidate表的外键。
该表的每一行决定了在选举中获得第i张选票的候选人。

编写一个SQL查询来报告获胜候选人的名字(即获得最多选票的候选人)。
生成测试用例以确保 只有一个候选人赢得选举。

# Write your MySQL query statement below

SELECT name
FROM
(
    SELECT id, candidateId, COUNT(id) count
    FROM Vote
    GROUP BY candidateId
) a JOIN Candidate c
    ON a.candidateId = c.id
ORDER BY count DESC
LIMIT 1;

# Write your MySQL query statement below

SELECT name
FROM Candidate
WHERE id=
(
    SELECT candidateId
    FROM Vote
    GROUP BY candidateId
    ORDER BY count(1) DESC
    LIMIT 1
)

#577. 员工奖金

https://leetcode-cn.com/problems/employee-bonus/

选出所有 bonus < 1000 的员工的 name 及其 bonus。

+-------+--------+-----------+--------+
| empId |  name  | supervisor| salary |
+-------+--------+-----------+--------+
|   1   | John   |  3        | 1000   |
|   2   | Dan    |  3        | 2000   |
|   3   | Brad   |  null     | 4000   |
|   4   | Thomas |  3        | 4000   |
+-------+--------+-----------+--------+
empId 是这张表单的主关键字

+-------+-------+
| empId | bonus |
+-------+-------+
| 2     | 500   |
| 4     | 2000  |
+-------+-------+
empId 是这张表单的主关键字

# Write your MySQL query statement below

SELECT e.name, a.bonus
FROM
(
    SELECT empId, bonus
    FROM Bonus
) a RIGHT JOIN Employee e
            ON a.empId=e.empId
WHERE a.bonus < 1000 OR a.bonus IS NULL

# Write your MySQL query statement below

SELECT e.name, b.bonus
FROM Bonus b
RIGHT JOIN Employee e
            ON b.empId=e.empId
WHERE b.bonus < 1000 OR b.bonus IS NULL

#578. 查询回答率最高的问题

https://leetcode-cn.com/problems/get-highest-answer-rate-question/

+-------------+------+
| Column Name | Type |
+-------------+------+
| id          | int  |
| action      | ENUM |
| question_id | int  |
| answer_id   | int  |
| q_num       | int  |
| timestamp   | int  |
+-------------+------+
这张表没有主键，其中可能包含重复项。
action 是一个 ENUM 数据，可以是 "show"、"answer" 或者 "skip" 。
这张表的每一行表示：ID = id 的用户对 question_id 的问题在 timestamp 时间进行了 action 操作。
如果用户对应的操作是 "answer" ，answer_id 将会是对应答案的 id ，否则，值为 null 。
q_num 是该问题在当前会话中的数字顺序。

回答率是指：同一问题编号中回答次数占显示次数的比率。

编写一个 SQL 查询以报告回答率最高的问题。如果有多个问题具有相同的最大回答率，返回 question_id 最小的那个。

# Write your MySQL query statement below

SELECT question_id survey_log
FROM
(
    SELECT question_id,
        SUM(CASE WHEN action='answer' THEN 1 ELSE 0 END)/
        SUM(CASE WHEN action='show' THEN 1 ELSE 0 END) answer_rate
    FROM SurveyLog
    GROUP BY question_id
) a
ORDER BY a.answer_rate DESC, question_id 
LIMIT 1

#579. 查询员工的累计薪水

https://leetcode-cn.com/problems/find-cumulative-salary-of-an-employee/

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     |

# Write your MySQL query statement below


SELECT a.Id, a.Month, a.sum_Salary `Salary`
FROM 
(
    SELECT Id, Month,
        RANK() OVER (PARTITION BY Id ORDER BY Month DESC) temp,
        SUM(Salary) OVER (PARTITION BY Id ORDER BY Month Range 2 PRECEDING) sum_Salary
    FROM Employee
) a
WHERE temp>1
ORDER BY a.Id ASC, a.Month DESC

窗口函数中 `Preceding` 的使用

使用 Row 2 Preceding
- Rows是物理窗口，即根据order by 子句排序后，取的前N行及后N行的数据计算（与当前行的值无关，只与排序后的行号相关）
使用 Range 2 Preceding
- Range是逻辑窗口，是指定当前行对应值的范围取值，列数不固定，只要行值在范围内，对应列都包含在内
  
  #580. 统计各专业学生人数
  https://leetcode-cn.com/problems/count-student-number-in-departments/

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| student_id   | int     |
| student_name | varchar |
| gender       | varchar |
| dept_id      | int     |
+--------------+---------+
Student_id是该表的主键。
dept_id是Department表中dept_id的外键。
该表的每一行都表示学生的姓名、性别和所属系的id。

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| dept_id     | int     |
| dept_name   | varchar |
+-------------+---------+
Dept_id是该表的主键。
该表的每一行包含一个部门的id和名称。

编写一个SQL查询，为 Department 表中的所有部门(甚至是没有当前学生的部门)报告各自的部门名称和每个部门的学生人数。

按 student_number 降序返回结果表。如果是平局，则按 dept_name 的字母顺序排序。

# Write your MySQL query statement below


SELECT d.dept_name, IFNULL(a.count, 0) student_number
FROM
(
    SELECT dept_id, COUNT(student_id) count
    FROM Student
    GROUP BY dept_id
)a RIGHT JOIN Department d
            ON a.dept_id=d.dept_id
ORDER BY student_number DESC, dept_name ASC

IFNULL() 函数

IFNULL是很常用的功能，能够将输出的结果为null时，修改为其他结果。
在hive sql中一般用nvl

今日知识点

复习了limit函数的用法
1. limit函数能够返回结果的第一个值
复习了order by的用法，在需要对多个输出order by时如何使用
表的连接-left join
sum( case when )的使用，能够在相加时添加条件函数
sum() over ()窗口函数的使用，以及在窗口函数中添加 rows 2 prededing来限制行数
ifnull()空值的处理方式

SQL Day5