SQL连续登录等连续判断问题解题思路

#作者原创,转载请注明出处

经常遇到判断n天连续登录的问题(衡量用户活跃度),用SQL怎么实现呢?

首先计量单位是天,所以先把登录时间戳去掉时分秒,保留到年月日.

一天多次登录会有多条记录,所以需要用group by或者distinct或者排名之类的方法去重

得到呢每天是否登录的信息(二值化)

思考连续N天登录有啥特征

特征1:一个连续队列(公差d1)和另外一个连续队列(公差d2)做差(向量运算),当d1=d2的时候,有差值为一个常量,然后统计常量的个数.

特征2:一个连续队列,从某数x开始后n个公差内有n个数

特征3:连续队列中,相邻两个数相减为公差d,常量

所以有两种算法,

算法1:去重之后的登录时间减去公差为1的队列(123456...),得到一系列时间(Oracle),统计相同的时间个数,看有没有大于n的.

算法2:去重的数据,以当前时间为"计算节点",计算当前时间到其后n天有多少条数据

算法3:按照id分组,按时间排序过后,行偏移错位相减,统计一个固定间隔内(like算法2)相减结果(间距)个数是否为n-1

当然啦,穷举大法也挺不错的,[手动滑稽]