有一張學習打卡表
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 + 環境裡才能正常執行。