SQL连续数据的查询，查询最大连续签到的时长、查询连续人流量超过2w的日期。

当前位置：点晴教程→知识管理交流 →『技术文档交流』

freeflydom

2024年8月2日 15:15 本文热度 902

场景

场景一：需要查询最大连续签到的时长，一般用于toc的场景中
场景二：查询连续人流量超过2w的日期，某游乐场的业务管理系统

思路

这种题目我一般常用的思路就是动转静，连续转不变。
比如场景二，需要找连续的日期，那么就要再找一个连续的变量，让两个变量进行相减或者其他操作，得到不变的数据，然后通过分组或者分区查询即可。

例题

体育馆人流量表

visit_date 是该表中具有唯一值的列。
每日人流量信息被记录在这三列信息中：序号 (id)、日期 (visit_date)、人流量 (people)
每天只有一行记录，id与日期递增，日期不一定是连续递增。
编写SQL找出每天的人数大于或等于 100 且日期连续的三行或更多行记录。返回按 visit_date 升序排列的结果表

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)

例题解释

体育馆的人流表，要求找出最少三天连续人流量超过100的数据，并且按照
visit_date升序排序。

实战

做题目前先回顾下SqlServer中的一个函数

DATEADD(DAY,2,'2020-03-27');

该函数可以在日期上进行加减，并且可以执行加减的日期部分，DAY表示天数增加。

按照思路，日期连续，那么可以生成一列行号，用日期与行号作差，如果结果相等则表示连续。

解题生成行号

需要自己创造动态列，比如自己添加行号列
至于为什么不用ID，id也是递增的，我们做完再讲。

SELECT t1.id,t1.visit_date,t1.people FROM (SELECT t.*, COUNT(1) OVER(partition by DATEADD(DAY, t.rowId * -1,t.visit_date)) AS daysCount FROM(SELECT *,ROW_NUMBER() Over (ORDER BY id) as rowId FROM Stadium where people >= 100) t ) t1 WHERE t1.daysCount > =3

步骤
1.筛选人流量大于等于100的数据，并且通过分区函数增加行号
2.用日期减去行号得到一个日期，相等则表示日期连续，再次通过分区函数基于得到的日期获取分区数量
3.筛选分区数量大于等于3的就是连续三天或者三天以上人流量大于等于100的数据
4.作为子查询结果处理得到结果。

结果