常用sql--连续天数问题

原创 于 2024-11-13 11:00:17 发布 · 282 阅读 · 1 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#sql

常用解决方法：

窗口函数lag

找一个基准时间相减再减窗口函数row number

在有过滤条件的情况下，时间不连续，则会发生以下两种情况

第一种是连续计数

第二种是重新计数

Azoner
关注
SQL面试题——最大连续登陆问题
11-10 8750
最大连续登陆是我们计算比较常用的指标，我们可以将其当成一个经典的SQL模型，后面我们也会陆续总结出其他的模型，你还知道其他的比较经典的SQL 吗？欢迎分享，或者你还知道其他的关于计算连续的指标？最大连续登陆重要的是计算思想，最大连续才是关键，登陆只是一个场景，要学会举一反三。
【hive】SQL之连续登录
weixin_50573352的博客
09-09 748
在日常工作进行数据的ETL或者面试时，经常遇到类似的问题，比如"统计连续N天交易额超过100万的店铺"、"统计连续登录天数超过3天的用户"等。对于这类问题，思路基本都是一样的。本文将介绍常用的两种解决方案。如何是连续的应该和需要是一样的，如果有跳变（断层）说明不是连续。以"统计连续登录天数超过3天的用户"为需求。
【SQL面试题】——连续天数计算
William
04-01 919
请注意，这个查询假设activity_date列的数据类型是日期类型，并且表中没有缺失的日期记录。如果你的数据表结构或数据库系统与上述示例不同，你可能需要调整查询语句以适应你的实际情况。如果你提供具体的数据库表结构和使用的数据库系统，我可以给出更精确的查询方法。表名：activity_log。
SQL-计算连续签到天数
weixin_47198715的博客
05-24 2337
计算连续签到天数，需要用到窗口函数，日期计算知识点
SQL数据分析——连续数值差、连续时间差、连续登陆时间、连续离线时间
ellinzhang的博客
07-19 601
----step3. 用登陆日期-rank，理论上来讲，如果用户连续登陆，那么减的结果是相等的，我们可以计算这些相同的日期次数，得出连续登陆的天数。-----step2. 将用户登陆日期，按照ID分组，登陆日期从小到大排序，我这里用的是开窗函数row_number()，可以参考站内大神的教程。如何去计算中间离线的天数，可以参考的思路是，将原登陆表建两个临时表，用表1时间和表2时间（次序）差值。现在去看问题4，求连续7天以上离线但回归的用户，因为有回归，那么不能计算最后登陆日期到现在的离线天数。
SQL求解用户连续登录天数
围城客的专栏
08-31 2183
分享一道面试中常常被问到的一类SQL问题：连续问题。
再议SQL问题中，连续天数的求解
陈先生的博客
09-18 1016
SQL连续问题的一种优雅解决方法
sql----连续性问题终极万能模板（2021-07-08）
qq_42166308的博客
07-08 1083
CODE君这几天遇到个烦人的问题，连续性问题！！在这里看到别人的帖子备受触动，写一篇文章分享给大家，一定要收藏好，面试必备，个人能力的体现~~~~~~~~ 有一张“用户登陆记录表”，包含两个字段：用户id、日期。 【问题】查询2021年每个月，每个用户连续登陆的最多天数。 【解题步骤】 连续问题的万能模板 在《拼多多面试题：如何找出连续出现N次的内容？》里讲过遇到“连续问题”如何解决，并送出了一个万能模板，模板使用的是窗口函数解决连续问题。 1 select distinct 列1 2 from (
SQL Server 连续n天问题
最新发布
04-21
- 常用的窗口函数有ROW_NUMBER()、LAG()、LEAD()等，它们可以在连续天数查询中发挥重要作用。 3. 日期函数 - 在处理连续日期查询时，我们会用到一些日期函数，例如DATEADD()、DATEDIFF()等，这些函数能够帮助我们...
Oracle计算连续天数，计算连续时间，Oracle连续天数统计
06-10
在处理连续天数时，`DATE`和`INTERVAL`数据类型也是常用工具。 2. **自连接查询** 对于计算连续天数，一个常见方法是使用自连接查询。假设我们有一个名为`attendance`的表，记录了每个员工每天的出勤情况，包含...
hive经典面试题连续天数sql题
weixin_45758499的博客
02-08 258
hive经典面试题连续天数sql题
关于到今天考勤系统连续上班天数的问题或者连续登陆天数的问题(sql)
yzq_0202的专栏
10-31 4650
DateValue(打卡或者登陆日期),ITime(上班时间),OTime(下班时间),StaffID工号,EMPID为员工编号 一个很好的解题思路 找到最近没打卡或者上班记录的最近日期max(DateValue) select datediff(day,max(DateValue),getdate() ) as DateValue,StaffID from 考勤表 where DateVa
SQL经典题目练习——连续登陆天数（一）
Fairyyyyyy_的博客
07-23 1771
SQL经典题目练习
SQL篇- 最大连续登录天数
麻辣清汤的博客
02-27 2397
sql小白最懂，最大连续登录天数
SQL面试题练习 —— 查询每个用户最大连续登录天数
hu_wei123的博客
07-17 3007
（3）按照 diff 分组，获取每个用户每次连续登录的天数。（2）利用等差数列的特性，如果是连续登录，（4）得出每个用户最大连续登录天数。查询每个用户最大连续登录天数。
sql连续天数计算
起風了
10-30 636
<!--小程序-我的-学习记录-连续学习天数--> <select id="studyStatistics2" resultType="java.util.Map"> SELECT uid, max(days) lianxu_days, min(login_day) start_date,max(login_day) end_date FROM (SELECT uid, @cont_day := (CASE WHEN (@last_uid
SQL如何查询用户的最长连续活跃天数？
qq443967620的博客
06-30 1836
SQL如何查询用户的最长连续活跃天数？
SQL连续登录、活跃、持仓天数问题
imalady的博客
10-10 1189
面试常问问题之SQL连续问题@SQL连续**问题编辑于2024-10-10在我们面试过程当中，经常会被问到经典的SQL连续问题，可能是连续10天活跃用户数，可能是连续十天登录的用户，也可能是计算用户的持仓天数。下面我们综合chatGPT和力扣来进行一下SQL连续问题的一个汇总。
SQL统计连续问题
Eric_qiushui的博客
10-16 1591
连续登录3天及以上的用户、连续提交5天及以上的用户、连续2天及以上购买商品的用户及其对应的天数（连续购物的天数）
SQL提 用户截止某天连续登录的天数
03-27
<think>好的，我需要解决用户的问题：如何用SQL查询用户截止某天的连续登录天数。首先，我得明确用户的需求，是要计算到特定日期为止，每个用户连续登录了多少天。连续登录通常指用户每天都有登录记录，中间没有间断。 接下来，我得考虑数据表的结构。假设有一个登录记录表login_records，包含用户ID(user_id)和登录日期(login_date)两个字段。用户可能会在一天内有多次登录，但计算连续登录时，同一天只算一次，所以需要先去重。 首先，我需要为每个用户生成唯一的登录日期列表，并按日期排序。可以用DISTINCT和GROUP BY来实现。例如：SELECT user_id, DATE(login_date) AS login_day FROM login_records GROUP BY user_id, login_day。这样每个用户每天只保留一条记录。 接下来是关键部分：如何确定连续登录的区间。常用的方法是使用窗口函数ROW_NUMBER()，按用户分区并按日期排序，为每条记录编号。然后用登录日期减去这个编号，得到一个基准日期。如果登录是连续的，这个基准日期应该相同，因为每天递增的日期和编号相减后结果一致。 例如，用户A在2023-01-01、2023-01-02、2023-01-03登录，编号分别是1、2、3。基准日期为2023-01-01 - 1天 = 2022-12-31，同理其他日期减去编号也会得到同样的基准日期。这样，连续登录的天数可以通过计算每个基准日期组内的天数来确定。 然后，针对截止某天的要求，需要筛选出所有登录日期小于等于该日期的记录，并对每个用户的每个基准日期组，找出最大的登录日期。如果最大的登录日期等于截止日期，则该组的连续天数有效。最后，取最大的连续天数作为结果。 不过，用户可能多次连续登录，比如中间断了一天又继续登录，这时需要找出所有可能的连续区间，并计算截止日期所在的区间的天数。如果截止日期在某个连续区间内，则取该区间的天数；否则，可能没有连续登录。 另外，还需要考虑用户截止日期当天是否有登录记录。如果没有，则连续天数可能为0，或者不计算该用户。具体要看业务需求。 总结步骤： 1. 去重处理，确保每天一条记录。 2. 使用窗口函数计算基准日期。 3. 按用户和基准日期分组，计算每组的开始和结束日期。 4. 筛选出包含截止日期的组，并计算该组的连续天数。 5. 处理用户可能在截止日期当天没有登录的情况。 可能的挑战是处理大量数据时的性能问题，以及正确识别所有连续区间。需要确保窗口函数和分组的正确性，并进行测试验证。 最后，编写SQL示例，可能需要使用CTE或者子查询来分步骤实现，确保逻辑清晰，并处理截止日期当天的存在性检查。</think>要计算用户截止某天的连续登录天数，可以通过以下步骤实现： --- ### **步骤1：数据准备** 假设存在登录记录表 `login_records`，包含字段： - `user_id`（用户ID） - `login_date`（登录日期，日期类型） **目标**：计算每个用户截至指定日期（如 `2023-10-01`）的连续登录天数。 --- ### **步骤2：去重处理** 用户可能一天内多次登录，需按天去重： ```sql WITH distinct_logins AS ( SELECT user_id, DATE(login_date) AS login_day -- 转换为日期类型，去除时间部分 FROM login_records GROUP BY user_id, DATE(login_date) ) ``` --- ### **步骤3：标记连续登录区间** 使用窗口函数 `ROW_NUMBER()` 为每个用户的登录日期生成序号，再通过日期与序号差值标记连续区间： ```sql , ranked_logins AS ( SELECT user_id, login_day, login_day - INTERVAL '1 day' * ROW_NUMBER() OVER ( PARTITION BY user_id ORDER BY login_day ) AS grp -- 关键：计算连续区间标识 FROM distinct_logins WHERE login_day <= '2023-10-01' -- 筛选截止日期前的记录 ) ``` --- ### **步骤4：计算连续天数** 按用户和连续区间分组，统计每个区间的起止日期和天数： ```sql , consecutive_groups AS ( SELECT user_id, grp, MIN(login_day) AS start_date, MAX(login_day) AS end_date, COUNT(*) AS consecutive_days FROM ranked_logins GROUP BY user_id, grp ) ``` --- ### **步骤5：筛选截止日期的连续天数** 找到包含截止日期的连续区间，并提取天数： ```sql SELECT user_id, COALESCE(MAX(consecutive_days), 0) AS max_consecutive_days FROM consecutive_groups WHERE end_date = '2023-10-01' -- 仅保留包含截止日期的连续区间 GROUP BY user_id; ``` --- ### **完整SQL示例** ```sql WITH distinct_logins AS ( SELECT user_id, DATE(login_date) AS login_day FROM login_records GROUP BY user_id, DATE(login_date) ), ranked_logins AS ( SELECT user_id, login_day, login_day - INTERVAL '1 day' * ROW_NUMBER() OVER ( PARTITION BY user_id ORDER BY login_day ) AS grp FROM distinct_logins WHERE login_day <= '2023-10-01' ), consecutive_groups AS ( SELECT user_id, grp, MIN(login_day) AS start_date, MAX(login_day) AS end_date, COUNT(*) AS consecutive_days FROM ranked_logins GROUP BY user_id, grp ) SELECT user_id, COALESCE(MAX(consecutive_days), 0) AS max_consecutive_days FROM consecutive_groups WHERE end_date = '2023-10-01' GROUP BY user_id; ``` --- ### **关键逻辑说明** 1. **去重**：确保每个用户每天仅保留一条记录。 2. **连续区间标记**：通过 `login_day - ROW_NUMBER()` 将连续日期映射到同一基准值 `grp`。 3. **筛选有效区间**：仅保留包含截止日期的连续区间，避免计算历史区间。 --- ### **注意事项** - 如果用户截止当天未登录，结果中 `max_consecutive_days` 为 `0`。 - 性能优化：可为 `login_date` 字段建立索引。
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
红包 添加红包
表情包 插入表情
表情包 代码片
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值