LeetCode-579、查询员工的累计薪水，601、体育馆的人流量

蔚落 2022-12-07 04:25 171阅读 0赞

> 来源：力扣（LeetCode）  
> 链接：https://leetcode-cn.com/problemset/database/

### 文章目录 ###

*  SQL结构：
 *   *  579. 查询员工的累计薪水（困难）
     *   *   *  解法一
 *  SQL结构：
 *   *  601. 体育馆的人流量（困难）
     *   *   *  解法一

# SQL结构： #

Create table If Not Exists Employee (Id int, Month int, Salary int)
    Truncate table Employee
    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')

## 579. 查询员工的累计薪水（困难） ##

`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’）后有两个月，分别为：月份 ‘4’ 薪水为 60 和 月份 ‘2’ 薪水为 40。所以各月的累计情况如下：

| Id | Month | Salary |
    |----|-------|--------|
    | 3  | 3     | 100    |
    | 3  | 2     | 40     |

#### 解法一 ####

-- 先用开窗lead()，取后一个月份，取不到补0，为了过滤最大月份，即值为0的为最大月份
    -- 再用开窗sum()，直接求近三个月的和，所以需要用到rows between来确定范围，即（上两条 到 当前行）
    -- 最后用id、月份desc排序
    
    SELECT
        Id,
        Month,
        SUM(Salary) OVER (PARTITION BY Id ORDER BY Month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) Salary
    FROM
        (
            SELECT
                Id,
                Month,
                Salary,
                LEAD(Month, 1, 0) OVER (PARTITION BY Id ORDER BY Month) lm
            FROM
                Employee
        ) t1
    WHERE 
    	lm != 0
    ORDER BY 
    	Id, Month DESC

\----------------------------华丽的分割线--------------------------------

# SQL结构： #

Create table If Not Exists stadium (id int, visit_date DATE NULL, people int);
    Truncate table stadium;
    insert into stadium (id, visit_date, people) values ('1', '2017-01-01', '10');
    insert into stadium (id, visit_date, people) values ('2', '2017-01-02', '109');
    insert into stadium (id, visit_date, people) values ('3', '2017-01-03', '150');
    insert into stadium (id, visit_date, people) values ('4', '2017-01-04', '99');
    insert into stadium (id, visit_date, people) values ('5', '2017-01-05', '145');
    insert into stadium (id, visit_date, people) values ('6', '2017-01-06', '1455');
    insert into stadium (id, visit_date, people) values ('7', '2017-01-07', '199');
    insert into stadium (id, visit_date, people) values ('8', '2017-01-08', '188');

## 601. 体育馆的人流量（困难） ##

X 市建了一个新的体育馆，每日人流量信息被记录在这三列信息中：`序号 (id)`、`日期 (visit_date)`、 `人流量 (people)`。

需求：请编写一个查询语句，找出人流量的高峰期。高峰期时，至少连续三行记录中的人流量不少于100。

例如，表 stadium：
    +------+------------+-----------+
    | id   | visit_date | people    |
    +------+------------+-----------+
    | 1    | 2017-01-01 | 10        |
    | 2    | 2017-01-02 | 109       |
    | 3    | 2017-01-03 | 150       |
    | 4    | 2017-01-04 | 99        |
    | 5    | 2017-01-05 | 145       |
    | 6    | 2017-01-06 | 1455      |
    | 7    | 2017-01-07 | 199       |
    | 8    | 2017-01-08 | 188       |
    +------+------------+-----------+
    
    对于上面的示例数据，输出为：
    +------+------------+-----------+
    | id   | visit_date | people    |
    +------+------------+-----------+
    | 5    | 2017-01-05 | 145       |
    | 6    | 2017-01-06 | 1455      |
    | 7    | 2017-01-07 | 199       |
    | 8    | 2017-01-08 | 188       |
    +------+------------+-----------+

提示：

*  每天只有一行记录，日期随着 id 的增加而增加。
 *  体育馆并不是每天都开放的，所以记录中的日期可能会出现断层。

#### 解法一 ####

思路：连续N天，只要是连续的，都可以用等差的思想；
    	 即（用某个连续字段 - 行号/排行 ），若是连续的，差值一定相同，再统计相同差值的个数即可。
    -- 先过滤出人流程量>=100的记录，然后再用id - 行号，得出差值（因为id是连续的）
    -- 再统计出，差值相同的个数，即个数多少，就连续多少。
    -- 最后根据条件 where 连续个数>=3 的记录即可。
    
    SELECT
        id,
        visit_date,
        people
    FROM
        (
            SELECT
                id,
                visit_date,
                people,
                COUNT(*) OVER (PARTITION BY diff_value) AS diff_value_number
            FROM
                (
                    SELECT
                        id,
                        visit_date,
                        people,
                        id - (ROW_NUMBER() OVER (ORDER BY id)) AS diff_value
                    FROM
                        stadium
                    WHERE
                        people >= 100
                ) AS t1
        ) AS t2
    WHERE
        t2.diff_value_number >= 3