求连续3天访问的用户id

深藏阁楼爱情的钟 2024-04-07 16:06 72阅读 0赞

**题目**：  
下面表格是用户访问表users，记录了用户id（usr\_id）和访问日期（log\_date）,求出连续3天以上访问的用户id。  
![在这里插入图片描述][9cd2a23854b54ff696799738b6ebe793.png]

**思路**：

1.  按照 **用户 id** 开窗，给访问日期排名（重复的不多占排名，所以用`DENSE_RANK()`）
2.  用访问日期减去排名，得到一个时间tmp ，如果用户是连续访问的，那么这个时间差 `tmp`就是一样的！
3.  一个用户的这个时间如果出现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;

![在这里插入图片描述][4882899abe3e474081979bb971273f1d.png]  
(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 ;

![在这里插入图片描述][e3a43b2d582342e8bcc534df5ab0c166.png]

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

1.  在MySQL中，group by可以使用select的别名！Hive 不行！
2.  在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 ;

![在这里插入图片描述][6c21937d73bf45bfb0e36906a2c96d53.png]

（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

![在这里插入图片描述][a75acf50859d4e9488929bbab0ad938e.png]

参考：https://blog.csdn.net/m0\_66557301/article/details/126055254

[9cd2a23854b54ff696799738b6ebe793.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/4542934d8b2b4401bdbc78f2cdfafb86.png
[4882899abe3e474081979bb971273f1d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/117879ca802647459e6eed59b4ad1359.png
[e3a43b2d582342e8bcc534df5ab0c166.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/1766e287f56b49d19b156e8e3c35989c.png
[6c21937d73bf45bfb0e36906a2c96d53.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/5836f45010e64322b9cbb5e36cd427d6.png
[a75acf50859d4e9488929bbab0ad938e.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/53853d2ec40542818bec6bc8670c5203.png