如何用Oracle SQL实现多用户基于10分钟间隔的工作时长统计

我有一个Oracle数据库，用于存储用户每次操作的记录，希望统计每位用户的工作时长。统计规则如下：只要某个10分钟时段内存在用户操作，该时段即被计入"工作时间"。

例如，若用户在10:05和10:25各进行一次操作，则统计其工作时段为10:00-10:10和10:20-10:30，总计20分钟。

我需要编写一个SQL查询，按照上述逻辑分用户统计总工作时长。我曾实现了单用户场景下的预期结果，但当尝试适配多用户时，查询会无限运行。以下是适用于单用户的查询代码：

WITH database AS(
SELECT
    operation_date,
    user
FROM
    database
where 
    user = 'user1'
)
    ,start_date as (
  select 
  min (operation_date ) AS start_date,
         10 / 1440 AS time_interval
  from   database
)
, intervals as (
  select 
  
  start_date + ( ( level - 1 ) * time_interval ) start_date,
         start_date + ( ( level ) * time_interval ) end_date
  from   start_date
  connect by level <= 144
)

,timesum as(
  select
     user,
     to_char(start_date,'dd/mm/yyyy hh24:mi'),
     count ( operation_date ) as oper_num
  from   intervals
  left  join database
  on     start_date <= operation_date
  and    operation_date < end_date
  group  by user, start_date
  order  by start_date
  )
  
  select
  user,
  (count(oper_num)*10)/60 as hours
  from timesum
  where
  oper_num > 0
  group by user

请问如何修改以适配多用户场景？

原查询在多用户场景下无限运行的核心原因是connect by未针对用户做隔离，导致生成时间区间时出现无限递归。以下是适配多用户的修改方案：

修改后的SQL #

WITH user_time_ranges AS (
    -- 按用户分组，获取每个用户的最早操作时间和10分钟时间间隔
    SELECT 
        user_id,
        MIN(operation_date) AS user_min_date,
        10/1440 AS time_interval
    FROM 
        operation_records  -- 替换为你的实际表名，避免与关键字重名
    GROUP BY 
        user_id
),
user_intervals AS (
    -- 为每个用户独立生成其时间范围内的所有10分钟区间
    SELECT 
        utr.user_id,
        utr.user_min_date + ((level - 1) * utr.time_interval) AS interval_start,
        utr.user_min_date + (level * utr.time_interval) AS interval_end
    FROM 
        user_time_ranges utr
    CONNECT BY 
        level <= 144  -- 一天最多包含144个10分钟区间
        AND PRIOR utr.user_id = utr.user_id  -- 确保递归仅在同一用户维度内进行
        AND PRIOR sys_guid() IS NOT NULL  -- 防止Oracle优化导致的无限递归
),
user_interval_ops AS (
    -- 关联操作记录，统计每个用户的每个时间区间是否有操作
    SELECT 
        ui.user_id,
        COUNT(or_rec.operation_date) AS oper_num
    FROM 
        user_intervals ui
    LEFT JOIN 
        operation_records or_rec
        ON or_rec.user_id = ui.user_id
        AND or_rec.operation_date >= ui.interval_start
        AND or_rec.operation_date < ui.interval_end
    GROUP BY 
        ui.user_id, ui.interval_start
)
-- 最终统计每个用户的有效工作时长
SELECT 
    user_id,
    (COUNT(CASE WHEN oper_num > 0 THEN 1 END) * 10)/60 AS total_work_hours
FROM 
    user_interval_ops
GROUP BY 
    user_id;

关键修改点说明 #

• 用户维度的区间生成：先按用户分组获取各自最早操作时间，再为每个用户独立生成时间区间，避免跨用户的无效区间。
• CONNECT BY的隔离处理：添加PRIOR utr.user_id = utr.user_id确保递归仅在同一用户内执行，PRIOR sys_guid() IS NOT NULL规避Oracle优化引发的无限递归问题。
• 表名与别名优化：避免使用database这类关键字作为表/别名，替换为更清晰的名称如operation_records，减少语法冲突。
• 关联逻辑优化：关联操作记录时增加用户ID匹配，确保每个用户的操作仅与自身时间区间关联，避免笛卡尔积导致的性能损耗。

内容的提问来源于stack exchange，提问作者Gustavo Cazarotto