PL/pgSQL进出事件时长计算函数简化及去重实现咨询

优化后完整函数 #

CREATE OR REPLACE FUNCTION public.calculateduration()
RETURNS void
LANGUAGE plpgsql
AS $function$
DECLARE
    v_duration_limit_days INTEGER;
    v_duration_limit_date TIMESTAMP;
    v_max_event_time TIMESTAMP;
BEGIN
    -- 读取配置参数
    SELECT prop_value::INTEGER INTO v_duration_limit_days 
    FROM properties 
    WHERE prop_key = 'DURATION.LIMIT.DAYS';

    SELECT prop_value::TIMESTAMP INTO v_duration_limit_date 
    FROM properties 
    WHERE prop_key = 'DURATION.LIMIT.DATE';

    RAISE NOTICE '当前参数：时长限制天数 %，起始计算日期 %', v_duration_limit_days, v_duration_limit_date;

    -- 配对入场出场并写入durations表
    WITH valid_events AS (
        SELECT 
            id,
            card_nr,
            event_time,
            ticket_type,
            device_type,
            manufacturer,
            carpark_id,
            -- 按卡号、车场分组，按时间排序取相邻事件
            LEAD(id) OVER w AS next_id,
            LEAD(device_type) OVER w AS next_device_type,
            LEAD(event_time) OVER w AS next_event_time
        FROM events e
        WHERE 
            event_time >= v_duration_limit_date
            AND device_type IN (1,2)
            AND event_type = 2
            AND manufacturer LIKE 'DESIGNA_ABACUS%'
            -- 排除已写入过的事件
            AND NOT EXISTS (SELECT 1 FROM durations d WHERE d.event_id_arrival = e.id OR d.event_id_departure = e.id)
        WINDOW w AS (PARTITION BY card_nr, carpark_id ORDER BY event_time ASC)
    ),
    matched_pairs AS (
        SELECT 
            id AS arrival_id,
            next_id AS departure_id,
            event_time AS arrival_time,
            next_event_time AS departure_time,
            card_nr,
            ticket_type,
            manufacturer,
            carpark_id
        FROM valid_events
        WHERE 
            device_type = 1 
            AND next_device_type = 2
    )
    INSERT INTO durations (
        odb_created_at, event_id_arrival, event_id_departure, 
        event_time_arrival, event_time_departure, card_nr, 
        ticket_type, duration, manufacturer, carpark_id
    )
    SELECT 
        CURRENT_TIMESTAMP,
        arrival_id,
        departure_id,
        arrival_time,
        departure_time,
        card_nr,
        ticket_type,
        EXTRACT(EPOCH FROM (departure_time - arrival_time))::INTEGER,
        manufacturer,
        carpark_id
    FROM matched_pairs;

    -- 更新配置表的limit date
    SELECT MAX(event_time) INTO v_max_event_time 
    FROM events 
    WHERE event_time >= v_duration_limit_date;

    -- 处理无符合条件事件的场景，避免更新为空
    IF v_max_event_time IS NOT NULL THEN
        v_duration_limit_date := v_max_event_time - (v_duration_limit_days || ' days')::INTERVAL;
        UPDATE properties 
        SET prop_value = v_duration_limit_date::VARCHAR
        WHERE prop_key = 'DURATION.LIMIT.DATE';
        RAISE NOTICE '已更新DURATION.LIMIT.DATE为：%', v_duration_limit_date;
    END IF;
END;
$function$;

问题解决说明 #

• 重复匹配问题修复：使用窗口函数按card_nr、carpark_id分组排序后，仅匹配相邻的入场、出场事件，完全避免了一个入场匹配多个出场的重复问题，同时兼容原函数遇到连续入场时取最新入场的逻辑。
• 性能优化：完全去掉了原函数的游标遍历逻辑和动态SQL拼接，改为批量SQL执行，性能提升非常明显，同时避免了SQL注入风险。
• 去重逻辑保留：查询时直接过滤已存在于durations表的事件ID，完全符合避免重复写入的要求。
• 配置更新逻辑完善：增加了空值判断，避免无新事件时更新配置为NULL的异常，严格遵循durationLimitDate = Max(event_time) - durationLimitDays的规则。

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