开窗函数应用

1.编写一个 SQL 查询，查找所有至少连续出现三次的数字。

leetcode:https://leetcode-cn.com/problems/consecutive-numbers/

+----+-----+
| Id | Num |
+----+-----+
| 1  |  1  |
| 2  |  1  |
| 3  |  1  |
| 4  |  2  |
| 5  |  1  |
| 6  |  2  |
| 7  |  2  |
+----+-----+

例如，给定上面的 Logs 表， 1 是唯一连续出现至少三次的数字。

+-----------------+
| ConsecutiveNums |
+-----------------+
| 1               |
+-----------------+

//postgres
select distinct(num) "ConsecutiveNums"
from (
	select num,(row_number() over(order by id )-row_number() over(partition by num order by id))                rank_
	from Logs
) tmp
group by rank_,num
having count(rank_)>=3;

//mysql
/*
通过排名算法实现,当前值@n和上一个值@pre比较,如果相等@n+1 否则@n:=1
最后在查询 count>=3的数据
*/
select distinct  dd.Num ConsecutiveNums
from  (
select d.Num,
       @n :=if(@pre=Num,@n+1,@n:=1) count,
       @pre:=Num
from Logs d,
     (select @pre:=null, @n :=1)r)dd
where dd.count>=3;

2.连续7天登录的用户

user_login 包含date和userId,请用sql找出连续7天登陆的用户

https://www.cnblogs.com/ikww/p/12012831.html

查询7天连续登陆用户这个问题很经典，解决方法也有很多，这里我讲一下笔者的方法，希望对大家有帮助。

具体思路:

1、因为每天用户登录次数可能不止一次，所以需要先将用户每天的登录日期去重。

2、再用row_number() over(partition by _ order by _)函数将用户id分组，按照登陆时间进行排序。

3、计算登录日期减去第二步骤得到的结果值，用户连续登陆情况下，每次相减的结果都相同。

4、按照id和日期分组并求和，筛选大于等于7的即为连续7天登陆的用户。