横扫SQL面试——时间序列分组与合并（会话划分）问题

横扫SQL面试题

📌 时间序列分组与合并问题

📚 横扫SQL面试——时间序列分组与合并解析

🌟 核心问题类型

时间序列分组（Sessionization）
处理具有时间维度的连续数据流，根据特定规则（如时间间隔、属性连续性）将相邻记录合并为逻辑组。

话不多说——直接上题：🎈🎈🎈🎈🎈🎈🎈

一、用户访问记录会话划分🎈🎈🎈

给定用户的访问记录access_records表，需要按照同一用户相邻两次访问记录时间间隔小于等于60秒则认为两次浏览记录属于同一会话的规则，为属于同一会话的访问记录增加一个相同的会话id字段。

结果预期✅

✅✅✅

原始数据 → 计算相邻时间差 → 标记新会话 → 累加分组 → 分配ID
     ↓              ↓               ↓             ↓
prev_access_time → new_session_start → session_groups → 最终结果

步骤1：获取相邻记录时间差（CTE: prev_access_time）✅

with prev_access_time as (
    select
        user_id,
        access_time,
        lag(access_time) over (partition by user_id order by access_time
        ) as prev_time
    from access_records
)

输入数据 ✅

中间表 prev_access_time

• LAG(access_time) 获取同一用户前一条记录的访问时间
• PARTITION BY user_id 确保按用户独立处理
• ORDER BY access_time 按时间顺序排序

步骤2：标记新会话起点（CTE: new_session_start）✅

new_session_start as (
    select
        user_id,
        access_time,
        case
            when timestampdiff(second, prev_time, access_time) >= 60 
                 or prev_time is null
            then 1
            else 0
        end as is_new_session
    from prev_access_time
)

中间表 new_session_start

判断逻辑

• 首条记录：prev_time IS NULL → 标记为1
• 间隔计算：08:00:50 - 08:00:00 = 50秒 → <60秒 → 标记0
• 间隔计算：08:02:00 - 08:00:50 = 70秒 → >=60秒 → 标记1

步骤3：生成会话分组（CTE: session_groups）✅

session_groups as (
    select
        user_id,
        access_time,
        sum(is_new_session) over (partition by user_id order by access_time
        ) as session_group
    from new_session_start
)

中间表 session_groups

• SUM(is_new_session) 按用户和时间顺序累加标记
• 同一会话组内的记录具有相同的 session_group 值

步骤4：分配会话ID（主查询）✅

select
    user_id,
    access_time,
    dense_rank() over (
        partition by user_id 
        order by session_group
    ) as session_id
from session_groups;

最终结果

• DENSE_RANK() 保证会话ID连续（无间隔）
• 按 session_group 排序确保同一组ID相同

二、网站页面浏览时长统计🎈🎈🎈

给定网站页面浏览记录，需要按照用户在离开页面后30分钟内再次访问同一页面则认为是连续浏览的规则。
计算：

1. 合并时长：将连续访问合并为一个时间段，计算从首次开始时间到最后结束时间的总跨度
2. 实际时长：累加每次独立访问的实际时长（不合并连续访问）
3. 增加会话id：属于同一浏览的访问记录增加一个相同的会话id字段。

结果预期

还是跟上题同样的套路~😝😝😝

步骤1：排序并获取前次结束时间✅

with ordered_views as(
	select
		user_id,
		page_url,
		view_start_time,
		view_end_time,
		lag(view_end_time) over(partition by user_id,page_url 
								order by view_start_time) as prev_end_time
	from table
)

中间表 ordered_views ✅

步骤2：标记新会话起点✅

session_flags as(
  select *,
    case
      when prev_end_time IS NULL 
        OR timestampdiff(minute, prev_end_time, view_start_time) > 30 
      then 1 
      else 0 
    end as is_new_session
  from ordered_views
)

中间表 session_flags ✅

步骤3：生成会话分组ID✅

session_groups as(
  select*,
    sum(is_new_session) over(partition by user_id, page_url 
      						 order by view_start_time) as session_group
  from session_flags
)

中间表 session_groups ✅

步骤4：分配唯一会话ID✅

session_ids AS (
  SELECT 
    *,
    dense_rank() over (
      partition by user_id, page_url order by session_group ) as session_id
  from session_groups
)

中间表 session_ids ✅

步骤5：聚合结果并计算双指标✅

select
  user_id,
  page_url,
  session_id,
  min(view_start_time) AS view_start_time,
  max(view_end_time) AS view_end_time,
  TIMESTAMPDIFF(MINUTE, min(view_start_time), max(view_end_time)) AS merged_duration,
  sum(TIMESTAMPDIFF(MINUTE, view_start_time, view_end_time)) AS total_real_duration
from session_ids
group by user_id, page_url, session_id;

最终结果 ✅

还是有些难度的哈~

-- 使用CTE（公共表表达式），命名为ordered_views
with ordered_views as (
    -- 从“网站页面浏览记录表”中选择数据
    select 
        user_id,
        page_url,
        view_start_time,
        view_end_time,
        -- 使用LAG窗口函数，获取每个用户和页面URL分组下，按浏览开始时间排序后的上一条记录的浏览结束时间
        lag(view_end_time) over (
            partition by user_id, page_url 
            order by view_start_time
        ) as prev_end_time
    from 网站页面浏览记录表
)
-- 第二个CTE，命名为session_flags
, session_flags as (
    -- 选择ordered_views CTE中的所有列，并添加一个新列is_new_session
    select *,
        -- 使用CASE语句判断是否为新会话
        case 
            -- 如果上一条记录的结束时间为空（即当前是该用户和页面的第一条记录）
            -- 或者当前记录的开始时间与上一条记录的结束时间间隔大于30分钟
            when prev_end_time is null 
                or timestampdiff(minute, prev_end_time, view_start_time) > 30 
            then 1 
            else 0 
        end as is_new_session
    from ordered_views
)
-- 第三个CTE，命名为session_groups
, session_groups as (
    -- 选择session_flags CTE中的所有列，并添加一个新列session_group
    select *,
        -- 使用SUM窗口函数，对每个用户和页面URL分组下，按浏览开始时间排序后的is_new_session列进行累加
        sum(is_new_session) over (
            partition by user_id, page_url 
            order by view_start_time
        ) as session_group
    from session_flags
)
-- 第四个CTE，命名为session_ids
, session_ids as (
    -- 选择session_groups CTE中的所有列，并添加一个新列session_id
    select 
        *,
        -- 使用DENSE_RANK窗口函数，对每个用户和页面URL分组下，按session_group进行排名，得到会话ID
        dense_rank() over (
            partition by user_id, page_url 
            order by session_group
        ) as session_id
    from session_groups
)

-- 主查询
select 
    user_id,
    page_url,
    session_id,
    -- 对于每个用户、页面和会话ID分组，获取最小的浏览开始时间
    min(view_start_time) as view_start_time,
    -- 对于每个用户、页面和会话ID分组，获取最大的浏览结束时间
    max(view_end_time) as view_end_time,
    -- 计算合并后的会话时长（以分钟为单位）
    timestampdiff(minute, min(view_start_time), max(view_end_time)) as merged_duration,
    -- 计算每个会话内实际的浏览时长总和（以分钟为单位）
    sum(timestampdiff(minute, view_start_time, view_end_time)) as total_real_duration
from session_ids
-- 按照用户ID、页面URL和会话ID进行分组
group by user_id, page_url, session_id
-- 按照用户ID、页面URL和会话ID进行排序
order by user_id, page_url, session_id;

三、公交乘客乘车时间分析🎈🎈🎈

根据公交乘客乘车记录，按照 “下车后90分钟内再次上车视为同一次出行” 的规则，完成以下任务：

1. 分配出行ID：为每个乘客的每次连续出行分配唯一ID
2. 计算双指标：
   • 合并时长：从首次上车到最后一次下车的总时间跨度
   • 实际时长：累加各次乘车的独立时长

结果预期 ✅

换汤不换药~😂

步骤1：排序并获取前次下车时间✅

中间表 ordered_records ✅

步骤2：标记新出行起点✅

中间表 session_flags ✅

步骤3：生成出行分组ID✅

中间表 session_groups ✅

步骤4：分配出行ID并聚合结果✅

最终结果 ✅

-- 使用CTE（公共表表达式），命名为ordered_records
with ordered_records as (
    -- 从“公交乘车记录表”中选择数据
    select 
        passenger_id,
        boarding_time,
        alighting_time,
        -- 使用LAG窗口函数，获取每个乘客分组下，按上车时间排序后的上一条记录的下车时间
        lag(alighting_time) over (
            partition by passenger_id 
            order by boarding_time
        ) as prev_alighting_time
    from 公交乘车记录表
)
-- 第二个CTE，命名为session_flags
, session_flags as (
    -- 选择ordered_records CTE中的所有列，并添加一个新列is_new_session
    select 
        *,
        -- 使用CASE语句判断是否为新会话
        case 
            -- 如果上一条记录的下车时间为空（即当前是该乘客的第一条记录）
            -- 或者当前记录的上车时间与上一条记录的下车时间间隔大于90分钟
            when prev_alighting_time is null 
                or timestampdiff(minute, prev_alighting_time, boarding_time) > 90 
            then 1 
            else 0 
        end as is_new_session
    from ordered_records
)
-- 第三个CTE，命名为session_groups
, session_groups as (
    -- 选择session_flags CTE中的所有列，并添加一个新列session_group
    select 
        *,
        -- 使用SUM窗口函数，对每个乘客分组下，按上车时间排序后的is_new_session列进行累加
        sum(is_new_session) over (
            partition by passenger_id 
            order by boarding_time
        ) as session_group
    from session_flags
)

-- 主查询
select 
    passenger_id,
    -- 对于每个乘客和会话组分组，获取最小的上车时间
    min(boarding_time) as boarding_time,
    -- 对于每个乘客和会话组分组，获取最大的下车时间
    max(alighting_time) as alighting_time,
    -- 计算总的乘车时长（以分钟为单位）
    timestampdiff(minute, min(boarding_time), max(alighting_time)) as total_riding_duration,
    -- 计算每个会话组内实际的乘车时长总和（以分钟为单位）
    sum(timestampdiff(minute, boarding_time, alighting_time)) as total_actual_duration
from session_groups
-- 按照乘客ID和会话组进行分组
group by passenger_id, session_group
-- 按照乘客ID和会话组进行排序
order by passenger_id, session_group;

熟悉的套路😂

后面大家可以自己做一做 博主就不啰嗦啦~ 答案贴在最后啦 做完再看哈🤣

📚 时间序列分组与合并扩展题库

题目一：用户登录会话合并

题干
合并用户登录记录，若两次登录间隔 ≤15分钟 视为同一会话，计算每个会话的持续时间和登录次数。

表结构

示例数据

期望输出

题目二：设备故障时段统计

题干
合并设备的连续故障时段（故障状态代码=500），若两次故障间隔 ≤10分钟 视为同一故障事件。

表结构

示例数据

期望输出

题目三：订单支付超时分析

题干
合并订单的创建和支付操作，若支付时间在创建后 30分钟内 视为有效支付，统计每个订单的实际支付处理时长。

表结构

示例数据

期望输出

题目四：交通卡口连续通行分析

题干
合并车辆通过相邻卡口的记录，若通过时间间隔 ≤5分钟 且行驶方向相同，视为连续通行，计算平均速度。

表结构

示例数据

期望输出

答案：

题目一：用户登录会话合并

-- 使用公共表表达式（CTE），命名为 ordered_logins
with ordered_logins as (
    -- 从 user_login 表中选择数据
    select 
        user_id,
        login_time,
        -- 使用 LAG 窗口函数，在每个 user_id 分区内，按照 login_time 排序
        -- 获取当前记录的上一条记录的 login_time，命名为 prev_login
        lag(login_time) over (partition by user_id order by login_time) as prev_login
    from user_login
)
-- 第二个 CTE，命名为 session_flags
, session_flags as (
    -- 选择 ordered_logins CTE 中的所有列，并添加一个新列 is_new_session
    select 
        *,
        -- 使用 CASE 语句判断是否为新的会话
        case 
            -- 如果上一条记录的登录时间为空（即当前是该用户的第一条登录记录）
            -- 或者当前记录的登录时间与上一条记录的登录时间间隔大于 15 分钟
            when prev_login is null 
                or timestampdiff(minute, prev_login, login_time) > 15 
            then 1 
            else 0 
        end as is_new_session
    from ordered_logins
)
-- 第三个 CTE，命名为 session_groups
, session_groups as (
    -- 选择 session_flags CTE 中的所有列，并添加一个新列 session_id
    select 
        *,
        -- 使用 SUM 窗口函数，在每个 user_id 分区内，按照 login_time 排序
        -- 对 is_new_session 列进行累加，得到每个会话的唯一标识 session_id
        sum(is_new_session) over (partition by user_id order by login_time) as session_id
    from session_flags
)
-- 主查询
select 
    user_id,
    -- 对于每个用户和会话 ID 分组，获取最小的登录时间作为会话开始时间
    min(login_time) as session_start,
    -- 对于每个用户和会话 ID 分组，获取最大的登录时间作为会话结束时间
    max(login_time) as session_end,
    -- 计算会话的持续时间（以分钟为单位）
    timestampdiff(minute, min(login_time), max(login_time)) as duration_min,
    -- 统计每个会话内的登录次数
    count(*) as login_count
from session_groups
-- 按照用户 ID 和会话 ID 进行分组
group by user_id, session_id;

题目二：设备故障时段统计

-- 使用公共表表达式（CTE），命名为 ordered_logs
with ordered_logs as (
    -- 从 device_log 表中选择数据，且只选择状态码为 500 的记录
    select 
        device_id,
        log_time,
        -- 使用 LAG 窗口函数，在每个 device_id 分区内，按照 log_time 排序
        -- 获取当前记录的上一条记录的 log_time，命名为 prev_log
        lag(log_time) over (partition by device_id order by log_time) as prev_log
    from device_log
    where status_code = 500
)
-- 第二个 CTE，命名为 session_flags
, session_flags as (
    -- 选择 ordered_logs CTE 中的所有列，并添加一个新列 is_new_session
    select 
        *,
        -- 使用 CASE 语句判断是否为新的故障时段
        case 
            -- 如果上一条记录的日志时间为空（即当前是该设备的第一条故障记录）
            -- 或者当前记录的日志时间与上一条记录的日志时间间隔大于 10 分钟
            when prev_log is null 
                or timestampdiff(minute, prev_log, log_time) > 10 
            then 1 
            else 0 
        end as is_new_session
    from ordered_logs
)
-- 第三个 CTE，命名为 session_groups
, session_groups as (
    -- 选择 session_flags CTE 中的所有列，并添加一个新列 session_id
    select 
        *,
        -- 使用 SUM 窗口函数，在每个 device_id 分区内，按照 log_time 排序
        -- 对 is_new_session 列进行累加，得到每个故障时段的唯一标识 session_id
        sum(is_new_session) over (partition by device_id order by log_time) as session_id
    from session_flags
)
-- 主查询
select 
    device_id,
    -- 对于每个设备和故障时段 ID 分组，获取最小的日志时间作为故障开始时间
    min(log_time) as start_time,
    -- 对于每个设备和故障时段 ID 分组，获取最大的日志时间作为故障结束时间
    max(log_time) as end_time,
    -- 计算故障时段的持续时间（以分钟为单位）
    timestampdiff(minute, min(log_time), max(log_time)) as error_duration_min
from session_groups
-- 按照设备 ID 和故障时段 ID 进行分组
group by device_id, session_id;

题目三：订单支付超时分析

select 
    order_id,
    -- 使用 COALESCE 函数，若第一个参数不为 NULL，则返回第一个参数的值，否则返回第二个参数的值
    -- 这里计算有效支付时长（以分钟为单位）
    coalesce(
        -- 计算在创建时间和支付时间间隔不超过 30 分钟的情况下，最大支付时间和最小支付时间的差值（以分钟为单位）
        timestampdiff(
            minute, 
            min(case when timestampdiff(minute, create_time, pay_time) <= 30 then pay_time end), 
            max(case when timestampdiff(minute, create_time, pay_time) <= 30 then pay_time end)
        ), 
        0
    ) as valid_pay_duration_min
from orders
-- 按照订单 ID 进行分组
group by order_id;

题目四：交通卡口连续通行分析

-- 使用公共表表达式（CTE），命名为 ordered_passes
with ordered_passes as (
    -- 从 traffic_log 表中选择数据
    select 
        car_plate,
        direction,
        pass_time,
        -- 使用 LAG 窗口函数，在每个 car_plate 和 direction 分区内，按照 pass_time 排序
        -- 获取当前记录的上一条记录的 pass_time，命名为 prev_pass
        lag(pass_time) over (partition by car_plate, direction order by pass_time) as prev_pass
    from traffic_log
)
-- 第二个 CTE，命名为 session_flags
, session_flags as (
    -- 选择 ordered_passes CTE 中的所有列，并添加一个新列 is_new_group
    select 
        *,
        -- 使用 CASE 语句判断是否为新的连续通行组
        case 
            -- 如果上一条记录的通过时间为空（即当前是该车牌和方向的第一条记录）
            -- 或者当前记录的通过时间与上一条记录的通过时间间隔大于 5 分钟
            when prev_pass is null 
                or timestampdiff(minute, prev_pass, pass_time) > 5 
            then 1 
            else 0 
        end as is_new_group
    from ordered_passes
)
-- 第三个 CTE，命名为 session_groups
, session_groups as (
    -- 选择 session_flags CTE 中的所有列，并添加一个新列 group_id
    select 
        *,
        -- 使用 SUM 窗口函数，在每个 car_plate 和 direction 分区内，按照 pass_time 排序
        -- 对 is_new_group 列进行累加，得到每个连续通行组的唯一标识 group_id
        sum(is_new_group) over (partition by car_plate, direction order by pass_time) as group_id
    from session_flags
)
-- 第四个 CTE，命名为 aggregated
, aggregated as (
    -- 选择 car_plate、计算每个连续通行组的最小通过时间、最大通过时间和通过次数
    select 
        car_plate,
        min(pass_time) as start_time,
        max(pass_time) as end_time,
        count(*) as num_passes
    from session_groups
    -- 按照 car_plate、direction 和 group_id 进行分组
    group by car_plate, direction, group_id
)
-- 主查询
select 
    car_plate,
    start_time,
    end_time,
    -- 计算平均速度（单位：km/h），假设每次通过间隔 4 公里（(num_passes - 1) * 4）
    -- 用通过间隔距离除以总时间（以小时为单位），并四舍五入到整数
    round((num_passes - 1) * 4 / (timestampdiff(minute, start_time, end_time) / 60.0), 0) as avg_speed_kmh
from aggregated
-- 只选择通过次数大于 1 的记录
where num_passes > 1;