今天分享的题目综合考察SQL语句中的窗口函数、时间函数、子查询、表连接等知识点。

| 题目

某产品在2022年2月8日各端口用户注册信息及后几日登录信息如下：

用户注册信息表register_tb(user_id-用户id, reg_time-注册时间, reg_port-注册端口)

用户登录信息表login_tb(log_id-登录动作id,user_id-用户id, log_time-登录时间, log_port-登录端口)

问题：请查询连续登录不少于3天的新注册用户？要求：输出user_id并升序排序。

注：登录表为单日随机一次登录数据，该题忽略单日多次登录情况。

| 输出

示例数据结果如下：

解释：1102在9日、10日、11日，登录了系统满足查询条件；1106在10日、11日、12日登录了系统满足查询条件。| 详细解析本题小编用两种方法进行作答，下面进行逐一讲解~

way1: 窗口函数+时间函数+子查询+表连接

子查询

• 使用内连接(inner join)关联注册表和登录表，确保只分析已注册用户的登录记录。
• date_format(log_time, '%Y-%m-%d'): 使用date_format()函数将log_time格式化为日期(如2022-02-09)
• dense_rank() over(partition by r.user_id order by log_time):
• partition by r.user_id：按用户分区单独计算order by log_time：按登录时间升序排序dense_rank()：分配连续排名，日期相同则排名相同，但后续排名连续(因该题单日仅一次登录，此处等价于row_number())
• date_sub(..., interval rank day): 用格式化后的登录日期减去其对应的排名值，生成基准点 startday。
• 核心思想是：对每个用户的登录日期排序后，用登录日期减去排序序号，连续日期会得到相同的基准点(startday)。

外部查询

• 从子查询 t 中提取user_id字段，按照user_id和startday分组
• 筛选出分组内startday出现次数大于等于 3 的用户，将结果按user_id升序排列。

逻辑顺序

①FROM & JOIN：先执行register_tb和login_tb的INNER JOIN，生成关联结果集(仅包含注册用户的登录记录)。

②窗口函数计算：按user_id分区、log_time排序。计算dense_rank()和startday(子查询的SELECT阶段)。

③子查询结果物化：生成临时表 t，包含user_id和startday。

④GROUP BY：对表 t 按user_id和startday分组。

⑤HAVING：过滤分组结果(count(startday) >= 3)。

⑥SELECT：选择满足条件的user_id。

⑦ORDER BY：对最终结果按user_id升序排序。

way2: 时间函数+子查询

核心思想

way2的核心思想是对每个用户的每个登录日期，检查其后一天和后两天是否也有登录记录。

子查询 t

使用date_format()函数将log_time字段转换为YYYY-MM-DD的日期格式，从login_tb表中获取用户id和格式化后的log_time。

主查询

①where条件

• 条件1：确保只处理新注册的用户。
• 条件2：对 t 中的每条记录，检查该用户当前登录日期的后一天(通过adddate()函数添加日期)是否存在于登录表中。
• 条件3：对 t 中的每条记录，检查该用户当前登录日期的后两天是否存在于登录表中。
• 若某条记录同时满足三个条件，说明存在以该日期为起点的连续三天登录。

②分组

group by user_id: 按用户分组，可确保每个用户只输出一次。

③排序输出

order by user_id: 最终按用户id升序输出结果

逻辑顺序

①最内层子查询 t：从 login_tb 表中提取用户id和格式化后的登录日期，生成一个包含用户及其对应登录日期的中间结果集。

②筛选新注册用户：通过where user_id in (select user_id from register_tb)条件，限定只处理属于新注册用户的记录。

③判断连续登录3天：对 t 中每条记录，执行两次子查询检查后一天和后两天的登录记录。

④分组和排序：按user_id分组，每个用户保留一条记录。按user_id升序排列，得到最终符合要求的用户列表。

以上便是本题的全部分析，希望对您有帮助~