mysql 记录连续问题

mysql 记录连续问题

最近碰到一个sql问题：要求连续三天及以上，并且每天人流量均不少于100

mysql> select * from t_people_stat;
+----+------------+--------+
| id | start_date | people |
+----+------------+--------+
|  1 | 2019-04-03 |    100 |
|  3 | 2019-04-04 |     90 |
|  4 | 2019-04-05 |     80 |
|  5 | 2019-04-06 |    105 |
|  6 | 2019-04-07 |    109 |
|  7 | 2019-04-09 |    100 |
|  8 | 2019-04-10 |     70 |
+----+------------+--------+
7 rows in set (0.13 sec)

解决思路

• 过滤掉人流量<100的记录
• 生成虚拟的自增长列gid
• 用mysql数据库中自增长的id减去虚拟的自增长的列gid,即(id-gid) as diff，构造成等差数列
• 如果diff相同，则说明是连续的

实现

这里还用到了FIND_IN_SET和GROUP_CONCAT函数，注意：GROUP_CONCAT默认最长为1024个字节

SELECT
	tps.*
FROM
	t_people_stat tps,
(SELECT
	tps.id,
	tps.start_date,
	tps.people,
	( tps.id - tps.gid ) diff,
	GROUP_CONCAT(tps.id ORDER BY tps.id) ids,
	GROUP_CONCAT(tps.people) peoples
FROM
	( SELECT tps.*, @a := @a + 1 gid FROM t_people_stat tps, ( SELECT @a := 0 ) AS t WHERE tps.people >= 100) tps
GROUP BY diff HAVING count(id) >= 3)tpss
WHERE
FIND_IN_SET(tps.id,tpss.ids)

输出结果:

+----+------------+--------+
| id | start_date | people |
+----+------------+--------+
|  5 | 2019-04-06 |    105 |
|  6 | 2019-04-07 |    109 |
|  7 | 2019-04-09 |    100 |
+----+------------+--------+
3 rows in set (0.06 sec)