原题链接:http://practice.atguigu.cn/#/question/15/desc?qType=SQL
题目需求
从登录明细表(user_login_detail)中查询出,所有用户的连续登录两天及以上的日期区间,以登录时间(login_ts)为准。
期望结果如下:
user_id(用户id) | start_date(开始日期) | end_date(结束日期) |
---|---|---|
101 | 2021-09-27 | 2021-09-30 |
102 | 2021-10-01 | 2021-10-02 |
106 | 2021-10-04 | 2021-10-05 |
107 | 2021-10-05 | 2021-10-06 |
需要用到的表:
登录明细表:user_login_detail
user_id(用户id) | ip_address(ip地址) | login_ts(登录时间) | logout_ts(登出时间) |
---|---|---|---|
101 | 180.149.130.161 | 2021-09-21 08:00:00 | 2021-09-27 08:30:00 |
102 | 120.245.11.2 | 2021-09-22 09:00:00 | 2021-09-27 09:30:00 |
103 | 27.184.97.3 | 2021-09-23 10:00:00 | 2021-09-27 10:30:00 |
解题思路
SELECT user_id,
MIN(login_date) AS start_date,
MAX(login_date) AS end_date
FROM
(
SELECT user_id,
login_date,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn
FROM
(
SELECT user_id,
to_date(login_ts) AS login_date
FROM user_login_detail
GROUP BY user_id,
to_date(login_ts)
) t1
) t2
GROUP BY user_id,
date_sub(login_date,rn)
HAVING COUNT(1) >= 2