SQLite基于自动化流程日志表创建样本加载至处理时长视图

可以通过关联子查询或者窗口函数实现需求，这里提供两种适配SQLite的方案：

方案1：关联子查询（兼容性更强） #

这种方式不依赖高版本SQLite的窗口函数，适用于所有支持子查询的SQLite版本：

CREATE VIEW IF NOT EXISTS sample_processing_view AS
SELECT
    pe.sampleID,
    pe.carrierID,
    le.timestamp AS timestamp_loaded,
    le.nodeID AS nodeID_loaded,
    pe.timestamp AS timestamp_processed,
    pe.nodeID AS nodeID_processed,
    -- 计算时长，单位为秒（可根据需求调整）
    ROUND((JULIANDAY(pe.timestamp) - JULIANDAY(le.timestamp)) * 86400, 3) AS duration
FROM
    automationProcessLog pe
-- 关联最近的加载事件
JOIN automationProcessLog le ON
    le.sampleID = pe.sampleID
    AND le.carrierID = pe.carrierID
    AND le.IOnode = 'True'
    AND le.processStep = 'Processed'
    AND le.timestamp < pe.timestamp
WHERE
    -- 筛选非加载事件
    NOT (pe.IOnode = 'True' AND pe.processStep = 'Processed')
-- 确保只匹配每个非加载事件对应的最近一次加载事件
GROUP BY
    pe.id, pe.sampleID, pe.carrierID, pe.timestamp, pe.nodeID
HAVING
    le.timestamp = MAX(le.timestamp);

方案2：窗口函数（SQLite 3.25+） #

如果你的SQLite版本在3.25及以上，窗口函数的方式性能更优，逻辑更清晰：

CREATE VIEW IF NOT EXISTS sample_processing_view AS
WITH event_groups AS (
    SELECT
        *,
        -- 为每个样本+载体分组，标记每个非加载事件对应的最近加载事件的时间戳
        MAX(CASE WHEN IOnode = 'True' AND processStep = 'Processed' THEN timestamp END) 
            OVER (PARTITION BY sampleID, carrierID ORDER BY timestamp ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS latest_loaded_ts
    FROM automationProcessLog
)
SELECT
    eg.sampleID,
    eg.carrierID,
    le.timestamp AS timestamp_loaded,
    le.nodeID AS nodeID_loaded,
    eg.timestamp AS timestamp_processed,
    eg.nodeID AS nodeID_processed,
    ROUND((JULIANDAY(eg.timestamp) - JULIANDAY(le.timestamp)) * 86400, 3) AS duration
FROM event_groups eg
JOIN automationProcessLog le ON
    le.sampleID = eg.sampleID
    AND le.carrierID = eg.carrierID
    AND le.timestamp = eg.latest_loaded_ts
    AND le.IOnode = 'True'
    AND le.processStep = 'Processed'
WHERE
    NOT (eg.IOnode = 'True' AND eg.processStep = 'Processed');

关键说明： #

• 时间差计算：使用SQLite内置的JULIANDAY()函数将时间戳转换为儒略日，乘以86400转换为秒（1天=86400秒），ROUND()用于保留小数位
• 匹配逻辑：两种方案都确保了每个非加载事件匹配同一样本+载体下、时间戳更早的最近一次加载事件，符合样本卸载后重新加载时从最新加载时间计算时长的要求
• 视图创建：使用CREATE VIEW IF NOT EXISTS避免重复创建视图的报错

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