【窗口函数】DENSE_RANK 求连续天数

usr_id	log_date
001	2021/5/1
002	2021/5/1
003	2021/5/1
001	2021/5/2
003	2021/5/2
001	2021/5/3
003	2021/5/4

上面面表格是用户访问表 users，记录了用户 id（usr_id）和访问日期（log_date），求出连续 3 天以上访问的用户 id。

解题思路

我们需要根据这么一个简单的表，求出连续 3 天以上访问的用户。可以按照用户 id 给访问日期排名，然后再用访问日期减去排名，得到一个时间。如果用户是连续访问的，这个时间就是一样的，一个用户的这个时间如果出现 3 次及以上，说明这个用户连续访问了 3 天。

首先生成模拟数据

CREATE TABLE users (
    usr_id VARCHAR(10) NOT NULL COMMENT '用户ID',
    log_date DATE NOT NULL COMMENT '访问日期'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '用户访问记录表';
INSERT INTO users(usr_id, log_date)
VALUES
('001', '2021-05-01'),
('002', '2021-05-01'),
('003', '2021-05-01'),
('001', '2021-05-02'),
('003', '2021-05-02'),
('001', '2021-05-03'),
('003', '2021-05-04');

第一步：
先按照用户 id（usr_id）对访问日期（log_date）进行排名，这里要用到 DENSE_RANK () 这个窗口函数，用于给出排名序号。这个函数经常应用在给学生成绩进行排名。
第二张图文字

select usr_id,log_date,DENSE_RANK() OVER (
	PARTITION BY usr_id 
	order by log_date) 
AS rank_id 
from users

第二步：
得到排名后，我们用访问日期减去排名，得到一个时间 flg_date。

select usr_id,DATE_SUB(log_date,INTERVAL rank_id DAY) as flag_date
from (
	select usr_id,log_date,DENSE_RANK() OVER (
		PARTITION BY usr_id 
		order by log_date) 
	AS rank_id 
	from users
) as A;

第三步：
同一个用户有 3 个及以上 flg_date 相同，说明用户连续访问了 3 天，所以我们对上面查出的这个结果进行分组，并统计判断是否大于 3

select usr_id,DATE_SUB(log_date,INTERVAL rank_id DAY) as flag_date
from (
	select usr_id,log_date,DENSE_RANK() OVER (
		PARTITION BY usr_id 
		order by log_date) 
	AS rank_id 
	from users
) as A
group by usr_id,flag_date
having count(flag_date)>=3;

练习题目：找出每个部门工资前三高的员工（相同工资并列排名）

题目描述

现有两张数据表：Employee 员工信息表、Department 部门信息表

1. Employee 员工信息表字段：工号 Id，姓名 Name，工资 Salary，部门编号 DepartmentId
2. Department 部门信息表字段：部门编号 ID，部门名称 Name

Employee 员工表数据

id	name	salary	department_id
1	Joe	85000	1
2	Henry	80000	2
3	San	60000	2
4	Max	90000	1
5	Janet	69000	1
6	Randy	85000	1
7	Will	70000	1

Department 部门表数据

id	name
1	IT
2	Sales

查询需求

编写一个 SQL 查询，找出每个部门获得前三高工资的所有员工，相同工资并列排名。

预期输出结果表

department_name	employee_name	salary
IT	Max	90000
IT	Joe	85000
IT	Randy	85000
IT	Will	70000
Sales	Henry	80000
Sales	San	60000

建表语句：

CREATE TABLE Department (
    id INT PRIMARY KEY COMMENT '部门编号',
    name VARCHAR(20) NOT NULL COMMENT '部门名称'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO Department(id, name)
VALUES
(1, 'IT'),
(2, 'Sales');
CREATE TABLE Employee (
    id INT PRIMARY KEY COMMENT '员工工号',
    name VARCHAR(20) NOT NULL COMMENT '员工姓名',
    salary INT NOT NULL COMMENT '工资',
    department_id INT COMMENT '部门编号',
    FOREIGN KEY (department_id) REFERENCES Department(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO Employee(id, name, salary, department_id)
VALUES
(1, 'Joe', 85000, 1),
(2, 'Henry', 80000, 2),
(3, 'San', 60000, 2),
(4, 'Max', 90000, 1),
(5, 'Janet', 69000, 1),
(6, 'Randy', 85000, 1),
(7, 'Will', 70000, 1);

答案：

select tem.department_name,tem.employee_name,tem.salary
from (
	select d.name as department_name,e.name as employee_name,salary,department_id,
				DENSE_RANK() over(
					PARTITION by e.name
					ORDER BY salary desc) as salary_rank
		from Employee e
		left join Department d on d.id = e.department_id)tem
where tem.salary_rank<=3

## 或者
select t.`name` as department_name,e.name,e.salary from Employee e
left join Department t on t.id = e.department_id
where e.id in (
	select id from (
		select id,name,salary,department_id,
			DENSE_RANK() over(
				PARTITION by name
				ORDER BY salary desc) as rank_id
		from Employee) tem
	where tem.rank_id <=3
)