基于日志表获取批处理起止时间：跨天及多Start场景问询

需要从包含LOG_TIME时间戳和STEP_DETAIL的日志表中，捕获批处理的Process Start和Process Finish时间。批处理可能跨天运行，且失败重启会产生多个Process Start步骤。

示例数据 #

期望输出 #

现有尝试（存在跨天问题） #

SELECT 
    TO_DATE(LOG_DATE, 'YYYYMMDD') LOG_DATE, 
    MAX(CASE WHEN STEP_DETAIL = 'Process Start' THEN TO_TIMESTAMP(LOG_DATE) END) AS START_TIME, 
    MAX(CASE WHEN STEP_DETAIL = 'Process Finish' THEN TO_TIMESTAMP(LOG_DATE) END) AS END_TIME 
FROM 
    SAMPLE 
GROUP BY 
    TO_DATE(LOG_DATE, 'YYYYMMDD') 
ORDER BY 
    TO_DATE(LOG_DATE, 'YYYYMMDD') DESC

解决方案 #

核心思路是将每个Process Finish与它之前最后一个Process Start配对，通过窗口函数给每个批次分配唯一分组ID，再在分组内提取有效启动和结束时间。以下是通用SQL实现（适配多数关系型数据库，如PostgreSQL、Oracle、SQL Server）：

WITH batch_groups AS (
    SELECT 
        LOG_TIME,
        STEP_DETAIL,
        -- 累计统计Process Finish的数量，作为批次分组ID
        SUM(CASE WHEN STEP_DETAIL = 'Process Finish' THEN 1 ELSE 0 END) 
            OVER (ORDER BY LOG_TIME ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS batch_id
    FROM SAMPLE
),
batch_summary AS (
    SELECT 
        batch_id,
        -- 取分组内最晚的Process Start（失败重启后的有效启动时间）
        MAX(CASE WHEN STEP_DETAIL = 'Process Start' THEN LOG_TIME END) AS start_time,
        -- 取分组内的Process Finish时间（每个分组仅存在一个）
        MAX(CASE WHEN STEP_DETAIL = 'Process Finish' THEN LOG_TIME END) AS end_time
    FROM batch_groups
    GROUP BY batch_id
)
SELECT 
    TO_CHAR(start_time, 'DD/MM/YYYY') AS "Batch Run Date",
    start_time AS "Start Time",
    end_time AS "End Time"
FROM batch_summary
WHERE start_time IS NOT NULL AND end_time IS NOT NULL -- 过滤未完成的批次
ORDER BY "Batch Run Date";

逻辑说明 #

1. batch_groups CTE：按时间顺序遍历日志，每遇到一条Process Finish记录，就给后续所有记录的批次ID加1。这样每个完整批次会被划分为独立的分组（从上次Finish后的Start到下一个Finish）。
2. batch_summary CTE：在每个批次分组内，取最晚的Process Start作为有效启动时间（覆盖失败重启的旧Start），取唯一的Process Finish作为结束时间。
3. 外层查询：用启动时间的日期作为Batch Run Date，过滤掉未完成的批次（若存在），最终按日期排序输出。

数据库适配提示 #

• 若使用MySQL，将TO_CHAR替换为DATE_FORMAT(start_time, '%d/%m/%Y')，窗口函数语法保持一致（MySQL 8.0+支持）。
• 若使用SQL Server，TO_CHAR替换为FORMAT(start_time, 'dd/MM/yyyy')。

内容的提问来源于stack exchange，提问作者A K