有一张学习打卡表
his_sign
表，简单起见，只设置了两个字段
(id,create_ts)
，一个是主键，另一个是打卡时间。
his_sign
表的数据如下，我们要统计出这张表里面最长的连续打卡记录。
id create_ts
------ ---------------------
1 2020-05-01 09:04:26
2 2020-05-02 11:54:45
3 2020-05-04 23:05:03
4 2020-05-06 07:12:31
5 2020-05-06 08:01:52
6 2020-05-07 22:06:48
7 2020-05-08 12:36:58
8 2020-05-09 11:49:13
9 2020-05-12 08:52:35
10 2020-05-13 23:45:57
11 2020-05-14 00:02:24
12 2020-05-14 09:24:18
13 2020-05-19 15:34:45
14 2020-05-21 21:10:02 复制
先检查数据，我们发现在一天之内可以多次打卡，因此需要先去掉重复打卡的记录，并将字段
create_ts
使用日期格式展示。
SELECT DISTINCT
(DATE(create_ts)) AS create_ts
FROM
his_sign 复制
去重并格式化后的数据如下：
create_ts
------------
2020-05-01
2020-05-02
2020-05-04
2020-05-06
2020-05-07
2020-05-08
2020-05-09
2020-05-12
2020-05-13
2020-05-14
2020-05-19
2020-05-21 复制
由于数据量不大，我们观察表数据可知，
2020-05-06
到
2020-05-09
是最长的序列，总共 4 天。
解题的思路就是把连续的日期编为一组，然后从多组数据中找到数量最多的一组数据，那组数据就是最长的序列。
将表里面的数据按日期的升序排序，并给每个日期分配一个连续的自然数序号，用日期减去它对应的序号，会得到一个新的日期值。我们发现，连续的日期它们对应的新的日期值为同一个，因此，这个新的日期值就是序列的组别。
找到连续日期的组的 SQL 如下：
WITH t1 AS
(SELECT DISTINCT
(DATE(create_ts)) AS create_ts
FROM
his_sign),
t2 AS
(SELECT
create_ts,
row_number () over (
ORDER BY create_ts) AS rn
FROM
t1)
SELECT
create_ts,
DATE_SUB(create_ts, INTERVAL rn DAY) AS grp
FROM
t2 复制
上面 SQL 执行后输出的结果：
create_ts grp
---------- ------------
2020-05-01 2020-04-30
2020-05-02 2020-04-30
2020-05-04 2020-05-01
2020-05-06 2020-05-02
2020-05-07 2020-05-02
2020-05-08 2020-05-02
2020-05-09 2020-05-02
2020-05-12 2020-05-04
2020-05-13 2020-05-04
2020-05-14 2020-05-04
2020-05-19 2020-05-08
2020-05-21 2020-05-09 复制
剩下的操作就简单多了，把数据最多的那组找出来就对了。只是需要注意，最长的序列有可能有多个，因此在找最长的序列的时候需要注意方法。
结合开窗函数
rank() over(ORDER BY xxx)
可以找到多个最长序列，完整的 SQL 如下：
# 1.去掉重复日期，并格式化
WITH t1 AS
(SELECT DISTINCT
(DATE(create_ts)) AS create_ts
FROM
his_sign),
# 2.给每个日期指定一个序号
t2 AS
(SELECT
create_ts,
row_number () over (
ORDER BY create_ts) AS rn
FROM
t1),
# 3.找到分组的依据
t3 AS
(SELECT
create_ts,
DATE_SUB(create_ts, INTERVAL rn DAY) AS grp
FROM
t2),
# 4.分组
t4 AS
(SELECT
MIN(create_ts) AS start_date,
MAX(create_ts) AS end_date,
COUNT(*) AS cnt
FROM
t3
GROUP BY grp),
# 5.对所有序列按照长度降序排序
t5 AS
(SELECT
*,
rank () over (
ORDER BY cnt DESC) AS rk
FROM
t4)
# 6.只选择最长的序列
SELECT
start_date,
end_date,
cnt
FROM
t5
WHERE rk = 1 复制
输出：
start_date end_date cnt
---------- ---------- --------
2020-05-06 2020-05-09 4 复制
为了让大家看得更明白，我用 CTE 表达式把每个过程都写出来了。每段表达式都加了注释，理解起来应该不难。
注意，上述的 SQL 需要在 MySQL 8.0 + 环境里才能正常执行。