用户登录表 用户每一次登录都会在数据中产生一行数据 字段:userid 用户唯一标识,login_timestamp 登录时间戳,p_date 登录时间年月日; 需求一:获取连续登录三天的用户。 需求二:补充说明连续登录的用户,在哪些时间天是连续登录的。 用oracle sql写
时间: 2024-09-18 21:14:24 浏览: 29
为了满足这两个需求,我们可以使用Oracle SQL的`CONNECT BY`子句以及窗口函数来处理。这里有一个基本的SQL查询示例:
**需求一:获取连续登录三天的用户**
```sql
SELECT userid
FROM (
SELECT userid, login_timestamp,
ROW_NUMBER() OVER (PARTITION BY userid ORDER BY login_timestamp) as row_num
FROM user_login_table
)
WHERE DATEDIFF('day', lag(login_timestamp) OVER (PARTITION BY userid ORDER BY login_timestamp), login_timestamp) <= 3
AND row_num = 1;
```
这个查询首先按用户ID分组并排序登录时间,然后计算每个用户的行数(`row_num`)。连续登录三天的用户在`row_num`列会是1,同时满足连续登录的条件。
**需求二:补充说明连续登录的用户在哪些时间段是连续登录的**
```sql
WITH consecutive_days AS (
SELECT userid, login_timestamp,
LAG(login_timestamp) OVER (PARTITION BY userid ORDER BY login_timestamp) as prev_login
FROM user_login_table
),
three_consecutive_days AS (
SELECT *
FROM consecutive_days
WHERE DATEDIFF('day', prev_login, login_timestamp) <= 3
)
SELECT t1.userid, t1.login_timestamp, t1.prev_login
FROM three_consecutive_days t1
LEFT JOIN consecutive_days t2 ON t1.userid = t2.userid AND t1.prev_login + 1 = t2.login_timestamp
WHERE t2.userid IS NULL;
```
在这个查询中,我们先创建了一个名为`consecutive_days`的临时表,记录了每个用户的连续登录时间。接着在`three_consecutive_days`中筛选出那些连续登录三天的用户。最后通过左连接查找没有后续连续登录的日期,即得到用户连续登录的具体天数。