假設(shè)說我們現(xiàn)在有這樣一張表

初始表

問題分析

連續(xù)登陸码俩，也就是在連續(xù)登陸的期間內(nèi)，后一天和前一天的差值為1歼捏，不能為大于1的值稿存，直到間斷。那么在這里其實我們可以設(shè)置一列序號瞳秽，如果是連續(xù)的話瓣履，這列序號也是會隨著日期同步增長的，那么日期減去這個序號练俐，應(yīng)該都是一個確定的日期袖迎。比如說9月16號對應(yīng)的序號是1，9月17號對應(yīng)的序號是2腺晾，9月18號對應(yīng)的序號是3燕锥，那么9月16號-1=9月15號，同理悯蝉，9月17號-2=9月15號归形，都是同樣的日期。那么我們根據(jù)這個日期和user_id和日期標(biāo)示進(jìn)行分組即可鼻由。

step 1 添加一列

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

返回結(jié)果1

我們可以看到用ROW_NUMBER()函數(shù)已經(jīng)成功加上了一列暇榴。

step 2

我們用log_date這一列減去row_number這一列得到一個時間標(biāo)志

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函數(shù)里面做減法嗡靡。這里要注意跺撼，做subquery的時候要制定子表格的名字，比如我在這里就指定了子表格名字為c讨彼。

step 3

接下來我們要根據(jù)user_id和log_date進(jìn)行分組然后統(tǒng)計歉井。

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;

輸出結(jié)果

step 4

現(xiàn)在我們已經(jīng)得到了每個日期的分組的數(shù)目統(tǒng)計，這里的天數(shù)實際上是開始連續(xù)的那個日期-1（因為不連續(xù)的都不是同一個標(biāo)志日哈误，不會分到一組）但是還有兩個問題哩至，一個是要求出每個id的最大值，另外就是要將天數(shù)加1回到一開始連續(xù)開始的日期蜜自。

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;

輸出結(jié)果

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;

如果原始數(shù)據(jù)表有連續(xù)的日期，應(yīng)該考慮用dense_number()，同樣的操作后仇参，用select distinct的方法來選出user_id, date以及symbol_date都不一樣的一行嘹叫。
關(guān)于這個問題可以參考鏈接：

1. 從用戶連續(xù)活躍的最大天數(shù)說起
2. SQL連續(xù)問題