如何用Oracle SQL按每日9:00拆分带时长的时间戳数据

可以通过**递归CTE（公共表表达式）**实现按每日9:00拆分状态记录，自动处理跨多个9点的长时长情况，以下是具体SQL实现：

WITH recursive_data AS (
    -- 初始数据：计算每条记录的结束时间和后续第一个9点
    SELECT 
        timestamp_col AS start_ts,
        state,
        timestamp_col + NUMTODSINTERVAL(duration_min, 'MINUTE') AS end_ts,
        -- 判断当前时间对应的下一个9点：当天9点（若当前时间早于9点）或次日9点（若当前时间晚于等于9点）
        CASE 
            WHEN EXTRACT(HOUR FROM timestamp_col) >= 9 
            THEN TRUNC(timestamp_col) + INTERVAL '1' DAY + INTERVAL '9' HOUR
            ELSE TRUNC(timestamp_col) + INTERVAL '9' HOUR
        END AS next_9am
    FROM status_log -- 替换为你的表名
    UNION ALL
    -- 递归生成跨9点的分段记录
    SELECT 
        next_9am AS start_ts,
        state,
        end_ts,
        next_9am + INTERVAL '1' DAY AS next_9am
    FROM recursive_data
    WHERE next_9am < end_ts -- 直到下一个9点超过记录结束时间
)
-- 输出最终拆分后的记录，计算每段的时长（分钟）
SELECT 
    start_ts AS "Timestamp",
    state AS "State",
    EXTRACT(DAY FROM (LEAST(end_ts, next_9am) - start_ts)) * 1440 +
    EXTRACT(HOUR FROM (LEAST(end_ts, next_9am) - start_ts)) * 60 +
    EXTRACT(MINUTE FROM (LEAST(end_ts, next_9am) - start_ts)) AS "Duration(minutes)"
FROM recursive_data
ORDER BY start_ts;

关键逻辑说明 #

1. 初始数据处理：

   • 计算每条记录的实际结束时间：timestamp_col + NUMTODSINTERVAL(duration_min, 'MINUTE')
   • 确定当前记录起始时间后的第一个9点：若当前时间早于9点则取当天9点，否则取次日9点

2. 递归生成分段：

   • 若当前分段的下一个9点早于记录结束时间，就生成以该9点为起始的新分段，直到所有跨9点的情况都被拆分

3. 时长计算：

   • 每段的时长取「当前分段起始时间到下一个9点」和「当前分段起始时间到原记录结束时间」的较小值，转换为分钟数输出

适配你的表结构 #

• 若Timestamp字段是字符串类型而非TIMESTAMP，需先转换为时间类型，例如：

  TO_TIMESTAMP(timestamp_str, 'MM/DD/YYYY HH:MI:SS AM') AS timestamp_col
  

• 替换SQL中的status_log为你的实际表名，timestamp_col、state、duration_min为对应字段名

内容的提问来源于stack exchange，提问作者Sea Stormy