CREATE TABLE T202304 ( UUID VARCHAR2(10), LOADTIME DATE ) ; INSERT INTO T202304 SELECT '201',TO_DATE ('2017/01/28','YYYY-MM-DD') FROM DUAL UNION ALL SELECT '201',TO_DATE ('2017/01/29','YYYY-MM-DD') FROM DUAL UNION ALL SELECT '202',TO_DATE ('2017/01/29','YYYY-MM-DD') FROM DUAL UNION ALL SELECT '202',TO_DATE ('2017/01/30','YYYY-MM-DD') FROM DUAL UNION ALL SELECT '203',TO_DATE ('2017/01/30','YYYY-MM-DD') FROM DUAL UNION ALL SELECT '201',TO_DATE ('2017/01/31','YYYY-MM-DD') FROM DUAL UNION ALL SELECT '202',TO_DATE ('2017/01/31','YYYY-MM-DD') FROM DUAL UNION ALL SELECT '201',TO_DATE ('2017/02/01','YYYY-MM-DD') FROM DUAL UNION ALL SELECT '202',TO_DATE ('2017/02/01','YYYY-MM-DD') FROM DUAL UNION ALL SELECT '201',TO_DATE ('2017/02/02','YYYY-MM-DD') FROM DUAL UNION ALL SELECT '203',TO_DATE ('2017/02/02','YYYY-MM-DD') FROM DUAL UNION ALL SELECT '203',TO_DATE ('2017/02/03','YYYY-MM-DD') FROM DUAL ; COMMIT ; 查询每个用户最大连续登录天数 预计实现效果如下：

可以使用以下 SQL 语句实现：

WITH t1 AS (
SELECT UUID,
LOADTIME,
LOADTIME - ROW_NUMBER() OVER (PARTITION BY UUID ORDER BY LOADTIME) AS grp
FROM T202304
), t2 AS (
SELECT UUID,
COUNT(*) AS cnt,
MIN(LOADTIME) AS start_date,
MAX(LOADTIME) AS end_date
FROM t1
GROUP BY UUID, grp
), t3 AS (
SELECT UUID,
MAX(cnt) AS max_cnt
FROM t2
GROUP BY UUID
)
SELECT t2.UUID,
t3.max_cnt AS max_continuous_days
FROM t2
JOIN t3 ON t2.UUID = t3.UUID AND t2.cnt = t3.max_cnt;

解释一下这个 SQL 语句的实现思路：

- 首先使用 WITH 子句创建一个名为 t1 的临时表，其中计算出每个用户的每次登录所处的连续登录天数的组编号 grp。这个 grp 的计算方法为 LOADTIME 减去行号，即 LOADTIME - ROW_NUMBER() OVER (PARTITION BY UUID ORDER BY LOADTIME)。
- 然后使用 t1 表来创建名为 t2 的临时表，其中对每个用户的每个连续登录天数的组进行汇总，计算出每个组的登录天数 cnt、开始日期 start_date 和结束日期 end_date。
- 接着使用 t2 表来创建名为 t3 的临时表，其中对每个用户的所有连续登录天数的组进行汇总，计算出每个用户的最大连续登录天数 max_cnt。
- 最后使用 t2 和 t3 表来查询每个用户的最大连续登录天数。查询结果为每个用户的 UUID 和最大连续登录天数 max_continuous_days。