關(guān)于連續(xù)問(wèn)題的那些事兒
鏈接：https://leetcode-cn.com/problems/active-users
1. #1454.活躍用戶
寫一個(gè) SQL 查詢, 找到活躍用戶的 id 和 name厚脉。
活躍用戶是指那些至少連續(xù)5 天登錄賬戶的用戶。
返回的結(jié)果表按照 id 排序。
SQL架構(gòu)
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')
表結(jié)構(gòu)
表 Accounts:
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| id | int |
| name | varchar |
+---------------+---------+
id 是該表主鍵。
該表包含賬戶 id 和賬戶的用戶名失都。
表 Logins:
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| id | int |
| login_date | date |
+---------------+---------+
該表無(wú)主鍵, 可能包含重復(fù)項(xiàng)。
該表包含登錄用戶的賬戶 id 和登錄日期. 用戶也許一天內(nèi)登錄多次钢悲。
結(jié)果表格式如下例所示:
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 天內(nèi)登錄了 2 次, 所以, Winston 不是活躍用戶.
id = 7 的用戶 Jonathon 在不同的 6 天內(nèi)登錄了 7 次, , 6 天中有 5 天是連續(xù)的, 所以, 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) 返回起始時(shí)間 date1 和結(jié)束時(shí)間 date2 之間的天數(shù)焕阿。返回 date1-date2 后的值。還有一個(gè)timestampdiff() 返回值是正好相反的坷随。
https://blog.csdn.net/liguangix/article/details/80243197
2.lead(arg1, arg2) arg1表示列名房铭，arg2表示向后行偏移量，默認(rèn)為1温眉。 當(dāng)找不到值時(shí)返回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()函數(shù)從日期減去指定的時(shí)間間隔凌蔬。DATE_SUB(OrderDate,INTERVAL 2 DAY)
2.row_number()12345
dense_rank()122345
rank()1224
2. #180連續(xù)出現(xiàn)的數(shù)字
鏈接：https://leetcode-cn.com/problems/consecutive-numbers
編寫一個(gè) SQL 查詢，查找所有至少連續(xù)出現(xiàn)三次的數(shù)字豌骏。
+----+-----+
| Id | Num |
+----+-----+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 1 |
| 6 | 2 |
| 7 | 2 |
+----+-----+
例如龟梦，給定上面的 Logs 表， 1 是唯一連續(xù)出現(xiàn)至少三次的數(shù)字窃躲。
+-----------------+
| 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
力扣給的測(cè)試樣例里邊有一個(gè)序號(hào)從0開(kāi)始的计贰，所以id+1保證不是負(fù)數(shù)
3.# 601. 體育館的人流量
鏈接：https://leetcode-cn.com/problems/human-traffic-of-stadium
編寫一個(gè) SQL 查詢以找出每行的人數(shù)大于或等于 100 且 id 連續(xù)的三行或更多行記錄。返回按 visit_date 升序排列的結(jié)果表蒂窒。
表：Stadium
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| id | int |
| visit_date | date |
| people | int |
+---------------+---------+
visit_date 是表的主鍵
每日人流量信息被記錄在這三列信息中：序號(hào) (id)躁倒、日期 (visit_date)荞怒、 人流量 (people)
每天只有一行記錄，日期隨著 id 的增加而增加
查詢結(jié)果格式如下所示秧秉。
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 連續(xù)荧嵌，并且每行都有 >= 100 的人數(shù)記錄。
請(qǐng)注意砾淌，即使第 7 行和第 8 行的 visit_date 不是連續(xù)的啦撮，輸出也應(yīng)當(dāng)包含第 8 行，因?yàn)槲覀冎恍枰紤] id 連續(xù)的記錄汪厨。
不輸出 id 為 2 和 3 的行赃春，因?yàn)橹辽傩枰龡l id 連續(xù)的記錄。
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
這個(gè)和上一道的區(qū)別在于劫乱，上一道只是要求算出連續(xù)的那個(gè)數(shù)织中，這道題要求把所有連續(xù)的行都列出來(lái)。所以這道需要取出每行上下共5行的記錄衷戈，因?yàn)樗蟮挠涗浛赡芪挥谥虚g收尾和末位狭吼。
#603. 連續(xù)空余座位
幾個(gè)朋友來(lái)到電影院的售票處，準(zhǔn)備預(yù)約連續(xù)空余座位脱惰。
你能利用表 cinema 搏嗡，幫他們寫一個(gè)查詢語(yǔ)句，獲取所有空余座位拉一，并將它們按照 seat_id 排序后返回嗎采盒？
| seat_id | free |
|---|---|
| 1 | 1 |
| 2 | 0 |
| 3 | 1 |
| 4 | 1 |
| 5 | 1 |
對(duì)于如上樣例，你的查詢語(yǔ)句應(yīng)該返回如下結(jié)果蔚润。
| 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
