假设说我们现在有这样一张表

初始表

问题分析

连续登陆，也就是在连续登陆的期间内，后一天和前一天的差值为1，不能为大于1的值，直到间断。那么在这里其实我们可以设置一列序号，如果是连续的话，这列序号也是会随着日期同步增长的，那么日期减去这个序号，应该都是一个确定的日期。比如说9月16号对应的序号是1，9月17号对应的序号是2，9月18号对应的序号是3，那么9月16号-1=9月15号，同理，9月17号-2=9月15号，都是同样的日期。那么我们根据这个日期和user_id和日期标示进行分组即可。

step 1 添加一列

SELECT user_id, log_date, ROW_NUMBER() OVER(PARTITION BY 
user_id ORDER BY log_date) AS rn FROM user_logging_format;

返回结果1

我们可以看到用ROW_NUMBER()函数已经成功加上了一列。

step 2

我们用log_date这一列减去row_number这一列得到一个时间标志

SELECT user_id, log_date, date_sub(log_date, cast(rn AS INT)) AS symbol_date from(SELECT user_id, log_date, ROW_NUMBER() OVER(PARTITION BY 
user_id ORDER BY log_date) AS rn FROM user_logging_format)c;

image.png

在这里做的事情是把之前的rn从string的格式改为了int的格式，这样才能够传入到date_sub函数里面做减法。这里要注意，做subquery的时候要制定子表格的名字，比如我在这里就指定了子表格名字为c。

step 3

接下来我们要根据user_id和log_date进行分组然后统计。

SELECT user_id, symbol_date, count(*) AS count_val FROM(SELECT user_id, log_date, date_sub(log_date, cast(rn AS INT)) AS symbol_date from(SELECT user_id, log_date, ROW_NUMBER() OVER(PARTITION BY 
user_id ORDER BY log_date) AS rn FROM user_logging_format)c)d GROUP BY user_id, symbol_date;

输出结果

step 4

现在我们已经得到了每个日期的分组的数目统计，这里的天数实际上是开始连续的那个日期-1（因为不连续的都不是同一个标志日，不会分到一组）但是还有两个问题，一个是要求出每个id的最大值，另外就是要将天数加1回到一开始连续开始的日期。

SELECT user_id, MAX(count_val) AS max_count FROM (SELECT user_id, symbol_date, count(*) AS count_val FROM(SELECT user_id, log_date, date_sub(log_date, cast(rn AS INT)) AS symbol_date from(SELECT user_id, log_date, ROW_NUMBER() OVER(PARTITION BY 
user_id ORDER BY log_date) AS rn FROM user_logging_format)c)d GROUP BY user_id, symbol_date)e GROUP BY user_id;

输出结果

step 5

以上解决了找到最大值，但是最大值的日期没有返回。
由于嵌套写得太多了，接下来还是新建一张表格，用连接的方式找出最大日期

CREATE TABLE count_table AS SELECT user_id, symbol_date, count(*) AS count_val FROM(SELECT user_id, log_date, date_sub(log_date, cast(rn AS INT)) AS symbol_date from(SELECT user_id, log_date, ROW_NUMBER() OVER(PARTITION BY 
user_id ORDER BY log_date) AS rn FROM user_logging_format)c)d GROUP BY user_id, symbol_date

CREATE TABLE max_table AS SELECT user_id, MAX(count_val) AS max_count FROM (SELECT user_id, symbol_date, count(*) AS count_val FROM(SELECT user_id, log_date, date_sub(log_date, cast(rn AS INT)) AS symbol_date from(SELECT user_id, log_date, ROW_NUMBER() OVER(PARTITION BY 
user_id ORDER BY log_date) AS rn FROM user_logging_format)c)d GROUP BY user_id, symbol_date)e GROUP BY user_id;

SELECT m.user_id, m.max_count, DATE_ADD(t.symbol_date,1) AS start_date FROM max_table AS m INNER JOIN count_table t ON m.user_id=t.user_id and m.max_count=t.count_val;

如果原始数据表有连续的日期，应该考虑用dense_number()，同样的操作后，用select distinct的方法来选出user_id, date以及symbol_date都不一样的一行。
关于这个问题可以参考链接：

1. 从用户连续活跃的最大天数说起
2. SQL连续问题