Oracle基于多时间戳计算特定病区通气时长求和逻辑优化咨询

现有逻辑错误原因 #

你当前的统计逻辑确实会导致结果偏低，核心问题是：要求整段通气记录的起止时间完全落在目标病区的最早入科、最晚出科时间范围内，会直接排除所有和病区停留时段部分重叠的通气记录，比如：

• 通气开始在入病区前、结束在病区内
• 通气开始在病区内、结束在出病区后
• 通气跨过多段病区停留记录，只有部分时段重叠
  这些场景下的有效重叠时长都会被完全漏掉，自然统计结果偏低。另外你代码里的两个关联子查询（MIN/MAX取值）属于逐行计算，数据量大时性能也会很差。

正确计算逻辑 #

统计目标病区的通气时长，核心是计算每一条通气记录与同病例目标病区的每一条停留记录的重叠时段，累加所有有效重叠的时长即可：

1. 先判断两个时间段是否重叠：通气记录的开始 < 病区停留的结束 AND 通气记录的结束 > 病区停留的开始
2. 计算重叠时长：有效重叠的开始时间取GREATEST(通气开始时间, 病区停留开始时间)，结束时间取LEAST(通气结束时间, 病区停留结束时间)，二者差值转成分钟即为这段重叠的通气时长
3. 累加所有符合条件的重叠时长得到总时长

优化后的SQL示例 #

@set start_date = '01.01.2018 00:00:00'
@set end_date = '31.08.2021 23:59:59'
@set format = 'DD.MM.YYYY HH24:MI:SS'

SELECT 
    TO_CHAR(f.DISCHARGE_DATE, 'YYYY') AS "Year",
    SUM(
        -- 计算重叠时长转分钟，保留原有业务规则
        DECODE(
            ROUND((LEAST(fb.END, fa.END) - GREATEST(fb.BEGIN, fa.BEGIN)) * 24 * 60, 0),
            20, ROUND(((LEAST(fb.END, fa.END) - GREATEST(fb.BEGIN, fa.BEGIN)) * 24 * 60)/60, 0),
            21, ROUND((LEAST(fb.END, fa.END) - GREATEST(fb.BEGIN, fa.BEGIN)) * 24 * 60, 0),
            ROUND((LEAST(fb.END, fa.END) - GREATEST(fb.BEGIN, fa.BEGIN)) * 24 * 60, 0)
        )
    ) AS "Sum"
FROM 
    CASE_VENTLATION fb
INNER JOIN 
    CASE_STAY fa ON fa.CASEID = fb.CASEID
INNER JOIN 
    CASE f ON f.CASEID = fb.CASEID 
INNER JOIN 
    STATION oe ON oe.STATIONID = fa.STATIONID
WHERE 
    f.STORNO_DATUM IS NULL 
    AND f.DISCHARGE_DATE BETWEEN TO_DATE(:start_date, :format) AND TO_DATE(:end_date, :format)
    AND fa.STATIONID = 10097
    AND f.CASEMARK IN (38140, 38142)
    -- 仅保留存在时间重叠的记录，替换原有低性能MIN/MAX子查询
    AND fb.BEGIN < fa.END
    AND fb.END > fa.BEGIN
GROUP BY TO_CHAR(f.DISCHARGE_DATE, 'YYYY')
ORDER BY "Year"

如果业务要求重叠时长不满1分钟按1分钟计算，或者需要处理通气跨多年统计的场景，只要保留上述重叠判断逻辑即可，不需要额外加子查询，性能比原有写法提升明显。

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