三步： 第一步：求连续值，和lag作差=1的为0表示连续，不连续的则为1，确定断裂点 第二步：组内加合，断裂点的连续 假设原来 1 0 1 0 1，SUM之后就是 11 22 3 也就表示了前两个一组连续 3和4位连续一组，最后一个自己一组 第三步: 组内赋rank，row_number 给group内部排序，新的组新的排序，就可以了 WITH RankedDates AS ( SELECT userid, dt, (CASE WHEN dt - LAG(dt) OVER(PARTITION BY userid ORDER BY dt) = 1 THEN 0 ELSE 1 END) AS NewGroupFlag FROM table1 ), GroupedDates AS ( SELECT userid, dt, SUM(NewGroupFlag) OVER(PARTITION BY userid ORDER BY dt) AS GroupNum FROM RankedDates ) SELECT userid, dt, ROW_NUMBER() OVER(PARTITION BY userid, GroupNum ORDER BY dt) AS days FROM GroupedDates ORDER BY userid, dt;