求连续3天访问的用户id

小半 • 2023年2月3日上午10:46 • 技术随笔 • 阅读 225

题目：
下面表格是用户访问表users，记录了用户id（usr_id）和访问日期（log_date）,求出连续3天以上访问的用户id。

思路：

按照 用户 id 开窗，给访问日期排名（重复的不多占排名，所以用DENSE_RANK()）
用访问日期减去排名，得到一个时间tmp ，如果用户是连续访问的，那么这个时间差 tmp就是一样的！
一个用户的这个时间如果出现3次及以上，说明这个用户连续访问了3天。

(1）先按照用户id（usr_id）对访问日期（log_date）进行排名，这里用DENSE_RANK() 而不是 RANK()

SELECT usr_id,log_date,
DENSE_RANK() OVER (PARTITION BY usr_id order_by log_date) AS rank
FROM users;

(2) 得到排名后，用log_date 减去排名序号！得到差值 tmp ；

SELECT 
    usr_id,
    DATE_SUB(log_date,INTERVAL rank DAY) AS tmp  / 用log_date 减去 排名序号！
    FROM (
        SELECT 
        usr_id,
        log_date,
        DENSE_RANK() OVER (PARTITION BY usr_id ORDER BY log_date) AS rank
        FROM users
    ) AS A ;

(3）同一个用户有3个及以上的时间差值tmp 相同，说明用户连续访问了3天，所以我们对用户和tmp进行分组，并统计判断tmp大于等于3的；
注意：

在MySQL中，group by可以使用select的别名！Hive 不行！
在MySQL 和 Hive中，Having都能使用 select 中的别名！）

SELECT 
usr_id,
DATE_SUB(log_date,INTERVAL rank DAY) AS tmp
FROM (
    SELECT 
    usr_id,
    log_date,
    DENSE_RANK() OVER (PARTITION BY usr_id ORDER BY log_date) AS rank
    FROM users
) AS A
GROUP BY usr_id, tmp  / 联合分组
HAVING COUNT(flg_date) >=3 ;

（4）这样就已经得出最终结果了，因为题目只想知道usr_id，我们最后再将usr_id展示出来就可以了。

最终具体代码如下：

SELECT DISTINCT usr_id
FROM(
    SELECT 
    usr_id,
    DATE_SUB(log_date,INTERVAL rank DAY) AS tmp
    FROM (
        SELECT 
        usr_id,
        log_date,
        DENSE_RANK() OVER (PARTITION BY usr_id ORDER BY log_date) AS rank
        FROM users
    ) AS A
    GROUP BY usr_id,tmp
    HAVING COUNT(tmp) >=3
) AS B