rownumber

row_number() over(partition by 分组列 order by 排序列 desc)
row_number() over 具有分组排序的功能

#第一步
#判断连续的核心是row_number
#因为row_number是连续的
#所以day-row_number，如果值是恒定的，说明也是连续的，反之一定会变化
select uid,(day(loadtime)-row_number() over(partition by uid order by uid) ) AS cnt 
from loadrecord;

#第二步
#第一步得到的结果还不是很明显，需要分组用count()计数，取得不同连续值的次数
SELECT uid,count(*) FROM
(
SELECT uid,(DAY(loadtime)-row_number() over(PARTITION BY uid ORDER BY uid) ) AS cnt 
FROM loadrecord
) AS a
GROUP BY uid,cnt;

#第三步
#取得最大连续值
#采用类似部门最高薪的方法 select max() group by 
SELECT uid, MAX(cnt) FROM
(
SELECT uid,count(*) AS cnt FROM
(
SELECT uid,(DAY(loadtime)-row_number() over(PARTITION BY uid ORDER BY uid) ) AS cnt 
FROM loadrecord
) AS a
GROUP BY uid,cnt
) AS b
GROUP BY uid;

#row_number  无视相同值
SELECT * FROM(
SELECT *,row_number() over(partition by subject order by score desc) AS 'rank' FROM datasubject) AS t 
WHERE t.rank < 4;

#rank() 遇到相同值跳跃式排名
SELECT * FROM(
SELECT *,rank() over(partition by subject order by score desc) AS 'rank' FROM datasubject) AS t 
WHERE t.rank < 4;

#dense_rank() 遇到相同值连续式排名
SELECT * FROM(
SELECT *,dense_rank() over(partition by subject order by score desc) AS 'rank' FROM datasubject) AS t 
WHERE t.rank < 4;