关于连续问题的那些事儿
链接：https://leetcode-cn.com/problems/active-users
1. #1454.活跃用户
写一个 SQL 查询, 找到活跃用户的 id 和 name。
活跃用户是指那些至少连续5 天登录账户的用户。
返回的结果表按照 id 排序。
SQL架构
Create table If Not Exists Accounts (id int, name varchar(10))
Create table If Not Exists Logins (id int, login_date date)
Truncate table Accounts
insert into Accounts (id, name) values ('1', 'Winston')
insert into Accounts (id, name) values ('7', 'Jonathan')
Truncate table Logins
insert into Logins (id, login_date) values ('7', '2020-05-30')
insert into Logins (id, login_date) values ('1', '2020-05-30')
insert into Logins (id, login_date) values ('7', '2020-05-31')
insert into Logins (id, login_date) values ('7', '2020-06-01')
insert into Logins (id, login_date) values ('7', '2020-06-02')
insert into Logins (id, login_date) values ('7', '2020-06-02')
insert into Logins (id, login_date) values ('7', '2020-06-03')
insert into Logins (id, login_date) values ('1', '2020-06-07')
insert into Logins (id, login_date) values ('7', '2020-06-10')
表结构
表 Accounts:
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| id | int |
| name | varchar |
+---------------+---------+
id 是该表主键。
该表包含账户 id 和账户的用户名。
表 Logins:
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| id | int |
| login_date | date |
+---------------+---------+
该表无主键, 可能包含重复项。
该表包含登录用户的账户 id 和登录日期. 用户也许一天内登录多次。
结果表格式如下例所示:
Accounts 表:
+----+----------+
| id | name |
+----+----------+
| 1 | Winston |
| 7 | Jonathan |
+----+----------+
Logins 表:
+----+------------+
| id | login_date |
+----+------------+
| 7 | 2020-05-30 |
| 1 | 2020-05-30 |
| 7 | 2020-05-31 |
| 7 | 2020-06-01 |
| 7 | 2020-06-02 |
| 7 | 2020-06-02 |
| 7 | 2020-06-03 |
| 1 | 2020-06-07 |
| 7 | 2020-06-10 |
+----+------------+
Result 表:
+----+----------+
| id | name |
+----+----------+
| 7 | Jonathan |
+----+----------+
id = 1 的用户 Winston 仅仅在不同的 2 天内登录了 2 次, 所以, Winston 不是活跃用户.
id = 7 的用户 Jonathon 在不同的 6 天内登录了 7 次, , 6 天中有 5 天是连续的, 所以, Jonathan 是活跃用户.
方法1
select distinct a.id,a.name from accounts a join
(select id,datediff(lead(login_date,4)over(partition by id order by login_date),login_date) as diff
from (select distinct id,login_date from logins) a) c
on a.id=c.id
where c.diff=4
order by id
1.datediff(date1,date2) 返回起始时间 date1 和结束时间 date2 之间的天数。返回 date1-date2 后的值。还有一个timestampdiff() 返回值是正好相反的。
https://blog.csdn.net/liguangix/article/details/80243197
2.lead(arg1, arg2) arg1表示列名，arg2表示向后行偏移量，默认为1。 当找不到值时返回null 。lag 向前，lead 向后。
方法2
select distinct a.id,a.name from accounts a
join(
select id,login_date,
date_sub(login_date,interval row_number()over(partition by id order by login_date) day) as diff
from (select distinct id,login_date from logins) a)aa
on a.id=aa.id
group by a.id,aa.diff
having count(*)>=5
order by a.id
1.date_sub()函数从日期减去指定的时间间隔。DATE_SUB(OrderDate,INTERVAL 2 DAY)
2.row_number()12345
dense_rank()122345
rank()1224
2. #180连续出现的数字
链接：https://leetcode-cn.com/problems/consecutive-numbers
编写一个 SQL 查询，查找所有至少连续出现三次的数字。
+----+-----+
| Id | Num |
+----+-----+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 1 |
| 6 | 2 |
| 7 | 2 |
+----+-----+
例如，给定上面的 Logs 表， 1 是唯一连续出现至少三次的数字。
+-----------------+
| ConsecutiveNums |
+-----------------+
| 1 |
+-----------------+
Create table If Not Exists Logs (Id int, Num int)
Truncate table Logs
insert into Logs (Id, Num) values ('1', '1')
insert into Logs (Id, Num) values ('2', '1')
insert into Logs (Id, Num) values ('3', '1')
insert into Logs (Id, Num) values ('4', '2')
insert into Logs (Id, Num) values ('5', '1')
insert into Logs (Id, Num) values ('6', '2')
insert into Logs (Id, Num) values ('7', '2')
方法1
select distinct num as ConsecutiveNums from(
select id,num,
lead(num,1)over(order by id) as lead_1,
lead(num,2)over(order by id) as lead_2
from logs )aa
where aa.lead_1=num and aa.lead_2=num
方法2
select distinct num as ConsecutiveNums from(
select id,num,id+1-row_number()over(partition by num order by id)as diff
from logs)aa
group by num,diff
having count(*)>=3
力扣给的测试样例里边有一个序号从0开始的，所以id+1保证不是负数
3.# 601. 体育馆的人流量
链接：https://leetcode-cn.com/problems/human-traffic-of-stadium
编写一个 SQL 查询以找出每行的人数大于或等于 100 且 id 连续的三行或更多行记录。返回按 visit_date 升序排列的结果表。
表：Stadium
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| id | int |
| visit_date | date |
| people | int |
+---------------+---------+
visit_date 是表的主键
每日人流量信息被记录在这三列信息中：序号 (id)、日期 (visit_date)、 人流量 (people)
每天只有一行记录，日期随着 id 的增加而增加
查询结果格式如下所示。
Stadium table:
+------+------------+-----------+
| 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-09 | 188 |
+------+------------+-----------+
Result table:
| id | visit_date | people |
|---|---|---|
| 5 | 2017-01-05 | 145 |
| 6 | 2017-01-06 | 1455 |
| 7 | 2017-01-07 | 199 |
| 8 | 2017-01-09 | 188 |
id 为 5、6、7、8 的四行 id 连续，并且每行都有 >= 100 的人数记录。
请注意，即使第 7 行和第 8 行的 visit_date 不是连续的，输出也应当包含第 8 行，因为我们只需要考虑 id 连续的记录。
不输出 id 为 2 和 3 的行，因为至少需要三条 id 连续的记录。
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-09', '188')
方法1
select id,visit_date,people from(
select id,visit_date,people,
lead(people,1)over(order by id) as ld_1,
lead(people,2)over(order by id) as ld_2,
lag(people,1)over(order by id) as lg_1,
lag(people,2)over(order by id) as lg_2
from stadium )aa
where
aa.people>=100 and (
(aa.ld_1>=100 and aa.ld_2>=100)or(aa.lg_1>=100
and aa.lg_2>=100)or( aa.lg_1>=100 and aa.ld_1>=100))
order by visit_date
这个和上一道的区别在于，上一道只是要求算出连续的那个数，这道题要求把所有连续的行都列出来。所以这道需要取出每行上下共5行的记录，因为所求的记录可能位于中间收尾和末位。
#603. 连续空余座位
几个朋友来到电影院的售票处，准备预约连续空余座位。
你能利用表 cinema ，帮他们写一个查询语句，获取所有空余座位，并将它们按照 seat_id 排序后返回吗？
| seat_id | free |
|---|---|
| 1 | 1 |
| 2 | 0 |
| 3 | 1 |
| 4 | 1 |
| 5 | 1 |
对于如上样例，你的查询语句应该返回如下结果。
| seat_id |
|---|
| 3 |
| 4 |
| 5 |
Create table If Not Exists cinema (seat_id int primary key auto_increment, free bool)
Truncate table cinema
insert into cinema (seat_id, free) values ('1', '1')
insert into cinema (seat_id, free) values ('2', '0')
insert into cinema (seat_id, free) values ('3', '1')
insert into cinema (seat_id, free) values ('4', '1')
insert into cinema (seat_id, free) values ('5', '1')
方法
select distinct seat_id
from(
select seat_id,
lead(seat_id,1)over(order by seat_id) as ld1,
lag(seat_id,1)over(order by seat_id) as lg1
from cinema
where free=1)aa
where seat_id=ld1-1 or seat_id=lg1+1
