SQL进阶能力：经典面试题

1. 连续登录问题

问题描述：

用户信息表 user_info 记录了用户的登录数据，包含以下字段：

• user_id：用户 ID
• login_date：登录日期（日期格式）

要求取出 连续三天登录 的用户。

数据示例：

假设 user_info 表数据如下：

实现思路:
思路1：

1. 按用户分组：根据 user_id 分组，按 login_date 排序。
2. 计算日期差：使用 lag 函数获取上一条记录的登录日期，并计算当前记录与上一条记录的日期差。
3. 标记连续登录：如果日期差为 1，则标记为连续登录。
4. 判断连续三天登录：使用窗口函数统计连续登录的天数，筛选出连续三天登录的用户。

思路2：

1. 巧用连续登录的数据规律，使用row_number()窗口函数实现分组统计；

Hive/Spark SQL 实现:
思路1:

select  distinct user_id
from    (
            select   user_id
                    ,login_date
                    ,sum(if(lag_login_date is null or datediff(login_date, lag_login_date) != 1, 1, 0)) over (partition by user_id order by login_date) as group_id
            from    (
                        select   user_id
                                ,login_date
                                ,lag(login_date) over (partition by user_id order by login_date) lag_login_date
                        from    user_info
                    ) a
        ) t
group by
        user_id
        ,group_id
having  count(1) >= 3

思路2：

select  distinct user_id
from    (
            select   user_id
                    ,login_date
                    ,date_sub(login_date, rn) sub_login_date
            from    (
                        select   user_id
                                ,login_date
                                ,row_number() over (partition by user_id order by login_date) rn
                        from    user_info
                    ) a
        ) t
group by
        user_id
        ,sub_login_date
having  count(1) >= 3;

结果示例:
运行上述 SQL 后，结果如下：

问题总结:

• 这个问题归根到底还是分组的问题，将连续登录的日期按照一定的方法归到相同的组再进行统计；

2. 分组问题

问题描述:
用户访问一个 APP，连续访问多个页面，后端会记录每次访问的时间日志（时间戳格式，单位为秒）。需要按照 60 秒 的时间间隔作为一次会话进行切分。如果一条日志的时间距离上一条日志的时间相差超过 60 秒，则属于不同的会话。

数据示例:
假设原始数据表为 user_logs，包含以下字段：

• user_id：用户 ID
• page_id：访问的页面 ID
• log_time：访问时间（秒时间戳）

示例数据：

实现思路:

1. 按用户分组：根据 user_id 分组，按 log_time 排序。
2. 计算时间差：使用 LAG、LEAD 函数获取上一条记录的时间，并计算当前记录与上一条记录的时间差。
3. 判断会话：如果时间差超过 60 秒，则标记为新会话的开始。
4. 生成会话 ID：使用 SUM 函数累加会话标记，生成唯一的会话 ID。

Hive/Spark SQL 实现:

select   user_id
        ,page_id
        ,log_time
        -- 累加会话标记，生成会话 ID
        ,sum(if(ts_diff >= 60, 1, 0)) over (partition by user_id order by log_time) as session_id
from    (
            select   a.user_id
                    ,a.page_id
                    ,a.log_time
                    ,a.log_time - lag_log_time ts_diff
            from    (
                        select   user_id
                                ,page_id
                                ,log_time
                                ,lag(log_time, 1, 0) over (partition by user_id order by log_time) lag_log_time
                        from    user_logs
                    ) a
        ) t

结果示例:
运行上述 SQL 后，结果如下：

问题总结:

• 关键考察lag/sum的窗口函数用法，其中lag函数用lead函数替代同样可以实现

3. 间隔连续问题

问题描述:
某公司记录了用户的每日登录数据，包含以下字段：

• user_id：用户 ID
• login_date：登录日期（日期格式）

要求计算每个用户的最大连续登录天数，允许 间隔一天。例如，用户在 1/3/5/6 登录，则视为连续 6 天登录。

数据示例:
假设 user_login 表数据如下：

实现思路:

1. 按用户分组：根据 user_id 分组，按 login_date 排序。
2. 计算日期差：使用 lag 函数获取上一条记录的登录日期，并计算当前记录与上一条记录的日期差。
3. 标记连续登录：如果日期差小于等于 2，则标记为连续登录；否则标记为新分组。
4. 计算连续登录天数：按分组统计连续登录的天数，取最大值。

Hive/Spark SQL 实现:

select   user_id
        ,max(day_cnt) + 1 as max_consecutive_days
from    (
            select   user_id
                    ,flag
                    ,datediff(max(login_date), min(login_date)) as day_cnt
            from    (
                        select   user_id
                                ,login_date
                                ,sum(if(datediff(login_date, lag_login_date) > 2, 1, 0)) over (partition by user_id order by login_date) flag
                        from    (
                                    select   user_id
                                            ,login_date
                                            ,lag(login_date, 1, '1970-01-01') over (partition by user_id order by login_date) lag_login_date
                                    from    user_login
                                ) a
                    ) t
            group by
                    user_id
                    ,flag
        ) t
group by
        user_id;

结果示例:
运行上述 SQL 后，结果如下：

问题总结:
该问题本质上又回归到了分组问题。

4. 打折日期交叉问题

问题描述:
平台商品促销数据表 promotion_data 包含以下字段：

• brand：品牌
• start_date：打折开始日期
• end_date：打折结束日期

要求计算每个品牌的总打折销售天数，注意处理日期交叉问题。例如：

• vivo 品牌第一次活动时间为 2023-06-05 到 2023-06-15。
• 第二次活动时间为 2023-06-09 到 2023-06-21。
• 其中 2023-06-09 到 2023-06-15 为重复天数，只需要统计一次。
• 总打折天数为 2023-06-05 到 2023-06-21，共计 17 天。

数据示例:
假设 promotion_data 表数据如下：

实现思路:
该问题有两种场景需要考虑：

• 一种是两次活动的日期没有交叉，对于这种情况我们分别求diff再做sum即可；
• 另一种是两次活动日期有交叉，对于这种情况，出现日期交叉的原因是第二次活动的开始日期小于第一次活动的结束日期，也就是说第一次活动还没有结束第二次活动就开始了，换个角度思考，如果第二次活动在第一次活动结束后再开始就不会存在交叉的问题啦；

Hive/Spark SQL 实现:

select   brand
         -- 按照品牌分组，对于大于0的每条数据，计算每条数据加一的总和
         -- 可能存在为负的情况，比如 活动1：2023-06-05 至 2023-06-21；活动2：2023-06-11 至 2023-06-19；活动3：2023-06-20 至 2023-06-27；按照前面对start_date的替换规则，活动2这条数据的day_cnt就小于0；
        ,sum(if(day_cnt >= 0, day_cnt + 1, 0)) total_discount_days
from    (
            select   brand
                    ,datediff(end_date, start_date) day_cnt
            from    (
                        select   brand
                                 -- 比较开始时间与移动下来的数据,如果开始时间大,则不需要操作,
                                 -- 反之则需要将移动下来的数据加一替换当前行的开始时间
                                 -- 如果是第一行数据,maxEDT为null,则不需要操作
                                ,if(max_end_date is null, start_date, if(start_date > max_end_date, start_date, date_add(max_end_date, 1))) start_date
                                ,end_date
                        from    (
                                    select   brand
                                            ,start_date
                                            ,end_date
                                            -- 本次活动之前的所有活动的最大结束日期
                                            ,max(end_date) over (partition by brand order by start_date rows between unbounded preceding and 1 preceding) max_end_date
                                    from    promotion_data
                                ) t1
                    ) t2
        ) t3
group by
        brand

结果示例:
运行上述 SQL 后，结果如下：

问题总结:
实现过程中会用到开窗取前N行数据的最大值的知识点，具体的可以再深入了解下窗口函数的相关知识点；

5. 同时在线问题

问题描述:
某直播平台记录了主播的开播和关播时间，数据表 live_stream 包含以下字段：

• anchor_id：主播 ID
• start_time：开播时间
• end_time：关播时间

要求计算平台最高峰时，同时在线的主播人数。

数据示例:
假设 live_stream 表数据如下：

实现思路:

1. 拆分时间点：将每条记录的 start_time 和 end_time 拆分为两个事件：
   • start_time：主播上线，在线人数 +1。
   • end_time：主播下线，在线人数 -1。
2. 排序事件：将所有事件按时间排序。
3. 计算在线人数：遍历事件，累加在线人数，记录最大值。

Hive/Spark SQL 实现:

select  max(online_count) as max_online_anchor_count
from    (
            select   time
                    ,sum(change) over ( order by time) as online_count
            from    (
                        select   start_time as time
                                ,1          as change
                        from    live_stream
                        union all
                        select   end_time as time
                                ,-1       as change
                        from    live_stream
                    ) t1
        ) t2

结果示例:
运行上述 SQL 后，结果如下：

数据验证:
以下是关键时间点的在线人数变化：

• 2023-10-01 10:00:00：主播 1 和 11 上线，在线人数 = 2。
• 2023-10-01 10:15:00：主播 4 上线，在线人数 = 3。
• 2023-10-01 10:30:00：主播 3 上线，在线人数 = 4。
• 2023-10-01 10:45:00：主播 7 上线，在线人数 = 5。
• 2023-10-01 11:00:00：主播 2 上线，主播 11 下线，在线人数 = 5。
• 2023-10-01 11:15:00：主播 8 上线，在线人数 = 6。
• 2023-10-01 11:30:00：主播 5 上线，主播 3 下线，在线人数 = 6。
• 2023-10-01 11:45:00：主播 12 上线，主播 4 下线，在线人数 = 6。
• 2023-10-01 12:00:00：主播 6 上线，主播 1 下线，在线人数 = 6。
• 2023-10-01 12:15:00：主播 13 上线，在线人数 = 7。
• 2023-10-01 12:30:00：主播 9 上线，主播 7 下线，在线人数 = 7。
• 2023-10-01 13:00:00：主播 10 上线，主播 2 下线，在线人数 = 7。
• 2023-10-01 13:30:00：主播 14 上线，主播 8 下线，在线人数 = 7。
• 2023-10-01 14:00:00：主播 15 上线，主播 5 下线，在线人数 = 7。
• 2023-10-01 14:15:00：主播 12 下线，在线人数 = 6。
• 2023-10-01 14:30:00：主播 9 下线，在线人数 = 5。
• 2023-10-01 15:00:00：主播 6 下线，在线人数 = 4。
• 2023-10-01 15:30:00：主播 13 下线，在线人数 = 3。
• 2023-10-01 16:00:00：主播 10 下线，在线人数 = 2。
• 2023-10-01 17:00:00：主播 14 下线，在线人数 = 1。
• 2023-10-01 18:00:00：主播 15 下线，在线人数 = 0。

最高峰同时在线主播人数为 8，出现在 2023-10-01 12:15:00 到 2023-10-01 12:30:00 之间。