今天分享的题目综合考察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并升序排序。

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

| 输出

示例数据结果如下：

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)。

外部查询

逻辑顺序

①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条件

②分组

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升序排列，得到最终符合要求的用户列表。

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